Geek Answers Handbook

SQL Query for Date Range, multiple start and end times

Microsoft SQL Server has a table with the record identifier, start date, end date, and quantity.

The idea is that for each record, the number / amount of days in the range = daily value.

Given that there is a table containing all possible dates, how can I create a result set in SQL Server to look like this?

Example:

RecordID | Start Date | End Date  | Quantity
1        |  1/1/2010  | 1/5/2010  | 30000
2        |  1/3/2010  | 1/9/2010  | 20000
3        |  1/1/2010  | 1/7/2010  | 10000

Results as
1        | 1/1/2010 |  QTY (I can do the math easy, just need the dates view)
1        | 1/2/2010 | 
1        | 1/3/2010 | 
1        | 1/4/2010 | 
1        | 1/3/2010 | 
2        | 1/4/2010 | 
2        | 1/5/2010 | 
2        | 1/6/2010 | 
2        | 1/7/2010 | 
2        | 1/8/2010 | 
2        | 1/9/2010 | 
3        | 1/1/2010 | 
3        | 1/2/2010 | 
3        | 1/3/2010 | 
3        | 1/4/2010 | 
3        | 1/5/2010 | 
3        | 1/6/2010 | 
3        | 1/7/2010 |

Grouping by dates that I could get, then get the amount amount on that day, however, the final result set cannot be aggregated due to user-provided filters that may exclude some of these entries along the way.

EDIT

, . , , , .

N , , . 2000 ... . , , .

, . . , 50 , MAXRECURSION , .

0
2

select RecordId,d.[Date], Qty/ COUNT(*) OVER (PARTITION BY RecordId) AS Qty
from EX join Dates d on d.Date between [Start Date] and [End Date]
ORDER BY RecordId,[Date]

: CTE date, SQL Server 2008, SQL2005.

/*CTEs for testing purposes only*/

WITH EX AS
(
    SELECT 1 AS RecordId, 
    cast('1/1/2010' as date) as [Start Date], 
    cast('1/5/2010' as date) as  [End Date], 
    30000 AS Qty
union all
    SELECT 2 AS RecordId, 
    cast('1/3/2010' as date) as [Start Date], 
    cast('1/9/2010' as date) as  [End Date], 
    20000  AS Qty
),Dates AS /*Dates Table now adjusted to do greater range*/
(

SELECT  DATEADD(day,s1.number + 2048*s2.number,'1990-01-01') AS [Date] 
FROM master.dbo.spt_values s1 CROSS JOIN master.dbo.spt_values s2
where s1.type='P' AND s2.type='P' and s2.number <= 8
order by  [Date] 
)


select RecordId,d.[Date], Qty/ COUNT(*) OVER (PARTITION BY RecordId) AS Qty
from EX join Dates d on d.Date between [Start Date] and [End Date]
ORDER BY RecordId,[Date]


RecordId    Date       Qty
----------- ---------- -----------
1           2010-01-01 6000
1           2010-01-02 6000
1           2010-01-03 6000
1           2010-01-04 6000
1           2010-01-05 6000
2           2010-01-03 2857
2           2010-01-04 2857
2           2010-01-05 2857
2           2010-01-06 2857
2           2010-01-07 2857
2           2010-01-08 2857
2           2010-01-09 2857
+3

, .

SELECT [Quantities].[RecordID], [Dates].[Date], SUM([Quantity])
FROM [Dates]
JOIN [Quantities] on [Dates].[Date] between [Quantities].[Start Date] and [End Date]
GROUP BY [Quantities].[RecordID], [Dates].[Date]
ORDER BY [Quantities].[RecordID], [Dates].[Date]
+1

More articles:


All Articles