Weird SQL Conditions date , STUCK

Question

I and creating view in SQL Server

Below is my Results , based on this I have to actually achieved the results shown below in the second table

PersonID      MonthYear     DateTimevalue
----------    ----------    --------------
 1             201101        NULL
 1             201102        NULL
 1             201103        2011-03-10 09:35:57.387
 1             201104        2011-04-26 13:25:00.050
 1             201105        NULL
 1             201106        NULL
 1             201107        2011-07-30 16:49:26.050
 1             201108        NULL
 1             201109        2011-09-21 13:33:42.273
 1             201110        2011-10-20 08:55:59.873
 1             201111        NULL
 1             201112        NULL

So , the case when the DateTimeValue is NULL , we need to take the value from previous month

As shown in the below table

PersonID      MonthYear     DateTimevalue
----------    ----------    --------------
 1             201101        NULL
 1             201102        NULL
 1             201103        2011-03-10 09:35:57.387
 1             201104        2011-04-26 13:25:00.050
 1             201105        2011-04-26 13:25:00.050
 1             201106        2011-04-26 13:25:00.050
 1             201107        2011-07-30 16:49:26.050
 1             201108        2011-07-30 16:49:26.050
 1             201109        2011-09-21 13:33:42.273
 1             201110        2011-10-20 08:55:59.873
 1             201111        2011-10-20 08:55:59.873
 1             201112        2011-10-20 08:55:59.873

Any help would be great !!!!

Thanks !!!!

Thanks !!!

If you had a normal `date` field then you can just select the previous month using `CASE`... but that would be *too* easy — Kermit, Jan 24 '13 at 20:07
Case WHEN NULL go to previous month , where is not null. I m trying to figure how that would be possible in SQL — user1141584, Jan 24 '13 at 20:09
I think @njk is suggesting that MonthYear should be a DateTime instead of what appears to be a char/varchar. Then you could join the table back on itself to get the value from the previous month. — cadrell0, Jan 24 '13 at 20:10
@user1141584 Perhaps [this question](http://stackoverflow.com/questions/1345065/sql-query-replace-null-value-in-a-row-with-a-value-from-the-previous-known-value) may help — Kermit, Jan 24 '13 at 20:11

score 2 · Answer 1 · answered Jan 24 '13 at 20:21

Try the following:

SELECT  A.PersonID, 
        A.MonthYear, 
        ISNULL(A.DateTimeValue,B.DateTimeValue) DateTimeValue
FROM YourTable A
OUTER APPLY (   SELECT TOP 1 DateTimeValue 
                FROM YourTable
                WHERE PersonID = A.PersonID 
                AND MonthYear < A.MonthYear 
                AND DateTimeValue IS NOT NULL
                ORDER BY MonthYear DESC) B

score 0 · Answer 2 · edited Jan 24 '13 at 20:12

Does Sql Server allow nested SQL in the Select clause? If it does:

select personId, monthYear, 
       (select T2.DateTimeValue from Tbl T2 
        where T2.monthYear=(select max(T3.monthYear) from Tbl T3
                            where T3.personid=T1.personId and T3.monthYear<=T1.monthYear
                              and T3.DateTimeValue is not null
                           )
       )
from Tbl T1

i.e.

For each month, find the month less than equal to this month, but with a not null DateTime;
Look up the DateTime value to the month found in the step above

Ian Preston · Accepted Answer · 2013-01-24T20:32:12.070

Try this:

select t1.personid
  , t1.monthyear
  , datetimevalue = prev.datetimevalue
from t1
  outer apply
  (
    select top 1 datetimevalue
      , personid
      , monthyear
    from t1 prev
    where prev.monthyear <= t1.monthyear
      and prev.datetimevalue is not null
    order by monthyear desc
) prev
order by t1.personid
  , t1.monthyear

SQLFiddle demo.

Edited to change cross apply to outer apply, @Lamak had it right.

Weird SQL Conditions date , STUCK

3 Answers3