September 30, 2009 at 2:19 pm
I have another question regarding CalendarDate and closed date, which table or view do they come from in the query. Neither of these columns have a table alias attached.
September 30, 2009 at 2:42 pm
in your join criteria...
ON CONVERT(VARCHAR(11),date_closed,120) = CONVERT(VARCHAR(11),[CalendarDate],120)
If you are joining on dates (date_closed to CalendarDate), they will always be the same.
So all your case staements are effectively =
DAY - wouldn't you want a case statement based on the DATEPART of the GETDATE() function vs. the date_closed?
MTD - evaluate the month of date_closed vs. the month of GETDATE()?
YTD - evaluate the year of date_closed vs. the year of GETDATE()?
Sending some of the sample schema and sample data would likely make this much easier for everyone to help solve.
Greg E
October 1, 2009 at 11:49 am
Greg Edwards-268690 (9/30/2009)
in your join criteria...ON CONVERT(VARCHAR(11),date_closed,120) = CONVERT(VARCHAR(11),[CalendarDate],120)
If you are joining on dates (date_closed to CalendarDate), they will always be the same.
So all your case staements are effectively =
DAY - wouldn't you want a case statement based on the DATEPART of the GETDATE() function vs. the date_closed?
MTD - evaluate the month of date_closed vs. the month of GETDATE()?
YTD - evaluate the year of date_closed vs. the year of GETDATE()?
Sending some of the sample schema and sample data would likely make this much easier for everyone to help solve.
Greg E
So you are suggesting something like this instead?
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, getdate()), 0)
and date_closed < dateadd(yy, datediff(yy, 0, getdate()) + 1, 0) then 1 else 0 end) as YTD
,sum(case when date_closed >= dateadd(mm, datediff(mm, 0, getdate()), 0)
and date_closed < dateadd(mm, datediff(mm, 0, getdate()) + 1, 0) then 1 else 0 end) as MTD
,sum(case when date_closed >= dateadd(dd, datediff(dd, 0, getdate()), 0)
and date_closed < dateadd(dd, datediff(dd, 0, getdate()) + 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
;
Could be, we'll have to see if the OP agrees.
October 1, 2009 at 3:11 pm
Something along these lines...
sum(case when DATEPART(yy,date_closed) = DATEPART(yy,getdate()) then 1 else 0 end) as 'YTD',
sum(case when DATEPART(mm,date_closed) = DATEPART(mm,getdate()) then 1 else 0 end) as 'MTD',
sum(case when DATEPART(dd,date_closed) = DATEPART(dd,getdate()) then 1 else 0 end) as 'Today',
Comparing the datepart of the dd, mm, and yy of the date_closed to current date.
My take on the orginal is they were comparing date_closed to calendardate.
These would have always been the same as they were joined =
So you would see all the totals being the same.
But without the OP giving additional info, we are just guessing.
Greg E
October 1, 2009 at 7:06 pm
Greg Edwards-268690 (10/1/2009)
Something along these lines...sum(case when DATEPART(yy,date_closed) = DATEPART(yy,getdate()) then 1 else 0 end) as 'YTD',
sum(case when DATEPART(mm,date_closed) = DATEPART(mm,getdate()) then 1 else 0 end) as 'MTD',
sum(case when DATEPART(dd,date_closed) = DATEPART(dd,getdate()) then 1 else 0 end) as 'Today',
Comparing the datepart of the dd, mm, and yy of the date_closed to current date.
My take on the orginal is they were comparing date_closed to calendardate.
These would have always been the same as they were joined =
So you would see all the totals being the same.
But without the OP giving additional info, we are just guessing.
Greg E
Slight problem with your code possibly:
sum(case when DATEPART(yy,date_closed) = DATEPART(yy,getdate()) then 1 else 0 end) as 'YTD',
^-- This should be okay
sum(case when DATEPART(mm,date_closed) = DATEPART(mm,getdate()) then 1 else 0 end) as 'MTD',
^-- This one will sum multiple years with the same month
sum(case when DATEPART(dd,date_closed) = DATEPART(dd,getdate()) then 1 else 0 end)
^-- This one will sum multiple months and years with the same day
October 2, 2009 at 6:14 am
You are correct.
When I've done this, it's with a query that starts at the beggining of the year.
So I don't need to quallify with the year too.
Greg E
October 2, 2009 at 6:48 am
Greg Edwards-268690 (10/2/2009)
You are correct.When I've done this, it's with a query that starts at the beggining of the year.
So I don't need to quallify with the year too.
Greg E
That would fix the MTD, but what about DAY (the third summation)?
October 2, 2009 at 6:59 am
Compare yy, mm, dd of date_closed to getdate()
Wonder if the OP is even looking anymore?
Interesting to hear if they think their problem is solved.
Greg E
October 2, 2009 at 7:55 am
It is still not working especial the DTD part,
Basically my initial query was using the getdate() function and for the DTD I was using datepart(dd,getdate())-1 to get the previous day info as we are working with the previous day data
October 2, 2009 at 8:08 am
Posting schema and sample data, along with expected results will help out a lot.
Sounds like you want Prior Day, MTD, and YTD.
Although you also have to be careful with MTD and YTD too - you probably want a cutoff to exclude today.
Greg E
October 2, 2009 at 8:14 am
It is still not working especial the DTD part,
Basically my initial query was using the getdate() function and for the DTD I was using datepart(dd,getdate())-1 to get the previous day info as we are working with the previous day data
October 2, 2009 at 8:23 am
If you would like some helpful help give us what we have asked for. Read the first article I reference in my signature block. Follow the instructions in that article and you will get the help you are expecting, plus TESTED code as well.
October 2, 2009 at 9:38 am
Here is my code with a slight change to hopefully account for Previous Day.
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, getdate()), 0)
and date_closed < dateadd(yy, datediff(yy, 0, getdate()) + 1, 0) then 1 else 0 end) as YTD
,sum(case when date_closed >= dateadd(mm, datediff(mm, 0, getdate()), 0)
and date_closed < dateadd(mm, datediff(mm, 0, getdate()) + 1, 0) then 1 else 0 end) as MTD
,sum(case when date_closed >= dateadd(dd, datediff(dd, 0, getdate()) - 1, 0)
and date_closed < dateadd(dd, datediff(dd, 0, getdate()), 0) then 1 else 0 end) as PreviousDay
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
AND date_closed < dateadd(dd, datediff(dd, 0, getdate()), 0)
GROUP BY
C.[IPC_SITE]
,C.[VALUE_STREAM]
,C.
,C.[CAPABILITY]
,D.[USER_NAME]
,[CalendarDate]
ORDER BY
CalendarDate
;
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply