membership sql - SQL Server Forums

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.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

membership sql

Author

Topic

kostaras
Starting Member

2 Posts

Posted - 2015-01-07 : 17:20:13

I have a project about gym management and I want to check monthly payments.

For example, I want to check the last time a member paid, if the payment day was 30 days before, and if his status is active.

Here is my code, but it needs to be fixed.

 SELECT * FROM (
               SELECT 
               pi.FighterID, 
               FName, 
               LName, 
               Convert(varchar(20), 
               PaymentDay,1), 
               PaymentDescr,
               PaymentAmount, 
               Active,
               ROW_NUMBER() OVER (PARTITION BY pi.FighterID ORDER BY PaymentDay DESC) rn
                        FROM 
                          FightersInfo fi LEFT JOIN 
                          PaymentInfo pi ON pi.FighterID = fi.FighterID
                        WHERE 
                           NOT EXISTS (SELECT * FROM PaymentInfo WHERE FighterID = fi.FighterID
                                                                                     AND DATEDIFF(day, PaymentDay, GETDATE()) <= 30 )
                                                                  AND Active =1) t
                                                            WHERE rn = 1

This only returns the first member. If I remove WHERE rn =1, it returns all payments. I only want the last payment of each member.

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-08 : 09:02:17

SELECT * FROM (
SELECT pi.FighterID, FName, LName,
Convert(varchar(20), max(PaymentDay),1),
PaymentDescr, PaymentAmount, Active
FROM FightersInfo fi
LEFT JOIN PaymentInfo pi
ON pi.FighterID = fi.FighterID
WHERE Active =1
Group By pi.FighterID, FName, LName, PaymentDescr, PaymentAmount, Active
Having DATEDIFF(day, max(PaymentDay) , GETDATE()) >= 30
) t

Regards
Viggneshwar A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources