DECLARE @month INT SET @month=1 SELECT CLOI_ClientOrderItems.cl_Id, NoOfInv = SUM(CASE WHEN DATEPART(mm, in_date_issued) <= @month AND DATEPART(yyyy, in_date_issued) = 2014 THEN 1 ELSE 0 END), MonthTotal = SUM(CASE WHEN DATEPART(mm, in_date_issued) <= @month AND DATEPART(yyyy, in_date_issued) = 2014 THEN in_total ELSE 0 END), Grandtotal = SUM(in_total), RemainingAmount = SUM(in_total) - SUM(CASE WHEN DATEPART(mm, in_date_issued) <= @month THEN in_total ELSE 0 END) FROM (SELECT DISTINCT MasterOrderId, cl_Id FROM CLOI_ClientOrderItems) as CLOI_ClientOrderItems INNER JOIN IN_Invoices ON IN_Invoices.MasterOrderId = CLOI_ClientOrderItems.MasterOrderId GROUP BY CLOI_ClientOrderItems.cl_id
noofinv |amt |clid | grandtotal | jan |feb |march |remainingamt --------------------------------------------------------------------------------------------- 5 |7.00 |100000_Pri | 245.00 | 0.00 |238.00 |7.00 |238.00 12 |2510.12 |100001_pro | 181110.29 | 138891.92 |9708.25 |510.12 |178600
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)