0

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 :)

Jayank
  • 81
  • 1
  • 11
  • Can you provide sample input data and appropriate expected output? – Kannan Kandasamy May 25 '17 at 19:07
  • Prv_Data is input data and Cur_Data is expected output – Jayank May 25 '17 at 19:13
  • 1
    sounds like a bad design... can you elaborate as to why? And i assume / hope these are views... not tables. – S3S May 25 '17 at 21:16
  • tables has million of records, so I illustrate sample/case of it. May I know what are you not able to understand. Table Prv_Data is my existing table which has report ID and it timeline, report_ID 01 has Monday timeline which occur 4times in previous month and Report_ID 02 has Thrusday Timeline which occur 5times in previous month. Now what i want to change the records according to occurrence of Mon and Thru according to cur_month data. – Jayank May 26 '17 at 06:41
  • the only change i see between `Prv_Data` and `Cur_Data ` data is the varying number of `ReportID`s, you should be more clear on the logic why you achieved that desired output. – xGeo May 26 '17 at 13:59
  • 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 – Jayank May 26 '17 at 14:18

1 Answers1

0

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
KumarHarsh
  • 5,046
  • 1
  • 18
  • 22
  • Thanks Harsh for code, it gives desired output. But could you explain the use of "master..spt_values", what exactly it is doing here. I studied "master..spt_values" and came to know that, it is Undocumented system table and which is unsafe to use because Microsoft can it any time. So, we have any alternative for it. Thanks again! – Jayank Jul 05 '17 at 17:05