| Author |
 Topic  |
|
James K
Flowing Fount of Yak Knowledge
1481 Posts |
 Posted - 01/16/2013 : 11:44:27
|
| The reference date in SQL is 1900-01-01 midnight. Internally datetime is stored using eight bytes, one byte representing date and one byte representing time. Date = 0 is 1900-01-01. That is why those formulas use that as the reference. |
 |
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 01/16/2013 : 11:47:11
|
Ended up using which got me as close as I can I think...
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))
--And c.OCCURED_DT < DATEADD(HOUR,200,GETDATE())
--ANd c.OCCURED_DT < dateadd(hour,32, dateadd(day, datediff(day, -1, getdate()), -1))
--And c.Occured_DT < DATEADD(dd,-1,DATEADD(hh,6,(CAST(CAST(GETDATE() AS DATE) AS DATETIME))))
And c.OCCURED_DT < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),'1899-12-31T15:00:00.000') --noticed as I changed the time the numbers started to add up...
RESULT
Central 68 -- correct
Core Applications 64 --- 66 out by 2
Printers 51 --- 52 out by 1
Local 48 --- 49 out by 1
Mail Services 22 --- 23 out by 1
VPN 20 --- 19 out by 1
Thanks very much for all your help, really helps!!
Order by TotalCat Desc /*c.OCCURED_DT desc*/
SZ1
Learning and development is the driving force in the universe...! |
|
|
|
James K
Flowing Fount of Yak Knowledge
1481 Posts |
Posted - 01/16/2013 : 12:02:12
|
I don't know enough to comment on your results, but the where clause looks for anything earlier than 3:00 PM yesterday. You can add additional columns to the select to see the details of the rows you are picking upSelect Distinct c.INTI_CATEGORY, count(c.ID) OVER (PARTITION BY c.INTI_CATEGORY) As TotalCat, * --- THIS
From DIM_CALL c
.... |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 01/16/2013 : 12:26:34
|
cool thanks :)
SZ1
Learning and development is the driving force in the universe...! |
|
|
|
Topic |
|
|
|
Top 6 yesterday
