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 2008 Forums
 Transact-SQL (2008)
 Top 6 yesterday

Author  Topic 

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-01-15 : 10:24:42
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...!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-15 : 10:42:08
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
Aged Yak Warrior

555 Posts

Posted - 2013-01-15 : 10:58:52
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
Aged Yak Warrior

555 Posts

Posted - 2013-01-15 : 11:07:37
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...!
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

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

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-01-15 : 11:48:57
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-15 : 12:13:50
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

352 Posts

Posted - 2013-01-15 : 13:11:02
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
Aged Yak Warrior

555 Posts

Posted - 2013-01-16 : 05:20:35
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-16 : 06:23:42
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
Aged Yak Warrior

555 Posts

Posted - 2013-01-16 : 06:34:59
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...!
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-16 : 06:48:48
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
Aged Yak Warrior

555 Posts

Posted - 2013-01-16 : 08:09:05
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-16 : 08:17:15
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
Aged Yak Warrior

555 Posts

Posted - 2013-01-16 : 08:23:50
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...!
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-16 : 08:45:24
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
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-01-16 : 09:15:30
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
Aged Yak Warrior

555 Posts

Posted - 2013-01-16 : 09:20:46
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...!
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-01-16 : 09:37:40
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...!
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-16 : 10:47:46
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
Aged Yak Warrior

555 Posts

Posted - 2013-01-16 : 11:16:26
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
  Previous Page&nsp;  Next Page

- Advertisement -