Click here to Skip to main content
15,891,777 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
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


I want output like

XML
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
Posted
Updated 28-Mar-14 0:46am
v2

1 solution

On SQL Server you can use PIVOT to create a cross-tab query. See the following link: Crosstab Queries using PIVOT[^].
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900