October 6, 2011 at 2:47 pm
Ok so I am stuck on a query - basically I have a table which stores various money transactions and I need to return the cumulative total amount taken at the start of the day (up until the start of that day) and the total amount taken at the end of the day (both the individual amount for the day, and this amount added to the start amount to give the 'money position' at the end that day).
The next days start amount would need to be the day before's end amount, obviously.
This needs to be cumulative, so the first day the starting amount would be NULL as there are no transactions prior to that day.
Also bear in mind there might not be transactions every day, but I want it to show a row for every single day (even if there are no transactions on that day) in the date range of the MIN and MAX transaction dates. I also would like to be able to filter this result set by individual dates and date ranges if possible.
Here is a small test platform I made:
CREATE TABLE transTest (
transID int NOT NULL
,transDate datetime NOT NULL
,amount money NOT NULL
,CONSTRAINT [PK_transTest] PRIMARY KEY(transID)
INSERT INTO transTest (transID,transDate,amount)
VALUES
(1,'2011-10-02 00:00:00.000',-20.00)
,(2,'2011-10-03 00:00:00.000',40.00)
,(3,'2011-10-04 00:00:00.000',-60.00)
,(4,'2011-10-04 00:00:00.000',40.00)
,(5,'2011-10-06 00:00:00.000',20.00)
I have spent most of today thinking of a solution but haven't had much luck, its a bit beyond my skill set at the moment. I could knock something together using while loops and variables, but I would like to figure out (hopefully with your help) a faster set based solution as IRL this query would be pulling data from a large and quickly growing table - so speed is quite important.
October 6, 2011 at 2:51 pm
Based on the sample data what is your expected results and what are the criteria?
October 6, 2011 at 2:59 pm
Lynn Pettis (10/6/2011)
Based on the sample data what is your expected results and what are the criteria?
Hi Lynn, run this to see expected result set based on sample data.
SELECT '2011-10-02 00:00:00.000' [DATE],NULL [START],-20.00 [TAKEN],-20.00 [END] UNION ALL
SELECT '2011-10-03 00:00:00.000',-20.00,40.00,20.00 UNION ALL
SELECT '2011-10-04 00:00:00.000',20.00,-20.00,0.00 UNION ALL
SELECT '2011-10-05 00:00:00.000',0.00,0.00,0.00 UNION ALL
SELECT '2011-10-06 00:00:00.000',0.00,20.00,20.00
October 6, 2011 at 7:01 pm
WITH CTEDATE AS (
SELECT DATEADD(DAY,num-2,(SELECT MIN(transDATE) FROM transTest)) [date]
FROM dbo.Nums
WHERE num <= DATEDIFF(DAY,(SELECT MIN(transDATE) FROM transTest),(SELECT MAX(transDATE) FROM transTest)) + 2
),CTEAMT AS (
SELECTrow_number() over(order by b.[date]) [rownum]
,B.[date]
,isnull(SUM(amount),0) [amt]
FROM transTest A
RIGHT JOIN CTEDATE B
ON A.transDate = B.[date]
GROUP BY B.[date])
SELECT[date]
,(select [amt] from CTEAMT d where c.rownum = d.rownum + 1) [startAmount]
,[amt]
,(select [amt] from CTEAMT d where c.rownum = d.rownum + 1) + [amt] AS [endAmount]
FROM CTEAMT c
WHERE c.rownum <> 1
ORDER BY c.[date]
This is what I have so far, but its returning me this:
2011-10-02 00:00:00.0000 -20.00 -20.00
2011-10-03 00:00:00.000-20.0040.00 20.00
2011-10-04 00:00:00.00040.00 -20.00 20.00
2011-10-05 00:00:00.000-20.000.00 -20.00
2011-10-06 00:00:00.0000.00 20.00 20.00
The bolded 40 is wrong, it should be 20 as that is the previous row's 3rd column.
October 6, 2011 at 7:03 pm
I think I see what's happening now, its taking the amount as opposed to the calculated amount so for the first two rows coincidentally it was correct but once you start putting more numbers into the mix it goes wrong.
So I need to find a way to tell it to use the calculated amount from the previous row, ouch 🙁
Another late night, 2am now - must sleep!
October 10, 2011 at 12:53 am
We could come up with the below query which sounds like a passion ambition to proceed such complex finical operations for huge data entity with the optimal performance
create table #Amounts (transdate datetime , start_amount int, end_amount int)
create clustered index #Amounts_index1 on #Amounts (transdate asc)
insert into #Amounts (transdate) select transDate from transTest
update #Amounts set start_amount = amount_first from (select SUM(s1.amount)as amount_first,S2.transDate from (select amount , transDate from transTest) S1 inner join transTest S2 on S2.transDate>s1.transDate group by S2.transDate)S3 INNER JOIN #Amounts ON S3.transDate=#Amounts.transdate
update #Amounts set end_amount = amount_final from (select SUM(s1.amount)as amount_final,S2.transDate from (select amount , transDate from transTest) S1 inner join transTest S2 on S2.transDate=s1.transDate group by S2.transDate)S3 INNER JOIN #Amounts ON S3.transDate=#Amounts.transdate
select * , (isnull(end_amount,0)+isnull(start_amount,0)) as Gross_Benefit from #Amounts
drop table #Amounts
But you have also to create a nonclustered index on the main table as next :
Create nonclustered index transTest_index1 on transTest (transDate asc) include (amount)
And if needed more performance , you have to apply page compression for that index
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply