report with closing periods in another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

report with closing periods in another table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-20 : 09:51:57

stockcowboy writes "Whats the best way to do SQL reporting where you have a table with transaction amounts and transaction datetimes and then another table with closing periods date times in it that you need to do going forward reporting based on those closing datetimes. For instance, lets say our closing periods are every thrusday night at 1 am, so in the closing period table i looks like this.

12/4/2003 1:00 am
12/11/2003 1:00 am
2/12/2004 1:00 am
2/19/2004 1:00 am
2/26/2004 1:00 am

Then in my transaction table looks somthing like this

12/4/2003 1:10 543
12/6/2003 2:10 22
12/7/2003 17:10 55
12/8/2003 1:15 331
12/12/2003 18:10 232

How do I produce a report that would show amounts grouped by the thursday datetime closing periods - going backwords until the next preivious closing period. The report must not show money behind any closing period, only after until the next period close - does this make sense? Its for a financial/accounting app where there are many various types of closing periods and we never want to payout the transactions until the weekly release date/time occurs and the previous weeks funds would be released.

I'd prefer not to use "set datefirst" and there has to be a way to do this without a cursor

This is my query below that works - but doesn't use a more flexable 2nd table to hold the period ending datetimes, encase of downtimes or holidays.

SET DATEFIRST 5
SELECT
TRANSACTION_RESERVE_RELEASE_DATE = (Cast(floor(Cast(TRANSACTION_RESERVE_RELEASE_DATE as float)) as smalldatetime) + 7 - (DatePart(dw, Cast(floor(Cast(TRANSACTION_RESERVE_RELEASE_DATE as float)) as smalldatetime)) % 7)),
TRANSACTION_RESERVE_HOLD_AMOUNT = SUM(TRANSACTION_RESERVE_HOLD_AMOUNT)
FROM dbo.GIFT_CARD_TRANSACTION
WHERE CLIENT_ID = @CLIENT_ID AND
TRANSACTION_STATUS = 1 AND
TRANSACTION_RESERVE_RELEASED = 0
GROUP BY (Cast(floor(Cast(TRANSACTION_RESERVE_RELEASE_DATE as float)) as smalldatetime) + 7 - (DatePart(dw, Cast(floor(Cast(TRANSACTION_RESERVE_RELEASE_DATE as float)) as smalldatetime)) % 7))
ORDER BY (Cast(floor(Cast(TRANSACTION_RESERVE_RELEASE_DATE as float)) as smalldatetime) + 7 - (DatePart(dw, Cast(floor(Cast(TRANSACTION_RESERVE_RELEASE_DATE as float)) as smalldatetime)) % 7))
SET DATEFIRST 7

--RESULTS OF ABOVE
12/4/2003 0:00 10210.5
12/11/2003 0:00 33033.8
12/18/2003 0:00 23235.5
1/8/2004 0:00 0.1
1/22/2004 0:00 0.1
1/29/2004 0:00 0.1
2/12/2004 0:00 2950.5
2/19/2004 0:00 27863.5
2/26/2004 0:00 21599
3/4/2004 0:00 19118.049
3/11/2004 0:00 17548.775
3/18/2004 0:00 13564.9
3/25/2004 0:00 19293.6
4/1/2004 0:00 32235.65
4/8/2004 0:00 21835.8
4/15/2004 0:00 20633.6
4/22/2004 0:00 18988.8
4/29/2004 0:00 11882.4
5/6/2004 0:00 26695.8
6/3/2004 0:00 45.4"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources