-- Prepare sample data
DECLARE	@Sample TABLE
	(
		Date DATETIME,
		Name VARCHAR(20),
		Amt MONEY
	)

INSERT	@Sample
SELECT	'1-Jan-09', 'A', 100 UNION ALL
SELECT	'2-Jan-09', 'A', 200 UNION ALL
SELECT	'3-Jan-09', 'A', 300 UNION ALL
SELECT	'4-Jan-09', 'A', 600 UNION ALL
SELECT	'5-Jan-09', 'A',  50 UNION ALL
SELECT	'6-Jan-09', 'A',  50

-- Solution by Peso
SELECT		DATEADD(DAY, -v.Number, s.Date) AS theDate,
		s.Name AS theName,
		SUM(s.Amt) AS theSum
FROM		@Sample AS s
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
			AND v.Number < 5
GROUP BY	DATEADD(DAY, -v.Number, s.Date),
		s.Name
HAVING		MIN(v.Number) = 0
		AND SUM(s.Amt) > 500
ORDER BY	DATEADD(DAY, -v.Number, s.Date)

quote:

---

Originally posted by jai2808

Hi,
I need to generate a record set based on the logic mentioned below.
Date Name Amt
1-Jan-09 A 100
2-Jan-09 A 200
3-Jan-09 A 300
4-Jan-09 A 600
5-Jan-09 A 50
6-Jan-09 A 50

I need to get only those records where when i add 5 days to date and the sum of amount is more then 500.
for eg., for first record, i add 5 days and check the sum of amt then the first record should be the part of resultset.
For second record, i add 5 days and the amount is more than 500.
But for 5th record, i add 5 days and check the sum of amount then i get only 100.

Can some one help how to get the logic.
I am looping through each row which is taking lot of time as we have nearly 2 lac records.

---

SELECT t.*
FROM YourTable t
CROSS APPLY (SELECT SUM(Amt) AS Total
             FROM YourTable
             WHERE Name = t.Name
              AND Date >= t.Date
              AND Date <= DATEADD(dd,5,t.Date)
            )t1
WHERE t1.Total >=500

2009-01-01 00:00:00.000	A	1250,00
2009-01-02 00:00:00.000	A	1200,00
2009-01-03 00:00:00.000	A	1000,00
2009-01-04 00:00:00.000	A	700,00

2009-01-01 00:00:00.000	A	100,00
2009-01-02 00:00:00.000	A	200,00
2009-01-03 00:00:00.000	A	300,00
2009-01-04 00:00:00.000	A	600,00