Need to select Erolldt - 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)

Need to select Erolldt

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-03-01 : 11:47:35

[code]Hi all

I have one requirement where I have to get the ENrolldt from my table Raw_source on the basis of Linecontractindex and canceldt.
I am giving you sample data for this requirement.
I have retrieved all maximum Linecontractindex in my #tmp table
with following query .

SELECT MAX(LineContractIndex) LineContractIndex, LineContractNbr
into tmp
FROM HSEnroll..Raw_Source rsmain
WHERE 1=1
AND (
ISDATE(CANCELDT) = 1
OR
InterfaceType = 1
)
AND EditFlag <> 1
GROUP BY LineContractNbr

Now my temp table contains data like below:

Linecontractindex LineContractNbr
25 10821200037
77 10821200046
24 10821200074

Now my raw_source table contains following records .These are sample records

LineContractNbr Linecontractindex enrolldt canceldt
10821200037 20 20090331
10821200037 25 20090428
10821200046 66 20080203
10821200046 73 20081121
10821200046 75 20090304
10821200046 77 20090401
10821200046 98 20110614
10821200074 21 20101006
10821200074 22 20101030
10821200074 23 20110215
10821200074 24 20110215 20110311

Now to retrive the Enrolldt from Raw_source table there are multiple
case

1.for any LineContractNbr if there is last record which is on canceldt in that case previous enrolldt should be retived.
See LineContractNbr 10821200074 and 10821200037
2.for any LineContractNbr with the canceldt check next record
if it is there with enrolldt retrive that enrolldt see LineContractNbr 10821200046

My table is having lakhs of records .These are the specific case which I have mentioned here.

Thanks
VIjay Sahu

[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-01 : 13:54:43

so what should be the output for above data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-03-01 : 14:05:55

[code]This should be the output

LineContractNbr Linecontractindex enrolldt
10821200037 20 20090331
10821200046 98 20110614
10821200074 23 20110215
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-01 : 14:22:09

[code]
SELECT t.LineContractNbr,Linecontractindex, enrolldt
FROM (SELECT DISTINCT LineContractNbr FROM table)t
CROSS APPLY (SELECT TOP 1 Linecontractindex, enrolldt
FROM table
WHERE LineContractNbr = t.LineContractNbr
AND enrolldt IS NOT NULL
AND canceldt IS NULL
ORDER BY Linecontractindex DESC
)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-03-01 : 14:45:54

[code]
I replaced the table keyword in your query with my table raw_source table and also extra condition included after running below query

SELECT t.LineContractNbr,Linecontractindex, enrolldt
FROM (SELECT DISTINCT LineContractNbr FROM raw_source)t
CROSS APPLY (SELECT TOP 1 Linecontractindex, enrolldt
FROM raw_source
WHERE LineContractNbr = t.LineContractNbr
AND enrolldt IS NOT NULL or enrolldt <>''
AND canceldt IS NULL or canceldt = ''
ORDER BY Linecontractindex DESC
)t1

This query retrieving below data

LineContractNbr Linecontractindex enrolldt
010821200037 98 20110614
010821200046 98 20110614
010821200074 98 20110614
above data is not desired output.
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-01 : 23:07:12

you've some additional conditions thats causing this

if you want to put them use like


SELECT t.LineContractNbr,Linecontractindex, enrolldt 	
FROM (SELECT DISTINCT LineContractNbr	FROM raw_source)t
CROSS APPLY (SELECT TOP 1 Linecontractindex, enrolldt 
             FROM raw_source
             WHERE LineContractNbr = t.LineContractNbr	
             AND (enrolldt IS NOT NULL or enrolldt <>'')
             AND (canceldt IS NULL or canceldt = '')
             ORDER BY 	Linecontractindex DESC
            )t1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources