
DB design/logic problem
I'm having a problem defining the logic for the following situation. <BR><BR>We store products for customers in our coldstore, the first half year we don't charge <BR>them anything, but when the product is longer in our coldstore we charge them per KG. <BR><BR>The design is not done yet but I'm thinking of a table like this: <BR><BR><BR><BR>CREATE TABLE [Transactions] (<BR> [CustomerId] [varchar] (50) NOT NULL ,<BR> [TransactionType] [char] (1) NOT NULL ,<BR> [TransactionDate] [smalldatetime] NOT NULL ,<BR> [KG] [int] NOT NULL <BR>) <BR><BR><BR><BR>This table stores the transactions made for that specific customer. <BR>A transactionType would be I for Input or O for output. This is a first idea and can <BR>be changed if neccesary. <BR><BR>The problem I'm having is how to calculate what our customers have to pay every period (a week, a month). <BR><BR>Assuming the following transactions: <BR><BR><BR><BR>INSERT Transactions (CustomerId, TransactionType, Transactiondate, KG) <BR> VALUES ('cust1', 'I', '20030101',1000)<BR>INSERT Transactions (CustomerId, TransactionType, Transactiondate, KG) <BR> VALUES ('cust1', 'I', '20030201',500)<BR>INSERT Transactions (CustomerId, TransactionType, Transactiondate, KG) <BR> VALUES ('cust1', 'O', '20030115',500)<BR>INSERT Transactions (CustomerId, TransactionType, Transactiondate, KG) <BR> VALUES ('cust1', 'O', '20030401',250)<BR>INSERT Transactions (CustomerId, TransactionType, Transactiondate, KG) <BR> VALUES ('cust1', 'I', '20030501',2000)<BR>INSERT Transactions (CustomerId, TransactionType, Transactiondate, KG) <BR> VALUES ('cust1', 'O', '20030703',2000)<BR><BR><BR><BR>and assuming we charge 1 cent per kg per day,  here comes the q. <BR><BR>What would we invoice to our customer for the first week of july? <BR>Ideally I'd like a stored proc which takes one parameter (weeknumber) and it returns the amount due for every customer. <BR><BR>N.B. output transactions are based on the FIFO (first in first out) principle <BR><BR>I hope this made sense...<BR><BR>If you have any questions let me know. <BR><BR>Peter <BR><BR><BR>(I posted this also here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25598 but I'm having trouble explaining what exactly I need, maybe here someone understands the problem)

RE: DB design/logic problem
First of all, you could eliminate the TransactionType column (or keep it just for reference), and when it's an Output then put a negative amount in the KG. That way you can find the "running total" just from a SUM of that column.<BR><BR>One way to do the calculation in a SP would be to create a temp table with columns FromDate,TotalKG,Days<BR><BR>First insert a row for the situation on the first day of the required daterange (which i shall call @DateFrom):<BR>INSERT INTO #temp (FromDate,TotalKG)<BR>VALUES(@DateFrom,(SELECT SUM(KG) FROM Transactions WHERE TransactionDate<=@DateFrom AND CustomerID=@CustomerID))<BR><BR>Then insert runningtotal rows for each transaction during the daterange:<BR>INSERT INTO #temp (FromDate,TotalKG)<BR>SELECT t1.TransactionDate,SUM(t2.KG) FROM Transactions t1 JOIN Transactions t2 ON t2.TransactionDate<=t1.TransactionDate WHERE <BR>t1.TransactionDate BETWEEN @DateFrom AND @DateTo<BR>GROUP BY t1.TransactionDate<BR><BR>Finally run an UPDATE statement to calculate the number of days each row applies for:<BR>UPDATE #temp SET Days=DATEDIFF(dd,FromDate,ISNULL((SELECT MAX(FromDate) FROM #temp t2 WHERE t2.FromDate<t1.FromDate),@DateTo)) FROM #temp t1<BR><BR>We're just about done: now you can select SUM(Days*TotalKG) from the temp table.<BR><BR>There's probably several slight mistakes in a couple of these queries, particularly in getting the exact dates right, you'll have to check the logic. But shoudln't be anything that brings this approach grinding to a halt.<BR><BR>Hope this helps!

RE: DB design/logic problem
Ian, thanks for your response. <BR><BR>I think you made a valid point with your remark on removing the TransactionType column. I don't need it for anything. <BR><BR>I can see where you're going with your solution, but it doesn't take the 6 month 'waiting period' into account, does it.<BR><BR>I'll try to play with this a bit.<BR><BR>Please don't reply here anymore, as I've also posted this on SQLTeam.com. (see my original post)<BR><BR>(so I shouldn't have posted it here also you say, well I agree, and I won't do it again)
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

