Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Trying to get data into one view Expand / Collapse
Author
Message
Posted Friday, September 7, 2012 3:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 4:42 AM
Points: 16, Visits: 40
Yes, it works.
Thanks, now I can dig into the script what it really does so I can also try to understand.
Thanks very much.
Post #1355854
Posted Friday, September 7, 2012 3:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 4:42 AM
Points: 16, Visits: 40
Yes, yours is working great now. thanks a lot
Post #1355857
Posted Friday, September 7, 2012 3:45 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:58 PM
Points: 3,422, Visits: 5,366
peter2501 (9/7/2012)
Yes, it works.
Thanks, now I can dig into the script what it really does so I can also try to understand.
Thanks very much.


You're welcome.

A brief explanation:

The Tally table is used to generate a set of "dummy" logins for each date in the range (the NULL Username ensures these aren't counted). In the query out of your table, I've truncated each login time to the day (without time stamp), so I can use this for GROUP and ORDER BY. The range of records starts at @Start but ends before @End + 1 day.

Hope that helps.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1355870
Posted Thursday, September 13, 2012 7:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 17, 2014 2:27 AM
Points: 418, Visits: 652
just to be clear.

What you want is a pivot table with months across the top (columns), days down the side (31 rows, include rows with no logins) and #logins as values in the intersect

Post #1358548
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse