Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2010
    Posts
    3

    Unanswered: Pretty Hard SQL Statemant

    Hey Guys i need some help with a very hard sql query.

    at first here is how my table looks like:

    Code:
    incident | location | date
    1234        GER         25-01-2010
    3453        GER         24-01-2010
    4321        US          24-01-2010
    4251        US          16-01-2010
    2153        GER         14-01-2010
    etc

    there are more locations but for this example 2 are enough.

    I need a qry that gives me this as result:

    Code:
    DAY      |     GER        |     US
    14               1                  
    16                             1
    24               1             1
    25               1

    tryed around for a few days now but can't get it work,
    if someone has a idea i would be very happy to hear.


    Greetings,

    Nova


    PS: this is what i got so far but its now working:

    Code:
    select  
    	convert(varchar(50),datepart(dd, workitem_created_on), 120) as 'Day',
    
    	( 
    SELECT  convert(varchar(50),datepart(dd, workitem_created_on), 120) as '_Parameter', count(*) as 'Number of Calls'                   
    FROM dbo.HD_workitem_current_view                  
    WHERE workitem_status_lookup_id IN (600, 700)      
    AND (contact_location = N'BK')     
    AND (datepart(mm, workitem_created_on) = datepart(mm, getdate()))     
    GROUP BY   count(*) 
    	 )
    	 as 'BK'
    
    	,
    
    	
    	( 
    SELECT  convert(varchar(50),datepart(dd, workitem_created_on), 120) as '_Parameter', count(*) as 'Number of Calls'                   
    FROM dbo.HD_workitem_current_view                  
    WHERE workitem_status_lookup_id IN (600, 700)      
    AND (contact_location = N'SR')     
    AND (datepart(mm, workitem_created_on) = datepart(mm, getdate()))   
    GROUP BY   count(*) 
    	 )
    	 as 'SR'
    
    FROM dbo.HD_workitem_current_view
    	WHERE workitem_status_lookup_id IN (600, 700)  AND (datepart(mm, workitem_created_on) = datepart(mm, getdate()))  
    	GROUP BY   datepart(dd, workitem_created_on)
    Last edited by OneNonlyNova; 01-26-10 at 05:24.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT [day]
         , SUM(ger) AS ger
         , SUM(us) AS us
      FROM ( SELECT DAY(date) AS [day]
                  , CASE WHEN location = 'GER'
                         THEN 1 ELSE NULL END) AS ger
                  , CASE WHEN location = 'US'
                         THEN 1 ELSE NULL END) AS us
               FROM daTable ) AS d
    GROUP
        BY [day]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2010
    Posts
    3
    hey r937 thanks for the fast reply
    the sql looks nice but i get:
    "Error in list of function arguments: 'CASE' not recognized.
    Unable to parse query text."

    when trying to execute:

    [CODE]SELECT [day]
    , SUM(BK) AS BK
    , SUM(SR) AS SR
    FROM ( SELECT DAY(workitem_created_on) AS [day]
    , CASE WHEN contact_location = 'BK'
    THEN 1 ELSE NULL END) AS BK
    , CASE WHEN contact_location = 'SR'
    THEN 1 ELSE NULL END) AS SR
    FROM dbo.HD_workitem_current_view ) AS d
    GROUP
    BY [day]CODE]

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my apologies, i had a couple of typos

    try this --
    Code:
    SELECT [day]
         , SUM(bk) AS bk
         , SUM(sr) AS sr
      FROM ( SELECT DAY(workitem_created_on) AS [day]
                  , CASE WHEN contact_location = 'BK'
                         THEN 1 ELSE NULL END  AS bk
                  , CASE WHEN contact_location = 'SR'
                         THEN 1 ELSE NULL END  AS sr
               FROM dbo.HD_workitem_current_view ) AS d
    GROUP
        BY [day]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2010
    Posts
    3
    nothing to apologie you are the best ^^ hell thanks so much

    Code:
    SELECT [day]
         , SUM(bk) AS bk
         , SUM(sr) AS sr
      FROM ( SELECT DAY(workitem_created_on) AS [day]
                  , CASE WHEN contact_location = 'BK'
                         THEN 1 ELSE NULL END  AS bk
                  , CASE WHEN contact_location = 'SR'
                         THEN 1 ELSE NULL END  AS sr
               FROM dbo.HD_workitem_current_view WHERE (datepart(mm, workitem_created_on) = datepart(mm, getdate()))    ) AS d
    GROUP
        BY [day]
    ORDER BY [day]
    works perfekt with one exeption, sometimes a day is left out
    not filled with NULL at the locations but not even in the list
    for example the 2nd jan is not in the current list?! any
    idea where this comes from?

    again very much thanks for this r937

    greetings

    Nova

    EDIT: got it working, i just converted the date to a string that fixed it dunno why...

    just a last little question, is it possible to do the same without selecting all rows?
    cause i have to select a lot of locations and our sql server is set to a max of
    75000 rows so currently i am not able to execute due to that restriction.

    greetings

    nova
    Last edited by OneNonlyNova; 01-26-10 at 08:34.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The nested SELECT is unnecessary:
    Code:
    SELECT	DAY(workitem_created_on) AS [day],
    	sum(CASE WHEN contact_location = 'BK' THEN 1 ELSE NULL END) AS bk,
    	sum(CASE WHEN contact_location = 'SR' THEN 1 ELSE NULL END) AS sr
    FROM	dbo.HD_workitem_current_view
    group by DAY(workitem_created_on)
    And you can use TOP N to limit the number of rows returned.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •