Sort order problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sort order problem

Author

Topic

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-11-12 : 16:22:14

here is sample of a returned set

SOso RTGso RTGwc RTGop RTGhrs TCso TCjo TCwc TCop TChrs
-------- -------- ------------ ------ ------------ -------- ------------ -------- ------ ------------
C2302 C2302 CASSEM 137 0 C2302 01086-0000 CASSEM 137 199.75
C2302 C2302 CBORING 124 700 C2302 01086-0000 CBORING 124 641.75
C2302 C2302 CCARBON 127 0 C2302 01086-0000 CCARBON 127 28.5
C2302 C2302 CCARBON 128 70 C2302 01086-0000 CCARBON 128 27.5
C2302 C2302 CCARBON 129 0 C2302 01086-0000 CCARBON 129 25.75
C2302 C2302 CCMM 132 150 C2302 01086-0000 CCMM 132 6
C2302 C2302 CCNC 121 1500 C2302 01086-0000 CCNC 121 114
C2302 C2302 CCNC 122 0 C2302 01086-0000 CCNC 122 661.75
C2302 C2302 CCNC 123 0 C2302 01086-0000 CCNC 123 286.25
C2302 C2302 CEDM 130 70 C2302 01086-0000 CEDM 130 65.75
C2302 C2302 CENG 101 0 NULL NULL NULL NULL 0
C2302 C2302 CENG 106 0 NULL NULL NULL NULL 0
C2302 C2302 CENG 112 0 NULL NULL NULL NULL 0
C2302 C2302 CENG 113 0 NULL NULL NULL NULL 0
C2302 C2302 CENG 100 300 C2302 01086-0000 CENG 100 163
C2302 C2302 CENG 102 100 C2302 01086-0000 CENG 102 15.25
C2302 C2302 CENG 103 0 C2302 01086-0000 CENG 103 4
C2302 C2302 CENG 104 0 C2302 01086-0000 CENG 104 16.5
C2302 C2302 CENG 105 0 C2302 01086-0000 CENG 105 4.5
C2302 C2302 CENG 107 0 C2302 01086-0000 CENG 107 1.5
C2302 C2302 CENG 108 0 C2302 01086-0000 CENG 108 5
C2302 C2302 CENG 109 0 C2302 01086-0000 CENG 109 2.25
C2302 C2302 CENG 110 0 C2302 01086-0000 CENG 110 3
C2302 C2302 CENG 111 0 C2302 01086-0000 CENG 111 14
C2302 C2302 CFITTIN 135 1500 C2302 01086-0000 CFITTIN 135 1020.25
C2302 C2302 CGUN 126 390 C2302 01086-0000 CGUN 126 441.75
C2302 C2302 CPLAN 133 0 C2302 01086-0000 CPLAN 133 171
C2302 C2302 CPLATE 125 70 C2302 01086-0000 CPLATE 125 203
C2302 C2302 CPOLISH 131 620 C2302 01086-0000 CPOLISH 131 485.75
C2302 C2302 CSAWCUT 120 0 C2302 01086-0000 CSAWCUT 120 3.25
C2302 C2302 CSPOT 136 300 C2302 01086-0000 CSPOT 136 10
C2302 C2302 CTOOLRM 134 0 C2302 01086-0000 CTOOLRM 134 725.5
C2302 C2302 CWELD 138 0 C2302 01086-0000 CWELD 138 15.5

And here is the clause

ORDER BY #SO.SOso, Case WHEN X.TCwc is not NULL THEN X.TCwc ELSE X.RTGwc END, X.TCop, X.RTGop

Basically #SO.SOso would repeat this is a single "Group" in that sense
but the ORDER I need is best displayed with the CENG values.
See how the RTGwc values have Order As does the TCwc values.
That is as it should be but then within each similar ...wc type I need the ...op type ordered as well. Like I mentioned CENG is the best example as the ...ops go out of order.

CENG 101 0 NULL NULL NULL NULL
CENG 106 0 NULL NULL NULL NULL
CENG 112 0 NULL NULL NULL NULL
CENG 113 0 NULL NULL NULL NULL
CENG 100 300 C2302 01086-0000 CENG 100
CENG 102 100 C2302 01086-0000 CENG 102
CENG 103 0 C2302 01086-0000 CENG 103
CENG 104 0 C2302 01086-0000 CENG 104
CENG 105 0 C2302 01086-0000 CENG 105
CENG 107 0 C2302 01086-0000 CENG 107
CENG 108 0 C2302 01086-0000 CENG 108
CENG 109 0 C2302 01086-0000 CENG 109
CENG 110 0 C2302 01086-0000 CENG 110
CENG 111 0 C2302 01086-0000 CENG 111

