How to do it?
with data
with format below
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
how can i get below layout
Columns marked in red
FREQ: "Count" of Days in "RDDYSR" field
LIFTS: "Lifts" = "RDQTY" times "Freq"
VOLUME: "Volume" = "RDQTY" times "RDSIZE" times "Freq"
Week Days columns:
MON: If "RDDYSR" includes "M", "RDQTY" times "RDSIZE", else "blank".
TUE: If "RDDYSR" includes "T", "RDQTY" times "RDSIZE", else "blank", etc.
Notes
that each "CCUST" generates only one data line unless
(1) there are multiple "RDCHGC" values (CCUST 43),
(2) there are multiple "RDSIZE" values (CCUST 10723); or,
(3) there are multiple "FREQ" values (no example).
Thank you for your help.
0
source to share
2 answers
This should give you what you are looking for a little simple. I'm not sure what your id is for Sunday, so I commented it out with
select CCUST# CCUST, CBLNAM,RDCHGC, RDQTY, RDSIZE, Len(Replace(RDDYSR, '-', '')) FREQ,
RDQTY * Len(Replace(RDDYSR, '-', '')) LIFTS,
RDQTY * Cast(RDSIZE as int) * Len(Replace(RDDYSR, '-', '')) Volume, RDDYSR,
Case when CHARINDEX('M', RDDYSR) > 0 then RDQTY * Cast(RDSIZE as int) end as Mon,
Case when CHARINDEX('T', RDDYSR) > 0 then RDQTY * Cast(RDSIZE as int) end as Tue,
Case when CHARINDEX('W', RDDYSR) > 0 then RDQTY * Cast(RDSIZE as int) end as Wed,
Case when CHARINDEX('H', RDDYSR) > 0 then RDQTY * Cast(RDSIZE as int) end as Thu,
Case when CHARINDEX('F', RDDYSR) > 0 then RDQTY * Cast(RDSIZE as int) end as Fri,
Case when CHARINDEX('S', RDDYSR) > 0 then RDQTY * Cast(RDSIZE as int) end as Sat
--,Case when CHARINDEX(RDDYSR, 'X') > 0 then RDQTY * Cast(RDSIZE as int) end as Sun --Whatever Sunday identifier is
from Sample
Group by CCUST#, CBLNAM, RDCHGC, RDQTY, RDSIZE, RDDYSR
SQLFiddle: http://www.sqlfiddle.com/#!6/cdb92/1/0
+1
source to share
Try this query,
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
+1
source to share