SQL Server Performance Forum – Threads Archive

Complex date group by – maybe…

Okay, the last challenge was simple and worked wonderfully. Here is one that seems slightly more complicated. I have a view that shows me the date a record is added (dateadded) and the date a record is activated (activation_dt). I need to return the number of items added and the number of items activated within a certain period of time, grouped by date. However, not grouped by activation_dt or dateadded – per se. I’ve included some simple SQL below. Basically, I calculate whether something is added by a sum on the dateadded between the specified period. I calculate an activation based on the sum of activation_dt between the specified period. The challenge is that I want to group by each day between the specific from and to date and return the activation or added date appropriately. If I group on either date, the number I show for the other date field will be skewed. Or am I over-complicating this? In fact, would I need to use the current iterated date in my calculations in the query. If further explanation is needed, let me know. Thanks or sorry, not sure which. Declare @dtfrom datetime
Declare @dtTo datetime
Set @dtfrom = ’07/08/2005′
Set @dtTo = ’07/20/2005′ Select {current_group_date}, sum(case when (dateadded between @dtfrom and @dtTo) then 1 else 0 end) as qtyAdded,
Sum(case when (Activation_dt between @dtfrom and @dtTo) Then 1 Else 0 end) as qtyActivated
From vwAddActivated
WHERE (dateadded between @dtfrom and @dtTo or activation_dt between @dtfrom and @dtTo)
Group By {would like the sequential dates based on the period above}
Matthew Moran
The IT Career Builder’s Toolkit
http://www.cbtoolkit.com

Can you post some sample data with expected result?
Madhivanan Failing to plan is Planning to fail

Yes, you should give some more informations. From your description, I don’t understand what you’re after. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

]]>