Nested Case statement

  • SELECT

    'OPEN' as [AREA_DESCRIPTION]

    ,'OPEN' as [METRIC_DESCRIPTION]

    ,C.[IPC_SITE]

    ,C.[VALUE_STREAM]

    ,C.

    ,C.[CAPABILITY]

    ,D.[USER_NAME]

    ,CONVERT(VARCHAR(11),[CalendarDate], 120) as DATE

    ,sum(case when (datepart(YEAR, date_closed) = datepart(YEAR,CONVERT(VARCHAR(11),[CalendarDate],120))) then 1 else 0 end) as [YTD]

    ,sum(case when (datepart(MONTH, date_closed) = datepart(MONTH,CONVERT(VARCHAR(11),[CalendarDate],120)))

    AND (datepart(YEAR, date_closed) = datepart(YEAR,CONVERT(VARCHAR(11),[CalendarDate],120))) then 1 else 0 end) as [MTD]

    ,sum (case when (datepart(DAY, date_closed) = datepart(DAY,CONVERT(VARCHAR(11),[CalendarDate],120)))

    AND (datepart(MONTH, date_closed)=datepart(MONTH,CONVERT(VARCHAR(11),[CalendarDate],120)))

    AND (datepart(YEAR, date_closed) = datepart(YEAR,CONVERT(VARCHAR(11),[CalendarDate],120)))then 1 else 0 end) as [DAY]

    FROM [Gijima].[dbo].[vw_GIJIMA_QUERY] C

    LEFT JOIN [Gijima].[dbo].[IPC_Names] D ON C.Closed_by = D.[user_name]

    LEFT JOIN [Lean_Engagement].[dbo].[Mart.TD_Date] E

    ON CONVERT(VARCHAR(11),date_closed,120) = CONVERT(VARCHAR(11),[CalendarDate],120)

    WHERE [2_Days_Out_Of_SLA] = 1

    GROUP BY

    C.[IPC_SITE]

    ,C.[VALUE_STREAM]

    ,C.

    ,C.[CAPABILITY]

    ,D.[USER_NAME]

    ,[CalendarDate]

    ORDER BY DATE

  • And your question is? I don't see a nested case in your code. If your question is "Is it possible?" then the answer is Yes.

  • Need more information. What precisely is your question? What does that query return?

    Also, is there a reason you're converting dates to varchars in your case statements?

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Maybe. It all depends.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Curious am I as to your question.

  • Somehow the text of the original question doesn't show up in the main post, but I was able to copy it from the annotation seen in the topic in the forum list. It says:

    I'm trying to calculate MTD, YTD, DATE using the Case statement but the results I'm getting for MTD and YTD is incorrect, It is picking the date figure, hence, All three of my calculation are the same. Please assist

    I still don't quite follow what the problem is, but perhaps someone else can.

    edit: the quoted text wass apparently entered in the SUBJECT box of the original post

  • john.arnott (9/29/2009)


    Somehow the text of the original question doesn't show up in the main post, but I was able to copy it from the annotation seen when hovering the mouse over the topic in the forum list. It says:

    I'm trying to calculate MTD, YTD, DATE using the Case statement but the results I'm getting for MTD and YTD is incorrect, It is picking the date figure, hence, All three of my calculation are the same. Please assist

    I still don't quite follow what the problem is, but perhaps someone else can.

    I think this blog post I wrote a while ago may help with this issue as it deals with aggregation by date using case.

  • The problem with my query is that the AND on the case statement seems not to be working as supposed, most of the example that I saw does not include the AND to combine conditions,

    Please assist as how can I combine two or three conditions on the CASE Statement

  • pitso.maceke (9/30/2009)


    The problem with my query is that the AND on the case statement seems not to be working as supposed, most of the example that I saw does not include the AND to combine conditions,

    Please assist as how can I combine two or three conditions on the CASE Statement

    hmm hard to do, lol

    ,sum(case when (datepart(MONTH, date_closed) = datepart(MONTH,CONVERT(VARCHAR(11),[CalendarDate],120))

    AND (datepart(YEAR, date_closed) = datepart(YEAR,CONVERT(VARCHAR(11),[CalendarDate],120)))) then 1 else 0 end) as [MTD]

    ,sum (case when (datepart(DAY, date_closed) = datepart(DAY,CONVERT(VARCHAR(11),[CalendarDate],120))

    AND (datepart(MONTH, date_closed)=datepart(MONTH,CONVERT(VARCHAR(11),[CalendarDate],120))

    AND (datepart(YEAR, date_closed) = datepart(YEAR,CONVERT(VARCHAR(11),[CalendarDate],120))then 1 else 0 end) as [DAY]

    brackets might not be exactly right in my try above but here is an example of what you want

    select case when 1 = 1 then 2 else 0 end, case when (1 = 1 and (2-1)=1) then 3 else 4 end

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • Can you post table definitions and some sample data? Is CalendarDate a datetime/smalldatetime?

  • What tables do CalendarDate and date_closed belong to? It really would be much simpler to provide an answer if we had table definitions, some sample data, and expected results (see the top link in my signature).

    Even without the added information here's an idea that might help and that I think simplifies the query:

    SELECT

    'OPEN' as [AREA_DESCRIPTION],

    'OPEN' as [METRIC_DESCRIPTION],

    C.[IPC_SITE],

    C.[VALUE_STREAM],

    C.

    ,

    C.[CAPABILITY],

    D.[USER_NAME],

    CONVERT(VARCHAR(11), [CalendarDate], 120) as DATE,

    -- the dateadd(datediff()) returns the start of the year

    sum(case when date_closed >= dateadd(yy, datediff(yy, 0, [CalendarDate]), 0) then 1

    else 0

    end) as [YTD],

    -- the dateadd(datediff()) returns the start of the month

    sum(case when date_closed >= dateadd(mm, datediff(mm, 0, [CalendarDate]), 0) AND

    -- by adding 1 to the datediff calc you get the start of the next month

    date_closed < dateadd(mm, datediff(mm, 0, [CalendarDate])+1, 0)then 1

    else 0

    end) as [MTD],

    -- the dateadd(datediff()) returns the start of the day

    sum(case when date_closed >= dateadd(dd, datediff(dd, 0, [CalendarDate]), 0) AND

    -- by adding 1 to the datediff calc you get the start of the next day

    date_closed < dateadd(dd, datediff(dd, 0, [CalendarDate])+1, 0) then 1

    else 0

    end) as [DAY]

    FROM

    [Gijima].[dbo].[vw_GIJIMA_QUERY] C LEFT JOIN

    [Gijima].[dbo].[IPC_Names] D

    ON C.Closed_by = D.[user_name] LEFT JOIN

    [Lean_Engagement].[dbo].[Mart.TD_Date] E

    ON CONVERT(VARCHAR(11), date_closed, 120) = CONVERT(VARCHAR(11), [CalendarDate], 120)

    WHERE

    [2_Days_Out_Of_SLA] = 1

    GROUP BY

    C.[IPC_SITE],

    C.[VALUE_STREAM],

    C.

    ,

    C.[CAPABILITY],

    D.[USER_NAME],

    [CalendarDate]

    ORDER BY

    DATE

    I have been using the DateAdd(DateDiff()) routine to get start of year/month/day ever since I found them on Lynn Pettis' blog here

  • Your blog post is too good but is not helping in my problem, can you give me another example with two or more conditions before then

    Just to reply to same of the questions:Calendardate is in datetime format (00:00 000) and date_closed is in actual datetime format

  • Jack Corbett (9/30/2009)


    What tables do CalendarDate and date_closed belong to? It really would be much simpler to provide an answer if we had table definitions, some sample data, and expected results (see the top link in my signature).

    Again, if you do this you will likely get a working solution. Without the information you have been asked to provide, there isn't much more anyone can do.

    The only reason I would guess that your code may not be working is because you are converting a date (CalendarDate) to varchar and then using a date function which is forcing a implicit conversion back to a date and something could be getting screwed up there.

    Have you tried any of the solutions that have been provided? What have you done to try to troubleshoot the problem?

  • HI, Thank you very much for the time you spent trying to assist me, but the results I'm getting when using the datediff is excatly the same as what I'm getting with my query problem.

    Will have to to get the table format

  • Having looked at your original code and seeing the CalendarDate always ends in 00:00:00.000 (ie 2009-09-30 00:00:00.000) and closed_date ends with a time (ie 2009-09-30 13:03:01.333), the following is how I would rewrite your code.

    SELECT

    'OPEN' as [AREA_DESCRIPTION]

    ,'OPEN' as [METRIC_DESCRIPTION]

    ,C.[IPC_SITE]

    ,C.[VALUE_STREAM]

    ,C.

    ,C.[CAPABILITY]

    ,D.[USER_NAME]

    ,CONVERT(VARCHAR(11),[CalendarDate], 120) as DATE

    ,sum(case when date_closed >= dateadd(yy, datediff(yy, 0, CalendarDate), 0)

    and date_closed < dateadd(yy, datediff(yy, 0, CalendarDate) + 1, 0) then 1 else 0 end) as YTD

    ,sum(case when date_closed >= dateadd(mm, datediff(mm, 0, CalendarDate), 0)

    and date_closed < dateadd(mm, datediff(mm, 0, CalendarDate) + 1, 0) then 1 else 0 end) as MTD

    ,sum(case when date_closed >= dateadd(dd, datediff(dd, 0, CalendarDate), 0)

    and date_closed < dateadd(dd, datediff(dd, 0, CalendarDate) + 1, 0) then 1 else 0 end) as DTD

    FROM

    [Gijima].[dbo].[vw_GIJIMA_QUERY] C

    LEFT JOIN [Gijima].[dbo].[IPC_Names] D

    ON C.Closed_by = D.[user_name]

    LEFT JOIN [Lean_Engagement].[dbo].[Mart.TD_Date] E

    ON date_closed >= dateadd(dd, datediff(dd, 0, CalendarDate), 0) and date_closed < dateadd(dd, datediff(dd, 0, CalendarDate) + 1, 0)

    WHERE

    [2_Days_Out_Of_SLA] = 1

    GROUP BY

    C.[IPC_SITE]

    ,C.[VALUE_STREAM]

    ,C.

    ,C.[CAPABILITY]

    ,D.[USER_NAME]

    ,[CalendarDate]

    ORDER BY

    CalendarDate

    ;

    Unfortunately this code is untested as I don't have your tables or any test data.

    Edit: And now that I look at Jack's code, it really looks the same. It would really help us if you could provide the DDL for the tables, sample data, and expected results based on the sample data.

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply