问题描述
我有一个表名Prv_Data，其中包含报表的前一个月，具有Report_Id和Timeline列.
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 @Mon01 | Weekly @Mon01 | Weekly @Mon01 | Weekly @Mon02 | Weekly @Thru02 | Weekly @Thru02 | Weekly @Thru02 | Weekly @Thru02 | Weekly @Thru
Prv_Data -->Report_ID | Timeline---------------|--------------01 | Weekly @Mon01 | Weekly @Mon01 | Weekly @Mon01 | Weekly @Mon02 | Weekly @Thru02 | Weekly @Thru02 | Weekly @Thru02 | Weekly @Thru02 | Weekly @Thru
我还有一个名为Cur_Month的表名，其中包含当前月份的详细信息.
I have another table name as Cur_Month which contain current month details.
Cur_Month-> Details | Count--------------|--------First Date | 05/01/2017Last Date | 05/31/2017Friday | 4Monday | 5Saturday | 4Sunday | 4Thursday | 4Tuesday | 5Wednesday | 5
Cur_Month-->Details | Count--------------|--------First Date | 05/01/2017Last Date | 05/31/2017Friday | 4Monday | 5Saturday | 4Sunday | 4Thursday | 4Tuesday | 5Wednesday | 5
现在，我想将一个表名称命名为Cur_Data，其中包含报告详细信息，但是根据当前月份的工作日计数，这意味着上个月的星期一计数为4，星期四计数为5，这就是为什么出现Report_ID 1和2的原因因为星期一发生了4次，星期四发生了5次，但是现在在current_Month中，星期一和星期四的发生次数是5和4(当前月信息来自Cur_Month表)，因此我想复制prv_month表数据，但要根据星期一的发生情况和星期四--- Cur_Data
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(所需表)-> Report_ID | Timeline---------------|--------------01 | Weekly @Mon01 | Weekly @Mon01 | Weekly @Mon01 | Weekly @Mon01 | Weekly @Mon02 | Weekly @Thru02 | Weekly @Thru02 | Weekly @Thru02 | Weekly @Thru
Cur_Data (Desired Table)-->Report_ID | Timeline---------------|--------------01 | Weekly @Mon01 | Weekly @Mon01 | Weekly @Mon01 | Weekly @Mon01 | Weekly @Mon02 | Weekly @Thru02 | Weekly @Thru02 | Weekly @Thru02 | Weekly @Thru
提前感谢:)
推荐答案
在我看来，您似乎还不能很好地满足您的要求.
To me also it appear that you are not covering your requirement very well.
我没有关注任何事情.
我唯一了解的是，您希望将某些格式的Cur_Month数据转换为Cur_Data.我不了解背景.
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.
尝试一下，让我知道，
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
这篇关于根据当前月份处理上个月的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！