1 week older than today - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

1 week older than today

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-05-01 : 07:05:30

HI there,

Trying to show records that are 1 week older than the start date of the tickets system from todays date?

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",
DATEDIFF(d, c.OCCURED_DT, GETDATE()) AS OpenDaysLastWeek,--here want to see same figure as Open Days above but only calc on tickets older than a week old using the c.OCCURED_DT field 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

Thanks

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

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-01 : 11:31:05

It is difficult to figure out what you want exactly.
You may want to take a look at the following article which would help you post the question
so it is easy for others to understand and answer your questions faster.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-02 : 01:11:58

do you mean count of records? otherwise it doesnt make any sense

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-05-07 : 07:07:27

The DATEDIFF(d, c.OCCURED_DT, GETDATE()) AS "Open Days",
works out open days but Id like to count only the records that are a week old, so count all tickets except last 7 days.

SZ1
to learn is to show the universe that you care...!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-07 : 07:29:49

quote:
Originally posted by sz1

The DATEDIFF(d, c.OCCURED_DT, GETDATE()) AS "Open Days",
works out open days but Id like to count only the records that are a week old, so count all tickets except last 7 days.

SZ1
to learn is to show the universe that you care...!

put a filter like

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-05-07 : 07:49:47

wont that just take 7 days from the total days, its got to be excluding the actual count for tickets older than a week.

SZ1
to learn is to show the universe that you care...!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-07 : 07:53:53

quote:
Originally posted by sz1

wont that just take 7 days from the total days, its got to be excluding the actual count for tickets older than a week.

SZ1
to learn is to show the universe that you care...!

Where are you applying count? i cant even see count in your query
So I thought whole attempt is to count the records which you get in posted queries resultset

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-05-07 : 08:29:15

the count is the datediff: DATEDIFF(d, c.OCCURED_DT, GETDATE()) AS "Open Days",
this returns me the number of days between the 2 dates, so I would expect to see less days if older than a week, also if I do a count on the count(ID) then I would expect to see less total due to the removal of the last 7 days.

I will have another think, maybe what I'm doing isnt the best way.
Thanks

SZ1
to learn is to show the universe that you care...!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-08 : 00:28:14

quote:
Originally posted by sz1

the count is the datediff: DATEDIFF(d, c.OCCURED_DT, GETDATE()) AS "Open Days",
this returns me the number of days between the 2 dates, so I would expect to see less days if older than a week, also if I do a count on the count(ID) then I would expect to see less total due to the removal of the last 7 days.

I will have another think, maybe what I'm doing isnt the best way.
Thanks

SZ1
to learn is to show the universe that you care...!

The way to do it is as i suggested

ie add a filter to exclude rows within 7 days

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-05-08 : 05:07:47

Ok you mean a filter in SSRS dataset in(report builder) and not including it in the actual transact, we could put that into a where clause too then?

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",
DATEDIFF(d, c.OCCURED_DT, GETDATE()) AS OpenDaysAVGLastWeek,
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.OCCURED_DT < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-7)

is there anyway I can include this as an alias as part of the main transact above so:
select c.OCCURED_DT < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-7) as OpenDaysOlderThan7Days

Can do this but that will only subtract 7 days from the total open days:

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",
DATEDIFF(d, c.OCCURED_DT, GETDATE())-7 AS OpenDaysAVGLastWeek,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.OCCURED_DT < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-7)

SZ1
to learn is to show the universe that you care...!

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-05-08 : 05:32:59

Actually the where clause might do it as it takes a week off the ocurred_dt so all I need to do is create 2 datasets one with and one without the where clause and add that to the report side by side, how does that sound?
Thanks

SZ1
to learn is to show the universe that you care...!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources