August 21, 2017 at 11:31 am
Create table #Main
(
ID INT,
Group_Num Varchar(100)
)
INSERT INTO #Main(ID) values(1),(2),(3),(4)
Create table #Detail
(
ID INT,
Default_Flag char(1),
SPAP_IND Char(1),
Group_Num Int,
Premium Money
)
INSERT INTO #Detail(ID,Default_Flag,SPAP_IND,Group_Num,Premium) values(1,'Y','N',123,12)
INSERT INTO #Detail(ID,Default_Flag,SPAP_IND,Group_Num,Premium) values(1,'Y','N',124,NULL)
INSERT INTO #Detail(ID,Default_Flag,SPAP_IND,Group_Num,Premium) values(2,'Y','N',121,12)
INSERT INTO #Detail(ID,Default_Flag,SPAP_IND,Group_Num,Premium) values(2,'Y','N',1231,NULL)
INSERT INTO #Detail(ID,Default_Flag,SPAP_IND,Group_Num,Premium) values(2,'N','N',1212,12)
INSERT INTO #Detail(ID,Default_Flag,SPAP_IND,Group_Num,Premium) values(2,'N','N',123,NULL)
INSERT INTO #Detail(ID,Default_Flag,SPAP_IND,Group_Num,Premium) values(3,'N','N',1255,12)
INSERT INTO #Detail(ID,Default_Flag,SPAP_IND,Group_Num,Premium) values(3,'N','N',155,12)
INSERT INTO #Detail(ID,Default_Flag,SPAP_IND,Group_Num,Premium) values(3,'N','N',855,NULL)
INSERT INTO #Detail(ID,Default_Flag,SPAP_IND,Group_Num,Premium) values(3,'N','Y',899,NULL)
INSERT INTO #Detail(ID,Default_Flag,SPAP_IND,Group_Num,Premium) values(4,'Y','N',899,NULL)
I feel little complicated, i will try my best to explain this,
end result is to get one group number from detail for one ID in main table
1) Join should happen based on ID
2) yes, Multiple records are there in detail table, below is the steps need to follow to get one
Priority is default flag
if Default flag is "Y" and result is one row (for example id 4 having one record, so we can directly updated group num)
if Default flag is "Y" and multiple rows -- check for premium if it is null take the value of group number (example ID 1)
3) If Default flag is "N" for all records then check for premium -if premium having 2 records nulls check spap_id indicator should be yes
process should be end if i get one record any point of time, if not continue the same.
so i have to check each time how many distinct group number have got for one ID until i get one
Let me know if any questions, thanks for looking into it, Much appreciated
August 21, 2017 at 12:07 pm
Based on your sample data, what results do you wish to see?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 21, 2017 at 12:13 pm
Phil Parkin - Monday, August 21, 2017 12:07 PMBased on your sample data, what results do you wish to see?
For ID 1 group number 124, ID 2 group number 1231, ID 3 group number 899, ID 4 group number 899, Thanks..one group number for one id
August 21, 2017 at 12:59 pm
Maybe something like this? It considers that the only values for the char(1) columns are Y or N.
WITH CTE AS(
SELECT m.ID, d.Group_Num, ROW_NUMBER() OVER( PARTITION BY m.ID ORDER BY Default_Flag DESC, Premium, SPAP_IND DESC) rn
FROM #Main m
JOIN #Detail d ON m.ID = d.ID
)
SELECT ID, Group_Num
FROM CTE
WHERE rn = 1;
August 21, 2017 at 1:01 pm
how about this?SELECT m.ID, t.Group_Num
FROM #Main m
CROSS APPLY (SELECT TOP 1 * FROM #Detail d WHERE d.ID = m.ID ORDER BY Default_Flag DESC, d.Premium, SPAP_IND DESC) t
EDIT: second time today someone beat me to it 😛
August 21, 2017 at 1:14 pm
Chris Harshman - Monday, August 21, 2017 1:01 PMEDIT: second time today someone beat me to it 😛
I just got lucky with the right timing 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply