| Author | Message | ||
Gandhiguevara Legend Username: Gandhiguevara Post Number: 55934 Registered: 10-2009 Posted From: 204.61.207.7 Rating: N/A |
hmmmm....let me check if my output is as expected | ||
Driverramudu Hero Username: Driverramudu Post Number: 14496 Registered: 02-2009 Posted From: 76.95.174.87 Rating: N/A |
if U use not exists u will not get all the rows where as in your output u r expecting all the rows back isn't it? Driving is my PASSION. Ball or Bimmer does not matter. | ||
Gandhiguevara Legend Username: Gandhiguevara Post Number: 55932 Registered: 10-2009 Posted From: 204.61.207.7 Rating: N/A |
driver kaka....thanks so much for your time... Actually Not Exists petti self join chesthe 2 lines lo aipoyindi....validate chesthunnaa...adi correct kaaka pothe nuvvu cheppindhi try chesthaa | ||
Driverramudu Hero Username: Driverramudu Post Number: 14491 Registered: 02-2009 Posted From: 76.95.174.87 Rating: N/A |
Correction ; WITH GroupInfo AS ( SELECT * ,GroupRowId = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Row_number DESC) FROM dbo.Groups ) SELCT gi.* ,NewFlag = CASE WHEN lr.Id IS NULL THEN NULL WHEN lr.Id IS NOT NULL AND lr.vedhava_yn = 'Y' CASE WHEN pr.IsPriorVedhava_yn = 1 THEN 'Y' ELSE 'N' END ELSE NULL END FROM GroupInfo AS gi OUTER APPLY ( SELECT * FROM GroupInfo AS igi WHERE igi.Id = gi.Id AND igi.Row_number = gi.Row_Number AND igi.GroupRowId = 1 ) AS lr OUTER APPLY ( SELECT TOP 1 1 AS IsPriorVedhava_yn FROM GroupInfo AS igi WHERE igi.Id = gi.Id --- AND igi.Row_number = gi.Row_Number --> Comment this line this condition is --invalid AND igi.GroupRowId > 1 AND igo.vedhava_yn = 'Y' ORDER BY igo.GroupRowId ) AS pr Driving is my PASSION. Ball or Bimmer does not matter. | ||
Driverramudu Hero Username: Driverramudu Post Number: 14490 Registered: 02-2009 Posted From: 76.95.174.87 Rating: N/A |
u r tool has Driving is my PASSION. Ball or Bimmer does not matter. | ||
Driverramudu Hero Username: Driverramudu Post Number: 14489 Registered: 02-2009 Posted From: 76.95.174.87 Rating: N/A |
provided u r tool as outer apply operators if not let me know i would give alternate query Driving is my PASSION. Ball or Bimmer does not matter. | ||
Driverramudu Hero Username: Driverramudu Post Number: 14488 Registered: 02-2009 Posted From: 76.95.174.87 Rating: N/A |
; WITH GroupInfo AS ( SELECT * ,GroupRowId = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Row_number DESC) FROM dbo.Groups ) SELCT gi.* ,NewFlag = CASE WHEN lr.Id IS NULL THEN NULL WHEN lr.Id IS NOT NULL AND lr.vedhava_yn = 'Y' CASE WHEN pr.IsPriorVedhava_yn = 1 THEN 'Y' ELSE 'N' END ELSE NULL END FROM GroupInfo AS gi OUTER APPLY ( SELECT * FROM GroupInfo AS igi WHERE igi.Id = gi.Id AND igi.Row_number = gi.Row_Number AND igi.GroupRowId = 1 ) AS lr OUTER APPLY ( SELECT TOP 1 1 AS IsPriorVedhava_yn FROM GroupInfo AS igi WHERE igi.Id = gi.Id AND igi.Row_number = gi.Row_Number AND igi.GroupRowId > 1 AND igo.vedhava_yn = 'Y' ORDER BY igo.GroupRowId ) AS pr Driving is my PASSION. Ball or Bimmer does not matter. | ||
Driverramudu Hero Username: Driverramudu Post Number: 14487 Registered: 02-2009 Posted From: 76.95.174.87 Rating: N/A |
Just saw ur mail let me understand clearly so I can come up with correct query Driving is my PASSION. Ball or Bimmer does not matter. | ||
Mass Moderator Username: Mass Post Number: 3182 Registered: 10-2014 Rating: N/A |
where rownumber not in (select max(row_number))ettachu ga Catalyst,Aquarian,Rusthum, Brickmansions Abhimanini | ||
Gandhiguevara Legend Username: Gandhiguevara Post Number: 55931 Registered: 10-2009 Posted From: 204.61.207.7 Rating: N/A |
Bump | ||
Gandhiguevara Legend Username: Gandhiguevara Post Number: 55929 Registered: 10-2009 Posted From: 204.61.207.7 Rating: N/A |
Kochen entante oka group lo last record tappa migina records annitlo oka condition ki match ayyaayoo ledho check cheyyadam elaa? thelivigaa row_number add chesaa each group ki | ||
Gandhiguevara Legend Username: Gandhiguevara Post Number: 55928 Registered: 10-2009 Posted From: 204.61.207.7 Rating: N/A |
Kalbat poth vundan...kalkeya vatt kismbeth pet moth kaatkaat | ||
Gandhiguevara Legend Username: Gandhiguevara Post Number: 55927 Registered: 10-2009 Posted From: 204.61.207.7 Rating: N/A |
Conditions: 1) In a group if the last record is VEDHAVA_YN = 'Y' and in that group no other records are not vedhava ...Flag 'N 2) If the last record is VEDHAVA_YN = 'Y' and any other records in the group has VEDHAVA_YN = 'Y' then flag 'Y' How? | ||
Mass Moderator Username: Mass Post Number: 3181 Registered: 10-2014 Rating: N/A |
e kya hora rey kaliya? Catalyst,Aquarian,Rusthum, Brickmansions Abhimanini | ||
Gandhiguevara Legend Username: Gandhiguevara Post Number: 55926 Registered: 10-2009 Posted From: 204.61.207.7 Rating: N/A |
ID DATE VEDHAVA_YN LAST_GROUP_REC_YN ROW_NUMBER 1 1/1/2015 N N 1 1 1/1/2015 N N 2 1 1/1/2015 N N 3 1 1/1/2015 Y Y 4 2 1/2/2015 N N 1 2 1/2/2015 Y N 2 2 1/2/2015 N N 3 2 1/2/2015 N N 4 2 1/2/2015 Y Y 5 RESULT SET : ID DATE VEDHAVA_YN LAST_GROUP_REC_YN ROW_NUMBER FLAG 1 1/1/2015 N N 1 1 1/1/2015 N N 2 1 1/1/2015 N N 3 1 1/1/2015 Y Y 4 2 1/2/2015 N N 1 2 1/2/2015 Y N 2 2 1/2/2015 N N 3 2 1/2/2015 N N 4 2 1/2/2015 Y Y 5 Y |