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)
 Top 6 yesterday
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/15/2013 :  10:24:42  Show Profile  Reply with Quote
Hi, This query works out the top 6 categories, can I add a dateadd getdate line to work out yesterdays top 6 using the date occured field? if the records are already up to date will it make any difference using the o.ccured_dt to eliminate todays??

--return top 6 Yesteday??
Select Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCat
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
And c.Occured_DT
Group by c.INTI_CATEGORY
Order By TotalCat Desc

--------------------------------------------------------
Tried this but it still gives me todays top 6?

--return top 6 only
Select Top 6 c.INTI_CATEGORY, DateAdd(day,-1,c.Occured_DT),
count(c.ID) As TotalCat
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
--DATEADD(day, DATEDIFF(d, 0, GetDate()), 0)
Group by c.INTI_CATEGORY,c.OCCURED_DT
Order By TotalCat Desc

---------------------------------------------------------
Also tried this but again todays top 6??

--return top 6 only
Select Top 6 c.INTI_CATEGORY,DATEADD(day, DATEDIFF(d, 0, GetDate()), -1), -- this returns yesterdays date but does not calculatye the top 6...
count(c.ID) As TotalCat
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
Group by c.INTI_CATEGORY
Order By TotalCat Desc

much appreciated!!



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

Edited by - sz1 on 01/15/2013 10:26:08

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 01/15/2013 :  10:42:08  Show Profile  Reply with Quote
The where clause in the first two queries don't seem to be complete - i.e., they should generate an error. Change the where clause to this:
WHERE  TYPE = 'Incident'
       AND c.OPEN_FLAG = 1
       AND c.ETL_CURRENT = 1
       AND c.Occured_DT >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)
       AND c.Occured_DT < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/15/2013 :  10:58:52  Show Profile  Reply with Quote
Hi

Yes they all return results just not what I'm after...your suggestion returns this:

Select Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCat
From DIM_CALL c
WHERE TYPE = 'Incident'
AND c.OPEN_FLAG = 1
AND c.ETL_CURRENT = 1
AND c.Occured_DT >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)
AND c.Occured_DT < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
Group by c.INTI_CATEGORY
Order By TotalCat Desc

--result
Central 8
Local 5
VPN 4
Domain Services 3
Thin Client 3
Core Applications 2

the actual results from yesteday are:

Central 68
Core Applications 66
Local 52
Printers 49
VPN 23
Mail Services 19

Even if If I comment out:

--AND c.OPEN_FLAG = 1
--AND c.ETL_CURRENT = 1

Still get wrong figures...

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

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/15/2013 :  11:07:37  Show Profile  Reply with Quote
Just realised I want everything to be included for yesterdays results, just not the current days stats...so everything apart from the most recent todays stats.

Tried this and the figures are closer but still not a match, will the datediff give me all prior to todays stats?
Is it everything from ma certain time using getdate? it needs to be all calls prior to 6am in the morning if thats case, can this be included...


Select Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCat
From DIM_CALL c
WHERE TYPE = 'Incident'
AND c.OPEN_FLAG = 1
AND c.ETL_CURRENT = 1
AND c.Occured_DT <= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)
--AND c.Occured_DT < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
Group by c.INTI_CATEGORY
Order By TotalCat Desc



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

Edited by - sz1 on 01/15/2013 11:11:41
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 01/15/2013 :  11:27:05  Show Profile  Reply with Quote
May be you need only this then:
	c.Occured_DT < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/15/2013 :  11:48:57  Show Profile  Reply with Quote
That brings back todays results...strange one this, thought it would be easy...I'll keep trying...
Thanks

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

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 01/15/2013 :  12:13:50  Show Profile  Reply with Quote
Can you post some sample data and the expected output? The where clause I posted definitely looks for Occured_DT less than today's date. You can see this if you run the following query - that gives you today's date with no time component. So when you ask for anything less than that, it is going to return rows for which c.Occured_DT is less than today - i.e., yesterday or earlier.
SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
306 Posts

Posted - 01/15/2013 :  13:11:02  Show Profile  Reply with Quote
Do not forget that GETDATE() has time. Thus DATEADD(day, -1, GETDATE()) will return '2013-01-15 13:10' if I ran it at this time.

You may need to CAST as DATE.

djj
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/16/2013 :  05:20:35  Show Profile  Reply with Quote
How do we set the getdate to look back from a certain time, you say cast...?

This statement returns the below data, all up to date categories based on top 6
--return top 6 today
Select Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCat
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
And c.Occured_DT
Group by c.INTI_CATEGORY
Order By TotalCat Desc

Data Returned
Central 69
Core Applications 64
Printers 51
Local 50
Mail Services 22
VPN 21

I was hoping the getdate would return me the same output but obviously showing different data as we would not be counting todays stats...so trying something like this, but this does not actually omit todays date it just sets the date as yesteday and nothing else.

--return top 6 only
Select Top 6 c.INTI_CATEGORY,DATEADD(day, DATEDIFF(d, 0, GetDate()), -1),
count(c.ID) As TotalCat
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
Group by c.INTI_CATEGORY
Order By TotalCat Desc

Also tried this, again its not calculating correctly, I just want to sum data for the top 6 where the date registered (occured_dt) is not today, that should return the top 6 minus today...

Select Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCat
From DIM_CALL c
WHERE TYPE = 'Incident'
AND c.OPEN_FLAG = 1
AND c.ETL_CURRENT = 1
--And c.Occured_DT < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND c.Occured_DT >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)--greater than or = yesterday
--AND c.Occured_DT < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) -- less than current date
Group by c.INTI_CATEGORY
Order By TotalCat Desc

I need to be able to run the script from 6am this morning backwards, and every day like that?

Thanks

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

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 01/16/2013 :  06:23:42  Show Profile  Reply with Quote
I didn't yet understand your requirement - initially I thought you wanted to get the data for the prior day. Then, I thought it was all data earlier than today going back to the beginning. I realize I was probably wrong in both those assumptions based on your latest post where you say "How do we set the getdate to look back from a certain time, you say cast...?

Assuming you have data in the database for a number of days (the date/time to which a row is associated determined by the Occured_DT column in DIM_CALL table), can you describe what you are trying to get. I understood that you want to run a query at 6:00 AM each morning. What should that query return? All results upto 6:00 AM when the query is run? Or the prior days results? Or results for all previous days? Or something else?

If you post some sample data and desired output that would be of great help.
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/16/2013 :  06:34:59  Show Profile  Reply with Quote
Hi,

I want to return all data from previous days but not including latest/todays data and from 6am every morning. So, If I run it now it would remove todays date and time parts up to 6am this morning, then it would return all data thats still in an open state for all days prior to everything from 6am this morning, everything from 6am this morning to now I dont want to see just everything else prior...

This is the output which I use, I want the same but the totals and ctegories should change slightly each day. I want to setup a dashboard that looks at previos days data, thats the reason for the query.

Data Returned
Central 69
Core Applications 64
Printers 51
Local 50
Mail Services 22
VPN 21

So, I need to build into this statement the datetime part that removes what I've mentioned above...

I can get the results using dateadd and getdate...but its not returning the correct figures as I already have a history for each day I can compare.

This is the data I use, so I need to add to this the previous days data and from 6am this morning backwards...
--return top 6 Yesteday??
Select Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCat
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
And c.Occured_DT
Group by c.INTI_CATEGORY
Order By TotalCat Desc



Thanks

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

Edited by - sz1 on 01/16/2013 06:41:59
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 01/16/2013 :  06:48:48  Show Profile  Reply with Quote
quote:
I want to return all data from previous days but not including latest/todays data and from 6am every morning. So, If I run it now it would remove todays date and time parts up to 6am this morning, then it would return all data thats still in an open state for all days prior to everything from 6am this morning, everything from 6am this morning to now I dont want to see just everything else prior...
I think this should do it
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
And c.Occured_DT < DATEADD(hh,6,CAST(CAST(GETDATE() AS DATE) AS DATETIME))
Group by c.INTI_CATEGORY
If you are on a version earlier than SQL 2008 (or even if you are on SQL 2008), you can use this instead:
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
And c.Occured_DT < DATEADD(hh,6,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))
Group by c.INTI_CATEGORY
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/16/2013 :  08:09:05  Show Profile  Reply with Quote
mmm

This returns: it does not pull the top 6 categories, for instance Desktop is there with 1 and I know there are categories higher than that.

Select Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCat
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
And c.Occured_DT < DATEADD(hh,6,CAST(CAST(GETDATE() AS DATE) AS DATETIME))
Group by c.INTI_CATEGORY

Output
ASP 5
Central 69
Core Applications 64
Desktop 1
Desktop Operating System 3
Domain Services 14


This query returns the same data:

--return top 6 only
Select Top 6 c.INTI_CATEGORY,DATEADD(day, DATEDIFF(d, 0, GetDate()), -1),
count(c.ID) As TotalCat
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
Group by c.INTI_CATEGORY

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

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 01/16/2013 :  08:17:15  Show Profile  Reply with Quote
For TOP to work as you would expect, you need an order by clause as well. Otherwise SQL Server returns any six rows from the result set that IT chooses. So perhaps you need something like this:
Select Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCat
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
And c.Occured_DT < DATEADD(hh,6,CAST(CAST(GETDATE() AS DATE) AS DATETIME))
Group by c.INTI_CATEGORY
ORDER BY TotalCat DESC; --- THIS?
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/16/2013 :  08:23:50  Show Profile  Reply with Quote
Yeah I ad that but it only returns todays stats again...

so:
Select Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCat
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
And c.Occured_DT < DATEADD(hh,6,CAST(CAST(GETDATE() AS DATE) AS DATETIME))
Group by c.INTI_CATEGORY
Order by TotalCat Desc

returns

Central 69
Core Applications 64
Printers 51
Local 50
Mail Services 22
VPN 21

But it should return, note the change of the bottom 2 and the totals...

Central 68
Core Applications 66
Printers 52
Local 49
VPN 23
Mail Services 19

Strange, I though using the occured_dt which is the logged date we would be able to disregard calls with a set date but this doesn't seem to be the case...




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

Edited by - sz1 on 01/16/2013 08:24:36
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 01/16/2013 :  08:45:24  Show Profile  Reply with Quote
Since I have not seen the data, there is nothing I can offer by way of help in figuring out why the numbers are different from what you expect. How did you calculate the expected numbers? Run this query and it will show you the non-aggregated data. Then compare that with what you expect to figure out what is missing/extra.
Select c.INTI_CATEGORY, count(c.ID) OVER (PARTITION BY c.INTI_CATEGORY) As TotalCat,*
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
And c.Occured_DT < DATEADD(hh,6,CAST(CAST(GETDATE() AS DATE) AS DATETIME))
Order by TotalCat Desc

Edited by - James K on 01/16/2013 08:51:25
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/16/2013 :  09:15:30  Show Profile  Reply with Quote
I can see that its giving me calls on 15th and can see all the categories, just a thought can I go back further to the 14th...so everything from 6am not this morning from yesterday morning, so -2 days, to remove the 15th date calls...can I just amend the 6 hors below to say 48? doesn;t seem to remove the 15th date calls?

Thanks, appreciate your help with this.

Select distinct c.INTI_CATEGORY, count(c.ID) OVER (PARTITION BY c.INTI_CATEGORY) As TotalCat,*
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
And c.Occured_DT < DATEADD(hh,48,CAST(CAST(GETDATE() AS DATE) AS DATETIME))
Order by TotalCat Desc, c.OCCURED_DT Desc

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

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/16/2013 :  09:20:46  Show Profile  Reply with Quote
This is getting closer, I've found out that the data I'm using is a day old because of the current BI, so its everything from 6am yesterday morning, anything after that omit...
using dd the numbers are getting higher
, just need the exact hours I think to look back...

Select distinct c.INTI_CATEGORY, count(c.ID) OVER (PARTITION BY c.INTI_CATEGORY) As TotalCat
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
And c.Occured_DT < DATEADD(DD,-1,CAST(CAST(GETDATE() AS DATE) AS DATETIME))
Order by TotalCat Desc

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

Edited by - sz1 on 01/16/2013 09:24:17
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/16/2013 :  09:37:40  Show Profile  Reply with Quote
Ok this is definately nealy there, its giving me the correct date back the 14th which is correct as we are always working a day behind. Its calculating 24 hours behind, so if I run now its this time but not back to 6am yesterday morning, its the 6am bit I need to add...so close...

Select distinct c.INTI_CATEGORY, count(c.ID) OVER (PARTITION BY c.INTI_CATEGORY) As TotalCat,*
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
And c.Occured_DT < DATEADD(dd,-1,CAST(CAST(GETDATE() AS DATE) AS DATETIME))
And c.OCCURED_DT < DATEADD(hh,6,CAST(CAST(GETDATE() AS DATE) AS DATETIME)) ---can I use hh to make it 6am backward from -1 days?
Order by TotalCat Desc, c.OCCURED_DT desc

I was trying something like this?

And c.Occured_DT < DATEADD(dd,-1,DATEADD(hh,6,(CAST(CAST(GETDATE() AS DATE) AS DATETIME))

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

Edited by - sz1 on 01/16/2013 09:46:58
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 01/16/2013 :  10:47:46  Show Profile  Reply with Quote
quote:
And c.OCCURED_DT < DATEADD(hh,6,CAST(CAST(GETDATE() AS DATE) AS DATETIME)) ---can I use hh to make it 6am backward from -1 days?
Yes, you could do that. I am giving several examples below. What you want is the fourth one.

If you notice, there is a pattern to it. Imagine that today is January 1 of 1900. Then, insert the point in time at which you want your filter to start - so if you wanted to filter out everything newer than 6:00 AM yesterday, you would have Dec 31, 1899 at 6:00 AM as the date and time.
SELECT 
	DATEADD(dd,DATEDIFF(dd,0,GETDATE()),'1900-01-01T00:00:00.000'), -- start of today
	DATEADD(dd,DATEDIFF(dd,0,GETDATE()),'1899-12-31T00:00:00.000'), -- start of yesterday
	DATEADD(dd,DATEDIFF(dd,0,GETDATE()),'1900-01-01T06:00:00.000'), -- 6:00 AM Today
	DATEADD(dd,DATEDIFF(dd,0,GETDATE()),'1899-12-31T06:00:00.000') -- 6:00 AM yesterday


So your where clause would be
And c.OCCURED_DT < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),'1899-12-31T06:00:00.000')
I know this is different from what I had posted earlier - hopefully this will be easier to understand and for you to modify any way you choose.
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/16/2013 :  11:16:26  Show Profile  Reply with Quote
Thanks for the explanation it helps me move forward, are the dates you selected standard for programming or can you use any set of dates...assuming that its standard sql otherwise the dateadd wouldn't know what it meant

Thanks

SZ1
Learning and development is the driving force in the universe...!
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 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.17 seconds. Powered By: Snitz Forums 2000