Query to get all non-zero and first zero value rows if there are 2 consecutive Zero value rows

• adhikari707

  SSChasing Mays

  Points: 639

  More actions

  April 14, 2016 at 1:47 pm

  #318115

  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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  April 14, 2016 at 1:59 pm

  #1872169

  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;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• adhikari707

  SSChasing Mays

  Points: 639

  More actions

  April 14, 2016 at 2:48 pm

  #1872180

  thanks this approach will work perfectly 🙂