0

Given a table with date and ramp%. I would like to add the missing dates with previous ramp%

Table and sample data:

+------------+-------+
| date       | ramp% |
+------------+-------+
| 01-01-2016 |  5%   |
| 03-01-2016 |  20%  |
| 07-01-2016 |  50%  |
+------------+-------+

How do I write a query in Apache Spark to return the following?

+------------+-------+
| date       | ramp% |
+------------+-------+
| 01-01-2016 | 5%    |
| 02-01-2016 | 5%    |
| 03-01-2016 | 20%   |
| 04-01-2016 | 20%   |
| 05-01-2016 | 20%   |
| 06-01-2016 | 20%   |
| 07-01-2016 | 50%   |
+------------+-------+
  • 1
    Please add a tag for the specific version of SQL Server. Window functions could help with this but were limited in early versions. – Michael Green May 21 '19 at 11:29
0

Sorry I read Apache Spark very late.

See if the logic help you.

First you should have date calendar table which have daily dates. This is one time activity.You can do so by any logic.

CREATE table tblDailyDate(Dates datetime primary key)

insert into tblDailyDate (Dates)
select top (50000) cast(dateadd(day,-(ROW_NUMBER()over(order by a.number)) ,getdate()) as date) from master..spt_values a
cross join master..spt_values b

The Script,

create table #temp(dates date,ramp int)
insert into #temp VALUES
('2016-01-01', 5 )
,('2016-01-03', 20)
,('2016-01-07', 50);

WITH CTE
AS (
    SELECT min(dates) MinDate
        ,max(Dates) MaxDate
    FROM #temp
        -- where condition 
    )
    ,cte1
AS (
    SELECT td.Dates
    FROM tblDailyDate td
    WHERE EXISTS (
            SELECT 1
            FROM cte t
            WHERE td.Dates >= t.MinDate
                AND td.dates <= t.maxdate
            )
    )
SELECT a.dates
    ,t.ramp
FROM cte1 A
OUTER APPLY (
    SELECT TOP 1 dates
        ,ramp
    FROM #temp t
    WHERE a.dates <= t.dates
    ORDER BY t.dates
    ) t

DROP TABLE #temp

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.