Again we are looking at the 100's (1xx) values.
My feeling is that the Null values here need an assignment that would partake in the second ordering or be ignored

CREATE PROCEDURE ab_actual_labor_vs_so_routing_v4
(
@leftso char(6) = null,
@rightso char(6) = null,
@custno char(6) = null,
@pm char(6) = null,
@leftdate char(10) = '01/01/1975',
@rightdate char(10) = '01/01/2100'
)
AS
SET NOCOUNT ON

IF @leftso IS NULL SET @leftso = 'C0001'
IF LEFT(@leftso,1) NOT IN ('P','W','C') BEGIN
RAISERROR('custom error jjri - @leftso parameter presented incorrectly... usage is Cxxxx, Pxxx, Wxxxx', 16, 10)
RETURN(-1)
END

IF @rightso IS NULL SET @rightso = 'W9999'
IF LEFT(@leftso,1) NOT IN ('P','W','C') BEGIN
RAISERROR('custom error jjri - @rightso parameter presented incorrectly... usage is Cxxxx, Pxxx, Wxxxx', 16, 20)
RETURN(-1)
END

CREATE TABLE #SO
(
SOso char(8)
)

INSERT INTO #SO
SELECT DISTINCT A.fsono FROM
(
(SELECT DISTINCT fsono FROM m2mdata01..somast) UNION ALL
(SELECT DISTINCT fsono FROM ab_jtd_july31_2002) UNION ALL
(SELECT DISTINCT fsono FROM wa_jtd_july31_2002)
) A

CREATE TABLE #RTG
(
RTGso char(8),
RTGwc char(12),
RTGop smallint,
RTGhrs numeric(15,6)
)

INSERT INTO #RTG

SELECT J.fsono AS RTGso, J.fpro_id AS RTGwc, J.foperno AS RTGop, SUM(J.fuprodtime) AS RTGhrs FROM m2mdata01..sodrtg J
GROUP BY J.fsono, J.fpro_id, J.foperno

CREATE TABLE #TC
(
TCso char(8),
TCjo char(12),
TCwc char(8),
TCop smallint,
TChrs numeric(15,6)
)

INSERT INTO #TC
SELECT I.fsono AS TCso, I.fjobno AS TCjo, I.fpro_id AS TCwc, I.foperno AS TCop, SUM(I.labhours) AS TChrs
FROM
(
SELECT T.fempno, T.fjobno, T.fpro_id, T.foperno, R.fsono,
SUM(ROUND(DATEDIFF(ss, T.fsdatetime, T.fedatetime)/3600.000,4)) AS labhours
FROM m2mdata01..ladetail AS T LEFT OUTER JOIN m2mdata01..jomast R ON T.fjobno = R.fjobno
WHERE T.fstatus= 'P' AND
T.fdate BETWEEN @leftdate AND @rightdate --filter on labor date
GROUP by R.fsono, T.fjobno, T.fempno, T.fpro_id, T.foperno
)
I GROUP BY I.fsono, I.fjobno, I.fpro_id, I.foperno

SELECT TOP 100 PERCENT #SO.SOso, X.RTGso, X.RTGwc, X.RTGop,
CASE
WHEN X.RTGhrs IS NULL THEN 0.00
WHEN X.RTGhrs <= .015 THEN 0.00
WHEN ABS(X.RTGhrs - ROUND(X.RTGhrs,0)) <= 0.015 THEN CAST(ROUND(X.RTGhrs,0) as numeric(10,2))
ELSE CAST(X.RTGhrs as numeric(10,2))
END AS RTGhrs,
X.TCso, X.TCjo, X.TCwc, X.TCop,
CASE
WHEN X.TChrs IS NULL THEN 0.00
WHEN X.TChrs <= .015 THEN 0.00
WHEN ABS(X.TChrs - ROUND(X.TChrs,0)) <= 0.015 THEN CAST(ROUND(X.TChrs,0) as numeric(10,2))
ELSE CAST(X.TChrs as numeric(10,2))
END AS TChrs,
COALESCE(J.ftothrs,0.00) AS OldHours, K.fcompany,
K.fcustno, K.fdistno, K.fordername, CAST(COALESCE(L.fopmemo,'blank') as varchar(254)) AS firstdesc,
CAST(COALESCE(M.fopmemo,'blank') as varchar(254)) AS seconddesc
FROM #SO LEFT OUTER JOIN
(
SELECT #RTG.RTGso, #RTG.RTGwc, #RTG.RTGop, #RTG.RTGhrs, #TC.TCso, #TC.TCjo, #TC.TCwc, #TC.TCop, #TC.TChrs FROM #RTG FULL OUTER JOIN #TC
ON #RTG.RTGso = #TC.TCso AND #RTG.RTGwc=#TC.TCwc AND #RTG.RTGop=#TC.TCop
) X
ON #SO.SOso=X.RTGso OR #SO.SOso = X.TCso
LEFT OUTER JOIN
(
SELECT ab_jtd_july31_2002.fsono, ab_jtd_july31_2002.ftothrs FROM ab_jtd_july31_2002 UNION ALL
SELECT wa_jtd_july31_2002.fsono, wa_jtd_july31_2002.ftothrs FROM wa_jtd_july31_2002 --old hours are always included, even when labor date filter is applied will be zero most cases in the near future
) J
ON #SO.SOso = J.fsono
LEFT OUTER JOIN m2mdata01..somast K
ON #SO.SOso = K.fsono
LEFT OUTER JOIN m2mdata01..inopds L
ON X.RTGop = L.fdescnum
LEFT OUTER JOIN m2mdata01..inopds M
ON X.TCop = M.fdescnum
WHERE SOso BETWEEN @leftso AND @rightso AND --filter on SO
fdistno = COALESCE(@pm,fdistno) AND --filter on Program Manager
fcustno = COALESCE(@custno,fcustno) --filter on Customer

ORDER BY #SO.SOso, Case WHEN X.TCwc is not NULL THEN X.TCwc ELSE X.RTGwc END, X.TCop, X.RTGop

DROP TABLE #TC
DROP TABLE #RTG
DROP TABLE #SO

Voted best SQL forum nickname...."Tutorial-D"

Edited by - Sitka on 11/12/2002 16:28:55

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-11-13 : 09:59:44

Well as always, posting here gives a person a different look at things so the solution as far as I can see it is.

ORDER BY #SO.SOso, Case WHEN X.TCwc is not NULL THEN X.TCwc ELSE X.RTGwc END, Case WHEN X.TCop is not NULL THEN X.TCop ELSE X.RTGop END.

I'm going to look around for the actual pattern this relates to.
It would be "conditional order by" (I may have already asked this question before.)

Ah shoot. I tried to make an example case with DDL but it sucks. The point being Sorting can get to look alot like grouping.

SET NOCOUNT ON

CREATE TABLE #RTG --this would represent result set 1 from a certain area
(
RTGmain CHAR(5),
RTGwc char(12) NULL,
RTGop smallint NULL,
RTGhrs numeric(15,6) NULL
)
INSERT INTO #RTG VALUES ('MAIN1','ENG',100,20.5)
INSERT INTO #RTG VALUES('MAIN1','ENG',103,30.5)
INSERT INTO #RTG VALUES('MAIN2','ENG',101,40.5)
INSERT INTO #RTG VALUES('MAIN1','ENG',102,50.5)
INSERT INTO #RTG VALUES('MAIN1',NULL,NULL,NULL)
INSERT INTO #RTG VALUES('MAIN1','PRO',NULL,100.5)
INSERT INTO #RTG VALUES('MAIN2','PRO',203,200.5)
INSERT INTO #RTG VALUES('MAIN1','PRO',201,300.5)
INSERT INTO #RTG VALUES('MAIN1','PRO',202,400.5)

CREATE TABLE #TC --this would represent result set 2 from a different area
(
TCwc char(8) NULL,
TCop smallint NULL,
TChrs numeric(15,6) NULL
)

INSERT INTO #TC VALUES('ENG',100,2)
INSERT INTO #TC VALUES('ENG',103,2.175)
INSERT INTO #TC VALUES('ENG',106,3)
INSERT INTO #TC VALUES('ENG',102,4)
INSERT INTO #TC VALUES(NULL,NULL,NULL)
INSERT INTO #TC VALUES('ENG',NULL,NULL)
INSERT INTO #TC VALUES ('PRO',NULL,100.5)
INSERT INTO #TC VALUES('PRO',203,200.5)
INSERT INTO #TC VALUES('PRO',201,300.5)
INSERT INTO #TC VALUES('PRO',202,400.5)
INSERT INTO #TC VALUES(NULL,NULL,NULL)

SELECT A.RTGmain,A.RTGwc,A.RTGop,A.RTGhrs,B.TCwc,B.TCop,B.TChrs FROM #RTG A FULL OUTER JOIN #TC B
ON A.RTGwc=B.TCwc AND A.RTGop=B.TCop

-- build ORDER BY's here

DROP TABLE #RTG, #TC
SET NOCOUNT OFF

Voted best SQL forum nickname...."Tutorial-D"

Edited by - Sitka on 11/13/2002 12:00:15

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources