Include DateTime Even If No Corresponding Data

Question

Include DateTime Even If No Corresponding Data

JuanBob

SSCertifiable

Points: 5059
More actions
June 8, 2007 at 1:27 pm

#117279

I need to create a running count of LOGINS based upon the Year, Month, Day and Hour. If there are no logins for particular Hour, I still want to display the respective date, just with a zero value for logins.
What is the most efficient way to do this?
EXAMPLE:
2007 | 01 | 01 | 12:00 | 5 Logins
2007 | 01 | 01 | 1:00 | 0 Logins
2007 | 01 | 01 | 2:00 | 10 Logins

My dates currently are based upon the Date of the Login so that obviously will not work since only rows 1 and 3 from above will be returned in my result set.
As always, I appreciate any help!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

You can build a table that contains a record for each date and hour for the time period you want to query and left join it to your login table.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

You may want a Calendar table in your database. Do a search of thetopics and articles on this site, and I am sure you will find something to help you.

JuanBob SSCertifiable Points: 5059 More actions · Answer 3

JuanBob

SSCertifiable

Points: 5059

June 8, 2007 at 2:01 pm

#711795

Got it. I'll run through it. Thanks!

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

Lynn -- I wonder what kind of rounding the forum used to make your post show up first?

David McFarland SSChampion Points: 11815 More actions · Answer 5

David McFarland

SSChampion

Points: 11815

June 8, 2007 at 2:21 pm

#711800

Okay, now that was funny.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Lynn Pettis

SSC Guru

Points: 442467

June 8, 2007 at 2:57 pm

#711803

Yes, that was a true act of wit!

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 7

mrpolecat wins a piece of PIE for that one.....

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

David Burrows SSC Guru Points: 65142 More actions · Answer 8

And I bet is had nothing to do with 2/3

Far away is close at hand in the images of elsewhere.
Anon.

HARIKRISHNA-451864 Newbie Points: 9 More actions · Answer 9

Hi,

Did u get the query, if so can you please give the same to me . i also need this.

thanks in advance.

regards,

Hari.

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 10

No... no query... the recommendation was to use a "Calendar Table"... there are many to choose from if you search this site.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mrpolecat SSCertifiable Points: 7014 More actions · Answer 11

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

example here