SQLTeam.com | Weblogs | Forums

Need help modifying statement to get desire results

sql2008r2

#1

could someone help me fixing below statement

http://www.sqlfiddle.com/#!6/4f8e8/1

CREATE TABLE [dbo].[Sample](
    [CCMPNY] [varchar](2) NOT NULL,
    [CCUST#] [numeric](7, 0) NOT NULL,
    [RDDYSR] [varchar](7) NOT NULL,
    [CBLNAM] [varchar](30) NOT NULL,
    [CCYCLE] [varchar](2) NOT NULL,
    [RDSIZE] [varchar](2) NOT NULL,
    [RDCHGC] [varchar](2) NOT NULL,
    [RDDAY] [numeric](1, 0) NOT NULL,
    [RDROUT] [varchar](4) NOT NULL,
    [RDQTY] [numeric](5, 0) NOT NULL)

INSERT INTO [Sample]
           ([CCMPNY]
           ,[CCUST#]
           ,[RDDYSR]
           ,[CBLNAM]
           ,[CCYCLE]
           ,[RDSIZE]
           ,[RDCHGC]
           ,[RDDAY]
           ,[RDROUT]
           ,[RDQTY])
VALUES 
(14, 1, 'MTWHFS-', 'COLOSSEO PIZZA', 'C1', 6, 'TR', 1, '002', 1),
(14, 1, 'MTWHFS-', 'COLOSSEO PIZZA', 'C1', '6', 'TR', 2, '002', 1),
(14, 1, 'MTWHFS-', 'COLOSSEO PIZZA', 'C1', '6', 'TR', 3, '004', 1),
(14, 1, 'MTWHFS-', 'COLOSSEO PIZZA', 'C1', '6', 'TR', 4, '001', 1),
(14, 1, 'MTWHFS-', 'COLOSSEO PIZZA', 'C1', '6', 'TR', 5, '002', 1),
(14, 1, 'MTWHFS-', 'COLOSSEO PIZZA', 'C1', '6', 'TR', 6, '006', 1),
(14, 4, 'M------', 'ONE WAY SUPPLY CORP', 'C1', '6', 'TR', 1, '008', 1),
(14, 43, 'M---F--', '7-11 STORE#11226', 'C1', '6', 'TR', 1, '001', 1),
(14, 43, 'M---F--', '7-11 STORE#11226', 'C1', '6', 'TR', 5, '002', 1),
(14, 43, '-T-H---', '7-11 STORE#11226', 'C1', '8', 'CA', 2, '003', 1),
(14, 43, '-T-H---', '7-11 STORE#11226', 'C1', '8', 'CA', 4, '012', 1),
(14, 10723, 'M------', 'BRINKMANN HARDWARE', 'C7', '4', 'TR', 1, '007', 1),
(14, 10723, 'M------', 'BRINKMANN HARDWARE', 'C7', '8', 'TR', 1, '007', 1),
(14, 10723, 'M-T----', 'BRINKMANN HARDWARE', 'C7', '6', 'CB', 1, '007', 1)


SELECT CCUST#
,CBLNAM
,RDCHGC
,RDQTY
,RDSIZE
,LEN(REPLACE(RDDYSR,'-','')) AS FREQ
,COUNT(RDDYSR)*RDQTY AS LIFTS
,RDSIZE * RDQTY * LEN(REPLACE(RDDYSR,'-','')) AS VOLUME
,FIRSTRDDYSR AS RDDYSR,FIRSTRDROUT AS RDROUT
,CASE WHEN (FIRSTRDDYSR LIKE 'M______') THEN RDQTY * RDSIZE END AS MON 
,CASE WHEN (FIRSTRDDYSR LIKE '_T_____') THEN RDQTY * RDSIZE END AS TUE
,CASE WHEN (FIRSTRDDYSR LIKE '__W____') THEN RDQTY * RDSIZE END AS WED
,CASE WHEN (FIRSTRDDYSR LIKE '___H___') THEN RDQTY * RDSIZE END AS THU
,CASE WHEN (FIRSTRDDYSR LIKE '____F__') THEN RDQTY * RDSIZE END AS FRI
,CASE WHEN (FIRSTRDDYSR LIKE '_____S_') THEN RDQTY * RDSIZE END AS SAT
FROM
(
	SELECT S.*, T.RDROUT AS FIRSTRDROUT, T.RDDYSR AS FIRSTRDDYSR FROM
	[SAMPLE] S
	INNER JOIN
	(
		SELECT CCUST#, RDROUT, RDDYSR, ROW_NUMBER() OVER(PARTITION BY CCUST# ORDER BY (SELECT 1)) N
		FROM SAMPLE
	) T ON S.CCUST# = T.CCUST# AND N = 1
) Tbl

GROUP BY CCUST#, CBLNAM, RDCHGC, RDQTY, RDSIZE, RDDYSR, FIRSTRDDYSR, FIRSTRDROUT

as you can see on the example, the last customer has different RDDYSR but is getting on the statement result the same RDDYSR for each instance

Thanks


#2
if you just run the inner query

  

  SELECT S.*, T.RDROUT AS FIRSTRDROUT, T.RDDYSR AS FIRSTRDDYSR FROM
    [SAMPLE] S
    INNER JOIN
    (
        SELECT CCUST#, RDROUT, RDDYSR, ROW_NUMBER() OVER(PARTITION BY CCUST# ORDER BY (SELECT 1)) N
        FROM SAMPLE
    ) T ON S.CCUST# = T.CCUST# AND N = 1

you can see that the Brinkman has the same FIRSTRDDYSR for all rows.


#3

I know the problem is there, but don't know how to fix it. That query was made by another guy I am not good on sql stuff. Could you please help me fixing that query?


#4

OK, so post what the query should return for those last three rows.


#5

If you go to OP link, you will see that for BRINKMANN HARDWARE the last record has M------ under RDDYSR when it should be M-T----

Thanks