April 14, 2016 at 1:47 pm
Hi need some help in querying a dataset to pull all non-zero value columns and first zero value column if there are consecutive zero value row for a specific group of data.
Below is the script to create sample data.
Any help will be appreciated.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#ProductDetails') IS NOT NULL
DROP TABLE #ProductDetails
--===== Create the test table with
CREATE TABLE #ProductDetails
( ProductID INT ,
Location Char(1) ,
Value INT ,
WeekendDateKey INT
)
--===== Insert the test data into the test table
INSERT INTO #ProductDetails
(ProductID , Location , Value , WeekendDateKey )
SELECT 1 ,'A', 25 , 7
UNION
SELECT 1 ,'A', 0 , 14
UNION
SELECT 1 ,'A', 0 , 21
UNION
SELECT 1 ,'A', 50 , 28
UNION
SELECT 1 ,'A', 0 , 35
UNION
SELECT 1 ,'A', 0 , 42
UNION
SELECT 1 ,'A', 0 , 49
UNION
SELECT 2 ,'B', 50 , 7
UNION
SELECT 2 ,'B', 0 , 14
UNION
SELECT 2 ,'B', 0 , 21
--===== Data set to query
SELECT
ProductID,
Location ,
WeekendDateKey,
Value
FROM #ProductDetails
ORDER BY
ProductID,
Location ,
WeekendDateKey
--===== Data i want to display will be like this
SELECT
ProductID,
Location ,
WeekendDateKey,
Value
FROM #ProductDetails
WHERE WeekendDateKey in (7,14,28,35)
ORDER BY
ProductID,
Location ,
WeekendDateKey
April 14, 2016 at 1:59 pm
This is one approach (assuming you posted on the correct forum).
WITH CTE AS(
SELECT
ProductID,
Location ,
WeekendDateKey,
Value,
LAG(Value) OVER(PARTITION BY ProductID, Location ORDER BY WeekendDateKey) PrevValue
FROM #ProductDetails
)
SELECT ProductID,
Location ,
WeekendDateKey,
Value
FROM CTE
WHERE PrevValue <> 0
OR Value <> 0
ORDER BY
ProductID,
Location ,
WeekendDateKey;
April 14, 2016 at 2:48 pm
thanks this approach will work perfectly 🙂
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply