Complex date group by – maybe… | SQL Server Performance Forums

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
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
Ich unterstütze PASS Deutschland e.V.