sz1
Aged Yak Warrior
555 Posts |
Posted - 2013-01-23 : 05:21:52
|
HiI'm trying to add a statement to this query that will return results 1 week earlier along with the actual results, can I do this in a case statement? I want to be able to select the field for a report. Do I need to use the Occurred_DT or can I use the DateDiff on its own to calculate a week back? does the DateDiff use the system date if not a field date in that case? case in red below. When I run this the Open Days Last Week returns the same results as Open Days...so its not looking back a week for Open Days Last Week, I want to calc on anything older than week and not include the last week.Thanksselect distinct [ID],c.DESC_SHORT,c.STATUS_NAME,c.OCCURED_DT AS "Open Date", DATEDIFF(d, c.OCCURED_DT, GETDATE()) AS "Open Days",AVG(DATEDIFF(d, c.OCCURED_DT, GETDATE())) AS "Day Average",c.ASSIGNED_GRP_NAME,c.ASSIGNED_REP_NAME, c.PRIORITY_NAME,TYPE, [SYMPTOM], [CONTACT_FIRST_NAME] + ' ' + [CONTACT_LAST_NAME] AS "Contact Name",INTI_CATEGORY,Case When c.Occured_DT < DATEADD(dd, -7, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()-1), 0)) Then DATEDIFF(d, c.OCCURED_DT, GETDATE()) End AS "Open Days Last Week"from DIM_CALL cwhere c.OPEN_FLAG = 1 and c.ETL_CURRENT =1AND TYPE ='Incident'AND STATUS_NAME Not In ('Resolved','Resolved Change','Transfer to Problem')AND [ID] <> 5193--and c.ASSIGNED_REP_NAME in (@User)Group By[ID],c.DESC_SHORT,c.STATUS_NAME,c.OCCURED_DT,c.ASSIGNED_GRP_NAME,c.ASSIGNED_REP_NAME,c.PRIORITY_NAME,TYPE,SYMPTOM,CONTACT_FIRST_NAME,CONTACT_LAST_NAME,INTI_CATEGORYOrder by "Open Days" DESC, OCCURED_DTSZ1Learning and development is the driving force in the universe...! |
|