September 29, 2009 at 8:33 am
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
September 29, 2009 at 9:27 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 29, 2009 at 9:35 am
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?
September 29, 2009 at 11:01 am
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
September 29, 2009 at 11:33 am
Curious am I as to your question.
September 29, 2009 at 6:38 pm
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
September 29, 2009 at 6:56 pm
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 30, 2009 at 1:07 am
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
September 30, 2009 at 1:21 am
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]
September 30, 2009 at 5:41 am
Can you post table definitions and some sample data? Is CalendarDate a datetime/smalldatetime?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 30, 2009 at 7:51 am
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
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 30, 2009 at 8:20 am
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
September 30, 2009 at 8:30 am
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?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 30, 2009 at 8:56 am
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
September 30, 2009 at 1:05 pm
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