SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Less 7 days
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sz1
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 01/09/2013 :  10:02:00  Show Profile  Reply with Quote
Is is possible to requery this query that a week old, so -7 days less from Occurred_DT calculate all tickets in open status - 7 days from current day? I tried putting -7 days after the datediff but this only subtracts 7 days from the open days total not the overall open calls. It needs to not count calls older than a week old only so discount last 7 days from Occurred_DT.

Ta


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
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, c.OCCURED_DT

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

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 01/09/2013 :  10:55:36  Show Profile  Reply with Quote
Try adding a dateadd to the where clause. Something like:
AND c.OCCURED_DT <= DATEADD(d, -7, GETDATE())


djj
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/09/2013 :  22:39:16  Show Profile  Reply with Quote
add below to where

AND c.OCCURED_DT > DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-7)

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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/09/2013 :  22:54:13  Show Profile  Reply with Quote
also see

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

http://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000