Finding Consecutive Values For a Record
-
I have a table that shows if a record has a value for each month. A zero is given for that particular month to show there is no value. Below is an example of how the table looks:
I need to be able to count how many consecutive months an ID did not have a value starting with the most recent month. For example, ID 123 will return 7 since all months have a value of zero. ID 456 will return 3 since there are three months with a value of zero before it comes to the value of 20 for 2017_04. Finally ID 789 would return 0 since the most recent month contains a value other than zero. What is the best way to code for something like this?
- RonMexico - Monday, August 28, 2017 8:37 AMI have a table that shows if a record has a value for each month. A zero is given for that particular month to show there is no value. Below is an example of how the table looks:
I need to be able to count how many consecutive months an ID did not have a value starting with the most recent month. For example, ID 123 will return 7 since all months have a value of zero. ID 456 will return 3 since there are three months with a value of zero before it comes to the value of 20 for 2017_04. Finally ID 789 would return 0 since the most recent month contains a value other than zero. What is the best way to code for something like this?
You should know the deal by now. Post DDL and insert statements for the table. There are several solutions to this.
-
The problem is the denormalized table. Create a view that has four columns for the ID, Year, Month, and Value. Then, write a simple query against the view.
-
Here are the statements to create the sample data I showed in the screen shot
create table ValueTracking
(
ID int
,[2017_01] int
,[2017_02] int
,[2017_03] int
,[2017_04] int
,[2017_05] int
,[2017_06] int
,[2017_07] int
)insert into ValueTracking
values (123, 0, 0, 0, 0, 0, 0, 0)
,(456, 0, 3, 0, 20, 0, 0, 0)
,(789, 40, 7, 18, 0, 0, 0, 6) -
Here's the simplest option if the table is already like this:
SELECT ID,
CASE WHEN [2017_07] > 0 THEN 0
WHEN [2017_06] > 0 THEN 1
WHEN [2017_05] > 0 THEN 2
WHEN [2017_04] > 0 THEN 3
WHEN [2017_03] > 0 THEN 4
WHEN [2017_02] > 0 THEN 5
ELSE 6
END
FROM ValueTracking; -
A very fast but hard-coded way would be to use CASE, similar to this pseudo-code. It should work due to short-cutting in the CASE execution:
CASE WHEN [2017_07] <> 0 THEN 0
WHEN [2017_06] <> 0 THEN 1
...
WHEN [2017_01] <> 0 THEN 6
ELSE 7
ENDIf your columns vary you can easily create this construct using dynamic SQL based on current columns.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Thanks for the help, Luis and SQLGuru! That does exactly what I need it to. Now I just need to figure out how to get around the error "Msg 125, Level 15, State 4, Line 67
Case expressions may only be nested to level 10" since the actual problem will look farther back than just the last seven months. - RonMexico - Monday, August 28, 2017 9:53 AM
You might be doing something different. There's no nesting in the case expressions we posted.
-
I'm using a linked server. That appears to be creating the problem.
- RonMexico - Monday, August 28, 2017 11:22 AM
The error specifically states that you've reached the limit on the number of nested CASE statements when you should not have any nesting at all. Using a linked server should have no effect on the number of nested CASE statements.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
I'm running the same exact query and only getting an error message with the linked server so that's why I believe it has something to do with that.
I created a copy of the table on my server and ran the following code which works fine
SELECT ID,
CASE WHEN [2017_07] > 0 THEN 0
WHEN [2017_06] > 0 THEN 1
WHEN [2017_05] > 0 THEN 2
WHEN [2017_04] > 0 THEN 3
WHEN [2017_03] > 0 THEN 4
WHEN [2017_02] > 0 THEN 5
WHEN [2017_01] > 0 THEN 6
WHEN [2016_12] > 0 THEN 7
WHEN [2016_11] > 0 THEN 8
WHEN [2016_10] > 0 THEN 9
WHEN [2016_09] > 0 THEN 10
WHEN [2016_08] > 0 THEN 11
ELSE 12
END
FROM DatabaseName.dbo.TableNameHowever, when I reference the linked server in this code then I get the error I mentioned earlier
SELECT ID,
CASE WHEN [2017_07] > 0 THEN 0
WHEN [2017_06] > 0 THEN 1
WHEN [2017_05] > 0 THEN 2
WHEN [2017_04] > 0 THEN 3
WHEN [2017_03] > 0 THEN 4
WHEN [2017_02] > 0 THEN 5
WHEN [2017_01] > 0 THEN 6
WHEN [2016_12] > 0 THEN 7
WHEN [2016_11] > 0 THEN 8
WHEN [2016_10] > 0 THEN 9
WHEN [2016_09] > 0 THEN 10
WHEN [2016_08] > 0 THEN 11
ELSE 12
END
FROM [remoteserver].DatabaseName.dbo.TableName -
Odd, but not the oddest thing I have come across when linked servers were involved! :w00t:
There are a variety of ways you can make that query run on the remote server instead of passing it over a linked server like that. Simplest is to create a sproc on other end that you call. openrowset/openquery could be options if sproc not available for some reason.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Here's another solution which does not involve CASE statement:
WITH
IDs AS(
SELECT DISTINCT
ID
FROM
ValueTracking),
Src AS(
SELECT
ID,
CAST(REPLACE(Month, '_', '') + '01' AS date) AS Month,
DataValues
FROM
(SELECT
*
FROM
ValueTracking) pvt
UNPIVOT
(DataValues FOR Month IN
([2017_01], [2017_02], [2017_03], [2017_04], [2017_05], [2017_06], [2017_07])
)AS unpvt),
FirstLastMonth AS(
SELECT
MIN(Month) AS FirstMonth,
MAX(Month) AS LastMonth
FROM
src),
LastDataValue AS(
SELECT
s.ID,
MAX(s.Month) AS Month
FROM
src s
WHERE
s.DataValues != 0
GROUP BY
s.ID)
SELECT
i.ID,
DATEDIFF(MONTH, ISNULL(o.Month, m.FirstMonth), m.LastMonth)
FROM
FirstLastMonth m,
LastDataValue o
RIGHT OUTER JOIN IDs i ON i.ID = o.ID; -
Thanks, Igor!
-
On the linked server + CASE thing, see Paul White's answer at https://dba.stackexchange.com/questions/42837/why-do-linked-servers-have-a-limitation-of-10-branches-in-a-case-expression
Cheers!
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply