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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sz1
Aged Yak Warrior

United Kingdom
555 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
352 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
52326 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
52326 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  
 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.04 seconds. Powered By: Snitz Forums 2000