September 25, 2010 at 3:48 am
Dear,
i have an table TblROHS_OcsData,
CREATE TABLE [dbo].[TblROHS_OcsData](
[ProdDate] [datetime] NULL,
[Line] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Model] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProdNo] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SSerial] [float] NULL,
[Lot] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SerialNoStart] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SerialNoEnd] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Period] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UploadDate] [datetime] NULL
) ON [PRIMARY]
And in this table I have 2 record with similar data, in field Model, and SSerial
INSERT INTO [TblROHS_OcsData]([ProdDate],[Line],[Model],[ProdNo],[SSerial],[Lot],[SerialNoStart],[SerialNoEnd],[Period],[UploadDate])
VALUES ('2010-09-22','06','KD-R316UHD','027A','11801','1.4K','145X8901','145X0000','201009','2010-09-24')
INSERT INTO [TblROHS_OcsData]([ProdDate],[Line],[Model],[ProdNo],[SSerial],[Lot],[SerialNoStart],[SerialNoEnd],[Period],[UploadDate])
VALUES ('2010-09-22','06','KD-R316UHD','027A','11801','1.4K','145V0001','145V0300','201009','2010-09-24')
I need combine 2 record with compare field [SerialNoStart] and [SerialNoEnd], the rules is:
Substring(SerialNoStart,4,1) =Priority L - M -P -X - V
Substring(SerialNoEnd,4,1) =Priority V - X -P -M - L
And I want data result like:
ModelProdNoSserialLotSerialNoStartSerialNoEnd
KD-R316UHD027A118011.4K145X8901145V0300
Please suggest with your experience which is suitable for me.
Thank You......
September 25, 2010 at 12:13 pm
Check out this query and see what you think
;WITH cte1 AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ProdDate, Line, Model, ProdNo, SSerial, Lot, Period, UploadDate ORDER BY CASE WHEN SUBSTRING(SerialNoStart, 4, 1) = 'V' THEN 'Z' ELSE SUBSTRING(SerialNoStart, 4, 1) END) AS RowStart,
ROW_NUMBER() OVER (PARTITION BY ProdDate, Line, Model, ProdNo, SSerial, Lot, Period, UploadDate ORDER BY CASE WHEN SUBSTRING(SerialNoEnd, 4, 1) = 'V' THEN 'Z' ELSE SUBSTRING(SerialNoEnd, 4, 1) END DESC) AS RowEnd,
* FROM TblROHS_OcsData
)
,
cte2 AS
(
SELECT *
FROM cte1
WHERE RowStart = 1
)
SELECT C.ProdDate, C.Line, C.Model, C.ProdNo, C.Sserial, C.Lot, C.SerialNoStart, Z.SerialNoEnd, C.Period, C.UploadDate
FROM cte2 AS C
CROSS APPLY
(
SELECT SerialNoEnd FROM cte1 WHERE ProdDate = C.ProdDate AND Line = C.Line AND Model = C.Model AND ProdNo = C.Prodno
AND SSerial = C.SSerial AND Lot = C.Lot AND Period = C.Period AND UploadDate = C.UploadDate AND RowEnd = 1
) AS Z
September 28, 2010 at 3:41 am
Hi...
Thank you for your big response, my project is resolved.
🙂
September 28, 2010 at 5:47 am
My dig at this one:
Advantage of my query over steve's is that, steve's is built on the assumption that the Priority list will always remain the same (He has used a logic where the first will be mapped to 'Z' and the rest will follow alphabetical order and ORDER BY of this mapping will do the trick) [Correct me if i am wrong , Steve]
Mine does not, you can set your priority in CTE0 or CTE1 ; if u cant edit the code in future, then u can create tables for Priorities.
Code :
; WITH CTE0 (Priority , Alpha) AS
(
SELECT 1, 'L'
UNION ALL SELECT 2, 'M'
UNION ALL SELECT 3, 'P'
UNION ALL SELECT 4, 'X'
UNION ALL SELECT 5, 'V'
)
, CTE1 (Priority , Alpha) AS
(
SELECT 1, 'V'
UNION ALL SELECT 2, 'X'
UNION ALL SELECT 3, 'P'
UNION ALL SELECT 4, 'M'
UNION ALL SELECT 5, 'L'
)
SELECT
[ProdDate],[Line],[Model],[ProdNo],[SSerial],[Lot],
[SerialNoStart] =
(
SELECT TOP 1 T.[SerialNoStart]
FROM [dbo].[TblROHS_OcsData] T
JOIN CTE0 CT
ON CT.Alpha = SUBSTRING ( T.[SerialNoStart] , 4,1)
WHERE ProdDate = C.ProdDate AND
Line = C.Line AND
Model = C.Model AND
ProdNo = C.Prodno AND
SSerial = C.SSerial AND
Lot = C.Lot AND
Period = C.Period AND
UploadDate = C.UploadDate
ORDER BY
CT.Priority
)
,[SerialNoEnd] =
(
SELECT TOP 1 T.[SerialNoEnd]
FROM [dbo].[TblROHS_OcsData] T
JOIN CTE1 CT
ON CT.Alpha = SUBSTRING ( T.[SerialNoEnd] , 4,1)
WHERE ProdDate = C.ProdDate AND
Line = C.Line AND
Model = C.Model AND
ProdNo = C.Prodno AND
SSerial = C.SSerial AND
Lot = C.Lot AND
Period = C.Period AND
UploadDate = C.UploadDate
ORDER BY
CT.Priority
)
,[Period],[UploadDate]
FROM
[dbo].[TblROHS_OcsData] C
GROUP BY
[ProdDate],[Line],[Model],[ProdNo],[SSerial],[Lot],[Period],[UploadDate]
~Edit : Replaced the code with the correct one ; old one had wrong column names..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply