Help on Query - 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)

Help on Query

Author

Topic

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-04-04 : 08:31:05

Hi folks,
I have a table like so

StoreCode
Consignor
Consignee
DocNo
DocType
TransactionDate
ItemCode
ItemCategory
Quantity
OpeningBalance
ClosingBalance

I need help to write a query which look for ClosingBalance and OpeningBalance of the subsequent record of the same itemcat and
present the Docno of the two records.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-04 : 08:33:33

what do you mean by subsequent record? what determines the order?

Em

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-04-04 : 08:35:51

Elancaster, First of all thanks for the blazingly fast reply.

Well the order is Itemcode, Itemcat, transactionDate. Thanx

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-04-04 : 08:38:21

Elancaster pl refer to my first post.

The closing balance of an item (order itemcode, itemcat, transactiondate) should be same as the opening balance of the subsequent transaction. If not the two docs must be fetched.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-04 : 08:39:04

so when you say subsequent record you mean the next transaction date for each itemcat?

can you supply some test data and expected output? it will make it clearer i'm sure.

see this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Em

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-04-04 : 08:46:41

Doc no :2002/26ED/INRV/CRV/001022
TransactionDate :2002-10-16 13:11:01.000
Itemcode :4528
Itemcat : 4401
Qty :3.000
OpeningBalance : 4.000
ClosingBalance : 3.000

Doc no :2002/26ED/INRV/CRV/001023
TransactionDate :2002-10-16 13:15:01.000
Itemcode :4528
Itemcat : 4401
Qty :3.000
OpeningBalance :.000 -- Mismatch should have been 3.00
ClosingBalance : 3.000 -- Should have been 0.00

Result
Doc no :2002/26ED/INRV/CRV/001022
Doc no :2002/26ED/INRV/CRV/001023

Hope this suffices

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-04-04 : 09:32:23

Create table #tbItemTransaction (Docno Varchar(35), Transactiondate smalldatetime, Itemcode int, itemcat int, qty int, openingbalance int, closingbalance int)

Insert into #tbItemTransaction (Docno, Transactiondate, Itemcode, itemcat, qty, openingbalance, closingbalance)
Select ('2002/26ED/INRV/CRV/001022', '2002-10-16 13:11:01.000', 4528 , 4401 , 3.000, 4.000, 3.000) Union all
Select ('2002/26ED/INRV/CRV/001023', '2002-10-16 13:15:01.000', 4528 , 4401 , 3.000, 4.000, 1.000) Union all
Select ('2002/26ED/INRV/CRV/001024', '2002-10-16 13:25:01.000', 4528 , 4402 , 1.000, 6.000, 5.000) Union all

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-04-04 : 09:37:06

Sorry folks think I screwed it up.

Create table #tbItemTransaction (Docno Varchar(35), Transactiondate smalldatetime, Itemcode int, itemcat int, qty int, openingbalance int, closingbalance int)

Insert into #tbItemTransaction (Docno, Transactiondate, Itemcode, itemcat, qty, openingbalance, closingbalance)
Select ('2002/26ED/INRV/CRV/001022', '2002-10-16 13:11:01.000', 4528 , 4401 , 3.000, 4.000, 1.000) Union all
Select ('2002/26ED/INRV/CRV/001023', '2002-10-16 13:15:01.000', 4528 , 4401 , 1.000, 0.000, 1.000) Union all -- Mismatch Opening balance should have been 1 and not 0
Select ('2002/26ED/INRV/CRV/001024', '2002-10-16 13:25:01.000', 4528 , 4402 , 1.000, 6.000, 5.000) Union all

RevMike
Starting Member

9 Posts

Posted - 2008-04-04 : 09:52:12

[code]
WITH tmpTab AS
(SELECT DocType, TransactionDate, ItemCode, ItemCategory, OpeningBalance, ClosingBalance
FROM tab)
SELECT a.DocType, b.DocType
FROM tmpTab a INNER JOIN tmpTab b
ON a.ItemCode = b.ItemCode
AND a.ItemCategory = b.ItemCategory
AND a.ClosingBalance <> b.OpeningBalance
AND b.TranactionDate = ( SELECT MIN(TransactionDate) FROM tmpTab c
WHERE c.TransactionDate > a.TransactionDate
AND c.ItemCode = a.ItemCode
AND c.ItemCategory = a.ITemCategory)
[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-04 : 09:58:01

CTE are only supported in SQL 2005.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources