Stuck, need some help - 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)

Stuck, need some help

Author

Topic

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-01-05 : 11:19:08

Stuck. I have whittled my table down to this size, but need to refine the data
just a little more.

Criteria: Need to update the NR row to group the records by their CID.If the
difference in the CID between SEQUENTIAL records is 89 days or LESS , then the records belong in the same group.

The primary key would be the RN, the NS and the TW.

-- DDL

CREATE TABLE #Yak (Rn int, NS char(9), TW varchar(9), CID datetime, NR int)

-- Sample Data

INSERT INTO #Yak
(Rn,NS,TW,CID,NR)
SELECT
1 , '123456789' , 'T60432 ' , '2007-10-30 00:00:00.000' , NULL union all select
2 , '123456789' , 'T60432 ' , '2007-11-07 00:00:00.000' , NULL union all select
3 , '123456789' , 'T60432 ' , '2007-11-21 00:00:00.000' , NULL union all select
4 , '123456789' , 'T60432 ' , '2008-10-25 00:00:00.000' , NULL union all select
5 , '123456789' , 'T60432 ' , '2008-11-06 00:00:00.000' , NULL union all select
6 , '123456789' , 'T60432 ' , '2008-11-20 00:00:00.000' , NULL union all select
7 , '123456789' , 'T60432 ' , '2008-12-04 00:00:00.000' , NULL union all select
8 , '123456789' , 'T60432 ' , '2009-01-28 00:00:00.000' , NULL union all select
1 , '987654321' , 'T50004' , '2006-10-04 00:00:00.000' , NULL union all select
2 , '987654321' , 'T50004' , '2006-10-21 00:00:00.000' , NULL union all select
3 , '987654321' , 'T50004' , '2006-11-01 00:00:00.000' , NULL union all select
4 , '987654321' , 'T50004' , '2006-11-17 00:00:00.000' , NULL

Desired output:

Rn NS TW CID NR
1 123456789 T60432 2007-10-30 00:00:00.000 1
2 123456789 T60432 2007-11-07 00:00:00.000 1
3 123456789 T60432 2007-11-21 00:00:00.000 1
4 123456789 T60432 2008-10-25 00:00:00.000 2
5 123456789 T60432 2008-11-06 00:00:00.000 2
6 123456789 T60432 2008-11-20 00:00:00.000 2
7 123456789 T60432 2008-12-04 00:00:00.000 2
8 123456789 T60432 2009-01-28 00:00:00.000 2
1 987654321 T50004 2006-10-04 00:00:00.000 1
2 987654321 T50004 2006-10-21 00:00:00.000 1
3 987654321 T50004 2006-11-01 00:00:00.000 1
4 987654321 T50004 2006-11-17 00:00:00.000 1

Drop Table #Yak

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-05 : 13:32:43

Well I'm working on it, and so far all my methods are unorthodox, here is a way to find out where you need to partition(the 1's in the last column show where ranking needs to start over)

Here you go: You will have to do some work to optimize it, but it gets the answer...

Create table #Yak 
(Rn int ,NS varchar (9),TW varchar(6),CID datetime,NR text)
insert #Yak 
SELECT 
1 , '123456789' , 'T60432 ' , '2007-10-30 00:00:00.000' , NULL union all select 
2 , '123456789' , 'T60432 ' , '2007-11-07 00:00:00.000' , NULL union all select 
3 , '123456789' , 'T60432 ' , '2007-11-21 00:00:00.000' , NULL union all select 
4 , '123456789' , 'T60432 ' , '2008-10-25 00:00:00.000' , NULL union all select 
5 , '123456789' , 'T60432 ' , '2008-11-06 00:00:00.000' , NULL union all select 
6 , '123456789' , 'T60432 ' , '2008-11-20 00:00:00.000' , NULL union all select 
7 , '123456789' , 'T60432 ' , '2008-12-04 00:00:00.000' , NULL union all select 
8 , '123456789' , 'T60432 ' , '2009-01-28 00:00:00.000' , NULL union all select 
1 , '987654321' , 'T50004' , '2006-10-04 00:00:00.000' , NULL union all select 
2 , '987654321' , 'T50004' , '2006-10-21 00:00:00.000' , NULL union all select 
3 , '987654321' , 'T50004' , '2006-11-01 00:00:00.000' , NULL union all select 
4 , '987654321' , 'T50004' , '2006-11-17 00:00:00.000' , NULL

Select a.*, b.Newrn,
datediff(D, b.CID, a.CID) as Diff,
 case when datediff(D, b.CID, a.CID) > 89 or a.Rn = 1 then 1 else NULL end as MeetDiff
Into #myTbl
  from #Yak a Left Join 
(
Select Rn +1 as NewRn , * from #Yak
) as b 
on a.rn = b.newRn and a.NS = b.NS

select
c.Rn, c.NS, c.TW, c.CID, sum( isnull(d.MeetDiff,0) )as subTotal
from #myTbl c
inner join 
       (
select *
from #myTbl
	) d 
on c.NS = D.NS and d.CID <= c.CID
Group by
c.Rn, c.NS, c.TW, c.CID
Order by 2, 1

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-01-05 : 14:14:40

Here is what i had when i gave up:

SELECT 
	t1.RN,t1.ns,t1.tw, t1.cid as Begin_Date
	,case when t2.cid IS NULL then 1
	when isnull(datediff(day , t2.cid,t1.cid), 0) > 89 
	THEN 1 ELSE 0 END  as NewRecord
FROM 	
	#Yak t1 
left join 
	#Yak t2
on	
	t1.RN 	= t2.RN + 1
and
	t1.ns = t2.ns 
and
	t1.tw = t2.tw 	
order by 
	t1.ns,t1.RN, t1.cid

which spits out:


RN	ns	        tw	       CID	                 NR
1	123456789	T60432 	2007-10-30 00:00:00.000	1
2	123456789	T60432 	2007-11-07 00:00:00.000	0
3	123456789	T60432 	2007-11-21 00:00:00.000	0
4	123456789	T60432 	2008-10-25 00:00:00.000	1
5	123456789	T60432 	2008-11-06 00:00:00.000	0
6	123456789	T60432 	2008-11-20 00:00:00.000	0
7	123456789	T60432 	2008-12-04 00:00:00.000	0
8	123456789	T60432 	2009-01-28 00:00:00.000	0
1	987654321	T50004	2006-10-04 00:00:00.000	1
2	987654321	T50004	2006-10-21 00:00:00.000	0
3	987654321	T50004	2006-11-01 00:00:00.000	0
4	987654321	T50004	2006-11-17 00:00:00.000	0

Ideally, It would make the record that is the starting one a " 1 ", and all the others that fall into the critiera a " 1 "
Any subsequent ones would be "2", and so on for the same NS (as in the desired output example)

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-01-05 : 14:15:50

Looks good! I will give it a run and see what happens. Thanks!

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-05 : 14:16:23

Don, my code works now, Just subtotal the 1's based on NS = NS and your good.

Select a.*, b.Newrn,
datediff(D, b.CID, a.CID) as Diff,
case when datediff(D, b.CID, a.CID) > 89 or a.Rn = 1 then 1 else NULL end as MeetDiff
Into #myTbl
from #Yak a Left Join
(
Select Rn +1 as NewRn , * from #Yak
) as b
on a.rn = b.newRn and a.NS = b.NS

select
c.Rn, c.NS, c.TW, c.CID, sum( isnull(d.MeetDiff,0) )as subTotal
from #myTbl c
inner join
(
select *
from #myTbl
) d
on c.NS = D.NS and d.CID <= c.CID
Group by
c.Rn, c.NS, c.TW, c.CID
Order by 2, 1

EDIT: SOrry my code is never pretty, I'm still quite new at this and don't always get the elegant answer, but Im sure you can make it look better :)

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-01-05 : 14:35:04

No problem, i really appreciate the effort and the solution!
Now i just have to wrap my head around it.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-01-05 : 14:54:14

Would something like this work?

with A as
(
   select
      y.*,
      case when datediff(dd,y.CID,x.CID) > 89 then 1 else 0 end as EndOfGroupMarker
   from
      #Yak y
      outer apply(select CID from #YAK p where y.NS = p.NS and y.TW = p.TW and y.Rn = p.Rn-1) x
),   
B as
(
   select
      y.*,
      1+x.s as NewNR
   from
      a y
      cross apply(select coalesce(sum(p.EndOfGroupMarker),0) S from A p where y.NS = p.NS and y.TW = p.TW and p.Rn <= y.Rn-1) x
)
update B set NR=NewNR

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-05 : 14:56:21

Basically you had the right starting point, finding out when you needed to either increment or start over.

The next step is to fill in the 0's with the correct number. You basically are creating a psuedo ranking system.
so you have:

RN	ns	        tw	       CID	                 NR
1	123456789	T60432 	2007-10-30 00:00:00.000	1
2	123456789	T60432 	2007-11-07 00:00:00.000	0
3	123456789	T60432 	2007-11-21 00:00:00.000	0
4	123456789	T60432 	2008-10-25 00:00:00.000	1
5	123456789	T60432 	2008-11-06 00:00:00.000	0
6	123456789	T60432 	2008-11-20 00:00:00.000	0
7	123456789	T60432 	2008-12-04 00:00:00.000	0
8	123456789	T60432 	2009-01-28 00:00:00.000	0
1	987654321	T50004	2006-10-04 00:00:00.000	1
2	987654321	T50004	2006-10-21 00:00:00.000	0
3	987654321	T50004	2006-11-01 00:00:00.000	0
4	987654321	T50004	2006-11-17 00:00:00.000	0

you basically are just adding a row to the row before it, thus why I called it 'Subtotal' See below

RN	ns	        tw	       CID	                 NR
1	123456789	T60432 	2007-10-30 00:00:00.000	1  + 0 = 1
2	123456789	T60432 	2007-11-07 00:00:00.000	0  + 1 = 1
3	123456789	T60432 	2007-11-21 00:00:00.000	0  + 1 = 1
4	123456789	T60432 	2008-10-25 00:00:00.000	1  + 1 = 2
5	123456789	T60432 	2008-11-06 00:00:00.000	0  + 2 = 2
6	123456789	T60432 	2008-11-20 00:00:00.000	0  + 2 = 2
7	123456789	T60432 	2008-12-04 00:00:00.000	0  + 2 = 2
8	123456789	T60432 	2009-01-28 00:00:00.000	0  + 2 = 2
1	987654321	T50004	2006-10-04 00:00:00.000	1  + 0 = 1 --NS <> Previous Rows NS so start subtotal over
2	987654321	T50004	2006-10-21 00:00:00.000	0  + 1 = 1
3	987654321	T50004	2006-11-01 00:00:00.000	0  + 1 = 1
4	987654321	T50004	2006-11-17 00:00:00.000	0  + 1 = 1

jcampbell
Starting Member

9 Posts

Posted - 2010-01-05 : 15:20:54

I see you have already had a response that looks like it works, I think my solution is almost identical to DP978's code.

good luck

SELECT YakPrevious.*,Datediff(dd,YakNext.CID,YakPrevious.CID) diff, CASE WHEN (Datediff(dd,YakNext.CID,YakPrevious.CID)<90) THEN 0 ELSE 1 END lessthan90
INTO #YakSummary
FROM #Yak YakPrevious LEFT OUTER JOIN #Yak YakNext on YakPrevious.RN=YakNext.RN+1 and YakPrevious.NS=YakNext.NS

SELECT YS.RN,YS.NS,YS.TW,YS.CID, SUM(YD.lessthan90) as Ranking
FROM #YakSummary YS
JOIN (SELECT * FROM #YakSummary) YD on YS.NS = YD.NS and YD.CID <= YS.CID
GROUP BY YS.Rn, YS.NS, YS.TW, YS.CID
ORDER BY YS.NS,YS.Rn

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-05 : 15:44:44

Im just happy my code came out so similarly to everyone elses! ;-) Must of done something right!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources