Date Range with Sub-Queries - SQL Server Forums

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)

Date Range with Sub-Queries

Author

Topic

masterslave
Starting Member

22 Posts

Posted - 2008-03-04 : 00:54:51

Hi,

I need to do a query that gets date range (date from and date to)
as parameters and then for each day in the range does a sub-query
with count so output would be like this for example:

Date Clicks
------------------------
5/3/2008 341
6/3/2008 748
7/3/2008 562

How can I do that?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-04 : 01:11:28

You've shown us the expected output, but we also need to see what the data looks like in the table and specifically the data that would produce this output after the solution runs.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

masterslave
Starting Member

22 Posts

Posted - 2008-03-04 : 01:21:36

Here it is! The table is called History.

Here's the structure:
HistoryId int
TableName nvarchar(50)
TableId int
DateCreated datetime
UserId int
Description nvarchar(255)

What I need is a number of records in the table
as per DateCreated for each date in the range...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 01:35:14

[code]SELECT DateCreated, COUNT(*)
FROM History
GROUP BY DateCreated[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-04 : 08:10:54

If datecol has time as well then

SELECT dateadd(day,datediff(day,0,DateCreated),0), COUNT(*)
FROM History
GROUP BY dateadd(day,datediff(day,0,DateCreated),0)

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-04 : 13:09:51

I love how I asked for sample data and we got the table structure.

masterslave, if the solutions provided aren't correct, then please provide sample data to illustrate your problem.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

masterslave
Starting Member

22 Posts

Posted - 2008-03-04 : 19:22:39

tkizer, what's wrong with providing table structure?
I thought it's better than sample data!

Thanks a lot guys!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-04 : 19:36:27

Table structure is helpful, however sample data is needed to illustrate your problem to us.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-04 : 20:13:21

quote:
Originally posted by masterslave

Here it is! The table is called History.

Here's the structure:
HistoryId int
TableName nvarchar(50)
TableId int
DateCreated datetime
UserId int
Description nvarchar(255)

What I need is a number of records in the table
as per DateCreated for each date in the range...


Select COUNT(*) as NumberofRecords, DateCreated
FROM History
Where DateCreated >= '20071201' and DateCreated <= '20080201'
Group by DateCreated

would be the number of records based on DateCreated column for each date in the range of dates specified?

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources