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
terminator_vnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 CroweDatabase AdministratorCommented:
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
PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.