-1

I'm not so great with SQL queries and would like to know if someone would know the correct query to return the information below. I'm having a difficult time trying to figure this out. I'm using Microsoft SQL Server 2012.

EXAMPLE DB DATA

ID   | DATE                | SHIFT#| LINE# | TOTAL | UPTIME (seconds)
1    | 2019-03-13 10:31:29 | 1     | 1     | 345   | 600
2    | 2019-03-13 10:31:39 | 1     | 2     | 254   | 400
3    | 2019-03-13 10:31:49 | 1     | 3     | 198   | 550
4    | 2019-03-13 10:41:29 | 1     | 1     | 300   | 500
5    | 2019-03-13 10:51:39 | 1     | 2     | 154   | 440
6    | 2019-03-13 10:54:49 | 1     | 3     | 298   | 520
7    | 2019-03-13 15:21:19 | 2     | 1     | 100   | 450
8    | 2019-03-13 15:21:29 | 2     | 2     | 330   | 360
9    | 2019-03-13 15:21:39 | 2     | 3     | 400   | 450
10   | 2019-03-13 15:41:19 | 2     | 1     | 200   | 450
11   | 2019-03-13 15:41:29 | 2     | 2     | 300   | 350
12   | 2019-03-13 15:41:39 | 2     | 3     | 400   | 450
13   | 2019-03-14 10:42:09 | 1     | 1     | 345   | 600
14   | 2019-03-14 10:42:19 | 1     | 2     | 254   | 400
15   | 2019-03-14 10:42:29 | 1     | 3     | 198   | 550
16   | 2019-03-14 10:44:09 | 1     | 1     | 340   | 500
17   | 2019-03-14 10:44:19 | 1     | 2     | 204   | 410
18   | 2019-03-14 10:44:29 | 1     | 3     | 178   | 450
19   | 2019-03-14 15:32:29 | 2     | 1     | 200   | 450
20   | 2019-03-14 15:32:39 | 2     | 2     | 300   | 350
21   | 2019-03-14 15:32:49 | 2     | 3     | 400   | 450
22   | 2019-03-14 15:36:29 | 2     | 1     | 210   | 250
23   | 2019-03-14 15:36:39 | 2     | 2     | 320   | 355
24   | 2019-03-14 15:36:49 | 2     | 3     | 450   | 455

I'd like the result of the query to be one line per DATE, SHIFT#, and LINE#. The SUM of the TOTAL field and the SUM of the UPTIME field needs to be included. I'm trying to get it to look like this:

RETURNED RECORD FORMAT

DATE       | SHIFT# | LINE# | SUM TOTAL | SUM UPTIME
2019-03-13 | 1      | 1     | 645       | 1100
2019-03-13 | 1      | 2     | 399       | 840
2019-03-13 | 1      | 3     | 494       | 1070
2019-03-13 | 2      | 1     | 300       | 900
2019-03-13 | 2      | 2     | 630       | 710
2019-03-13 | 2      | 3     | 800       | 900
2019-03-14 | 1      | 1     | 685       | 1100
2019-03-14 | 1      | 2     | 458       | 810
2019-03-14 | 1      | 3     | 376       | 1000
2019-03-14 | 2      | 1     | 410       | 700
2019-03-14 | 2      | 2     | 620       | 705
2019-03-14 | 2      | 3     | 850       | 905
Improve this question
1

3 Answers 3

Reset to default
4

You just need a basic SELECT with aggregation and GROUP BY. You can use the CAST function to truncate the DATETIME to just use the DATE portion:

SELECT CAST([DATE] AS DATE) AS [DATE]
     , [SHIFT]
     , [LINE#]
     , SUM([TOTAL]) AS [SUM TOTAL]
     , SUM([UPTIME (seconds)]) AS [UPTIME]
  FROM MyTable
GROUP BY CAST([DATE] AS DATE), [SHIFT], [LINE#]
ORDER BY CAST([DATE] AS DATE), [SHIFT], [LINE#];

Look at the Examples on the GROUP BY documentation.

Improve this answer
0
2

Here is a solution that appears to give the results you want.

--demo setup
drop table if exists table1
go
CREATE TABLE table1 (
  ID INTEGER,
  DATE VARCHAR(19),
  SHIFT# INTEGER,
  LINE# INTEGER,
  TOTAL INTEGER,
  UPTIME  INTEGER
);

INSERT INTO table1
  (ID, DATE, SHIFT#, LINE#, TOTAL, UPTIME )
VALUES
  ('1', '2019-03-13 10:31:29', '1', '1', '345', '600'),
  ('2', '2019-03-13 10:31:39', '1', '2', '254', '400'),
  ('3', '2019-03-13 10:31:49', '1', '3', '198', '550'),
  ('4', '2019-03-13 10:41:29', '1', '1', '300', '500'),
  ('5', '2019-03-13 10:51:39', '1', '2', '154', '440'),
  ('6', '2019-03-13 10:54:49', '1', '3', '298', '520'),
  ('7', '2019-03-13 15:21:19', '2', '1', '100', '450'),
  ('8', '2019-03-13 15:21:29', '2', '2', '330', '360'),
  ('9', '2019-03-13 15:21:39', '2', '3', '400', '450'),
  ('10', '2019-03-13 15:41:19', '2', '1', '200', '450'),
  ('11', '2019-03-13 15:41:29', '2', '2', '300', '350'),
  ('12', '2019-03-13 15:41:39', '2', '3', '400', '450'),
  ('13', '2019-03-14 10:42:09', '1', '1', '345', '600'),
  ('14', '2019-03-14 10:42:19', '1', '2', '254', '400'),
  ('15', '2019-03-14 10:42:29', '1', '3', '198', '550'),
  ('16', '2019-03-14 10:44:09', '1', '1', '340', '500'),
  ('17', '2019-03-14 10:44:19', '1', '2', '204', '410'),
  ('18', '2019-03-14 10:44:29', '1', '3', '178', '450'),
  ('19', '2019-03-14 15:32:29', '2', '1', '200', '450'),
  ('20', '2019-03-14 15:32:39', '2', '2', '300', '350'),
  ('21', '2019-03-14 15:32:49', '2', '3', '400', '450'),
  ('22', '2019-03-14 15:36:29', '2', '1', '210', '250'),
  ('23', '2019-03-14 15:36:39', '2', '2', '320', '355'),
  ('24', '2019-03-14 15:36:49', '2', '3', '450', '455');

--the solution
;WITH _cte
AS (
    SELECT convert(DATE, [date]) AS [date]
        ,[shift#]
        ,[line#]
        ,total
        ,uptime
    FROM table1
    )
SELECT [date]
    ,[shift#]
    ,[line#]
    ,sum(total) as [SUM TOTAL]
    ,sum(uptime) as [SUM UPTIME]
FROM _cte
GROUP BY [DATE]
    ,[SHIFT#]
    ,[line#]

| date       | shift# | line# | SUM TOTAL        | SUM UPTIME       |
|------------|--------|-------|------------------|------------------|
| 2019-03-13 | 1      | 1     | 645              | 1100             |
| 2019-03-13 | 1      | 2     | 408              | 840              |
| 2019-03-13 | 1      | 3     | 496              | 1070             |
| 2019-03-13 | 2      | 1     | 300              | 900              |
| 2019-03-13 | 2      | 2     | 630              | 710              |
| 2019-03-13 | 2      | 3     | 800              | 900              |
| 2019-03-14 | 1      | 1     | 685              | 1100             |
| 2019-03-14 | 1      | 2     | 458              | 810              |
| 2019-03-14 | 1      | 3     | 376              | 1000             |
| 2019-03-14 | 2      | 1     | 410              | 700              |
| 2019-03-14 | 2      | 2     | 620              | 705              |
| 2019-03-14 | 2      | 3     | 850              | 905              |
Improve this answer
0
-2 
SELECT CAST([DATE] AS DATE) AS [DATE], SHIFT, LINE, SUM([TOTAL]) AS [SUM TOTAL], SUM([UPTIME]) AS [SUM UPTIME] FROM MyTable
GROUP BY CAST([DATE] AS DATE), [SHIFT], [LINE]
ORDER BY CAST([DATE] AS DATE), [SHIFT], [LINE]
Improve this answer
1
  • 6
    Could you perhaps add some more information? Code-only answers aren't really seen as acceptable here. Also welcome to DBA.SE.
    – George.Palacios
    Commented Apr 5, 2019 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.