SProc to retrieve latest bal_qty

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

Transact-SQL (2000)

SProc to retrieve latest bal_qty

Author

Topic

azmi
Starting Member

37 Posts

Posted - 2006-03-16 : 21:00:08

Hi all,
I need your expertise and assistant to retrieve lastest bal_qty.I had create sproc to retrieve all the 'REC' doc but still hanging to retrive the latest bal_qty. There is some post input data for better understanding.

/*SAMPLE DATA*/
sl_no doc_dt doc_type doc_no ref_doc_no bal_qty
2 2006-02-07 REC 06RME000591 06EMR000772 16.00000000
3 2006-02-07 STR 06TRM000080 NULL 10.00000000
4 2006-02-08 STR 06TRM000084 NULL 5.00000000*
5 2006-02-10 REC 06RME000686 06EMR000881 20.00000000
6 2006-02-10 STR 06TRM000092 NULL 10.00000000
7 2006-02-13 STR 06TRM000095 NULL 00000000*
8 2006-02-14 REC 06RME000766 06EMR000971 15.00000000
9 2006-02-15 STR 06TRM000103 NULL 3.00000000*
10 2006-02-17 REC 06RME000878 06EMR001120 18.00000000
11 2006-02-17 STR 06TRM000111 NULL 8.00000000
12 2006-02-18 STR 06TRM000115 NULL 3.00000000*
13 2006-02-21 REC 06RME000947 06EMR001254 23.00000000
14 2006-02-21 STR 06TRM000125 NULL 13.00000000
15 2006-02-23 STR 06TRM000137 NULL 12.00000000*
16 2006-02-24 REC 06RME001057 06EMR001370 30.00000000*
17 2006-02-24 REC 06RME001058 06EMR001371 35.00000000
18 2006-02-24 STR 06TRM000138 NULL 23.00000000
19 2006-02-27 STR 06TRM000149 NULL 11.00000000*
20 2006-02-28 REC 06RME001149 06EMR001528 31.00000000
21 2006-02-28 STR 06TRM000152 NULL 26.00000000
22 2006-03-02 STR 06TRM000159 NULL 20.00000000*
23 2006-03-03 REC 06RME001266 06EMR001662 35.00000000
24 2006-03-04 STR 06TRM000168 NULL 29.00000000
25 2006-03-06 STR 06TRM000172 NULL 27.00000000*
26 2006-03-07 REC 06RME001375 06EMR001819 42.00000000
27 2006-03-07 STR 06TRM000176 NULL 30.00000000
28 2006-03-10 STR 06TRM000190 NULL 20.00000000*
29 2006-03-11 REC 06RME001488 06EMR001956 30.00000000
30 2006-03-13 STR 06TRM000199 NULL 18.00000000*
31 2006-03-14 REC 06RME001596 06EMR002078 33.00000000*

REMARKS
*-LATEST BALANCE

for your information fig. with mark * is the latest bal. Below is the result what i'am looking for.

/*RESULT*/
SL_NO DOC_DT DOC_TYPE DOC_NO REF_DOC_NO BAL_QTY
2 2006-02-07 REC 06RME000591 06EMR000772 5.00000000
5 2006-02-10 REC 06RME000686 06EMR000881 0.00000000
8 2006-02-14 REC 06RME000766 06EMR000971 3.00000000
10 2006-02-17 REC 06RME000878 06EMR001120 3.00000000
13 2006-02-21 REC 06RME000947 06EMR001254 12.00000000
16 2006-02-24 REC 06RME001057 06EMR001370 30.00000000
17 2006-02-24 REC 06RME001058 06EMR001371 11.00000000
20 2006-02-28 REC 06RME001149 06EMR001528 20.00000000
23 2006-03-03 REC 06RME001266 06EMR001662 27.00000000
26 2006-03-07 REC 06RME001375 06EMR001819 20.00000000
29 2006-03-11 REC 06RME001488 06EMR001956 18.00000000
31 2006-03-14 REC 06RME001596 06EMR002078 33.00000000

Sory cause the data that i post not properly alignment. I had setting properly before preview and look nice, but when i preview the msg it's still not properly alignment.Anywhere i hope u can understand my problem..Thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-16 : 21:09:29

quote:
Sory cause the data that i post not properly alignment. I had setting properly before preview and look nice, but when i preview the msg it's still not properly alignment

refer to Forum FAQ http://www.sqlteam.com/forums/faq.asp

How do you identify the related records ?

2 2006-02-07 REC 06RME000591 06EMR000772 16.00000000
3 2006-02-07 STR 06TRM000080 NULL        10.00000000
4 2006-02-08 STR 06TRM000084 NULL         5.00000000*
5 2006-02-10 REC 06RME000686 06EMR000881 20.00000000
6 2006-02-10 STR 06TRM000092 NULL        10.00000000
7 2006-02-13 STR 06TRM000095 NULL           00000000*

Like SL_NO 2, 3, 4 are related. 5, 6, 7 are related.

azmi
Starting Member

37 Posts

Posted - 2006-03-16 : 22:57:27

The sample data is taken from stock movement report.I still thinks how to indentify the record. According to the user, they want to retrieve REC doc only but the bal_qty will be the last before next item receive(REC doc) comes. What can i say is, bal_qty for SL_NO 2 will be next REC (SL_NO 5) - 1 = 4 that equal to 5. Any ideas...

2 2006-02-07 REC 06RME000591 06EMR000772 5.00000000
5 2006-02-10 REC 06RME000686 06EMR000881 00000000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-16 : 23:26:58

as per your specified logic.

select 	*,
	bal = (select top 1 bal_qty 
		from 	yourtable x 
		where 	x.sl_no >= t.sl_no 
		and	x.sl_no	< isnull((select min(sl_no) from yourtable y where y.sl_no > t.sl_no and doc_type = 'REC'), t.sl_no + 1)
		order by doc_dt desc) 
from	yourtable t
where	doc_type	= 'REC'

azmi
Starting Member

37 Posts

Posted - 2006-03-17 : 04:45:23

Thanks for your feedback. I try your logic first and let you know the status. Have a nice day...

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-17 : 13:30:01

Hi all,

Here's an alternative method which runs about 3 times faster on my machine and with azmi's sample data. I've provided a working example with both methods so you can see for yourself.

I think the performance benefit will increase as the data size increases too, although I haven't tested that.

azmi - Out of interest, how's the performance comparison for you (on your sample data and on your full data)?

khtan - There might be a small bug with your method when the last record is 'STR' - easy to fix though, I'm sure

Thanks!

--This SQL script is safe to run

--create and populate temporary table
create table #t (sl_no int CONSTRAINT pk PRIMARY KEY CLUSTERED, doc_dt smalldatetime, 
                doc_type varchar(3), doc_no varchar(20), ref_doc_no varchar(20), bal_qty int)

insert #t
          select 2, '20060207', 'REC', '06RME000591', '06EMR000772', 16
union all select 3, '20060207', 'STR', '06TRM000080', 'NULL', 10
union all select 4, '20060208', 'STR', '06TRM000084', 'NULL', 5
union all select 5, '20060210', 'REC', '06RME000686', '06EMR000881', 20
union all select 6, '20060210', 'STR', '06TRM000092', 'NULL', 10
union all select 7, '20060213', 'STR', '06TRM000095', 'NULL', 0
union all select 8, '20060214', 'REC', '06RME000766', '06EMR000971', 15
union all select 9, '20060215', 'STR', '06TRM000103', 'NULL', 3
union all select 10, '20060217', 'REC', '06RME000878', '06EMR001120', 18
union all select 11, '20060217', 'STR', '06TRM000111', 'NULL', 8
union all select 12, '20060218', 'STR', '06TRM000115', 'NULL', 3
union all select 13, '20060221', 'REC', '06RME000947', '06EMR001254', 23
union all select 14, '20060221', 'STR', '06TRM000125', 'NULL', 13
union all select 15, '20060223', 'STR', '06TRM000137', 'NULL', 12
union all select 16, '20060224', 'REC', '06RME001057', '06EMR001370', 30
union all select 17, '20060224', 'REC', '06RME001058', '06EMR001371', 35
union all select 18, '20060224', 'STR', '06TRM000138', 'NULL', 23
union all select 19, '20060227', 'STR', '06TRM000149', 'NULL', 11
union all select 20, '20060228', 'REC', '06RME001149', '06EMR001528', 31
union all select 21, '20060228', 'STR', '06TRM000152', 'NULL', 26
union all select 22, '20060302', 'STR', '06TRM000159', 'NULL', 20
union all select 23, '20060303', 'REC', '06RME001266', '06EMR001662', 35
union all select 24, '20060304', 'STR', '06TRM000168', 'NULL', 29
union all select 25, '20060306', 'STR', '06TRM000172', 'NULL', 27
union all select 26, '20060307', 'REC', '06RME001375', '06EMR001819', 42
union all select 27, '20060307', 'STR', '06TRM000176', 'NULL', 30
union all select 28, '20060310', 'STR', '06TRM000190', 'NULL', 20
union all select 29, '20060311', 'REC', '06RME001488', '06EMR001956', 30
union all select 30, '20060313', 'STR', '06TRM000199', 'NULL', 18
union all select 31, '20060314', 'REC', '06RME001596', '06EMR002078', 33

--khtan's method
select 	*,
	bal = (select top 1 bal_qty 
		from 	#t x 
		where 	x.sl_no >= t.sl_no 
		and	x.sl_no	< isnull((select min(sl_no) from #t y where y.sl_no > t.sl_no and doc_type = 'REC'), t.sl_no + 1)
		order by doc_dt desc) 
from	#t t
where	doc_type	= 'REC'

--alternative method
select e.*, f.bal_qty as latest_bal_qty from (
    select from_sl_no, max(c.sl_no) as to_sl_no from (
        select max(a.sl_no) as from_sl_no, b.sl_no
        from #t a
            inner join #t b on a.sl_no <= b.sl_no and a.doc_type = 'REC'
        group by b.sl_no                               ) c
    group by from_sl_no
              ) d
    inner join #t e on d.from_sl_no = e.sl_no
    inner join #t f on d.to_sl_no = f.sl_no

--tidy up
drop table #t

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-17 : 19:55:51

"khtan - There might be a small bug with your method when the last record is 'STR' - easy to fix though, I'm sure "
Thanks for pointing that out.

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

azmi
Starting Member

37 Posts

Posted - 2006-03-17 : 23:58:45

I already test the script, both script working, that's what i'am looking for. Anywhere guys thanks for your ideas..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-18 : 00:08:28

Better use Ryan's script. Mine has a bug handling the last record

azmi
Starting Member

37 Posts

Posted - 2006-03-19 : 19:39:39

yup, i use Ryan's script for my sproc. Thanks.