CODEWITHSUNDEEP
sqldatabaset-sqlsql-server-2014
ghulam_ali
ghulam_ali

Reputation: 295

Select Records against all months of a given year form one table with records from second table

I have two tables in my database:

I want to select month wise payments made by a clients in a year. And also amount paid before the start of that year. My desired result set is

Let's say for the year 2019: 

ClientName, RegistrationNumbers, PreviouslyPaidAmount (i.e. all amounts paid in 2018), AmountPaidInJuly 2019, AmountPaidInAugust2019. ..... AmountPaidIn Jun2020

where month is EntryDate from MembershipInstallments table.

I tried it like this, but it takes too long for large data set:

SELECT 
    ClientName, RegistrationNo,
    (SELECT ISNULL(SUM(AmountPaid),0)
     FROM MembershipInstallments 
     WHERE ProjectId = Membership.ProjectId 
       AND AllotmentId = Membership.AllotmentId 
       AND EntryDate < @fromDate) ASPreviouslyPaid,
    (SELECT ISNULL(SUM(AmountPaid), 0)
     FROM MembershipInstallments 
     WHERE ProjectId = Membership.ProjectId 
       AND AllotmentId = Memberhsip.AllotmentId 
       AND (MONTH(EntryDate) = 7) AND (YEAR(EntryDate) = YEAR(@fromDate)) AS JulyPayment
FROM 
    Memberships 
WHERE 
    ProjectId = @projectId

Any help will be highly appreciated.

Upvotes: 0

Views: 44

Answers (1)

dnoeth
dnoeth

Reputation: 60482

Scalar Subqueries tend to be badly optimized. In your case (same join-conditions) you can simply utilize conditional aggregation:

SELECT ms.ClientName,ms.RegistrationNo,
   Sum(CASE WHEN mi.EntryDate<@fromDate THEN mi.AmountPaid ELSE 0 end) AS PreviouslyPaid,
   Sum(CASE WHEN (Month(mi.EntryDate) = 7) AND (Year(mi.EntryDate) = Year(@fromDate)) THEN mi.AmountPaid ELSE 0)
FROM Memberships AS ms
LEFT JOIN MembershipInstallments AS mi -- don't know if Outer join is really needed
  ON mi.ProjectId=ms.ProjectId
  AND mi.AllotmentId=ms.AllotmentId
WHERE ms.ProjectId=@projectId

You might be able to do the aggregation before the join, i.e. in a CTE to further improve performance:

WITH cte AS
 (
   SELECT  
      ProjectId, AllotmentId,
      Sum(CASE WHEN EntryDate<@fromDate THEN AmountPaid ELSE 0 END) AS PreviouslyPaid,
      Sum(CASE WHEN (Month(EntryDate) = 7) AND (Year(EntryDate) = Year(@fromDate)) THEN AmountPaid ELSE 0 END) AS JulyPayment
   FROM MembershipInstallments 
   WHERE ProjectId=@projectId
   GROUP BY ProjectId, AllotmentId
 )
SELECT ms.ClientName, ms.RegistrationNo, cte.PreviouslyPaid, cte.JulyPayment
FROM Memberships AS ms
JOIN cte
  ON cte.ProjectId=ms.ProjectId
  AND cte.AllotmentId=ms.AllotmentId
WHERE ms.ProjectId=@projectId

In both cases double-check if the result is correct (depends on the actual relation between those tables)

Upvotes: 2

Related Questions