Need 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)

Need Help

Author

Topic

ranganath
Posting Yak Master

209 Posts

Posted - 2009-01-08 : 00:11:13

Hi

i have the Table as follows.

create TABLE #TEST
(
k_id VARCHAR(100),
lng varchar(100),
p_id varchar(200),
ValidFrom datetime,
validTo datetime,
)
insert into #TEST

SELECT 'k1', 'tel', 'p1', '1/1/2008', '1/2/2008' UNION ALL
SELECT 'k1', 'tel', 'p1', '1/2/2008', '1/3/2008' UNION ALL
SELECT 'k1', 'tel', 'p1', '1/3/2008', '1/4/2008' UNION ALL
SELECT 'k1', 'tel', 'p1', '1/7/2008', '1/9/2008' UNION ALL
SELECT 'k1', 'tel', 'p1', '1/10/2008','1/11/2008' UNION ALL
SELECT 'k1', 'eng', 'p2', '1/1/2009', '1/2/2009' UNION ALL
SELECT 'k1', 'hin', 'p3', '1/10/2009','1/11/2009' UNION ALL
SELECT 'k2', 'tel', 'p1', '1/1/2008', '1/2/2008' UNION ALL
SELECT 'k2', 'tel', 'p1', '1/2/2008', '1/3/2008' UNION ALL
SELECT 'k2', 'tel', 'p1', '1/3/2008', '1/4/2008' UNION ALL
SELECT 'k2', 'tel', 'p1', '1/7/2008' ,'1/9/2008' UNION ALL
SELECT 'k2', 'tel', 'p1', '1/10/2008','1/11/2008' UNION ALL
SELECT 'k2', 'eng', 'p2', '1/1/2009','1/2/2009' UNION ALL
SELECT 'k2', 'hin', 'p3', '1/10/2009','1/11/2009'

select * from #TEST

i need result as below

k_id lng p_id validfrom validTo seq Flag
k1 tel p1 1/1/2008 1/2/2008 1 F
k1 tel p1 1/2/2008 1/3/2008 1 M
k1 tel p1 1/3/2008 1/4/2008 1 L
k1 tel p1 1/7/2008 1/9/2008 2 S
k1 tel p1 1/10/2008 1/11/2008 3 S
k1 eng p2 1/1/2009 1/2/2009 1 S
k1 hin p3 1/10/2009 1/11/2009 1 S
k2 tel p1 1/1/2008 1/2/2008 1 F
k2 tel p1 1/2/2008 1/3/2008 1 M
k2 tel p1 1/3/2008 1/4/2008 1 L
k2 tel p1 1/7/2008 1/9/2008 2 S
k2 tel p1 1/10/2008 1/11/2008 3 S
k2 eng p2 1/1/2009 1/2/2009 1 S
k2 hin p3 1/10/2009 1/11/2009 1 S

here we have to consider ValidTo = ValidFrom
Ex : ValidTo ('1/2/2008') = ValidFrom ('1/2/2008') then Seq 1 else 2 like that.
and also if i have seq 1,1,1 then Flag F, M, L and if seq 1,1,1,1 then Flag F, M, M, L
considering k_id,lng,p_id (Group By)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-08 : 00:27:10

try this
select row_number()over (partition by k_id order by k_id)as rid,*,case when validfrom = validto-1 then 1 else 2 end as seq into #temp from #test

select * from #temp

SELECT K_ID,LNG,P_ID,VALIDFROM,VALIDTO,SEQ,CASE WHEN RID = 1 THEN 'F'
WHEN RID = 2 THEN 'M'
WHEN RID = 3 THEN 'L'
ELSE 'S'
END AS FLAG
FROM #TEMP

ranganath
Posting Yak Master

209 Posts

Posted - 2009-01-08 : 00:50:26

HI,

Thanks for u Help.

But if i have Seq have 10 ONES (1) then first record 'F' and last record as 'L' in between first and last are as 'M'

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-01-08 : 00:57:46

try like this

DECLARE @TEST TABLE 
(
id int identity(1,1),
k_id VARCHAR(100),
lng varchar(100),
p_id varchar(200),
ValidFrom datetime,
validTo datetime
)
insert into @TEST

SELECT 'k1', 'tel', 'p1', '1/1/2008', '1/2/2008' UNION ALL
SELECT 'k1', 'tel', 'p1', '1/2/2008', '1/3/2008' UNION ALL
SELECT 'k1', 'tel', 'p1', '1/3/2008', '1/4/2008' UNION ALL
SELECT 'k1', 'tel', 'p1', '1/4/2008', '1/5/2008' UNION ALL
SELECT 'k1', 'tel', 'p1', '1/7/2008', '1/9/2008' UNION ALL
SELECT 'k1', 'tel', 'p1', '1/10/2008','1/11/2008' UNION ALL
SELECT 'k1', 'eng', 'p2', '1/1/2009', '1/2/2009' UNION ALL
SELECT 'k1', 'hin', 'p3', '1/10/2009','1/11/2009' UNION ALL
SELECT 'k2', 'tel', 'p1', '1/1/2008', '1/2/2008' UNION ALL
SELECT 'k2', 'tel', 'p1', '1/2/2008', '1/3/2008' UNION ALL
SELECT 'k2', 'tel', 'p1', '1/3/2008', '1/4/2008' UNION ALL
SELECT 'k2', 'tel', 'p1', '1/7/2008' ,'1/9/2008' UNION ALL
SELECT 'k2', 'tel', 'p1', '1/10/2008','1/11/2008' UNION ALL
SELECT 'k2', 'eng', 'p2', '1/1/2009','1/2/2009' UNION ALL
SELECT 'k2', 'hin', 'p3', '1/10/2009','1/11/2009' 


SELECT ID, k_id, lng, p_id, VALIDFROM, VALIDTO, Seq, CASE WHEN RowNo = 1 THEN 'F' WHEN RowNo = MAX(ROWNO) OVER (PARTITION BY k_id, lng, p_id, Seq) THEN 'L' ELSE 'M' END
FROM (
		SELECT	ID, A.k_id, A.lng, A.p_id, A.VALIDFROM, a.VALIDTO, CT.Cnt + 1 as 'Seq', 
				ROW_NUMBER() OVER (PARTITION BY A.k_id, A.lng, A.p_id, CT.Cnt ORDER BY id) AS 'RowNo'
		FROM @TEST A
		CROSS APPLY
			(
					SELECT	COUNT(1) AS 'Cnt'
					FROM	@TEST T
					LEFT JOIN @TEST T2 ON T2.VALIDFROM = T.VALIDTO
					WHERE T.VALIDFROM < A.VALIDFROM 
							AND t2.VALIDFROM IS NULL
							AND T.k_id = A.k_id
							AND T.lng = A.lng
							AND T.p_id = A.p_id
			) CT
	) B
ORDER BY ID

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources