SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Thursday, November 16, 2006

Totals Help

SQL Apprentice Question
Based on this query..... how can I total just the starred SYS_OP's produced
column Compute by will sum them all.
I want daily totals of just the starred operations but still list them all.

DECLARE @sDate SmallDateTime
DECLARE @sEnd SmallDateTime
DECLARE @sPart Char(15)
SET @sDate = '20061002'
SET @sEnd = '20061005'
set @sPart = '24277'


SET NOCOUNT ON


SELECT CONVERT(CHAR(10),C_DATE,101) as JT_DATE,
JT.DEPT, JT.PART, OP AS JT_OP,
CASE
WHEN LOD.LAST_OP IS NULL THEN ''
ELSE
'***'
END
AS SYS_OP,
SHIFT, C_COUNT AS PRODUCED
FROM Label_Audit..JOB_TICKETS_RAW JT
LEFT JOIN LABEL_AUDIT..V_LAST_OP_DEPT LOD
ON LOD.PART = JT.PART
AND JT.OP = LOD.LAST_OP
WHERE C_DATE BETWEEN @sDate AND @sEnd
AND JT.PART = @sPart
ORDER BY C_DATE, OP, SHIFT


JT_DATE DEPT PART OP SYS_OP SHIFT PRODUCED
---------- ---- --------------- ----- ------ ----- -----------
10/02/2006 8800 24277 020 1 134
10/03/2006 8800 24277 020 1 201
10/03/2006 8800 24277 020 2 405
10/03/2006 8800 24277 020 3 500
10/04/2006 8800 24277 020 1 625
10/04/2006 8800 24277 020 2 620
10/04/2006 8800 24277 020 3 100
10/04/2006 8800 24277 025 2 634
10/04/2006 8800 24277 025 3 950
10/04/2006 8800 24277 030 *** 1 891
10/04/2006 8800 24277 030 *** 2 634
10/04/2006 8800 24277 030 *** 3 950
10/04/2006 8800 24277 030A 1 891
10/05/2006 8800 24277 020 3 401
10/05/2006 8800 24277 025 3 821
10/05/2006 8800 24277 030 *** 3 821


Celko Answers
1) start using ISO-8601 formats for dates, not local dialect

2) start using valid data element names (part?? Unh?! part_name?
part_nbr? part_wgt?) Is c_date "completion_date?" or what? Write code
as if another human being has to maintain it after you win the lottery.


3) Real SQL programmers do not write proprietary, deprecated COMPUTE BY
clause. This is a decades old legacy kludge from the Sybase days when
there were no report writers. Welcome to the year 2006 (actually, this
was bad code in 1996). You ought to be doing this in a proper tool and
not trying to fake a 1950's break and total file system report.


4) Your spec does not tell us what to sum! Production, maybe? And
what do we group by? Here is a skeleton query:


SELECT ..
SUM (CASE WHEN LOD.last_op IS NULL THEN c_count ELSE 0 END) AS
star_stuff
SUM (CASE WHEN LOD.last_op IS NOT NULL THEN c_count ELSE 0 END) AS
other_stuff
FROM ..
WHERE ..
GROUP BY .. ;

No comments: