# question

## Need help on a SQL Query

I have a table with the data below:

SqNo Month Price

1 201801 5000

2 201804 5250

3 201807 5512.5

4 201810 5788.12

How can I get the data through SQL query as given below (I want to use the same price of previous month for the missing months)

SqNo Month Price

1 201801 5000

2 201802 5000

3 201803 5000

4 201804 5250

5 201805 5250

6 201806 5250

7 201807 5512.5

8 201808 5512.5

9 201809 5512.5

10 201810 5788.12

11 201811 5788.12

12 201812 5788.12

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·

Here's a solution that works, but won't perform well on a large dataset due to the 'triangular join' to work out the previous Price.

There some precalculation of the start and end dates - I've presumed you want the full year for any and all of the dates that are present in your original data. From this I can use a 'tally' table (or cte version in this case) to generate all the missing rows, and then a join back to the original data to get the 'last' price to bring forward.

```declare @YourTable table (SqNo int, [Month] varchar(10), Price decimal(8,2));

insert into @YourTable select 1,201801,5000;
insert into @YourTable select 2,201804,5250;
insert into @YourTable select 3,201807,5512.5;
insert into @YourTable select 4,201810,5788.12;

set dateformat ymd

declare @startmonth datetime
declare @endmonth datetime

select @startmonth = datefromparts(year(cast(min([Month])+'01' as date)),1,1) from @YourTable;
select @endmonth = datefromparts(year(cast(max([Month])+'01' as date)),12,1) from @YourTable;

with cte_tally as
(
select top 10000
ROW_NUMBER() over(order by sc1.column_id) N
from
sys.columns sc1,sys.columns sc2
)
,cte_month_dates as
(
select
+ right('00'+cast(month(dateadd(month,N-1,@startmonth)) as varchar) ,2) as month_date
from cte_tally
)

select
row_number()over (order by cte_month_dates.month_date) as SqNo,
cte_month_dates.month_date as [Month],
isnull(YT.Price, PreviousData.Price) as Price
from @YourTable YT
right join cte_month_dates on YT.Month=cte_month_dates.month_date
cross apply (select top 1 price as Price from @YourTable yt2
where yt2.Month< cte_month_dates.month_date
order by yt2.Month desc) PreviousData
order by cte_month_dates.month_date

```

returns

```SqNo                 Month                            Price
-------------------- -------------------------------- ---------------------------------------
1                    201801                           5000.00
2                    201802                           5000.00
3                    201803                           5000.00
4                    201804                           5250.00
5                    201805                           5250.00
6                    201806                           5250.00
7                    201807                           5512.50
8                    201808                           5512.50
9                    201809                           5512.50
10                   201810                           5788.12
11                   201811                           5788.12
12                   201812                           5788.12

(12 rows affected)

```

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

--sql server 2016 sp2 cu3

declare @table as Table (sqno int, month date, price decimal(10,2))

insert @table

select 1,'20180101',5000

union all

select 2 ,'20180401',5250

union all

select 3,'20180701', 5512.5

union all

select 4, '20181001',5788.12

--SELECT * FROM @table declare @date int

; WITH CTE_DatesTable AS (

SELECT CAST('20180101' as date) AS [date]

UNION ALL

FROM CTE_DatesTable

WHERE DATEADD(mm, 1, [date]) <= '20181231' ), d as (

SELECT [date],t.price as price FROM CTE_DatesTable as c

left join @table as t on c.date = t.month ), s as (

SELECT date,price, case when month(date) between 1 and 3 then 1

when month(date) between 4 and 6 then 2

when month(date) between 7 and 9 then 3

else 4 end as Quarters from d )

select ROW_NUMBER() OVER(ORDER BY date ) AS SqNo,cast(YEAR(date) as varchar(4)) + '-'+ Right('00' + cast(month(date) as varchar(2)),2) as DD,

first_value(price) over(partition by Quarters order by date) as price

FROM s order by 1

OPTION (MAXRECURSION 0)

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.