Manupilating previous month data according to current month

Question

I have one table name Prv_Data which contain previous month of report, having Report_Id and Timeline column.

Prv_Data -->
Report_ID | Timeline ---------------|-------------- 01 | Weekly @Mon 01 | Weekly @Mon 01 | Weekly @Mon 01 | Weekly @Mon 02 | Weekly @Thru 02 | Weekly @Thru 02 | Weekly @Thru 02 | Weekly @Thru 02 | Weekly @Thru

I have another table name as Cur_Month which contain current month details.

Cur_Month-->
Details | Count --------------|-------- First Date | 05/01/2017 Last Date | 05/31/2017 Friday | 4 Monday | 5 Saturday | 4 Sunday | 4 Thursday | 4 Tuesday | 5 Wednesday | 5

Now I want to make a Table name as Cur_Data which contain the report details but according to current month weekday count, means in previous month count of Monday was 4 and count of Thursday was 5, that's why occurrence of Report_ID 1 and 2 as Monday occurred 4 time and Thursday occurred 5 but Now in current_Month we have occurrence of Monday and Thursday are 5 and 4 (current month information comes from Cur_Month Table) and according to this I want to replicate prv_month table data but according to occurrence of Monday and Thursday --- Cur_Data

Cur_Data (Desired Table)-->
Report_ID | Timeline ---------------|-------------- 01 | Weekly @Mon 01 | Weekly @Mon 01 | Weekly @Mon 01 | Weekly @Mon 01 | Weekly @Mon 02 | Weekly @Thru 02 | Weekly @Thru 02 | Weekly @Thru 02 | Weekly @Thru

thanks in advance :)

Answer 1

To me also it appear that you are not covering your requirement very well.

I have not followed a thing.

Only thing I understood is that you want to convert Cur_Month data into Cur_Data bease on certain format.I have not understood the background.

try this and let me know,

declare @Prv_Data table(Report_ID int,Timeline varchar(40))
insert into @Prv_Data VALUES
(01,'Weekly @Mon')
,(01,'Weekly @Mon')
,(01,'Weekly @Mon')
,(01,'Weekly @Mon')
,(02,'Weekly @Thru')
,(02,'Weekly @Thru')
,(02,'Weekly @Thru')
,(02,'Weekly @Thru')
,(02,'Weekly @Thru')


declare @Cur_Month table(Details varchar(40),Count varchar(40))
insert into @Cur_Month VALUES
('First Date','05/01/2017')
,('Last Date','05/31/2017')
,('Friday','4')
,('Monday','5')
,('Saturday','4')
,('Sunday','4')
,('Thursday','4')
,('Tuesday','5')
,('Wednesday','5')
;WITH Cur_Data as
(
select *
,case when Details='Monday' then '1' 
when Details='Thursday' then '2'  END ReportID
,case when Details='Monday' then 'Weekly @Mon' 
when Details='Thursday' then 'Weekly @Thru'  END Timeline
  from @Cur_Month
where Details in('Monday','Thursday')
)

select REPLICATE('0',len(ReportID))+  ReportID ReportID
,Timeline from Cur_Data c
cross apply  (select number 
from master..spt_values where number>0 
and number<=c.[count]  and type='LO')cs