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.
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 TotalCatFrom DIM_CALL cWhere TYPE = 'Incident'And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1And c.Occured_DT Group by c.INTI_CATEGORYOrder By TotalCat Desc--------------------------------------------------------Tried this but it still gives me todays top 6? --return top 6 onlySelect 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 onlySelect 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_CATEGORYOrder By TotalCat Descmuch appreciated!!SZ1Learning 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) |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2013-01-15 : 10:58:52
|
HiYes they all return results just not what I'm after...your suggestion returns this:Select Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCatFrom DIM_CALL cWHERE 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_CATEGORYOrder By TotalCat Desc--resultCentral 8Local 5VPN 4Domain Services 3Thin Client 3Core Applications 2the actual results from yesteday are:Central 68Core Applications 66Local 52Printers 49VPN 23Mail Services 19Even if If I comment out: --AND c.OPEN_FLAG = 1 --AND c.ETL_CURRENT = 1Still get wrong figures...SZ1Learning and development is the driving force in the universe...! |
|
|
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 TotalCatFrom DIM_CALL cWHERE 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_CATEGORYOrder By TotalCat DescSZ1Learning and development is the driving force in the universe...! |
|
|
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) |
|
|
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...ThanksSZ1Learning and development is the driving force in the universe...! |
|
|
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) |
|
|
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 |
|
|
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 todaySelect Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCatFrom DIM_CALL cWhere TYPE = 'Incident'And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1And c.Occured_DT Group by c.INTI_CATEGORYOrder By TotalCat DescData ReturnedCentral 69Core Applications 64Printers 51Local 50Mail Services 22VPN 21I 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 onlySelect 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_CATEGORYOrder By TotalCat DescAlso 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 TotalCatFrom DIM_CALL cWHERE 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_CATEGORYOrder By TotalCat DescI need to be able to run the script from 6am this morning backwards, and every day like that?ThanksSZ1Learning and development is the driving force in the universe...! |
|
|
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. |
|
|
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 ReturnedCentral 69Core Applications 64Printers 51Local 50Mail Services 22VPN 21So, 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 TotalCatFrom DIM_CALL cWhere TYPE = 'Incident'And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1And c.Occured_DT Group by c.INTI_CATEGORYOrder By TotalCat DescThanksSZ1Learning and development is the driving force in the universe...! |
|
|
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 itWhere TYPE = 'Incident'And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1And 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 =1And c.Occured_DT < DATEADD(hh,6,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))Group by c.INTI_CATEGORY |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2013-01-16 : 08:09:05
|
mmmThis 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 TotalCatFrom DIM_CALL cWhere TYPE = 'Incident'And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1And c.Occured_DT < DATEADD(hh,6,CAST(CAST(GETDATE() AS DATE) AS DATETIME))Group by c.INTI_CATEGORYOutputASP 5Central 69Core Applications 64Desktop 1Desktop Operating System 3Domain Services 14This query returns the same data: --return top 6 onlySelect 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_CATEGORYSZ1Learning and development is the driving force in the universe...! |
|
|
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 TotalCatFrom DIM_CALL cWhere TYPE = 'Incident'And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1And c.Occured_DT < DATEADD(hh,6,CAST(CAST(GETDATE() AS DATE) AS DATETIME))Group by c.INTI_CATEGORYORDER BY TotalCat DESC; --- THIS? |
|
|
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 TotalCatFrom DIM_CALL cWhere TYPE = 'Incident'And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1And c.Occured_DT < DATEADD(hh,6,CAST(CAST(GETDATE() AS DATE) AS DATETIME))Group by c.INTI_CATEGORYOrder by TotalCat DescreturnsCentral 69Core Applications 64Printers 51Local 50Mail Services 22VPN 21But it should return, note the change of the bottom 2 and the totals...Central 68Core Applications 66Printers 52Local 49VPN 23Mail Services 19Strange, 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...SZ1Learning and development is the driving force in the universe...! |
|
|
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 cWhere TYPE = 'Incident'And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1And c.Occured_DT < DATEADD(hh,6,CAST(CAST(GETDATE() AS DATE) AS DATETIME))Order by TotalCat Desc |
|
|
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 cWhere TYPE = 'Incident'And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1And c.Occured_DT < DATEADD(hh,48,CAST(CAST(GETDATE() AS DATE) AS DATETIME))Order by TotalCat Desc, c.OCCURED_DT DescSZ1Learning and development is the driving force in the universe...! |
|
|
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 TotalCatFrom DIM_CALL cWhere TYPE = 'Incident'And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1And c.Occured_DT < DATEADD(DD,-1,CAST(CAST(GETDATE() AS DATE) AS DATETIME))Order by TotalCat DescSZ1Learning and development is the driving force in the universe...! |
|
|
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 cWhere TYPE = 'Incident'And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1And 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 descI was trying something like this?And c.Occured_DT < DATEADD(dd,-1,DATEADD(hh,6,(CAST(CAST(GETDATE() AS DATE) AS DATETIME))SZ1Learning and development is the driving force in the universe...! |
|
|
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. |
|
|
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 meantThanksSZ1Learning and development is the driving force in the universe...! |
|
|
Next Page
|
|
|
|
|