Stock Ledger - 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 2008 Forums

Transact-SQL (2008)

Stock Ledger

Author

Topic

coagulance
Yak Posting Veteran

78 Posts

Posted - 2011-04-28 : 01:17:34

I have a table as below:
PartNumber TID TransactionDate OpenBalance Issue-Receipt-Qty Closing Balance
ABC001 R01 22/04/2011 10 2 12
ABC001 I01 23/04/2011 12 1 11
XYZ002 R02 21/04/2011 20 1 21
XYZ002 I02 22/04/2011 21 7 14
XYZ002 I03 23/04/2011 14 1 13

I would like to extract the Part Numbers Records that has the latest Transaction Date

Any Help is highly appreciated

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-28 : 01:49:30

what is your data type of your column TransactionDate

Raghu' S

coagulance
Yak Posting Veteran

78 Posts

Posted - 2011-04-28 : 02:05:17

its "Date" Type

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-28 : 02:39:56

Try this may help you
create table #T (PartNumber nvarchar(25), TID nvarchar(5), TransactionDate datetime, OpenBalance int, Issue_Receipt_Qty int, Closing_Balance int)
insert into #T
select 'ABC001','R01','04/22/2011',10, 2, 12 union all
select 'ABC001',' I01', '04/23/2011', 12, 1, 11 union all
select 'XYZ002','R02', '04/21/2011' ,20, 1, 21 union all
select 'XYZ002','I02','05/22/2011' ,21 ,7 ,14 union all
select 'XYZ002','I03', '04/23/2011' ,14, 1,13
select * from #T

select PartNumber,max(TransactionDate)as TransactionDate from #T group by PartNumber

select PartNumber,convert(varchar,TransactionDate,101) from (select PartNumber,max(TransactionDate)as TransactionDate from #T group by PartNumber) as t

drop table #T

Raghu' S

coagulance
Yak Posting Veteran

78 Posts

Posted - 2011-04-28 : 03:41:32

Thanks Raghu ,

I have two requirements here : the Result should be as below:

PartNumber OpeningBalance Issues Receipts Closing Balance

Opening Balance is the OpeningBalance at the start of the year (Min(TransactionDate))
Issues Sum of Qty for the selected partnumber where Transaction ID starts with I
Receipts Sum of Qty for the selected partnumber where Transaction ID starts with R
Closing Balance is the ClosingBalance at the end of the year (Max(TransactionDate))

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-28 : 05:11:11

How your output you want post your output

Raghu' S

coagulance
Yak Posting Veteran

78 Posts

Posted - 2011-04-28 : 05:47:36

The below is my table:

 
Transaction     Part#   Type    Year    TransactionDate         Name    OpBal   Qty    Closingbal
RD400001	A001007	R	2011	2011-04-01 00:00:00.000	steeve	10	0	10
RD400002	B002003	R	2011	2011-04-01 00:00:00.000	steeve	3	0	3
ID400001	A001007	I	2011	2011-04-07 00:00:00.000	steeve	10	5	5
ID400002	A001007	I	2011	2011-04-15 00:00:00.000	steeve	5	2	3
RD400003	B002003	R	2011	2011-04-22 00:00:00.000	steeve	3	1	4
ID400003	B002003	I	2011	2011-04-23 00:00:00.000	steeve	4	3	1

If you see for Part# A001007 there are 1 Receipt and Two Issues (Indicated by Type=R , Type=I Columns) and for Part# B002003 there are 2 Receipts and one issue

The result expected is :


Part#    OpBal   Issues   Receipts   ClosingBal LastTransactionDate
A001007  10       7         0         3         2011-04-15 
B002003   3       3         1         1         2011-04-23

Regards,

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-28 : 08:07:36

--CREATE TABLE #T (TRANSACTIONS NVARCHAR(25), PART NVARCHAR(25),TYPES CHAR(1), YEAR INT,TRANSACTIONDATE DATETIME,NAME NVARCHAR(25),
--OPBAL INT, QTY INT,CLOSINGBAL INT)
--INSERT INTO #T
--SELECT 'RD400001','A001007','R',2011,'2011-04-01 00:00:00.000','STEEVE',10,0,10 UNION ALL
--SELECT 'RD400002','B002003','R',2011,'2011-04-01 00:00:00.000','STEEVE',3,0,3 UNION ALL
--SELECT 'ID400001','A001007','I',2011,'2011-04-07 00:00:00.000','STEEVE',10,5,5 UNION ALL
--SELECT 'ID400002','A001007','I',2011,'2011-04-15 00:00:00.000','STEEVE',5,2,3 UNION ALL
--SELECT 'RD400003','B002003','R',2011,'2011-04-22 00:00:00.000','STEEVE',3,1,4 UNION ALL
--SELECT 'ID400003','B002003','I',2011,'2011-04-23 00:00:00.000','STEEVE',4,3,1

