-3

Photo of Sample Data in blue I have data in this format. I need to find missing for each set of non missing value. Or if I can create a columns that counts groups of non missing elements in SQL server.

MOB ID  Column1      Column2
0   123 Null                Null
1   123 Null                Null
2   123 25              25
3   123 36              25
4   123 Null                Null
5   123 Null                Null
6   123 Null                Null
7   123 15              15
8   123 23              15
9   123 24              15
10  123 41              15
11  123 Null                Null
12  123 Null                Null
13  123 Null                Null
14  123 Null                Null
15  123 Null                Null
16  123 Null                Null
17  123 Null                Null
18  123 77              77
19  123 Null                Null
20  123 Null                Null
21  123 Null                Null
22  123 22              22
23  123 35              22
24  123 38              22

In the example above, the first three columns represent the columns in my table. The fourth column (Column2) is an artificial column I want to generate in a SELECT statement, based on getting the minimum value of Column1 from each "island" of NON-NULL values in Column1.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
StatsR
  • 35
  • 9
  • 2
    Welcome to Stack Overflow. I'm not sure what you're really after here; your sentence doesn't really make sense. For the data you've supplied, what is the expected result set you are after? Also, what have *you* tried to solve this problem yourself? Please [**edit**](https://stackoverflow.com/posts/53886653/edit) your question to show the results you're after and provide the SQL from the attempts you've made. Thanks. – Thom A Dec 21 '18 at 14:47
  • Also, please don't use images of data (you had `text` before, which isn't bad). The **volunteers** trying to help you can't use images of data. When posting data, provide it as either formatted `text` (which it is now), or DDL and DML statements. Thanks. – Thom A Dec 21 '18 at 14:49
  • Which is missing value and which is not missing in any single or all columns. – Suraj Kumar Dec 21 '18 at 14:52
  • Based on your example data/result, I'd say you've got a `gaps and islands problem`. Google that term and you will find examples. Or click on the tag that I've added to your question. – Tab Alleman Dec 21 '18 at 14:57
  • That comment makes no sense I'm afraid, @StatsR. What you have in your example are columns, not variables. If there *are* indeed variables involved we really need to see your SQL. – Thom A Dec 21 '18 at 15:13
  • Unless you can provide some details about the logic here you are going to get many more downvotes and this question will be closed. My guess is this probably pretty easy to solve once we know what the question is. – Sean Lange Dec 21 '18 at 15:25
  • I have some data with 3 columns: MOB, ID and Column1. This is just an example. I need to create Column2 that will look like in this example. It will take minimum in the each non missing groups. For example: now we have in Column1 - Null Null 25 36 Null - so code need to find min between 25 and 36 - that will be 25 and override 36 with 25. Then we have 15 23 24 41 so code will find minimum between 15,23,24,41 = 15 and will override 23,24 and 41 with 15. and so on. Thank you @TabAlleman I am looking into it now. it looks like something i can use to solve it, nut i am not there yet. – StatsR Dec 21 '18 at 15:41
  • @Larnu I am not IT for me all of the data columns are variables for the stat model. Sorry for confusing naming. – StatsR Dec 21 '18 at 15:45
  • The problem is we can't see your screen and have no idea what you are trying to do. This question just makes no sense based on the information posted. – Sean Lange Dec 21 '18 at 15:48
  • So, wait, `Column2` isn't part of your actual data, what we have is your expected results, and not your sample data? Based on your vague comment above, though, why are `NULL, NULL, 25, 36` grouped together? How do you determine when one "island" ends and the other starts? Every 4th row? (but then you're group of `15,23, 24,41` couldn't exist, as `15` is ID 7, and thus in the 2nd island, not the 3rd. – Thom A Dec 21 '18 at 15:49
  • @Larnu Correct i need to create Column2. Non missing values before missing. so 25,36 group 1; 15,23,24,41 group 2; 77 group 3; 22,35,38 group 4 – StatsR Dec 21 '18 at 15:50
  • OK, so why haven't you supplied the sample data you have been asked for on several occasions? And your logic still makes no sense. We need concrete details on how to work out where an island starts and stops. – Thom A Dec 21 '18 at 15:52
  • @Larnu Island starts with first non missing value and ends with last non missing values where you dont have any missings. so new island have to be created when missings are present – StatsR Dec 21 '18 at 15:56
  • @Larnu Sample data is first 3 column : MOB, ID and Column1 – StatsR Dec 21 '18 at 15:57
  • @StatsR don't forget about the 2nd comment I made about images. – Thom A Dec 21 '18 at 16:00
  • I attached photo as well. Data with blue background is a sample data that i have. Last Column needs to be added to my data. Not sure what is not clear about it? – StatsR Dec 21 '18 at 16:01
  • @Larnu looks like some people cant see data – StatsR Dec 21 '18 at 16:02
  • No, we can see you're **expected** results just fine. The problem was that you've been asked several times for your sample data as well (which we're still missing). Anyway, I've used my crystal ball on the problem, if that isn't the answer, I don't know what is. – Thom A Dec 21 '18 at 16:03
  • @StatsR back to the subject of naming, you might want to start using the word "NULL" instead of "missing". The data isn't missing. It's there, and it's got a value of NULL. It's another one of those misnomers that can make your question confusing to more tech-savvy readers. – Tab Alleman Dec 21 '18 at 16:06

1 Answers1

0

Using my crystal ball here (It's almost Christmas, so maybe it should be a snow ball), however, perhaps this is what you are after:

WITH VTE AS(
    SELECT *
    FROM (VALUES(0 ,123,NULL),
                (1 ,123,NULL),
                (2 ,123,25  ),
                (3 ,123,36  ),
                (4 ,123,NULL),
                (5 ,123,NULL),
                (6 ,123,NULL),
                (7 ,123,15  ),
                (8 ,123,23  ),
                (9 ,123,24  ),
                (10,123,41  ),
                (11,123,NULL),
                (12,123,NULL),
                (13,123,NULL),
                (14,123,NULL),
                (15,123,NULL),
                (16,123,NULL),
                (17,123,NULL),
                (18,123,77  ),
                (19,123,NULL),
                (20,123,NULL),
                (21,123,NULL),
                (22,123,22  ),
                (23,123,35  ),
                (24,123,38  )) V(MOB,ID,Column1)),
Islands AS(
    SELECT MOB,
           ID,
           Column1,
           CASE WHEN Column1 IS NULL THEN 0 ELSE 1 END AS Gap,
           ROW_NUMBER() OVER (ORDER BY MOB) - 
           ROW_NUMBER() OVER (PARTITION BY CASE WHEN Column1 IS NULL THEN 0 ELSE 1 END ORDER BY MOB) AS Island
    FROM VTE)
SELECT MOB,
       ID,
       Column1,
       MIN(Column1) OVER (PARTITION BY Gap, Island) AS Column2
FROM Islands
ORDER BY MOB;
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • This is the answer. Not sure how what other way should i ask it? I gave you data right away. I said first 3 columns is what i have, all 4 columns what i need to get in the end. – StatsR Dec 21 '18 at 16:06
  • Glad the crystal ball worked, @StatsR. Please do mark the answer as the solution if so, so that future readers know the answer was helpful. Thanks. You also gain reputation from marking an answer as the solution; which will really help you start off as a new user on Stack Overflow; getting your first reputation points are really important to getting access to the functionality many of us take for granted (such as commenting). – Thom A Dec 21 '18 at 16:14
  • thank you for your help and showing me proper way to ask question. Term "gap and island" was new to me this why i did not use it when i asked question – StatsR Dec 21 '18 at 16:20