July 3, 2017 at 7:01 am
Hi I need a flag to tell me there is 7 1s in a row per site
This would class as a long run
Anyone get an ideas? i have been trying for some while
Please note that the databases are not compatible with lag/lead
thanks in advance
| Site | MonthYr | LongRun |
| A | Jan-16 | 1 |
| A | Feb-16 | 0 |
| A | Mar-16 | 0 |
| A | Apr-16 | 0 |
| A | May-16 | 0 |
| A | Jun-16 | 0 |
| A | Jul-16 | 1 |
| A | Aug-16 | 1 |
| A | Sep-16 | 1 |
| B | Jan-16 | 1 |
| B | Feb-16 | 0 |
| B | Mar-16 | 1 |
| B | Apr-16 | 1 |
| B | May-16 | 1 |
| B | Jun-16 | 1 |
| B | Jul-16 | 1 |
| B | Aug-16 | 1 |
| B | Sep-16 | 1 |
July 3, 2017 at 7:08 am
Please note that you have posted in the SQL Server 2016 forum, and that your comment about lag/lead is therefore somewhat puzzling. Why did you not post in the forum associated with the SQL Server version your question relates to?
Also, if you could post the sample data in a consumable format, along with your desired results (see the link in my signature), it will mean that people are more likely to help you.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 3, 2017 at 7:09 am
First, we really need to know what database version you are working with, as this is a SQL Server 2016 section of this forum. We also need to know more about your data. Is there always a row for each site for the same set of months? If that's the case, it might be fairly simple, however, the question is, what kind of output are you actually looking for? How many rows of output would you expect from the sample data you provided? Also, the data you provided is not what we call "easily consumable". Ideally, please re-post with the needed CREATE TABLE and INSERT statements for your data, and an indication of the expected output, given that sample data.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 3, 2017 at 7:12 am
Well the version of SSMS is 17 but the databases are old so they aren't compatible with the latest functions, I did post in the 2008 forum but with no luck.
Thanks for advice, will put an excel sheet up
July 3, 2017 at 7:21 am
sgmunson - Monday, July 3, 2017 7:09 AMFirst, we really need to know what database version you are working with, as this is a SQL Server 2016 section of this forum. We also need to know more about your data. Is there always a row for each site for the same set of months? If that's the case, it might be fairly simple, however, the question is, what kind of output are you actually looking for? How many rows of output would you expect from the sample data you provided? Also, the data you provided is not what we call "easily consumable". Ideally, please re-post with the needed CREATE TABLE and INSERT statements for your data, and an indication of the expected output, given that sample data.
Hello thank you its difficult as I have ssms v17 but the databases I am querying are old versions so I have found that some functions like LAG etc wont work
I have added a create/insert script and added it, thank you for the advice hopefully you guys can help
Kind Regards
July 3, 2017 at 7:24 am
here's your data setup with proper data types. your monthYr MUST BE a date datatype in order to get the proper order. you don't want the data to order in text order, but by date. I took a best guess on what your dates meant.
does this dow hat you are asking? if not what's not expected?
;WITH MyCTE([Site],[MonthYr],[LongRun])
AS
(
SELECT 'A',CONVERT(date,'Jan 1, 2016'),CONVERT(int,'1') UNION ALL
SELECT 'A','Feb 1, 2016','0' UNION ALL
SELECT 'A','Mar 1, 2016','0' UNION ALL
SELECT 'A','Apr 1, 2016','0' UNION ALL
SELECT 'A','May 1, 2016','0' UNION ALL
SELECT 'A','Jun 1, 2016','0' UNION ALL
SELECT 'A','Jul 1, 2016','1' UNION ALL
SELECT 'A','Aug 1, 2016','1' UNION ALL
SELECT 'A','Sep 1, 2016','1' UNION ALL
SELECT 'B','Jan 1, 2016','1' UNION ALL
SELECT 'B','Feb 1, 2016','0' UNION ALL
SELECT 'B','Mar 1, 2016','1' UNION ALL
SELECT 'B','Apr 1, 2016','1' UNION ALL
SELECT 'B','May 1, 2016','1' UNION ALL
SELECT 'B','Jun 1, 2016','1' UNION ALL
SELECT 'B','Jul 1, 2016','1' UNION ALL
SELECT 'B','Aug 1, 2016','1' UNION ALL
SELECT 'B','Sep 1, 2016','1'
)
--SELECT * FROM myCTE
,WithRowNum
AS
(
SELECT row_number() over(partition by [Site],CASE WHEN LongRun = 1 THEN 1 ELSE 0 END ORDER BY [MonthYr]) AS RW ,*
FROM MyCTE
)
SELECT * FROM WithRowNum WHERE RW >=7
Lowell
July 3, 2017 at 7:26 am
michelle.mabbs - Monday, July 3, 2017 7:21 AMsgmunson - Monday, July 3, 2017 7:09 AMFirst, we really need to know what database version you are working with, as this is a SQL Server 2016 section of this forum. We also need to know more about your data. Is there always a row for each site for the same set of months? If that's the case, it might be fairly simple, however, the question is, what kind of output are you actually looking for? How many rows of output would you expect from the sample data you provided? Also, the data you provided is not what we call "easily consumable". Ideally, please re-post with the needed CREATE TABLE and INSERT statements for your data, and an indication of the expected output, given that sample data.Hello thank you its difficult as I have ssms v17 but the databases I am querying are old versions so I have found that some functions like LAG etc wont work
I have added a create/insert script and added it, thank you for the advice hopefully you guys can help
Kind Regards
Excel spreadsheets are not what we consider "consumable", and most folks here are going to be apprehensive about opening such a file. As this is a volunteer-based forum, and people are helping you for free, it's incumbent on you to help us by doing enough of the work yourself (meaning taking the time to post actual CREATE TABLE statements as well as INSERT statements for your sample data. If you can't take the time, why should we? Also, I don't even see an attachment on any of your posts as yet.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 3, 2017 at 7:30 am
Hi thank you nearly, I need the result column to calculate when the long run column has a 1 in for 7 or more months
thank you
July 3, 2017 at 7:35 am
sgmunson - Monday, July 3, 2017 7:26 AMmichelle.mabbs - Monday, July 3, 2017 7:21 AMsgmunson - Monday, July 3, 2017 7:09 AMFirst, we really need to know what database version you are working with, as this is a SQL Server 2016 section of this forum. We also need to know more about your data. Is there always a row for each site for the same set of months? If that's the case, it might be fairly simple, however, the question is, what kind of output are you actually looking for? How many rows of output would you expect from the sample data you provided? Also, the data you provided is not what we call "easily consumable". Ideally, please re-post with the needed CREATE TABLE and INSERT statements for your data, and an indication of the expected output, given that sample data.Hello thank you its difficult as I have ssms v17 but the databases I am querying are old versions so I have found that some functions like LAG etc wont work
I have added a create/insert script and added it, thank you for the advice hopefully you guys can help
Kind Regards
Excel spreadsheets are not what we consider "consumable", and most folks here are going to be apprehensive about opening such a file. As this is a volunteer-based forum, and people are helping you for free, it's incumbent on you to help us by doing enough of the work yourself (meaning taking the time to post actual CREATE TABLE statements as well as INSERT statements for your sample data. If you can't take the time, why should we? Also, I don't even see an attachment on any of your posts as yet.
Thank you I have just added it using the AddFile, I cant see it on my original post however, I have added it to this reply
thanks
July 3, 2017 at 7:43 am
Using Lowell's code, I just added logic to return only when the 1's are consecutive.
WITH MyCTE([Site],[MonthYr],[LongRun])
AS
(
SELECT 'A',CONVERT(date,'Jan 1, 2016'),CONVERT(bit,'1') UNION ALL
SELECT 'A','Feb 1, 2016','0' UNION ALL
SELECT 'A','Mar 1, 2016','0' UNION ALL
SELECT 'A','Apr 1, 2016','0' UNION ALL
SELECT 'A','May 1, 2016','0' UNION ALL
SELECT 'A','Jun 1, 2016','0' UNION ALL
SELECT 'A','Jul 1, 2016','1' UNION ALL
SELECT 'A','Aug 1, 2016','1' UNION ALL
SELECT 'A','Sep 1, 2016','1' UNION ALL
SELECT 'B','Jan 1, 2016','1' UNION ALL
SELECT 'B','Feb 1, 2016','0' UNION ALL
SELECT 'B','Mar 1, 2016','1' UNION ALL
SELECT 'B','Apr 1, 2016','1' UNION ALL
SELECT 'B','May 1, 2016','1' UNION ALL
SELECT 'B','Jun 1, 2016','1' UNION ALL
SELECT 'B','Jul 1, 2016','1' UNION ALL
SELECT 'B','Aug 1, 2016','1' UNION ALL
SELECT 'B','Sep 1, 2016','1'
)
--SELECT * FROM myCTE
,ByGroups AS(
SELECT row_number() over(partition by [Site] ORDER BY [MonthYr]) - row_number() over(partition by [Site], LongRun ORDER BY [MonthYr]) AS Grouper,
*
FROM MyCTE
)
,WithRowNum AS(
SELECT ROW_NUMBER() OVER (PARTITION BY Site, Grouper, LongRun ORDER BY [MonthYr]) AS RW, * FROM ByGroups
)
SELECT [Site],[MonthYr],[LongRun]
FROM WithRowNum
WHERE RW >=7
July 3, 2017 at 7:45 am
Add File isn't what you need. Attachment is.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 3, 2017 at 8:03 am
You also posted the same question in a SQL 2008 forum at https://www.sqlservercentral.com/Forums/1884322/Running-total-for-previous-7-rows.
July 3, 2017 at 8:25 am
Closing as this isn't a SQL 2016 question.
The version of SSMS isn't relevant. The SQL Server database version is.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply