Saving cumulative data or something like that :)

Hi,
There are 3 tables
Table,
TableDetails,
TableDaily.

With structure

TABLE:

TableID UserID Money
---- ---- ----
(int) (int) (money)

TABLEDETAILS:

TableDetailsID TableID ItemID PaidForItem DayID
---- ---- ---- ---- ----
(int) (int) (int) (money) (int)

TABLEDAILY:

TableDailyID TableID PaidForItem Money Total Change
---- ---- ---- ---- ---- ----
(int) (int) (money) (money) (PaidForItem + Money) (money)

"Table" holds id for user and his money amount, which changes during time. "TableDetails" holds data about items user bought, amount paid for them and dayid which relates to one particular day.

"TableDaily" holds history. I do not know how to update this table.
I created job whish runs stored procedure. This procedure sums "PaidForItem" using group by TableID and WHERE DAYID = '11'.

Problem is with Change column. This column sould hold difference between today's Total and previous one etc.

Current procedure looks like this:
INSERT INTO TableDaily
(TableID, PaidForItem, Money, DayID)
SELECT TableDetails.TableID,
SUM(PaidForItem) AS PaidForItem,
Table.Money,
(SELECT DayID
FROM Days
WHERE (Aktive = 1)) AS DayID
FROM TableDetails INNER JOIN
Table ON TableDetails.TableID = Table.TableID
GROUP BY TableDetails.TableID, Table.MoneyShould TABLEDAILY have a DayId column? Or maby even 2 since Day table has an active column and difference could be calculated over several calender days.|||

Hi,

Try the following code for finding the change, u can add this to your exisiting SP

1declare @.changemoney2select @.change=t2_total-t1_totalfrom3(select t1.Total , t2.Total4from TABLEDAILYas t15leftouter join TABLEDAILYas t26on t2.TableDailyID= (select top 1 TabledailyIDfrom TABLEDAILY7where TabledailyID > t1.TabledailyID8and DayID=@.DayID)where t1.DayID=@.DayID) tab3

This is the general idea, here you can update the TABLEDAILY with @.change for the corresponding @.DayID..

|||

Hi, thanks for trying to help.

Yes, there should be one column with DayID. There is no need for 2, because I will use TabelDaily to calculate changes on demand for periods like week, month etc. For now I just want to have values from last day and changes compared to day before.

Besides column Change there is RelativeChange which will hold percentage value, but that is something that would be easy to implement once when I solve original problem.