Replace Null value of active date from previous date values

Question

How to write sql query for below Result:

I have below table:

Amount          Date    Code
100         01-11-2014  USD
200         02-11-2014  USD
200         02-11-2014  INR
NULL        03-11-2014  NULL
NULL        04-11-2014  NULL
200         05-11-2014  INR
NULL        06-11-2014  NULL
NULL        07-11-2014  NULL
200         08-11-2014  USD

I need below result:

When the Amount is NULL for a given date, then need to show Amount as Zero for both the codes. Here for the date 03-11-2014, the Amount is NULL, so in the output, there are two rows for that date one for US and one for INR with Amount as Zero.

Amount  Date    Code

100 01-11-2014  USD
200 02-11-2014  USD
200 02-11-2014  INR
0   03-11-2014  USD
0   03-11-2014  INR
0   04-11-2014  USD
0   04-11-2014  INR
200 05-11-2014  INR
0   06-11-2014  INR
0   07-11-2014  INR
200 08-11-2014  USD

score 0 · Answer 1 · answered Nov 10 '14 at 18:44

0

select isnull(Amount,0) as amount,  Date,    Code
from table

answered Nov 10 '14 at 18:44

Dgan

10,077
1
29
51

Please check result data for date 03-11-2014 and also check code – user3744032 Nov 10 '14 at 18:45
@user3744032 what is logic behind this?? – Dgan Nov 10 '14 at 18:46

radar · Answer 2 · 2014-11-10T21:30:39.250

0

Need to use CROSS APPLY to get values for the CODES as Zero where Amount is NULL.

SELECT Amount, DATE, code
FROM Table1 WHERE Amount is not NULL
UNION
SELECT 0, DATE , A.Code
FROM Table1 
CROSS APPLY ( SELECT distinct Code from Table1 where code is NOT NULL) A
WHERE Amount IS NULL 
ORDER By Date

edited Nov 10 '14 at 21:30

answered Nov 10 '14 at 18:44

radar

13,270
2
25
33

Please check result data for date 03-11-2014 – user3744032 Nov 10 '14 at 18:46

score 0 · Answer 3 · answered Nov 10 '14 at 19:26

Here is my approach. Enumerate the rows where code is NULL. When the code is null and the sequential value is 1, then 'USD', when 2, then 'INR'. The following uses amount for the indicator of missing data for both amount and code:

select (case when amount is null then amount else 0 end) as amount,
       date,
       (case when amount is not null then code
             when seqnum = 1 then 'USD'
             when seqnum = 2 then 'INR'
        end) as code
from (select t1.*,
             row_number() over (partition by date, amount order by (select null)) as seqnum
      from table1 t1
     ) t

Giorgos Betsos · Answer 4 · 2014-11-10T20:42:28.713

The following query, that makes use of an outer apply, will give you what you want:

SELECT COALESCE(Amount, 0), t1.Dates, 
       CASE WHEN t1.Code IS NOT NULL THEN t1.Code
            ELSE t2.Code
       END As Code
FROM MyTable AS t1
OUTER APPLY (
SELECT Dates, Code, RANK() OVER (ORDER BY t.Dates DESC) AS DatesRank
FROM MyTable AS t
WHERE t1.Code IS NULL AND t.Code IS NOT NULL AND t1.Dates > t.Dates 
) t2
Where DatesRank IS NULL OR DatesRank = 1

The basic idea behind the above query is to perform an OUTER APPLY only when Code is null. When an OUTER APPLY is performed, then we join the 'problematic' record with all the records that:

'Belong to the same date' (e.g. (200, '2014-11-02', 'USD'), (200, '2014-11-02', 'INR'), ) AND
'Are non problematic' (i.e. have a code that is not null) AND
'Are the first ones that precede the problematic record'.

I hope I did not confuse you! :=)

P.S. The above query will work even if you have more currency cases in your data, e.g. (200, '2014-11-02', 'USD'), (200, '2014-11-02', 'INR'), (200, '2014-11-02', 'EUR').

knkarthick24 · Answer 5 · 2014-11-10T20:51:56.433

I have tried your scenario in SQLFIDDLE and reached certain limit to your desired output:
Please have a look at your input and desired output in above hyperlink.

select ISNULL(c.Amount,d.Amount),c.MyDate,ISNULL(c.Code,d.Code) from 
sample c
left outer join 
(
select COALESCE(a.Amount,0) as Amount,
ISNULL(b.mydate,a.MyDate) as Mydate,
ISNULL(a.Code,'USD') as Code
from 
sample a
left outer join
(select Amount,MyDate,Code
from sample where Code = 'USD') b
on a.MyDate=b.MyDate
where a.Amount IS NULL
union all
select COALESCE(a.Amount,0) as Amount,
ISNULL(b.mydate,a.MyDate) as Mydate,
ISNULL(a.Code,'INR') as Code
from 
sample a
left outer join
(select Amount,MyDate,Code
from sample where Code = 'INR') b
on a.MyDate=b.MyDate
where a.Amount IS NULL) d
on c.Mydate = d.Mydate

Replace Null value of active date from previous date values

5 Answers5