Hi SQL guru,
I need to have a recursive query to return the data by each day.
There is an temp table to store the data for date interval selected by user. like startdate = '2016-01-10' and enddate = '2016-01-12'. then the temp table will store the data like
ID SD ED
1 2016-01-10 2016-01-11
2 2016-01-11 2016-01-12
3 2016-01-12 Null
Above table already created accordingly and my question comes from below logic
Ex. ;
SELECT DISTINCT brs.[BUILD_AGENT_ID]
, q.[TITLE]
, MAX(brs.[DURATION]/60/1000) OVER(PARTITION BY brs.[BUILD_AGENT_ID]) as [Maximum build duration]
FROM
[BUILDRESULTSUMMARY] AS brs
INNER JOIN [QUEUE] q on brs.[BUILD_AGENT_ID]=q.[QUEUE_ID]
WHERE brs.[BUILD_DATE] > (select t.[sd] from #temp t where t.[ID]=1) and brs.[BUILD_DATE] < (select t.[ed] from #temp t where t.[ID]=1) ( There is the place I need to do recursive from the temp table created above. the data should enumerate from from first ID to last one. I need to union all query with date interval )
Any help or suggestion is appreciated.
Thanks in advance
Roger