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
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

James K
Flowing Fount of Yak Knowledge

3334 Posts

Posted - 01/16/2013 :  11:44:27  Show Profile  Reply with Quote
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.
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 01/16/2013 :  11:47:11  Show Profile  Reply with Quote
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...!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3334 Posts

Posted - 01/16/2013 :  12:02:12  Show Profile  Reply with Quote
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 up
Select Distinct c.INTI_CATEGORY, count(c.ID) OVER (PARTITION BY c.INTI_CATEGORY) As TotalCat, * --- THIS
From DIM_CALL c
....
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 01/16/2013 :  12:26:34  Show Profile  Reply with Quote
cool 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  
Previous 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.05 seconds. Powered By: Snitz Forums 2000