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.
Author |
Topic |
vijays3
Constraint Violating Yak Guru
354 Posts |
Posted - 2012-03-01 : 11:47:35
|
[code]Hi allI 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 tablewith following query .SELECT MAX(LineContractIndex) LineContractIndex, LineContractNbrinto tmpFROM HSEnroll..Raw_Source rsmainWHERE 1=1AND (ISDATE(CANCELDT) = 1ORInterfaceType = 1)AND EditFlag <> 1GROUP BY LineContractNbrNow my temp table contains data like below:Linecontractindex LineContractNbr25 1082120003777 1082120004624 10821200074Now my raw_source table contains following records .These are sample recordsLineContractNbr Linecontractindex enrolldt canceldt10821200037 20 2009033110821200037 25 2009042810821200046 66 2008020310821200046 73 2008112110821200046 75 2009030410821200046 77 2009040110821200046 98 2011061410821200074 21 2010100610821200074 22 2010103010821200074 23 2011021510821200074 24 20110215 20110311Now to retrive the Enrolldt from Raw_source table there are multiplecase1.for any LineContractNbr if there is last record which is on canceldt in that case previous enrolldt should be retived.See LineContractNbr 10821200074 and 108212000372.for any LineContractNbr with the canceldt check next recordif it is there with enrolldt retrive that enrolldt see LineContractNbr 10821200046My table is having lakhs of records .These are the specific case which I have mentioned here.ThanksVIjay 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 MVPhttp://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 enrolldt10821200037 20 2009033110821200046 98 2011061410821200074 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)tCROSS 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 MVPhttp://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)tCROSS 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 )t1This query retrieving below data LineContractNbr Linecontractindex enrolldt010821200037 98 20110614010821200046 98 20110614010821200074 98 20110614above 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 thisif you want to put them use likeSELECT t.LineContractNbr,Linecontractindex, enrolldt FROM (SELECT DISTINCT LineContractNbr FROM raw_source)tCROSS 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 MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|