Separate Date from DateTime stamp then display by date totals

Question

I am very new to SQL, well rather, I have not used it in 8 years or so. This is being done in a restaurant point-of-sale system that uses SQL Server. Anyhow, here is my current code:

select cashier_id,grand_total, num_people_party, datetime
from invoice_totals
where datetime >= '2012-06-01'

Ultimately I want to display a report that lists the total number of people served (num_people_served) and the grand total (grand_total) by the cashier id (cashier_id) for each date.

So:

Cashier ID 223
2012-06-01   10   200.00
2012-06-02   23   245.00

Cashier ID 224
2012-06-01   15   123.00
2012-06-02   24   254.00

etc...

This should not be too tough, but I just don't remember and my searches have not proven useful. Also, it should be noted that the DateTime field will need to be split in order to group the totals by date.

In adding the dividion field from the comment below, I get the following error once it is added:

Message: Could not retrieve datatable.
SELECT CASHIER_ID AS SERVER, CONVERT(VARCHAR(10),[DATETIME],111) AS DATE, SUM(GRAND_TOTAL) AS TOTAL_SALES, SUM(NUM_PEOPLE_PARTY) AS NUMBER_SERVED, SUM(GRAND_TOTAL) / SUM(NUM_PEOPLE_PARTY) AS CASHPERCUSTOMER
FROM INVOICE_TOTALS
WHERE [DATETIME] >= '2012-06-01'
GROUP BY CASHIER_ID, CONVERT(VARCHAR(10),[DATETIME],111) 
Stack Trace:    at PCA_DataMod.PCA_DB.OpenDT(String SQLLoadInfo, Boolean NoSchema)
   at PCA_DataMod.PCA_DB.OpenDT(String SQLLoadInfo)
   at pcAmerica.Desktop.POS.Forms.frmReportCustomWiz.GetRawSQLFields()
   at pcAmerica.Desktop.POS.Forms.frmReportCustomWiz.Display_Step(WizardStep Index)

Message: Divide by zero error encountered.
Stack Trace:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.HasMoreRows()
   at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
   at System.Data.SqlClient.SqlDataReader.Read()
   at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at PCA_DataMod.PCA_DB.OpenDT(String SQLLoadInfo, Boolean NoSchema)

Any ideas?

possible duplicate of [Most efficient way in SQL Server to get date from date+time?](http://stackoverflow.com/questions/133081/most-efficient-way-in-sql-server-to-get-date-from-datetime) — Andriy M, Jun 09 '12 at 16:49

score 2 · Accepted Answer · answered Jun 09 '12 at 16:43

2

You could group by the cashier and the day:

select     cashier_id
,          dateadd(d, 0, datediff(d, 0, [datetime]))
,          sum(grand_total)
,          sum(num_people_party)
from       invoice_totals
where      [datetime] >= '2012-06-01'
group by
           cashier_id
,          dateadd(d, 0, datediff(d, 0, [datetime]))

In SQL Server 2008 and later, you can replace

dateadd(d, 0, datediff(d, 0, [datetime]))

with

cast([datetime] as date)

answered Jun 09 '12 at 16:43

Andomar

232,371
49
380
404

This seems to work great, a couple of follow-ups: -How can I change the name of the columns on the report, it current shows as CASHIER_ID, column 1, column 2, column 3? -Is there a way to omit the time from showing, it currently shows the dates and then a time of 12:00:00 AM for each record? -How Could I add 1 more column that divides the grand total by the number of people served for each record? – tmparisi Jun 09 '12 at 17:20
1, Add `as Alias` at the end, like `sum(grand_total) as total`. 2. Replace `dateadd(d, 0, datediff(d, 0, [datetime]))` with `convert(varchar(10),[datetime],111)` 3. `sum(grand_total) / sum(num_people_party) as CashPerCustomer` – Andomar Jun 09 '12 at 17:24
2 of 3 of those work perfectly! However, the division line seems to throw an error. I updated my original question to show the error message that is thrown. I really appreciate your help with this! – tmparisi Jun 09 '12 at 20:57
There was a divide by zero error, I added a case statement to take care of it. Thanks again for your help, I appreciate you taking the time! – tmparisi Jun 09 '12 at 21:35

score 0 · Answer 2 · answered Jun 09 '12 at 16:46

You can group by the date parts:

select cashier_id,SUM(grand_total), SUM(num_people_party), DATEPART(dd,[datetime]) AS Day, DATEPART(mm,[datetime]) AS Month, DATEPART(yyyy,[datetime]) AS Year
from invoice_totals
where datetime >= '2012-06-01'
group by cashier_id, DATEPART(dd,[datetime]), DATEPART(mm,[datetime]), DATEPART(yyyy,[datetime])

Or more simply

select cashier_id, SUM(grand_total), SUM(num_people_party), DAY([datetime]) AS Day, MONTH([datetime]) AS Month, YEAR([datetime]) AS Year
from invoice_totals
where datetime >= '2012-06-01'
group by cashier_id, DAY([datetime]), MONTH([datetime]), YEAR([datetime])

Separate Date from DateTime stamp then display by date totals

2 Answers2