June 28, 2008 at 12:06 pm
Hey Guys
Can you assist me with this one.
I have a table that is populated by an application logging assembly line rejects. There are generally between 0 (zero) and 10 entries per day. I would like to retrieve a report containing a count of these entries each day including the days where there are no errors.
At the moment I am using another table just populated with consecutive dates which I can then use as part of a join. Is there a neater way?
Thanks in advance for any help.
June 28, 2008 at 2:19 pm
There are complicated ways, but honestly I'd just use a dates table, since then you can account for holidays, weekend work, etc. in that table. I'd actually use the dates in the table and then an active/inactive flag, putting all dates in there and toggling them on or off.
I used to do this to report on accounting periods, which weren't always consistently long (almost always ending on the last Fri of the month, but sometimes not).
Populate out for a couple years, set some reminders on a few calendars (yours and whoever does the schedule) to mark next year's dates in late Dec.
June 28, 2008 at 3:32 pm
Thanks for the superfast reply Steve.
Your suggestion is not far from what I am doing now, except I'm automating the forward filling of the dates. The app that drops in the reports also checks to see if the dates table is populated a while ahead (using MAX()), and adds any entries where needed. There's very little speed impact, even on my prehistoric server.
Come to think of it, it would be handy to check for gaps in the sequence and fill them in if missing. No idea how to do that in SQL so might have to resort to a little more VB :o)
I like the idea of using the dates table to hold other stuff too.
Chris.
June 28, 2008 at 8:08 pm
Here's an easy way to create and populate a Dates table:
Create table Dates(D datetime primary key clustered)
GO
Declare @FirstDate datetime
Declare @LastDate datetime
Declare @Days int
Set @FirstDate = Cast('1970-01-01' as datetime)--<< change these to what you need
Set @LastDate = Cast('2050-12-31' as datetime)--<< change these to what you need
Select @Days = DateDiff(dd, @FirstDate, @LastDate) + 1
;With Numbers as (
Select Row_Number() Over (Order By colorder, id) as [N]
From master..syscolumns
)
Insert Into Dates
Select DateAdd(dd, N-1, @FirstDate)
From Numbers
where N <= @Days
It filled in these 80 year of dates in less that a second on my laptop.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 29, 2008 at 1:05 pm
Ummm... go check the final date Barry... might explain the "speed" you're getting...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2008 at 1:24 pm
Ack! Yeah, that does make sense...
OK, then this one runs in under a second:
Create table Dates(D datetime primary key clustered)
GO
Declare @FirstDate datetime
Declare @LastDate datetime
Declare @Days int
Set @FirstDate = Cast('1970-01-01' as datetime) --<< change these to what you need
Set @LastDate = Cast('2050-12-31' as datetime) --<< change these to what you need
Select @Days = DateDiff(dd, @FirstDate, @LastDate) + 1
;With Numbers as (
Select TOP(@Days) Row_Number() Over (Order By c1.id) as [N]
From master.sys.syscolumns c1, master.sys.syscolumns c2
Order By c1.id
)
Insert Into Dates
Select DateAdd(dd, N-1, @FirstDate)
From Numbers
where N <= @Days
select * from dates order by d desc
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 29, 2008 at 5:28 pm
Much better... now, with the TOP in the CTE, do you really need the WHERE clause?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2008 at 5:30 pm
... and, come to think of it... I think 1 Order By in the CTE will probably be enough, as well. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2008 at 5:37 pm
Jeff Moden (6/29/2008)
Much better... now, with the TOP in the CTE, do you really need the WHERE clause?
Prolly not...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 29, 2008 at 5:43 pm
Jeff Moden (6/29/2008)
... and, come to think of it... I think 1 Order By in the CTE will probably be enough, as well. 😉
I'll need some convincing on that one. It seems to me that there is not guarantee that ROW_NUMBER() and TOP(..) will use the same order otherwise. And if they don't, then TOP could cut off a set of N's (from ROW_NUMBER()) that have a "hole" in them (or worse).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 29, 2008 at 6:01 pm
Actually, no Order By is really necessary except to satisfy the syntactical requirements of Row_Number()...
For example...
[font="Courier New"]SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100) ---------------------------------------------------------
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
PRINT '===== Jeff Moden''s Method'
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @SomeConstant INT
SET @SomeConstant = 1
; WITH cTally AS
(-----------------------------------------------------------------------------
SELECT TOP (@TestSize)
ROW_NUMBER() OVER (ORDER BY @SomeConstant) AS N
FROM Master.sys.SysColumns t1
CROSS JOIN Master.sys.SysColumns t2
)-----------------------------------------------------------------------------
SELECT N FROM cTally[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2008 at 6:08 pm
rbarryyoung (6/29/2008)
Jeff Moden (6/29/2008)
... and, come to think of it... I think 1 Order By in the CTE will probably be enough, as well. 😉I'll need some convincing on that one. It seems to me that there is not guarantee that ROW_NUMBER() and TOP(..) will use the same order otherwise. And if they don't, then TOP could cut off a set of N's (from ROW_NUMBER()) that have a "hole" in them (or worse).
And since TOP in SQL Server 2005 can use an expression, you can get rid of @Days completely.
Drop Table dbo.Dates;
Go
Create Table dbo.Dates(D datetime primary key clustered)
GO
Declare @FirstDate datetime
,@LastDate datetime;
Set @FirstDate = Cast('1970-01-01' as datetime); --<< change these to what you need
Set @LastDate = Cast('2050-12-31' as datetime); --<< change these to what you need
;With Numbers (N) As (
Select TOP(datediff(day, @FirstDate, @LastDate) + 1) Row_Number() Over (Order By c1.id)
From master.sys.syscolumns c1, master.sys.syscolumns c2)
Insert Into dbo.Dates
Select DateAdd(dd, N -1, @FirstDate)
From Numbers;
Select * From dbo.Dates Order By D desc;
Barry, I don't think you need to worry about the order by for the TOP - since all we really care about is how many rows are going to be returned. The row_number() will guarantee sequential numbering as long as there are enough rows to satisfy the TOP requirement.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 29, 2008 at 6:25 pm
True enough... I kinda like @Days, though... makes the CTE easier to read from a self documenting aspect. Yeah, I know... easy to read anyway. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2008 at 7:24 pm
Jeffrey Williams (6/29/2008)And since TOP in SQL Server 2005 can use an expression, you can get rid of @Days completely.
Yeah, I knew that, but I wanted @Days because it makes the code clearer and easier to read.
... oops, I didn't see Jeff's reply before, he beat me to it!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 29, 2008 at 7:34 pm
Jeff Moden (6/29/2008)
Actually, no Order By is really necessary except to satisfy the syntactical requirements of Row_Number()...
Hmmm, ... for some reason I'm not scanning it. But I'll take your word for it 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply