sum data by field1 every 4 weeks

[mauu]

for simplicity sake I'm going to dumb this down and hope I can apply it to the more complicated situation I'm am looking at.
I have a dataset with 3 fields: customer, date, dollaramount
I want to gather the dollaramount in 4 week intervals for each customer.

 

[gmmastros]

I think you dumbed it down too much. Please post sample data and expected results.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Lamprey13]

This might help get you started:

DECLARE @T TABLE (CustID INT, Date DATETIME, Amount MONEY)

INSERT @T
SELECT 1, CURRENT_TIMESTAMP - 10, $1.00
UNION ALL SELECT 1, CURRENT_TIMESTAMP - 10, $2.00
UNION ALL SELECT 1, CURRENT_TIMESTAMP - 30, $5.00
UNION ALL SELECT 1, CURRENT_TIMESTAMP - 29, $9.00
UNION ALL SELECT 1, CURRENT_TIMESTAMP - 50, $5.00
UNION ALL SELECT 1, CURRENT_TIMESTAMP - 49, $99.00
UNION ALL SELECT 1, CURRENT_TIMESTAMP - 100, $123.00
UNION ALL SELECT 1, CURRENT_TIMESTAMP - 99, $-5.00
UNION ALL SELECT 1, CURRENT_TIMESTAMP - 400, $13.00
UNION ALL SELECT 1, CURRENT_TIMESTAMP - 400, $13.00

SELECT 
	CustID,
	DATEPART(YEAR,Date),
	DATEPART(WEEK, Date),
	SUM(Amount)
FROM
	@T
GROUP BY
	CustID,
	DATEPART(YEAR,Date),
	DATEPART(WEEK, Date)

 

[onpnt]

We need to know what the 4 week increment is based off of. Is it a datetime field? Are you concerned with the remainder of the month after the 4 week increment? I would think you would be.

Either way MOD based off the increment (and if you want the remainder of the month i.e. 29-31) then get the last day of the month after mod 28

But yeah. I'm dumb and thats dumbed down too much

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]