| Author |
 Topic  |
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
 Posted - 01/15/2013 : 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...! |
Edited by - sz1 on 01/15/2013 10:26:08
|
|
|
James K
Flowing Fount of Yak Knowledge
1496 Posts |
Posted - 01/15/2013 : 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)
|
 |
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 01/15/2013 : 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...! |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 01/15/2013 : 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...! |
Edited by - sz1 on 01/15/2013 11:11:41 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1496 Posts |
Posted - 01/15/2013 : 11:27:05
|
May be you need only this then: c.Occured_DT < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
|
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 01/15/2013 : 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...! |
|
|
|
James K
Flowing Fount of Yak Knowledge
1496 Posts |
Posted - 01/15/2013 : 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) |
|
|
|
djj55
Yak Posting Veteran
USA
84 Posts |
Posted - 01/15/2013 : 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 |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 01/16/2013 : 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...! |
|
|
|
James K
Flowing Fount of Yak Knowledge
1496 Posts |
Posted - 01/16/2013 : 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. |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 01/16/2013 : 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...! |
Edited by - sz1 on 01/16/2013 06:41:59 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1496 Posts |
Posted - 01/16/2013 : 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 itWhere 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 |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 01/16/2013 : 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...! |
|
|
|
James K
Flowing Fount of Yak Knowledge
1496 Posts |
Posted - 01/16/2013 : 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? |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 01/16/2013 : 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...! |
Edited by - sz1 on 01/16/2013 08:24:36 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1496 Posts |
Posted - 01/16/2013 : 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 |
Edited by - James K on 01/16/2013 08:51:25 |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 01/16/2013 : 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...! |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 01/16/2013 : 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...! |
Edited by - sz1 on 01/16/2013 09:24:17 |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 01/16/2013 : 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...! |
Edited by - sz1 on 01/16/2013 09:46:58 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1496 Posts |
Posted - 01/16/2013 : 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. |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 01/16/2013 : 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...! |
|
|
|
Topic |
|
|
|
Top 6 yesterday
