General discussion

Locked

summarize by date in a sql statement

By sparkee16 ·
I am trying to write a report in Excel, I am using 3 tables from a sql server database, one table has tech labor by invoice which I want to sum by date, the 2nd table is Tech name and id #,
the 3rd table is time clock entry by date. I have been having a hard time trying to get the 1st table summarized by date so I can join the 3 tables together. Here is an example of part of the query for the first table and the 2nd table.
SELECT DISTINCT Min(INSTHIST.DATESTAMP) AS 'DATE', INSTHIST.COMPANY AS 'STORE', INSTHIST.LABOR_TIME AS 'LABOR', MECHANIC.MECHANIC_NAME AS 'NAME'
FROM VastOffice.dbo.INSTHIST INSTHIST, VastOffice.dbo.MECHANIC MECHANIC
WHERE MECHANIC.MECHANIC_NUMBER = INSTHIST.MECHANIC_NUMBER
GROUP BY INSTHIST.COMPANY, INSTHIST.LABOR_TIME, MECHANIC.MECHANIC_NAME, INSTHIST.DATESTAMP, INSTHIST.INSTALLER
HAVING (INSTHIST.DATESTAMP>={ts '2005-11-26 00:00:00'}) AND (INSTHIST.LABOR_TIME<>0.0) AND (INSTHIST.INSTALLER=1)
ORDER BY Min(INSTHIST.DATESTAMP) DESC

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by Tony Hopkinson In reply to summarize by date in a sq ...

If you are usi9ng datetime types and you want the date yoou've got to drop the hours etc.
INSTHIST.DATESTAMP>={ts '2005-11-26 00:00:00'})
is going to drop everything that did not happen at midnight that day. Whatever dbms you are using there will be a function to get the date of a datetime, you need to select on that

Collapse -

by KeyJammer In reply to summarize by date in a sq ...

Try this using a date conversion function to strip hours and then you can use aggregrate functions on the rest without needing HAVING and MIN criteria.
SELECT CONVERT(CHAR(10),INSTHIST.DATESTAMP,121) AS 'DATE'
, INSTHIST.COMPANY AS 'STORE'
, SUM(INSTHIST.LABOR_TIME) AS 'LABOR'
, MECHANIC.MECHANIC_NAME AS 'NAME'
FROM VastOffice.dbo.INSTHIST INSTHIST
, VastOffice.dbo.MECHANIC MECHANIC
WHERE MECHANIC.MECHANIC_NUMBER = INSTHIST.MECHANIC_NUMBER
AND CONVERT(CHAR(10),INSTHIST.DATESTAMP,121)>={ts '2005-11-26}
AND INSTHIST.LABOR_TIME<>0.0
AND INSTHIST.INSTALLER=1
GROUP BY
INSTHIST.COMPANY
, MECHANIC.MECHANIC_NAME
, CONVERT(CHAR(10),INSTHIST.DATESTAMP,121)
, INSTHIST.INSTALLER
ORDER BY CONVERT(CHAR(10),INSTHIST.DATESTAMP,121) DESC

Back to Web Development Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums