DB design/logic problem

1. Senior Member
Join Date
Dec 1969
Posts
7,686

## DB design/logic problem

I&#039;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&#039;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&#039;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&#039;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 (&#039;cust1&#039;, &#039;I&#039;, &#039;2003-01-01&#039;,1000)<BR>INSERT Transactions (CustomerId, TransactionType, Transactiondate, KG) <BR> VALUES (&#039;cust1&#039;, &#039;I&#039;, &#039;2003-02-01&#039;,500)<BR>INSERT Transactions (CustomerId, TransactionType, Transactiondate, KG) <BR> VALUES (&#039;cust1&#039;, &#039;O&#039;, &#039;2003-01-15&#039;,500)<BR>INSERT Transactions (CustomerId, TransactionType, Transactiondate, KG) <BR> VALUES (&#039;cust1&#039;, &#039;O&#039;, &#039;2003-04-01&#039;,250)<BR>INSERT Transactions (CustomerId, TransactionType, Transactiondate, KG) <BR> VALUES (&#039;cust1&#039;, &#039;I&#039;, &#039;2003-05-01&#039;,2000)<BR>INSERT Transactions (CustomerId, TransactionType, Transactiondate, KG) <BR> VALUES (&#039;cust1&#039;, &#039;O&#039;, &#039;2003-07-03&#039;,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&#039;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&#039;m having trouble explaining what exactly I need, maybe here someone understands the problem)

2. Senior Member
Join Date
Dec 1969
Posts
342

## RE: DB design/logic problem

First of all, you could eliminate the TransactionType column (or keep it just for reference), and when it&#039;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&#060;=@DateFrom AND CustomerID=@CustomerID))<BR><BR>Then insert running-total 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&#060;=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&#060;t1.FromDate),@DateTo)) FROM #temp t1<BR><BR>We&#039;re just about done: now you can select SUM(Days*TotalKG) from the temp table.<BR><BR>There&#039;s probably several slight mistakes in a couple of these queries, particularly in getting the exact dates right, you&#039;ll have to check the logic. But shoudln&#039;t be anything that brings this approach grinding to a halt.<BR><BR>Hope this helps!

3. Senior Member
Join Date
Dec 1969
Posts
7,686

## 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&#039;t need it for anything. <BR><BR>I can see where you&#039;re going with your solution, but it doesn&#039;t take the 6 month &#039;waiting period&#039; into account, does it.<BR><BR>I&#039;ll try to play with this a bit.<BR><BR>Please don&#039;t reply here anymore, as I&#039;ve also posted this on SQLTeam.com. (see my original post)<BR><BR>(so I shouldn&#039;t have posted it here also you say, well I agree, and I won&#039;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
•