Design question - SQL Server Forums

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)

Design question

Author

Topic

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-04-22 : 08:21:25

I'm having a problem defining the logic for the following situation.

We store products for customers in our coldstore, the first half year we don't charge
them anything, but when the product is longer in our coldstore we charge them per KG.

The design is not done yet but I'm thinking of a table like this:


CREATE TABLE [Transactions] (
	[CustomerId] [varchar] (50)  NOT NULL  ,
	[TransactionType] [char] (1)  NOT NULL ,
	[TransactionDate] [smalldatetime] NOT NULL ,
	[KG] [int] NOT NULL 
)

This table stores the transactions made for that specific customer.
A transactionType would be I for Input or O for output. This is a first idea and can
be changed if neccesary.

The problem I'm having is how to calculate what our customers have to pay every period (a week, a month).

Assuming the following transactions:


INSERT Transactions (CustomerId, TransactionType, Transactiondate, KG) 
	VALUES ('cust1', 'I', '2003-01-01',1000)
INSERT Transactions (CustomerId, TransactionType, Transactiondate, KG) 
	VALUES ('cust1', 'I', '2003-02-01',500)
INSERT Transactions (CustomerId, TransactionType, Transactiondate, KG) 
	VALUES ('cust1', 'O', '2003-01-15',500)
INSERT Transactions (CustomerId, TransactionType, Transactiondate, KG) 
	VALUES ('cust1', 'O', '2003-04-01',250)
INSERT Transactions (CustomerId, TransactionType, Transactiondate, KG) 
	VALUES ('cust1', 'I', '2003-05-01',2000)
INSERT Transactions (CustomerId, TransactionType, Transactiondate, KG) 
	VALUES ('cust1', 'O', '2003-07-03',2000)

and assuming we charge 1 cent per kg per day, - here comes the q.-

What would we invoice to our customer for the first week of july?
Ideally I'd like a stored proc which takes one parameter (weeknumber) and it returns the amount due for every customer.

N.B. output transactions are based on the FIFO (first in first out) principle

I hope this made sense, I also hope this is even a SQL related problem.

If you have any questions let me know.

Peter

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-22 : 09:11:59

Something like this

DECLARE @date DATETIME
DECLARE @Week SMALLINT

SELECT @Week = 50
SELECT @date = DateAdd(wk,@Week,DateAdd(wk,-DatePart(wk,GetDate()),getdate()))

SELECT
CustomerID,
TransactionType,
KG,
DATEDIFF(m,TransactionDate,@date) NumMonthsInStorage,
DATEDIFF(d,TransactionDate,@date) NumDaysInStorage,
DATEDIFF(d,TransactionDate,@date) * KG /100.00 ChargeAmountInDollars
FROM transactions
WHERE DATEDIFF(m,TransactionDate,@date) > 6

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-22 : 09:13:05

Some questions that leave me hanging.

If a customer has 100 Kg in coldstore, it seems to me that the problem is simply calculate the Kg, multiply by 1 cent / day * 7 days, that's his invoice amount for the week? How do you account for a customer having paid last month's bill?

SELECT CustomerID, Sum(kg) AS Weight, Sum(kg * 0.01 * (DATEDIFF(dd, TransactionDate, GETDATE())-(26*7))) AS Amount

FROM Transactions

WHERE DATEDIFF(WK, TransactionDate, GETDATE()) > 26 -- 26 weeks in half year

GROUP BY CustomerID

I've tried to return the Customer, Weight and dollar Amount for anything in storage over 6 months ( I had to assume 6 months as 26 weeks, or 26*7 days)

I'm sure this isn't exactly right, hope it helps.

Sam

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-04-22 : 09:41:26

Thanks for both your replies but I think ~~you misunderstood~~ I explained not good enough.

It's not just checking which transactions are dated more than 6 months ago.

quote:

If a customer has 100 Kg in coldstore, it seems to me that theproblem is simply calculate the Kg, [..]

Well, if a customer has 100 Kg in coldstore it's not simply multplying that 100 Kg. We only need to send an invoice for the Kg that have been longer than 6 months in our coldstore.

I hope I've made myself clear. Thanks again for your trouble.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-22 : 09:45:11

Group By Customer, TransactionDate

should create on row for each customer's transaction

The where clause filters any transactions that have not been in cold storage for more than 6 months.

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-22 : 09:51:30

Peter -

I'd be interested in seeing the solution when you finish testing.

Did either of the above solutions help?

Sam

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-04-22 : 09:51:59

quote:
The where clause filters any transactions that have not been in cold storage for more than 6 months

True, but I can't put a where clause on the transaction date.

Let's assume the two following transactions

2003-01-01 I 1000 Kg
2003-04-01 O 500 Kg

and I want to calculate the invoice for 2003-07-07.

In this case I can't apply a where clause of DATEDIFF(m,GetDate()) > 6 since that would eliminate the second transaction. I do need this transaction since the customer only needs an invoice for 500 Kg

(because we only send an invoice for products that have been in store > 6 months)

Peter

I think I've hit the language barrier here to explain myself any bettter.....

[edit]
Sam, the solutions didn't work but that's I think because I didn't explain myself well enough.

I'm not sure how I could explain any better though...1

[/edit]

Edited by - Peter Dutch on 04/22/2003 09:53:31

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-22 : 10:00:30

You mean you still want to send the invoice but with $0 dollars charge for less than 6months?

I would be a matter of using a case statement instead of a where clause

SELECT
CustomerID,
Sum(kg) AS Weight,
CASE
WHEN DATEDIFF(WK, TransactionDate, GETDATE()) > 26 THEN
Sum(kg * 0.01 * (DATEDIFF(dd, TransactionDate, GETDATE())-(26*7)))
ELSE 0.00
AS Amount
END
FROM Transactions
GROUP BY CustomerID, TransactionDate

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-22 : 10:04:48

We have found the enemy and it is us....

Lemme state this problem klearly... Correct me if this is not right.

You need to track individual insertions ("I") which can have withdrawls("O") over time.

It's important to know if each insertion ("I") is unique or does it contribute to the initial insertion?

For example: Customer inserts 100 Kg on Jan 1. On June 30, he inserts 10,000 Kg. Do you bill him for 10,100 Kg the first week of July? I don't think so. This means you must have an identifier for each insertion (a key?) which is not yet present in the table.

From there, each removal ("O") would need to be applied to a specific insertion. Removals must also identify the insertion KEY.

With this information, a single query can calculate what a customer owes for each insertion that is older than 6 months.

Sam

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-04-22 : 10:10:33

Sam,

>> You need to track individual insertions ("I") which can have withdrawls("O") over time.

agreed

>> It's important to know if each insertion ("I") is unique or does it contribute to the initial insertion?

it contributes to the initial insertion

>> This means you must have an identifier for each insertion (a key?) which is not yet present in the table.

I don't think so, see below

>>From there, each removal ("O") would need to be applied to a specific insertion.

No, that's why I stated the FIFO, first in first out principle.
The first "O" is deducted from the first "I" until the first "I" is completely gone, than every "O" is deducted from the subsequent "I"'s.

We don't want to enter a specific "I" when we enter an "O".

Valter, I didn't look into your solution yet, I thought it would be a good idea to answer Sam's questions first so I'm sure we're all talking about the same thing

[edit]
typo
[/edit]

Edited by - Peter Dutch on 04/22/2003 10:11:54

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-22 : 10:12:02

Damn I missed the whole I/O part?

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-04-22 : 10:13:12

Valter, LOL yeah I think you did....

makes it a bit more difficult doesn't it?

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-22 : 10:17:23

This is nothing at all!

Gimme a minute!

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-22 : 10:36:26

SELECT CustomerID,

SUM(
CASE WHEN DATEDIFF(wk, TransactionDate, GETDATE()) > 26 AND TransactionType = 'I' THEN Kg ELSE 0 END
) AS Input,

SUM(
CASE WHEN TransactionType = 'O' THEN Kg ELSE 0 END
)

FROM Transactions

GROUP BY CustomerID

Nuts - got to go. Plumber's here to fix stuff.

The query above tallys what's old weight, and what's removed. The difference is what should be billed.

I'll check in later.

Sam

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-22 : 10:41:49

I don't think so it needs to be based on dates you can't just sum up all the outputs and subtract from the inputs because they were input and outputed on different dates. The solution will be more like what a bank does to calculate interest.

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-04-22 : 10:47:53

quote:

I don't think so it needs to be based on dates you can't just sum up all the outputs and subtract from the inputs because they were input and outputed on different dates. The solution will be more like what a bank does to calculate interest.

Yes, you're right! It similar to what a bank does. (now we only need to figure out how they do it :) )

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-04-22 : 10:50:54

Peter
Are the Transaction Kg. held true? You could then base it on an Item Transaction. Grouped by customer, order by Item# (System Assigned Auto number) This Would give you your first in first out. Then an Adjusted Date Diff would give you the elapsed days exceeding 6 months.

Example.

Sent to Cold Storage
Item# Entrydate Kg.

01 01/01/2002 27
45 02/15/2002 50
76 06/27/2002 71
99 09/02/2002 20

Removed from Cold Storage

01 08/15/2002 27
45 09/02/2002 50
76 10/27/2002 71
99 04/03/2003 20

A little different way of approaching it.

Jim

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-22 : 10:55:29

or a little mod of what samc says.

It would be sum of input > 26 weeks
minus
if (Items Input after 26 weeks) - (Output Items after 26 weeks) > 0
(Items Input after 26 weeks) - (Output Items after 26 weeks)
Grouped by Customer

Sql Time running out will play with this theory later.

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-04-22 : 10:59:26

Thanks for your replies until now, I have to run now but I'll check back later.

Peter

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-22 : 12:53:22

Peter - I see you're gone, here's another question before taking another stab.

I understood the first 6 months of every insertion was free. Now I get the impression that only the first 6 months is free. After the first 6 months, every day the total Kg, including new insertions are billed 0.01 cent.

Sam

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-04-22 : 15:42:40

quote:
I understood the first 6 months of every insertion was free. Now I get the impression that only the first 6 months is free. After the first 6 months, every day the total Kg, including new insertions are billed 0.01 cent.

No, _every_ insertion has 6 months free

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources