How to Group By day and get a total from the Amount column per day?

• chef423

  SSCertifiable

  Points: 5279

  More actions

  February 9, 2017 at 10:40 am

  #340461

  Select Invoice_Exceptions.DateTime, Invoice_Exceptions.Invoice_Number, Invoice_Exceptions.ItemNum, Inventory.ItemName, Invoice_Exceptions.Reason_Code, Invoice_Exceptions.LineNum, Invoice_Exceptions.Amount, Invoice_Exceptions.Quantity, SUM((Invoice_Exceptions.Amount) * (Invoice_Exceptions.Quantity)) AS Voided_Total, Invoice_Totals.Payment_Method
  From Invoice_Exceptions
  Inner join Inventory
  ON Invoice_Exceptions.ItemNum = Inventory.ItemNum
  Inner Join Invoice_Totals
  ON Invoice_Exceptions.Invoice_Number = Invoice_Totals.Invoice_Number
  where Invoice_Exceptions.DateTime between '2017-01-01 04:50:08.000' and '2017-02-08 03:50:08.000' and Invoice_Totals.Payment_Method = 'CA'
  GROUP BY Invoice_Exceptions.DateTime, Invoice_Totals.Payment_Method, Invoice_Exceptions.Invoice_Number, Invoice_Exceptions.ItemNum, Inventory.ItemName, Invoice_Exceptions.Reason_Code, Invoice_Exceptions.LineNum, Invoice_Exceptions.Amount, Invoice_Exceptions.Quantity, day(Invoice_Exceptions.DateTime)
  Order by Invoice_Exceptions.DateTime

  The SQL code above gives me the data that I need...these are invoices in a POS system. But i need to group by individual days, ala, Jan 1, Jan 2 etc..and get the Total Amount for that day [Invoice_Exceptions.Amount]

  Any help appreciated.

  Chris

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 9, 2017 at 11:29 am

  #1927610

  i think this will do it.
  i'm using datemath to round the dates down to midnight of each date, and grouping by that

  select DATEADD(dd, DATEDIFF(dd,0,Invoice_Exceptions.DateTime), 0) As ExceptionDate,
SUM(Invoice_Exceptions.Amount) As TotalAmount
FROM
Invoice_Exceptions
INNER JOIN Inventory
   ON Invoice_Exceptions.ItemNum = Inventory.ItemNum
INNER JOIN Invoice_Totals
   ON Invoice_Exceptions.Invoice_Number = Invoice_Totals.Invoice_Number
WHERE
Invoice_Exceptions.DateTime BETWEEN '2017-01-01 04:50:08.000' AND '2017-02-08 03:50:08.000'
AND Invoice_Totals.Payment_Method = 'CA'
GROUP  BY
DATEADD(dd, DATEDIFF(dd,0,Invoice_Exceptions.DateTime), 0)
ORDER  BY
ExceptionDatee


  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• chef423

  SSCertifiable

  Points: 5279

  More actions

  February 9, 2017 at 11:56 am

  #1927615

  Lowell - Thursday, February 9, 2017 11:29 AM

  i think this will do it.
  i'm using datemath to round the dates down to midnight of each date, and grouping by that

  select DATEADD(dd, DATEDIFF(dd,0,Invoice_Exceptions.DateTime), 0) As ExceptionDate,
SUM(Invoice_Exceptions.Amount) As TotalAmount
FROM
Invoice_Exceptions
INNER JOIN Inventory
   ON Invoice_Exceptions.ItemNum = Inventory.ItemNum
INNER JOIN Invoice_Totals
   ON Invoice_Exceptions.Invoice_Number = Invoice_Totals.Invoice_Number
WHERE
Invoice_Exceptions.DateTime BETWEEN '2017-01-01 04:50:08.000' AND '2017-02-08 03:50:08.000'
AND Invoice_Totals.Payment_Method = 'CA'
GROUP  BY
DATEADD(dd, DATEDIFF(dd,0,Invoice_Exceptions.DateTime), 0)
ORDER  BY
ExceptionDatee


  Thank you for the response, but that omits my other fields...Id love to have the totals all on the same table. Is that possible?

• chef423

  SSCertifiable

  Points: 5279

  More actions

  February 9, 2017 at 11:59 am

  #1927616

  chef423 - Thursday, February 9, 2017 11:56 AM

  Lowell - Thursday, February 9, 2017 11:29 AM

  i think this will do it.
  i'm using datemath to round the dates down to midnight of each date, and grouping by that

  select DATEADD(dd, DATEDIFF(dd,0,Invoice_Exceptions.DateTime), 0) As ExceptionDate,
SUM(Invoice_Exceptions.Amount) As TotalAmount
FROM
Invoice_Exceptions
INNER JOIN Inventory
   ON Invoice_Exceptions.ItemNum = Inventory.ItemNum
INNER JOIN Invoice_Totals
   ON Invoice_Exceptions.Invoice_Number = Invoice_Totals.Invoice_Number
WHERE
Invoice_Exceptions.DateTime BETWEEN '2017-01-01 04:50:08.000' AND '2017-02-08 03:50:08.000'
AND Invoice_Totals.Payment_Method = 'CA'
GROUP  BY
DATEADD(dd, DATEDIFF(dd,0,Invoice_Exceptions.DateTime), 0)
ORDER  BY
ExceptionDatee


  Thank you for the response, but that omits my other fields...Id love to have the totals all on the same table. Is that possible?

  Id love to have the TotalAmount, TotalQuantity, VoidedTotal on a separate line below each day (1st, 2nd, 3rd, etc)

• Jeff Moden

  SSC Guru

  Points: 1004318

  More actions

  February 9, 2017 at 12:04 pm

  #1927618

  Lowell - Thursday, February 9, 2017 11:29 AM

  i think this will do it.
  i'm using datemath to round the dates down to midnight of each date, and grouping by that

  select DATEADD(dd, DATEDIFF(dd,0,Invoice_Exceptions.DateTime), 0) As ExceptionDate,
SUM(Invoice_Exceptions.Amount) As TotalAmount
FROM
Invoice_Exceptions
INNER JOIN Inventory
   ON Invoice_Exceptions.ItemNum = Inventory.ItemNum
INNER JOIN Invoice_Totals
   ON Invoice_Exceptions.Invoice_Number = Invoice_Totals.Invoice_Number
WHERE
Invoice_Exceptions.DateTime BETWEEN '2017-01-01 04:50:08.000' AND '2017-02-08 03:50:08.000'
AND Invoice_Totals.Payment_Method = 'CA'
GROUP  BY
DATEADD(dd, DATEDIFF(dd,0,Invoice_Exceptions.DateTime), 0)
ORDER  BY
ExceptionDatee


  Good idea.  You don't need the DATEADD in the GROUP BY, though.  It's just using up clock cycles.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 9, 2017 at 12:13 pm

  #1927623

  chef423 - Thursday, February 9, 2017 11:59 AM

  chef423 - Thursday, February 9, 2017 11:56 AM

  Lowell - Thursday, February 9, 2017 11:29 AM

  i think this will do it.
  i'm using datemath to round the dates down to midnight of each date, and grouping by that

  select DATEADD(dd, DATEDIFF(dd,0,Invoice_Exceptions.DateTime), 0) As ExceptionDate,
SUM(Invoice_Exceptions.Amount) As TotalAmount
FROM
Invoice_Exceptions
INNER JOIN Inventory
   ON Invoice_Exceptions.ItemNum = Inventory.ItemNum
INNER JOIN Invoice_Totals
   ON Invoice_Exceptions.Invoice_Number = Invoice_Totals.Invoice_Number
WHERE
Invoice_Exceptions.DateTime BETWEEN '2017-01-01 04:50:08.000' AND '2017-02-08 03:50:08.000'
AND Invoice_Totals.Payment_Method = 'CA'
GROUP  BY
DATEADD(dd, DATEDIFF(dd,0,Invoice_Exceptions.DateTime), 0)
ORDER  BY
ExceptionDatee


  Thank you for the response, but that omits my other fields...Id love to have the totals all on the same table. Is that possible?

  Id love to have the TotalAmount, TotalQuantity, VoidedTotal on a separate line below each day (1st, 2nd, 3rd, etc)

  you would just add num SUM() or MIN() or MAX() or COUNT() for each of the columns you are talking about, then...the group by would still be the same.

  I'm pretty sure the group by would be manditory in this case.
  [SELECT
DATEADD(dd, DATEDIFF(dd,0,Invoice_Exceptions.DateTime), 0) As ExceptionDate,
MAX(Invoice_Exceptions.Invoice_Number) AS MaxInvoiceNumber,
MIN(Invoice_Exceptions.ItemNum) AS MinItemNum,
COUNT(Inventory.ItemName) AS CountItemName,
COUNT(Invoice_Exceptions.Reason_Code) AS CountReason_Code,
COUNT(Invoice_Exceptions.LineNum) AS CountLineNum,
SUM(Invoice_Exceptions.Amount) As TotalAmount,
SUM(Invoice_Exceptions.Quantity) AS TotalQuantity,
SUM(( Invoice_Exceptions.Amount ) * ( Invoice_Exceptions.Quantity )) AS Voided_Total,
Invoice_Totals.Payment_Method
FROM
Invoice_Exceptions
INNER JOIN Inventory
   ON Invoice_Exceptions.ItemNum = Inventory.ItemNum
INNER JOIN Invoice_Totals
   ON Invoice_Exceptions.Invoice_Number = Invoice_Totals.Invoice_Number
WHERE
Invoice_Exceptions.DateTime BETWEEN '2017-01-01 04:50:08.000' AND '2017-02-08 03:50:08.000'
AND Invoice_Totals.Payment_Method = 'CA'
GROUP  BY
DATEADD(dd, DATEDIFF(dd,0,Invoice_Exceptions.DateTime), 0)
ORDER  BY
ExceptionDate


  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  February 10, 2017 at 8:57 am

  #1927815

  How about this:

  
SELECT CAST(IX.[DateTime] AS date) AS [Date], IX.Invoice_Number, IX.ItemNum, I.ItemName, IX.Reason_Code, IX.LineNum,
    IX.Amount, IX.Quantity, SUM(IX.Amount * IX.Quantity) AS Voided_Total, IT.Payment_Method
FROM Invoice_Exceptions AS IX
    INNER JOIN Inventory AS I
        ON IX.ItemNum = I.ItemNum
    INNER JOIN Invoice_Totals AS IT
        ON IX.Invoice_Number = IT.Invoice_Number
WHERE IX.DateTime BETWEEN '2017-01-01 04:50:08.000' AND '2017-02-08 03:50:08.000'
    AND IT.Payment_Method = 'CA'
GROUP BY CAST(IX.[DateTime] AS date), IT.Payment_Method, IX.Invoice_Number, IX.ItemNum, I.ItemName, IX.Reason_Code,
    IX.LineNum, IX.Amount, IX.Quantity
ORDER BY IX.[DateTime];


  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Jeff Moden

  SSC Guru

  Points: 1004318

  More actions

  February 10, 2017 at 9:21 am

  #1927821

  Lots of the code posted is great.  The problem that I see with most of it is the omission of "zero" days that will occur.  You need a reference that contains all days if you want to report on "zero" days.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)