/*
Transaction Part# Type Year TransactionDate Name OpBal Qty Closingbal
RD400001 A001007 R 2011 2011-04-01 00:00:00.000 steeve 10 0 10
RD400002 B002003 R 2011 2011-04-01 00:00:00.000 steeve 3 0 3
ID400001 A001007 I 2011 2011-04-07 00:00:00.000 steeve 10 5 5
ID400002 A001007 I 2011 2011-04-15 00:00:00.000 steeve 5 2 3
RD400003 B002003 R 2011 2011-04-22 00:00:00.000 steeve 3 1 4
ID400003 B002003 I 2011 2011-04-23 00:00:00.000 steeve 4 3 1

If you see for Part# A001007 there are 1 Receipt and Two Issues (Indicated by Type=R , Type=I Columns) and for Part# B002003 there are 2 Receipts and one issue
The result expected is :

--Opening Balance is the OpeningBalance at the start of the year (Min(TransactionDate))
--Issues Sum of Qty for the selected partnumber where Transaction ID starts with I
--Receipts Sum of Qty for the selected partnumber where Transaction ID starts with R
Closing Balance is the ClosingBalance at the end of the year (Max(TransactionDate))

Part# OpBal Issues Receipts ClosingBal LastTransactionDate
A001007 10 7 0 3 2011-04-15
B002003 3 3 1 1 2011-04-23
*/

SELECT A.PART,A.OPBAL,B.ISSUES,C.RECEIPT,D.CLOSINGBAL,D.TRANSACTIONDATE
FROM
(
SELECT PART,OPBAL FROM (
SELECT TRANSACTIONS,PART,TYPES,YEAR,TRANSACTIONDATE,NAME,OPBAL,ROW=DENSE_RANK()
OVER(PARTITION BY PART ORDER BY TRANSACTIONDATE) FROM #T) T WHERE ROW=1)A
INNER JOIN
(
SELECT PART,SUM(QTY) AS ISSUES FROM (SELECT TRANSACTIONS,PART,TYPES,YEAR,TRANSACTIONDATE,NAME,OPBAL,QTY,CLOSINGBAL,ROW=DENSE_RANK()
OVER(PARTITION BY PART ORDER BY TRANSACTIONDATE) FROM #T)TT WHERE TYPES LIKE ('I%') GROUP BY PART
) B ON A.PART=B.PART
INNER JOIN
(
SELECT PART,SUM(QTY) AS RECEIPT FROM (SELECT TRANSACTIONS,PART,TYPES,YEAR,TRANSACTIONDATE,NAME,OPBAL,QTY,CLOSINGBAL,ROW=DENSE_RANK()
OVER(PARTITION BY PART ORDER BY TRANSACTIONDATE) FROM #T)TTT WHERE TYPES LIKE ('R%') GROUP BY PART
) C ON C.PART=A.PART
INNER JOIN
(
SELECT PART,CLOSINGBAL,CONVERT(VARCHAR,TRANSACTIONDATE,103) AS TRANSACTIONDATE FROM (
SELECT TRANSACTIONS,PART,TYPES,YEAR,TRANSACTIONDATE,NAME,OPBAL,QTY,CLOSINGBAL,ROW=DENSE_RANK()
OVER(PARTITION BY PART ORDER BY TRANSACTIONDATE DESC) FROM #T)TTTT WHERE ROW=1) D ON D.PART=A.PART

Raghu' S

coagulance
Yak Posting Veteran

78 Posts

Posted - 2011-05-03 : 13:06:07

Here there are two problems :
1. If there are no 'issues' and only 'receipts' the Output is not displayed.
2. PartNumbers with no issues and no receipts are not displayed.

Can any one suggest suitable modification in the code ?

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-04 : 02:51:26

Is your Transactiona column always starts with R when Types=R and I when Types=I

In Love... With Me!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-04 : 04:00:14

with cte as
(
select trtype = left(transaction,1), partnumber, trdate, openbalance, closebalance, seqasc = row_number() over (partition by partnumber order by trdate), seqdesc = = row_number() over (partition by partnumber order by trdate desc)
from tbl
)
select partno ,
openbalance = (select openbalance from cte where cte.partno = a.partno and cte.seqasc = 1) ,
issues = sum(case when trtype = 'I' then 1 else 0 end) ,
receipts = sum(case when trtype = 'R' then 1 else 0 end) ,
closebalance = (select closebalance from cte where cte.partno = a.partno and cte.seqdesc = 1) ,
LastTransactionDate = (select TrDate from cte where cte.partno = a.partno and cte.seqdesc = 1)
from cte
group by partno

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-04 : 06:06:29

SELECT A.PART,A.OPBAL,B.ISSUES,C.RECEIPT,D.CLOSINGBAL,D.TRANSACTIONDATE
FROM
(
SELECT PART,OPBAL FROM (
SELECT TRANSACTIONS,PART,
CASE WHEN TRANSACTIONS LIKE ('R%') THEN
CASE WHEN TYPES='' THEN LTRIM(TYPES+'R') ELSE TYPES END ELSE
CASE WHEN TRANSACTIONS LIKE ('I%') THEN
CASE WHEN TYPES='' THEN LTRIM(TYPES+'I') ELSE TYPES END END END AS TYPES,YEAR,TRANSACTIONDATE,NAME,OPBAL,ROW=DENSE_RANK()
OVER(PARTITION BY PART ORDER BY TRANSACTIONDATE) FROM #T) T WHERE ROW=1)A
INNER JOIN
(
SELECT PART,SUM(QTY) AS ISSUES FROM (
SELECT TRANSACTIONS,PART,
CASE WHEN TRANSACTIONS LIKE ('R%') THEN
CASE WHEN TYPES='' THEN LTRIM(TYPES+'R') ELSE TYPES END ELSE
CASE WHEN TRANSACTIONS LIKE ('I%') THEN
CASE WHEN TYPES='' THEN LTRIM(TYPES+'I') ELSE TYPES END END END AS TYPES
,YEAR,TRANSACTIONDATE,NAME,OPBAL,QTY,CLOSINGBAL,ROW=DENSE_RANK()
OVER(PARTITION BY PART ORDER BY TRANSACTIONDATE) FROM #T)TT WHERE TYPES LIKE ('I%') GROUP BY PART
) B ON A.PART=B.PART
INNER JOIN
(
SELECT PART,SUM(QTY) AS RECEIPT FROM (SELECT TRANSACTIONS,PART,
CASE WHEN TRANSACTIONS LIKE ('R%') THEN
CASE WHEN TYPES='' THEN LTRIM(TYPES+'R') ELSE TYPES END ELSE
CASE WHEN TRANSACTIONS LIKE ('I%') THEN
CASE WHEN TYPES='' THEN LTRIM(TYPES+'I') ELSE TYPES END END END AS TYPES,YEAR,TRANSACTIONDATE,NAME,OPBAL,QTY,CLOSINGBAL,ROW=DENSE_RANK()
OVER(PARTITION BY PART ORDER BY TRANSACTIONDATE) FROM #T)TTT WHERE TYPES LIKE ('R%') GROUP BY PART
) C ON C.PART=A.PART
INNER JOIN
(
SELECT PART,CLOSINGBAL,CONVERT(VARCHAR,TRANSACTIONDATE,103) AS TRANSACTIONDATE FROM (
SELECT TRANSACTIONS,PART,
CASE WHEN TRANSACTIONS LIKE ('R%') THEN
CASE WHEN TYPES='' THEN LTRIM(TYPES+'R') ELSE TYPES END ELSE
CASE WHEN TRANSACTIONS LIKE ('I%') THEN
CASE WHEN TYPES='' THEN LTRIM(TYPES+'I') ELSE TYPES END END END AS TYPES,YEAR,TRANSACTIONDATE,NAME,OPBAL,QTY,CLOSINGBAL,ROW=DENSE_RANK()
OVER(PARTITION BY PART ORDER BY TRANSACTIONDATE DESC) FROM #T)TTTT WHERE ROW=1) D ON D.PART=A.PART

In Love... With Me!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-04 : 06:18:50

I've converted this to use the posted table structure and corected a few mistypes.

declare @t table (PartNumber nvarchar(25), TID nvarchar(5), TransactionDate datetime, OpenBalance int, Issue_Receipt_Qty int, Closing_Balance int)
insert into @T
select 'ABC001','R01','04/22/2011',10, 2, 12 union all
select 'ABC001','I01', '04/23/2011', 12, 1, 11 union all
select 'XYZ002','R02', '04/21/2011' ,20, 1, 21 union all
select 'XYZ002','I02','05/22/2011' ,21 ,7 ,14 union all
select 'XYZ002','I03', '04/23/2011' ,14, 1,13
select * from @T

;with cte as
(
select trtype = left(TID,1), partnumber, TransactionDate, openbalance, Closing_Balance,
seqasc = row_number() over (partition by partnumber order by TransactionDate),
seqdesc = row_number() over (partition by partnumber order by TransactionDate desc)
from @t
)
select PartNumber ,
openbalance = (select openbalance from cte where cte.PartNumber = a.PartNumber and cte.seqasc = 1) ,
issues = sum(case when trtype = 'I' then 1 else 0 end) ,
receipts = sum(case when trtype = 'R' then 1 else 0 end) ,
closebalance = (select Closing_Balance from cte where cte.PartNumber = a.PartNumber and cte.seqdesc = 1) ,
LastTransactionDate = (select TransactionDate from cte where cte.PartNumber = a.PartNumber and cte.seqdesc = 1)
from cte a
group by PartNumber

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-04 : 15:07:06

For better performance, advoid to access the source too many times. I think to produce the expected output, we need to access the table just 1 time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources