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 2012 Forums
 Transact-SQL (2012)
 1 week older than today
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sz1
Constraint Violating Yak Guru

United Kingdom
439 Posts

Posted - 05/01/2013 :  07:05:30  Show Profile  Reply with Quote
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...!

Edited by - sz1 on 05/01/2013 08:50:18

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/01/2013 :  11:31:05  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/02/2013 :  01:11:58  Show Profile  Reply with Quote
do you mean count of records? otherwise it doesnt make any sense

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
439 Posts

Posted - 05/07/2013 :  07:07:27  Show Profile  Reply with Quote
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...!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/07/2013 :  07:29:49  Show Profile  Reply with Quote
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
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
439 Posts

Posted - 05/07/2013 :  07:49:47  Show Profile  Reply with Quote
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...!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/07/2013 :  07:53:53  Show Profile  Reply with Quote
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
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
439 Posts

Posted - 05/07/2013 :  08:29:15  Show Profile  Reply with Quote
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...!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/08/2013 :  00:28:14  Show Profile  Reply with Quote
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
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
439 Posts

Posted - 05/08/2013 :  05:07:47  Show Profile  Reply with Quote
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...!

Edited by - sz1 on 05/08/2013 05:23:42
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
439 Posts

Posted - 05/08/2013 :  05:32:59  Show Profile  Reply with Quote
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...!
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.16 seconds. Powered By: Snitz Forums 2000