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 |
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-04-08 : 08:45:37
|
hello all,i have query in which i will get data from different tables here i need to show like in example For one profile ID [CODE] ProfileID MembershipID AmtPaid Status 11011 1234 1000 1 11011 1233 2000 0 11011 1232 1000 0 [/CODE]here ProfileID ,MembershipID ,Paid i got data from different tables now the only thing i need to show dynamic column Status and for Paid Column first row i need to give status As '1' and for the rest of rows need to be status zero '0' and same like another profile ID first Amountpaid row is 1 and rest belong to that profile Id is Zerosuggest meP.V.P.MOhan |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-08 : 09:00:36
|
Means Do you want to latest MembershipID's status as 1?DECLARE @tab TABLE(ProfileID INT, MembershipID INT, AmtPaid INt)INSERT INTO @tab SELECT 11011, 1234, 1000 union allSELECT 11011, 1233, 2000union allSELECT 11011, 1232, 1000 union allSELECT 11012, 1256, 1000 union allSELECT 11012, 1226, 2000SELECT ProfileID, MembershipID, AmtPaid, CASE WHEN RN= 1 THEN 1 ELSE 0 END StatusFROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY ProfileId ORDER BY MembershipID DESC) RN FROM @tab ) t |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-04-09 : 00:57:10
|
Hi chandu it worked charm....thanksP.V.P.MOhan |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-09 : 01:00:50
|
quote: Originally posted by mohan123 Hi chandu it worked charm....thanksP.V.P.MOhan
Welcome--Chandu |
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2013-04-09 : 05:44:58
|
for funselect *, case when MembershipID = max(MembershipID) over (partition by ProfileID) then 1 else 0 end from @tab |
|
|
|
|
|
|
|