Identifying record set - 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 2005 Forums

Transact-SQL (2005)

Identifying record set

Author

Topic

vasu4us
Posting Yak Master

102 Posts

Posted - 2009-12-03 : 16:32:12

Hi, I have a requirment where in i have to identify the datasets, Its like this

cart_no STATUS DATE_TIME BILL_NO
1 L 2009-01-01 NULL *
1 L 2009-01-02 NULL
1 L 2009-01-02 NULL
1 E 2009-01-03 NULL
1 E 2009-01-03 NULL **
1 L 2009-01-04 NULL
1 L 2009-01-05 NULL
1 L 2009-01-06 NULL
1 L 2009-01-07 NULL
1 E 2009-01-07 NULL
1 E 2009-01-08 NULL
1 E 2009-01-09 NULL

I want to identify the records ** (actually the date_time on that record)
then i will be updating the bill_no from the first record to that record from a different table.

(I have table XYZ which has SHIP_DATE and BILL_NO.For the above data set between * and ** is between two ship dates from XYZ i need to update the BILL_NO. I have a running query which works fine as long as ther is next ship date. Problem is when there is a ship date missing. then i need to look at the STATUS column and recognise the dataset ie between first L and last E before the next set of L.

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-03 : 16:42:27

So what's on the Row that make it unique so you can select it?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

vasu4us
Posting Yak Master

102 Posts

Posted - 2009-12-03 : 16:50:07

There is nothing specific that i can use to identify it except scrowling down the status column to from status 'L' to max of 'E'
in the first setof L and E

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-04 : 07:08:36

Here's something which will calculate the 'batch number' for each cart_no. Maybe it could help?

declare @t table (cart_no int, STATUS char(1), DATE_TIME datetime, BILL_NO int)
insert @t
          select 1, 'L', '2009-01-01', NULL
union all select 1, 'L', '2009-01-02', NULL
union all select 1, 'L', '2009-01-02', NULL
union all select 1, 'E', '2009-01-03', NULL
union all select 1, 'E', '2009-01-03', NULL
union all select 1, 'L', '2009-01-04', NULL
union all select 1, 'L', '2009-01-05', NULL
union all select 1, 'L', '2009-01-06', NULL
union all select 1, 'L', '2009-01-07', NULL
union all select 1, 'E', '2009-01-07', NULL
union all select 1, 'E', '2009-01-08', NULL
union all select 1, 'E', '2009-01-09', NULL
union all select 1, 'L', '2009-01-10', NULL
union all select 1, 'E', '2009-01-10', NULL
union all select 1, 'L', '2009-01-11', NULL
union all select 1, 'E', '2009-01-11', NULL
union all select 1, 'E', '2009-01-11', NULL
union all select 1, 'E', '2009-01-11', NULL
union all select 2, 'L', '2009-01-01', NULL
union all select 2, 'L', '2009-01-02', NULL
union all select 2, 'L', '2009-01-02', NULL
union all select 2, 'E', '2009-01-11', NULL
union all select 2, 'E', '2009-01-11', NULL

; with t1 as (
    select *,
        row_number() over (partition by cart_no order by DATE_TIME, STATUS desc) - 
        row_number() over (partition by cart_no, STATUS order by DATE_TIME) as STATUS_Grouping
    from @t)
, t2 as (select distinct cart_no, STATUS, STATUS_Grouping from t1)
, t3 as (select *, row_number() over (partition by cart_no, STATUS order by STATUS_Grouping) as BatchNumber from t2)
select a.*, BatchNumber
from t1 a inner join t3 b on a.cart_no = b.cart_no and a.STATUS = b.STATUS and a.STATUS_Grouping = b.STATUS_Grouping
order by cart_no, DATE_TIME

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources