November 7, 2013 at 9:59 pm
I have four source tables as below:
[vw1], [vw2], [vw3], [vw4]
I need to get an output joining all these source tables such that the value under the columns should hold only that of a latest Last Scan Date (LSD).
For eg., Product 'A' should pick 'M1' Column value as 'A13' instead of 'A11', because source vw3 has the latest LSD.
In other words, I need the list of products with most upto date data. I have attached are the snapshots of output required for a better clarity.
Script for creation and data insertion for the source tables is attached.
Let me know, if i need to add anything.
Thanks in advance
Kiran
November 8, 2013 at 1:56 am
In order to find the latest value for each column, you need to unpivot and concatenate the values in the source tables.
Setup:
DECLARE @vw1 TABLE (
[PRODUCT] [varchar](50) NULL,
[M1] [varchar](50) NULL,
[M2] [varchar](50) NULL,
[M3] [varchar](50) NULL,
[M4] [varchar](50) NULL,
[M5] [varchar](50) NULL,
[M6] [varchar](50) NULL,
[M7] [varchar](50) NULL,
[SK3] [varchar](50) NULL,
[LSD] [datetime] NULL
)
DECLARE @vw2 TABLE (
[PRODUCT] [varchar](50) NULL,
[M1] [varchar](50) NULL,
[M3] [varchar](50) NULL,
[M4] [varchar](50) NULL,
[M5] [varchar](50) NULL,
[M8] [varchar](50) NULL,
[LSD] [datetime] NULL
)
DECLARE @vw3 TABLE (
[PRODUCT] [varchar](50) NULL,
[M1] [varchar](50) NULL,
[M6] [varchar](50) NULL,
[M9] [varchar](50) NULL,
[SK1] [varchar](50) NULL,
[SK2] [varchar](50) NULL,
[LSD] [datetime] NULL
)
DECLARE @vw4 TABLE (
[PRODUCT] [varchar](50) NULL,
[M1] [varchar](50) NULL,
[M2] [varchar](50) NULL,
[M4] [varchar](50) NULL,
[LSD] [datetime] NULL
)
INSERT @vw1 ([PRODUCT], [M1], [M2], [M3], [M4], [M5], [M6], [M7], [SK3], [LSD]) VALUES (N'A', N'A11', N'A12', N'A13', N'A14', N'A15', N'A16', N'A17', N'AK3', CAST(0x0000A11A00000000 AS DateTime))
INSERT @vw1 ([PRODUCT], [M1], [M2], [M3], [M4], [M5], [M6], [M7], [SK3], [LSD]) VALUES (N'B', N'B11', N'B12', N'B13', N'B14', N'B15', N'B16', N'B17', N'BK3', CAST(0x0000A06400000000 AS DateTime))
INSERT @vw1 ([PRODUCT], [M1], [M2], [M3], [M4], [M5], [M6], [M7], [SK3], [LSD]) VALUES (N'C', NULL, N'C12', N'C13', NULL, NULL, NULL, NULL, NULL, CAST(0x0000A0BF00000000 AS DateTime))
INSERT @vw2 ([PRODUCT], [M1], [M3], [M4], [M5], [M8], [LSD]) VALUES (N'B', N'B12', N'B52', N'B81', N'B32', N'B42', CAST(0x0000A15900000000 AS DateTime))
INSERT @vw2 ([PRODUCT], [M1], [M3], [M4], [M5], [M8], [LSD]) VALUES (N'C', N'C12', N'C52', N'C82', N'C32', N'C42', CAST(0x0000A17600000000 AS DateTime))
INSERT @vw2 ([PRODUCT], [M1], [M3], [M4], [M5], [M8], [LSD]) VALUES (N'D', NULL, NULL, NULL, N'D32', N'D42', CAST(0x0000A19600000000 AS DateTime))
INSERT @vw3 ([PRODUCT], [M1], [M6], [M9], [SK1], [SK2], [LSD]) VALUES (N'A', N'A13', N'A63', N'A93', N'AK1', N'AK2', CAST(0x0000A13900000000 AS DateTime))
INSERT @vw3 ([PRODUCT], [M1], [M6], [M9], [SK1], [SK2], [LSD]) VALUES (N'B', N'B13', N'B63', N'B93', N'BK1', N'BK2', CAST(0x0000A19600000000 AS DateTime))
INSERT @vw3 ([PRODUCT], [M1], [M6], [M9], [SK1], [SK2], [LSD]) VALUES (N'C', N'C13', N'C63', N'C93', N'CK1', N'CK2', CAST(0x0000A15900000000 AS DateTime))
INSERT @vw3 ([PRODUCT], [M1], [M6], [M9], [SK1], [SK2], [LSD]) VALUES (N'D', N'D13', NULL, N'D93', N'DK1', N'DK2', CAST(0x0000A17600000000 AS DateTime))
INSERT @vw4 ([PRODUCT], [M1], [M2], [M4], [LSD]) VALUES (N'B', N'B14', N'B24', N'B44', CAST(0x0000A1D500000000 AS DateTime))
INSERT @vw4 ([PRODUCT], [M1], [M2], [M4], [LSD]) VALUES (N'C', N'C14', N'C24', N'C44', CAST(0x0000A1B500000000 AS DateTime))
Solution:
;WITH unpivotedValues AS (
-- change columns to rows
SELECT product, lsd, col, value
FROM @vw1
UNPIVOT (value FOR col in (M1, M2, M3, M4, M5, M6, M7, SK3)) AS u
-- concatenate values from all source tables
UNION ALL
SELECT product, lsd, col, value
FROM @vw2
UNPIVOT (value FOR col in (M1, M3, M4, M5, M8)) AS u
UNION ALL
SELECT product, lsd, col, value
FROM @vw3
UNPIVOT (value FOR col in (M1, M6, M9, SK1, Sk2)) AS u
UNION ALL
SELECT product, lsd, col, value
FROM @vw4
UNPIVOT (value FOR col in (M1, M2, M4)) AS u
),
-- add a rank column to discover the latest value for each product / column
rankedValues AS (
SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY product, col ORDER BY lsd DESC)
FROM unpivotedValues
)
-- transform rows into columns
SELECT *, LSD = NULL
FROM (
SELECT product, col, value
FROM rankedValues
WHERE RN = 1
) AS src
PIVOT (MIN(value) FOR col IN ([M1],[M2],[M3],[M4],[M5],[M6],[M7],[M8],[M9],[SK1],[SK2],[SK3])) AS p
ORDER BY product;
-- Gianluca Sartori
November 8, 2013 at 2:02 am
Bro,
Thanks a ton for taking time and fixing it.
I have to now convert this to a dynamic query so that it works good irrespective of number of source tables.
Thanks Again 🙂
Kiran
November 8, 2013 at 2:27 am
You're welcome.
Glad I could help.
-- Gianluca Sartori
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply