Extracting data between two dates

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Extracting data between two dates

Author

Topic

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2005-02-23 : 10:33:10

Howdy folks

I'm attempting to generate graphs based upon statistics extracted from SQL Server.
I wish to work out the total numbers of views to a page between a set of dates.

The following is my current code.


SELECT
COUNT(*) 'TotalVisitors',
CONVERT( DATETIME, CONVERT( CHAR(10), ViewDatetime, 101 ) ) 'Date'
FROM
PageViews
WHERE
ViewDatetime >= @DateFrom AND
ViewDatetime <= @DateTo AND
PageID = @PageID
GROUP BY
CONVERT( DATETIME, CONVERT( CHAR(10), ViewDatetime, 101 ) ) 
ORDER BY
CONVERT( DATETIME, CONVERT( CHAR(10), ViewDatetime, 101 ) )  ASC

This works, but it only returns data on the dates that have views.
I also need it to return a row with 'TotalVisitors' at 0 for a date that doesn't have any views.

Does anyone know of a way to achieve this?

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-23 : 10:40:20

you need a table of all dates for this to join to.

Go with the flow & have fun! Else fight the flow

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2005-02-23 : 10:49:55

Doh!

I thought it might come to that.
I was hoping there was a clever one statement way to do it :/

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2005-02-23 : 10:57:32

Forgive my questioning again, but what would be the best way to populate a table full of dates between two points?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-23 : 11:31:20

this is as good as any...


declare @startDate datetime
set @startDate = '20050101'

declare @MyTable table (date datetime)
insert into @MyTable (date)
Select dateadd(d, n, @startDate)
from
(
	select n = n1.n+n2.n*10+n3.n*100
	From
		(Select n=0 Union All Select 1 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) n1,
		(Select n=0 Union All Select 1 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) n2,
		(Select n=0 Union All Select 1 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) n3
) t1
order by n
select * from @MyTable

Go with the flow & have fun! Else fight the flow

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2005-02-23 : 11:39:46

Danke

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-23 : 11:51:00

Bitte

Go with the flow & have fun! Else fight the flow

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2005-02-24 : 04:32:44

Perhaps it's the late nights I've been working this week, but I thought the following code would returns the results I desired


SELECT
COUNT(*) 'TotalVisitors',
TT_DS.ViewDate 'Date'
FROM
@TT_DateSpan AS TT_DS
LEFT JOIN 
PageViews AS PV ON
CONVERT( DATETIME, CONVERT( CHAR(10), PV.ViewDatetime, 101 ) ) = TT_DS.ViewDate
WHERE
PV.PageID = @PageID
GROUP BY
TT_DS.ViewDate
ORDER BY
TT_DS.ViewDate ASC

I'm sure I'm missing something obvious, so please chalk it up to needing a good nights sleep!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-24 : 04:46:44

well the error you get would be nice to know...

Go with the flow & have fun! Else fight the flow

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2005-02-24 : 04:56:02

If I specify the date range as between '2005/01/01' and '2005/03/01' then I get the following result set


TotalVisitors Date                                                   
------------- ------------------------------------------------------ 
120           2005-02-10 00:00:00
184           2005-02-14 00:00:00
3             2005-02-15 00:00:00
4             2005-02-16 00:00:00
5             2005-02-17 00:00:00
2             2005-02-21 00:00:00
4             2005-02-22 00:00:00
17            2005-02-23 00:00:00

which is only returning dates which have at least one view, whereas I need the result set to include the dates with zero views as well

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-24 : 05:36:59

try this:


SELECT
sum(case when TT_DS.ViewDate is null then 0 else 1 end) 'TotalVisitors',
TT_DS.ViewDate 'Date'
FROM
@TT_DateSpan AS TT_DS
LEFT JOIN 
PageViews AS PV ON
CONVERT( DATETIME, CONVERT( CHAR(10), PV.ViewDatetime, 101 ) ) = TT_DS.ViewDate
WHERE
PV.PageID = @PageID
GROUP BY
TT_DS.ViewDate
ORDER BY
TT_DS.ViewDate ASC

this is a sample in northwind that works:


set @startDate = '19960831'

select 	t1.date, sum(case when O.shippedDate is null then 0 else 1 end)
from 	@MyTable t1
	left join orders O on t1.date = DATEADD(d, DATEDIFF(d, 0, O.shippedDate), 0)
group by t1.date

Go with the flow & have fun! Else fight the flow

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2005-02-24 : 07:57:28

Hmmm... your revised query does not work either.

I don't understand why it's not working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources