Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Return Last Week Stats

Author  Topic 

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-01-23 : 05:21:52
Hi

I'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.
Thanks


select 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 c
where c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
AND 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_CATEGORY

Order by "Open Days" DESC, OCCURED_DT

SZ1
Learning and development is the driving force in the universe...!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 07:23:04
i think what you need is correlated subquery based on datefield c.Occured_DT to get last week data

see scenario 2 for similar correlated query (you need to use condition based on date instead of equality condition used there)

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-01-23 : 07:50:21
You mean like aggregate all previous week data and subtract that from the remainder...?I will take a look at your query.
Thanks

SZ1
Learning and development is the driving force in the universe...!
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-01-23 : 07:56:14
mmm I've looked but think I would have problems trying to build that into the current query...?

SZ1
Learning and development is the driving force in the universe...!
Go to Top of Page
   

- Advertisement -