Prev: How to check that a date field is null and replace by blank value
Next: Fix by Cloning table struction
From: Que on 11 Sep 2009 11:00
Hi

We record the closing Balance of Stock On Hand on a daily basis, I
need to calculate the differences between the values so that I can see
how much stock is being adjusted in a daily basis, the key to the File
is Date, Branch, Product, Location.

I have the following data

Date Branch Product Location
BalanceQty
11/06/09 PTA A100 PTA-1 10
12/06/09 PTA A100 PTA-1 11
13/06/09 PTA A100 PTA-1 12
14/06/09 PTA A100 PTA-1 10
11/06/09 PTA B100 PTA-1 10
12/06/09 PTA B100 PTA-1 11
13/06/09 PTA B100 PTA-1 12

The desired result should be
Date Branch Product Location
BalanceQty MovmentQty
11/06/09 PTA A100 PTA-1
10 10
12/06/09 PTA A100 PTA-1
11 1
13/06/09 PTA A100 PTA-1
12 2
14/06/09 PTA A100 PTA-1
10 -2
11/06/09 PTA B100 PTA-1
10 10
12/06/09 PTA B100 PTA-1
11 1
13/06/09 PTA B100 PTA-1
12 1

Many Thanks in advance
Regards
Que
From: Tom Moreau on 11 Sep 2009 11:23
The desired out:

13/06/09 PTA A100 PTA-1 12 2

is inconsistent. On that day, the delta was only 1, yet your desired output
says 2. Could you please clarify?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Que" <aq.mahomed(a)gmail.com> wrote in message
news:d11f7df5-f344-4531-88b5-9335c5e646ee(a)v36g2000yqv.googlegroups.com...
Hi

We record the closing Balance of Stock On Hand on a daily basis, I
need to calculate the differences between the values so that I can see
how much stock is being adjusted in a daily basis, the key to the File
is Date, Branch, Product, Location.

I have the following data

Date Branch Product Location
BalanceQty
11/06/09 PTA A100 PTA-1 10
12/06/09 PTA A100 PTA-1 11
13/06/09 PTA A100 PTA-1 12
14/06/09 PTA A100 PTA-1 10
11/06/09 PTA B100 PTA-1 10
12/06/09 PTA B100 PTA-1 11
13/06/09 PTA B100 PTA-1 12

The desired result should be
Date Branch Product Location
BalanceQty MovmentQty
11/06/09 PTA A100 PTA-1
10 10
12/06/09 PTA A100 PTA-1
11 1
13/06/09 PTA A100 PTA-1
12 2
14/06/09 PTA A100 PTA-1
10 -2
11/06/09 PTA B100 PTA-1
10 10
12/06/09 PTA B100 PTA-1
11 1
13/06/09 PTA B100 PTA-1
12 1

Many Thanks in advance
Regards
Que

From: Ooogy on 11 Sep 2009 12:20
Perhaps Que we can see the script you used to attempt to solve this
problem...

Don't you think you'd be a better programmer in the long run if you
learn from your mistakes as opposed to just asking others to do the
work for you?? Even as I sit here, there are probably already
several people working on the answer, but c'mon brother, show a little
initiative and make an attempt, then give us the "where did I go
wrong" post instead of the "do this for me" one.
From: Que on 11 Sep 2009 23:05

The Difference is 1 not 2

> 13/06/09 PTA B100 PTA-1
> 12 1

The example that I show above is only a small part of my issue, I did
not post the entire issue. I will in future post scrips to furthe
explain the Issue

Many Thanks
Que
From: Tom Moreau on 11 Sep 2009 23:22
In that case, try:

select
t1.Date
, t1.Branch
, t1.Product
, t1.Location
, t1.BalanceQty - isnull (t2.BalanceQty, 0)
from
Stock t1
left join
Stock t2 on t2.Date = t1.Date - 1
and t2.Branch = t1.Branch
and t2.Product = t1.Product
and t2.Location = t1.Location

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Que" <aq.mahomed(a)gmail.com> wrote in message
news:cfc667f4-2dcc-4538-837e-ad7ecdb31a82(a)m11g2000yqf.googlegroups.com...

The Difference is 1 not 2

> 13/06/09 PTA B100 PTA-1
> 12 1

The example that I show above is only a small part of my issue, I did
not post the entire issue. I will in future post scrips to furthe
explain the Issue

Many Thanks
Que

 |  Next  |  Last
Pages: 1 2 3
Prev: How to check that a date field is null and replace by blank value
Next: Fix by Cloning table struction