[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

SQL Statement for Group By and Sum

Hello Experts,

Can you help to fix this SQL Statement:  I can select statement to show all the SONumber, But Can't get it to work for a total amount in Month.  Please view a script and picture  below

select
tblServiceOrders.ARCustomerNumber, tblServiceOrders.SONumber, tblServiceOrders.Status AS ServiceStatus, tblInvoices.status AS InvoiceStatus, tblInvoices.InvoiceDate, SUM(tblInvoiceDetail.TotalPostedCost) AS DetailTotalPostedCost, SUM(tblInvoiceDetail.TotalSellingPrice) AS DetailTotalSellingPrice,
SUM(tblInvoiceDetail.TotalSellingPrice) - SUM(tblInvoiceDetail.TotalPostedCost) as Margin
FROM tblInvoices
 INNER JOIN tblAccounts
            ON  tblInvoices.AccountNumber = tblAccounts.AccountNumber
       LEFT OUTER JOIN tblReps
            ON  tblInvoices.SalesRep = tblReps.RepNumber
       INNER JOIN tblInvoiceDetail
            ON  tblInvoices.InvoiceNumber = tblInvoiceDetail.InvoiceNumber
       LEFT OUTER JOIN tblInvoiceAssemblyDetail
            ON  tblInvoiceDetail.InvoiceDetailKeyID = tblInvoiceAssemblyDetail.FKInvoiceDetail
		INNER JOIN tblServiceOrders
			ON tblInvoices.AccountNumber = tblServiceOrders.AccountNumber
			
						
			WHERE tblInvoices.status = 'Posted' AND tblInvoiceDetail.TotalSellingPrice > 0 
			GROUP BY tblServiceOrders.ARCustomerNumber, tblServiceOrders.SONumber, tblServiceOrders.Status,tblInvoices.Status, tblInvoices.InvoiceDate

			ORDER By tblServiceOrders.SONumber ASC;

Open in new window


I want to be able to get a total amount for all items in that particular MONTH associated with SONumber.  We may have the same SONumber with different month because the Invoice is posted on different time. How do I get a total for all DetailTotalPostedCost and DetailTotalSellingPrice in July, June, and August...ect.  base on ID 30003

For example:

SONumber                            Date                                          DetailTotalPriceCosted                          DetailTotalSellingPrice
30003                                     2014-June                                  1111111111111
30003                                     2014-July                                    34444322
30003                                     2014-August                              443333

I would like to thank you for your help in advanced
sqlstatement.jpg
0
terminator_vn
Asked:
terminator_vn
4 Solutions
 
ste5anSenior DeveloperCommented:
E.g. with SQL Server 2012+

SELECT  SO.SONumber ,
        EOMONTH(I.InvoiceDate) ,
        SUM(ID.TotalPostedCost) AS DetailTotalPostedCost ,
        SUM(ID.TotalSellingPrice) AS DetailTotalSellingPrice
FROM    tblInvoices I
        INNER JOIN tblAccounts A ON I.AccountNumber = A.AccountNumber
        INNER JOIN tblServiceOrders SO ON I.AccountNumber = SO.AccountNumber
        INNER JOIN tblInvoiceDetail ID ON I.InvoiceNumber = ID.InvoiceNumber
        LEFT OUTER JOIN tblReps R ON I.SalesRep = R.RepNumber
        LEFT OUTER JOIN tblInvoiceAssemblyDetail IAD ON ID.InvoiceDetailKeyID = IAD.FKInvoiceDetail
WHERE   I.status = 'Posted'
        AND ID.TotalSellingPrice > 0
GROUP BY SO.SONumber ,
        EOMONTH(I.InvoiceDate);

Open in new window

0
 
Brian CroweCommented:
Let me know if this works and we can work on the format of the [Date] field if necessary.

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, tblInvoices.InvoiceDate), 0) AS [Date],
	tblServiceOrders.ARCustomerNumber,
	tblServiceOrders.SONumber,
	tblServiceOrders.Status AS ServiceStatus,
	tblInvoices.status AS InvoiceStatus,
	tblInvoices.InvoiceDate,
	SUM(tblInvoiceDetail.TotalPostedCost) AS DetailTotalPostedCost,
	SUM(tblInvoiceDetail.TotalSellingPrice) AS DetailTotalSellingPrice,
	SUM(tblInvoiceDetail.TotalSellingPrice) - SUM(tblInvoiceDetail.TotalPostedCost) as Margin
FROM tblInvoices
INNER JOIN tblAccounts
    ON  tblInvoices.AccountNumber = tblAccounts.AccountNumber
LEFT OUTER JOIN tblReps
    ON  tblInvoices.SalesRep = tblReps.RepNumber
INNER JOIN tblInvoiceDetail
    ON  tblInvoices.InvoiceNumber = tblInvoiceDetail.InvoiceNumber
LEFT OUTER JOIN tblInvoiceAssemblyDetail
    ON  tblInvoiceDetail.InvoiceDetailKeyID = tblInvoiceAssemblyDetail.FKInvoiceDetail
INNER JOIN tblServiceOrders
	ON tblInvoices.AccountNumber = tblServiceOrders.AccountNumber			
WHERE tblInvoices.status = 'Posted' AND tblInvoiceDetail.TotalSellingPrice > 0 
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, tblInvoices.InvoiceDate), 0), tblServiceOrders.ARCustomerNumber, tblServiceOrders.SONumber, tblServiceOrders.Status,tblInvoices.Status, tblInvoices.InvoiceDate, 
ORDER By tblInvoices.InvoiceDate, tblServiceOrders.SONumber ASC;

Open in new window

0
 
PortletPaulCommented:
To increase the level of summarization, you need to drop the level of detail see in each row. This basically means fine tuning both the select clause and the group by clause.

Often you reduce the number or columns, or here you need to "round" each date to a year/month

Below you will see I have actually added a column, but this is so that you get year, and in another column you get month. This occurs in the select clause, and again in the group by clause. This should get you started at least
SELECT
      tblServiceOrders.ARCustomerNumber
    , tblServiceOrders.SONumber
    , tblServiceOrders.Status                                                         AS ServiceStatus
    , tblInvoices.status                                                              AS InvoiceStatus
    , year(tblInvoices.InvoiceDate)
    , month(tblInvoices.InvoiceDate)
    , SUM(tblInvoiceDetail.TotalPostedCost)                                           AS DetailTotalPostedCost
    , SUM(tblInvoiceDetail.TotalSellingPrice)                                         AS DetailTotalSellingPrice
    , SUM(tblInvoiceDetail.TotalSellingPrice) - SUM(tblInvoiceDetail.TotalPostedCost) AS Margin
FROM tblInvoices
      INNER JOIN tblAccounts
                  ON tblInvoices.AccountNumber = tblAccounts.AccountNumber
      LEFT OUTER JOIN tblReps
                  ON tblInvoices.SalesRep = tblReps.RepNumber
      INNER JOIN tblInvoiceDetail
                  ON tblInvoices.InvoiceNumber = tblInvoiceDetail.InvoiceNumber
      LEFT OUTER JOIN tblInvoiceAssemblyDetail
                  ON tblInvoiceDetail.InvoiceDetailKeyID = tblInvoiceAssemblyDetail.FKInvoiceDetail
      INNER JOIN tblServiceOrders
                  ON tblInvoices.AccountNumber = tblServiceOrders.AccountNumber


WHERE tblInvoices.status = 'Posted'
      AND tblInvoiceDetail.TotalSellingPrice > 0
GROUP BY
      tblServiceOrders.ARCustomerNumber
    , tblServiceOrders.SONumber
    , tblServiceOrders.Status
    , tblInvoices.Status
    , year(tblInvoices.InvoiceDate)
    , month(tblInvoices.InvoiceDate)

ORDER BY
      tblServiceOrders.SONumber ASC;

Open in new window

ps: I won't be online for 8 hours or so.
0
 
terminator_vnAuthor Commented:
Thank you for yours help.  I will test and let you know.
0
 
terminator_vnAuthor Commented:
Thank you for all yours help.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now