quote:

---

Originally posted by rchau861

Hi guys...... please treat me as a SQL noobie

I have a question.

Lets take a simple query

select date
,amount
,category

from abc
order by date desc

Output looks like this:

Date Amount Category
4/13/2012 1000 a
4/12/2012 800 b
4/11/2012 700 c
4/10/2012 650 c
4/09/2012 600 d
4/05/2012 500 a


Simple right....... Lets treat these days as business days. I need to now include non business days as well. (weekends and holidays)

The requirement is now that if the date does not exist, take the last known business day and copy that record and date it with the non business day

So using the information provided and using the date 4/06/2012 as an example, I would need to copy the record with 4/05/2012 and insert it as 4/06/2012. The same would apply to 04/07/2012 and 04/08/2012 that would need to "copy" the last known business date being 04/05/2012 and dating it with 04/07/2012 and 04/08/2012 respectively.

Desired output:
Take the above table or result set. I want this to display the missing dates of 4/6,4/7,4/8 with the last known business date being 4/05/2012.

How do I accomplish this?
How do I copy a row and change the date of that row?

Desired Results

Date Amount Category
4/13/2012 1000 a
4/12/2012 500 b
4/11/2012 700 c
4/10/2012 650 c
4/09/2012 600 d
4/08/2012 500 a
4/07/2012 500 a
4/06/2012 500 a
4/05/2012 500 a



Summary:
I am creating a report in desc order by date


Possible Solutions:
Do i need to insert my result set into a temp table then use a cursor to step through each record and check if datediff(day,date,cursor date) > 1 ?? ( sorry my poor coding its been really long) If it is greater then 1 then I know it satisfies the condition of a "non business day"


Any sample code would be appreciated..... please be simple for i am a noobie and forgive me if i accidently insulted you SQL gurus out there with my ignorant question.

---

DECLARE @Min datetime,@Max datetime

SELECT @MIN=MIN([Date]),@MAX=MAX([Date])
FROM Yourtable

SELECT [Date],[Amount],[Category]
FROM YourTable

UNION ALL

SELECT f.[Date],t1.Amount, t1.Category
FROM dbo.CalendarTable(@MIN,@MAX,0,0) f
LEFT JOIN YourTable t
ON f.[Date] = t.[Date]
CROSS APPLY (SELECT TOP 1 Amount, Category
             FROM YourTable
             WHERE [Date] < f.[Date]
             ORDER BY [Date] DESC
            )t1
WHERE t.[Date] IS NULL
ORDER BY [Date]