Author |
Topic |
eugz
Posting Yak Master
210 Posts |
Posted - 2012-12-01 : 23:42:57
|
Hi All.I have select it works fine. But I would like to modify it to display Active field from dbo.Payments table if one of record with the same Request_Id has Actuve='True'. Now it display only for latest PaidDate. If is it possible how it to do? My code is:select r.Request_Id,Customer_No,LName,FName,s.Status,t.EffDate,t.Active,Amountfrom dbo.Request rLEFT JOIN(SELECT p.*FROM dbo.Payments pINNER JOIN (SELECT Request_Id,MAX(PaidDate) AS EffDate FROM dbo.Payments GROUP BY Request_ID) xON p.Request_Id = x.Request_IdAND p.PaidDate = x.EffDate)tON t.Request_ID = r.Request_IDLeft join dbo.v_Status son t.Status_Id = s.Status_Id Thanks |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-02 : 01:52:29
|
[code]elect r.Request_Id,Customer_No,LName,FName,s.Status,t.EffDate,t.Active,Amountfrom dbo.Request rLEFT JOIN(SELECT p.*FROM dbo.Payments pINNER JOIN (SELECT Request_Id FROM dbo.Payments GROUP BY Request_ID HAVING SUM(CASE WHEN Active = 'true' THEN 1 ELSE 0 END) > 0) xON p.Request_Id = x.Request_Id)tON t.Request_ID = r.Request_IDLeft join dbo.v_Status son t.Status_Id = s.Status_Id[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-02 : 10:28:23
|
[code]select r.Request_Id,Customer_No,LName,FName,s.Status,t.PaidDate,t.Active,Amountfrom dbo.Request rLEFT JOIN(SELECT p.*,ROW_NUMBER() OVER(PARTITION BY p.RequestId Order by Case WHEN Active = 'true' THEN 1 ELSE 0 END) as SeqFROM dbo.Payments p)x ON p.Request_Id = r.Request_Id and Seq = 1Left join dbo.v_Status son t.Status_Id = s.Status_Id[/code] |
|
|
eugz
Posting Yak Master
210 Posts |
Posted - 2012-12-02 : 12:59:56
|
Thanks for replays.I would like to have select with latest PaidDate and display Active field from dbo.Payments table if one of record with the same Request_Id has Actuve='True'. Is that possible to get? If yes how it to do?Thanks. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-02 : 19:28:30
|
[code]select r.Request_Id,Customer_No,LName,FName,s.Status,t.PaidDate,t.Active,Amountfrom dbo.Request rLEFT JOIN(SELECT p.*,ROW_NUMBER() OVER(PARTITION BY p.RequestId Order by t.PaidDate desc,Case WHEN Active = 'true' THEN 1 ELSE 0 END) as SeqFROM dbo.Payments p)x ON p.Request_Id = r.Request_Id and Seq = 1Left join dbo.v_Status son t.Status_Id = s.Status_Id[/code] |
|
|
eugz
Posting Yak Master
210 Posts |
Posted - 2012-12-02 : 22:26:39
|
Hi sodeep. Thanks for replay.Your code return the same result like I had just for latest PaidDate without Active = 'True'. For instance, I haveRequest_Id PaidDate Active=========================================3 6/3/2011 FALSE3 9/20/2011 TRUE3 12/1/2012 FALSEAs the resalt I would like to getRequest_Id PaidDate Active=========================================3 12/1/2012 TRUEThanks |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-02 : 23:55:08
|
quote: Originally posted by eugz Hi sodeep. Thanks for replay.Your code return the same result like I had just for latest PaidDate without Active = 'True'. For instance, I haveRequest_Id PaidDate Active=========================================3 6/3/2011 FALSE3 9/20/2011 TRUE3 12/1/2012 FALSEAs the resalt I would like to getRequest_Id PaidDate Active=========================================3 12/1/2012 TRUEThanks
But Active is True for 9/20/2011 ? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-03 : 07:41:28
|
sound like this thenSelect r.Request_Id,Customer_No,LName,FName,s.Status,t.EffDate,CASE WHEN ActiveCnt > 0 THEN 'True' ELSE t.Active END,Amountfrom dbo.Request rLEFT JOIN(SELECT p.*FROM dbo.Payments pINNER JOIN (SELECT Request_Id,MAX(PaidDate) AS EffDate, SUM(CASE WHEN Active = 'true' THEN 1 ELSE 0 END) AS ActiveCnt FROM dbo.Payments GROUP BY Request_ID) xON p.Request_Id = x.Request_IdAND p.PaidDate = x.EffDate)tON t.Request_ID = r.Request_IDLeft join dbo.v_Status son t.Status_Id = s.Status_Id ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-03 : 16:39:01
|
quote: Originally posted by visakh16 sound like this thenSelect r.Request_Id,Customer_No,LName,FName,s.Status,t.EffDate,CASE WHEN ActiveCnt > 0 THEN 'True' ELSE 'False' END,Amountfrom dbo.Request rLEFT JOIN(SELECT p.*FROM dbo.Payments pINNER JOIN (SELECT Request_Id,MAX(PaidDate) AS EffDate, SUM(CASE WHEN Active = 'true' THEN 1 ELSE 0 END) AS ActiveCnt FROM dbo.Payments GROUP BY Request_ID) xON p.Request_Id = x.Request_IdAND p.PaidDate = x.EffDate)tON t.Request_ID = r.Request_IDLeft join dbo.v_Status son t.Status_Id = s.Status_Id ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Shouldn't it be this one? |
|
|
eugz
Posting Yak Master
210 Posts |
Posted - 2012-12-03 : 17:13:04
|
Hi visakh16. Thanks for replays.I tried your code. In lineCASE WHEN ActiveCnt > 0 THEN 'True' ELSE 'False' ENDActiveCnt indicate like invalid column name. I tried modify like t.ActiveCtn nothing help. When I run INNER JOIN SELECT itself result looks ok but when I run whole code result not like expected.Thanks. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-03 : 23:06:27
|
You need to explain with proper example and expected output. The output you want is confusing. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-04 : 00:58:52
|
quote: Originally posted by eugz Hi visakh16. Thanks for replays.I tried your code. In lineCASE WHEN ActiveCnt > 0 THEN 'True' ELSE 'False' ENDActiveCnt indicate like invalid column name. I tried modify like t.ActiveCtn nothing help. When I run INNER JOIN SELECT itself result looks ok but when I run whole code result not like expected.Thanks.
then i'm sure you've some other part too which you're not explaining us. Please post you exact scenario with sample data if you need more help------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
eugz
Posting Yak Master
210 Posts |
Posted - 2012-12-04 : 17:15:46
|
Hi visakh16. Thanks for continue help.I solved the problem by modified the lineSELECT p.*toSELECT p.*,EffDate,ActiveCntThanks for help. |
|
|
|