July 15, 2008 at 5:34 am
Hi guys!
I'm having a bit of a hard time believing that my way of sorting my little problem is the best one (or good at all for that matter)... I'm counting the number of logins each day in a database. Those days when no one has been logged in will be ignored unless I tell it otherwise, so I got the idea of joining it with a column containing 365 "days".. until I realised I would have to manually write select "day x" union all select "day x + 1" and so on and so forth... Is there any way of doing this without my fingertops going numb? I've tried doing it with a while loop, but I couldnt get it to work with the union all statement....
Here's something of what my code looks like:
declare @example table
( count int,
dayYear int,
year int,
month int,
week int,
datename varchar(15)
)
insert into @example
SELECT count(distinct login) as count, datepart(dy, logindate) as day, year(logindate) as year, month(logindate), datepart(ww, logindate) as week, datename(month, logindate) as monthname
from example_tbl
GROUP BY datepart(dy, logindate), datepart(ww, logindate), year(logindate), datename(month, logindate), month(logindate)
ORDER BY year(logindate), datepart(dy, logindate)
select
datepart(dd, dateadd(dy, d.dy -365, getdate())) as day,
datepart(dy, dateadd(dy, d.dy - 365, getdate())) as dayYear,
datepart(ww, dateadd(dy, d.dy - 365, getdate())) as week,
year(dateadd(dy, d.dy -365, getdate())) as year,
case
when a.count is null then 0
else a.count
end as count
from
(
select 1 as dy union all select 2 union all select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all
select 11 union all select 12 union all select 13 union all select 14 union all select 15 union all
select 16 union all select 17 /*and so on.....*/) d
left join @example a
on a.dayYear = datepart(dy, dateadd(dy, d.dy -365, getdate()))
and a.year = year(dateadd(dy, d.dy -365, getdate()))
group by
datepart(dd, dateadd(dy, d.dy -365, getdate())),
datepart(ww, dateadd(dy, d.dy - 365, getdate())),
datepart(dy, dateadd(dy, d.dy - 365, getdate())),
year(dateadd(dy, d.dy -365, getdate())),
a.count
order by
year(dateadd(dy, d.dy -365, getdate())),
datepart(ww, dateadd(dy, d.dy - 365, getdate())),
datepart(dy, dateadd(dy, d.dy -365, getdate()))
Thanks in advance,
Jonatan
July 15, 2008 at 5:53 am
Create a permanent numbers table with a couple thousand rows. It's handy for a number of problems, not just this one.
Somethng like this
CREATE TABLE Numbers (
Number INT PRIMARY KEY
)
GO
INSERT INTO Numbers (Number)
SELECT TOP 2000 number FROM
master..spt_values
where name is null and number>0
GO
Now you can use that table for your dateadds, amoung many other things.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2008 at 5:58 am
I've seen several examples to return a daterange, and since SQL2005 a recursive CTE is the most promising.
On i.e. this link you'll find an example you can use.
If the "calendar" is widely used though, I assume it might be a good idea to store it in a real table. I think that might generate some performance benefits. To populate that real table: use the CTE described in the link 😀
Peter Rijs
BI Consultant, The Netherlands
July 15, 2008 at 6:12 am
thanks for the quick replies guys!
@gilamonster: I dont have the privileges to add tables to the db, but I used the later part of the script;
from(
SELECT TOP 366 number as dy FROM
master..spt_values
where name is null and number>0) as d
worked like a charm 🙂
thanks a lot both of you!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply