27,582
社区成员




DROP TABLE [#MST_WEEK_TEMP]
CREATE TABLE [#MST_WEEK_TEMP](
[ID] [int] NOT NULL,
[YearWeekCode] [nvarchar](6) NOT NULL,
[YearMonthCode] [nvarchar](6) NOT NULL,
[Week] [int] NOT NULL,
[Month] [int] NOT NULL,
[Quarter] [int] NOT NULL,
[HalfYear] [int] NOT NULL,
[Year] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NOT NULL
)
INSERT INTO [#MST_WEEK_TEMP]
SELECT 823,'201801','201801',1,1,1,2,2018,'2018-01-01','2018-01-07' UNION ALL
SELECT 824,'201802','201801',2,1,1,2,2018,'2018-01-08','2018-01-14' UNION ALL
SELECT 825,'201803','201801',3,1,1,2,2018,'2018-01-15','2018-01-21' UNION ALL
SELECT 826,'201804','201801',4,1,1,2,2018,'2018-01-22','2018-01-28' UNION ALL
SELECT 827,'201805','201802',5,2,1,2,2018,'2018-01-29','2018-02-04' UNION ALL
SELECT 828,'201806','201802',6,2,1,2,2018,'2018-02-05','2018-02-11' UNION ALL
SELECT 829,'201807','201802',7,2,1,2,2018,'2018-02-12','2018-02-18' UNION ALL
SELECT 830,'201808','201802',8,2,1,2,2018,'2018-02-19','2018-02-25' UNION ALL
SELECT 831,'201809','201803',9,3,1,2,2018,'2018-02-26','2018-03-04' UNION ALL
SELECT 832,'201810','201803',10,3,1,2,2018,'2018-03-05','2018-03-11' UNION ALL
SELECT 833,'201811','201803',11,3,1,2,2018,'2018-03-12','2018-03-18' UNION ALL
SELECT 834,'201812','201803',12,3,1,2,2018,'2018-03-19','2018-03-25' UNION ALL
SELECT 835,'201813','201803',13,3,1,2,2018,'2018-03-26','2018-04-01' UNION ALL
SELECT 836,'201814','201804',14,4,2,1,2018,'2018-04-02','2018-04-08' UNION ALL
SELECT 837,'201815','201804',15,4,2,1,2018,'2018-04-09','2018-04-15' UNION ALL
SELECT 838,'201816','201804',16,4,2,1,2018,'2018-04-16','2018-04-22' UNION ALL
SELECT 839,'201817','201804',17,4,2,1,2018,'2018-04-23','2018-04-29' UNION ALL
SELECT 840,'201818','201805',18,5,2,1,2018,'2018-04-30','2018-05-06' UNION ALL
SELECT 841,'201819','201805',19,5,2,1,2018,'2018-05-07','2018-05-13' UNION ALL
SELECT 842,'201820','201805',20,5,2,1,2018,'2018-05-14','2018-05-20' UNION ALL
SELECT 843,'201821','201805',21,5,2,1,2018,'2018-05-21','2018-05-27' UNION ALL
SELECT 844,'201822','201805',22,5,2,1,2018,'2018-05-28','2018-06-03' UNION ALL
SELECT 845,'201823','201806',23,6,2,1,2018,'2018-06-04','2018-06-10' UNION ALL
SELECT 846,'201824','201806',24,6,2,1,2018,'2018-06-11','2018-06-17' UNION ALL
SELECT 847,'201825','201806',25,6,2,1,2018,'2018-06-18','2018-06-24' UNION ALL
SELECT 848,'201826','201806',26,6,2,1,2018,'2018-06-25','2018-07-01' UNION ALL
SELECT 849,'201827','201807',27,7,3,1,2018,'2018-07-02','2018-07-08' UNION ALL
SELECT 850,'201828','201807',28,7,3,1,2018,'2018-07-09','2018-07-15' UNION ALL
SELECT 851,'201829','201807',29,7,3,1,2018,'2018-07-16','2018-07-22' UNION ALL
SELECT 852,'201830','201807',30,7,3,1,2018,'2018-07-23','2018-07-29' UNION ALL
SELECT 853,'201831','201808',31,8,3,1,2018,'2018-07-30','2018-08-05' UNION ALL
SELECT 854,'201832','201808',32,8,3,1,2018,'2018-08-06','2018-08-12' UNION ALL
SELECT 855,'201833','201808',33,8,3,1,2018,'2018-08-13','2018-08-19' UNION ALL
SELECT 856,'201834','201808',34,8,3,1,2018,'2018-08-20','2018-08-26' UNION ALL
SELECT 857,'201835','201808',35,8,3,1,2018,'2018-08-27','2018-09-02' UNION ALL
SELECT 858,'201836','201809',36,9,3,1,2018,'2018-09-03','2018-09-09' UNION ALL
SELECT 859,'201837','201809',37,9,3,1,2018,'2018-09-10','2018-09-16' UNION ALL
SELECT 860,'201838','201809',38,9,3,1,2018,'2018-09-17','2018-09-23' UNION ALL
SELECT 861,'201839','201809',39,9,3,1,2018,'2018-09-24','2018-09-30' UNION ALL
SELECT 862,'201840','201810',40,10,4,2,2018,'2018-10-01','2018-10-07'UNION ALL
SELECT 863,'201841','201810',41,10,4,2,2018,'2018-10-08','2018-10-14'UNION ALL
SELECT 864,'201842','201810',42,10,4,2,2018,'2018-10-15','2018-10-21'UNION ALL
SELECT 865,'201843','201810',43,10,4,2,2018,'2018-10-22','2018-10-28'UNION ALL
SELECT 866,'201844','201811',44,11,4,2,2018,'2018-10-29','2018-11-04'UNION ALL
SELECT 867,'201845','201811',45,11,4,2,2018,'2018-11-05','2018-11-11'UNION ALL
SELECT 868,'201846','201811',46,11,4,2,2018,'2018-11-12','2018-11-18'UNION ALL
SELECT 869,'201847','201811',47,11,4,2,2018,'2018-11-19','2018-11-25'UNION ALL
SELECT 870,'201848','201811',48,11,4,2,2018,'2018-11-26','2018-12-02'UNION ALL
SELECT 871,'201849','201812',49,12,4,2,2018,'2018-12-03','2018-12-09'UNION ALL
SELECT 872,'201850','201812',50,12,4,2,2018,'2018-12-10','2018-12-16'UNION ALL
SELECT 873,'201851','201812',51,12,4,2,2018,'2018-12-17','2018-12-23'UNION ALL
SELECT 874,'201852','201812',52,12,4,2,2018,'2018-12-24','2018-12-30'UNION ALL
SELECT 875,'201901','201901',1,1,1,2,2019,'2018-12-31','2019-01-06' UNION ALL
SELECT 876,'201902','201901',2,1,1,2,2019,'2019-01-07','2019-01-13' UNION ALL
SELECT 877,'201903','201901',3,1,1,2,2019,'2019-01-14','2019-01-20' UNION ALL
SELECT 878,'201904','201901',4,1,1,2,2019,'2019-01-21','2019-01-27' UNION ALL
SELECT 879,'201905','201901',5,1,1,2,2019,'2019-01-28','2019-02-03' UNION ALL
SELECT 880,'201906','201902',6,2,1,2,2019,'2019-02-04','2019-02-10' UNION ALL
SELECT 881,'201907','201902',7,2,1,2,2019,'2019-02-11','2019-02-17' UNION ALL
SELECT 882,'201908','201902',8,2,1,2,2019,'2019-02-18','2019-02-24' UNION ALL
SELECT 883,'201909','201902',9,2,1,2,2019,'2019-02-25','2019-03-03' UNION ALL
SELECT 884,'201910','201903',10,3,1,2,2019,'2019-03-04','2019-03-10' UNION ALL
SELECT 885,'201911','201903',11,3,1,2,2019,'2019-03-11','2019-03-17' UNION ALL
SELECT 886,'201912','201903',12,3,1,2,2019,'2019-03-18','2019-03-24' UNION ALL
SELECT 887,'201913','201903',13,3,1,2,2019,'2019-03-25','2019-03-31' UNION ALL
SELECT 888,'201914','201904',14,4,2,1,2019,'2019-04-01','2019-04-07' UNION ALL
SELECT 889,'201915','201904',15,4,2,1,2019,'2019-04-08','2019-04-14' UNION ALL
SELECT 890,'201916','201904',16,4,2,1,2019,'2019-04-15','2019-04-21' UNION ALL
SELECT 891,'201917','201904',17,4,2,1,2019,'2019-04-22','2019-04-28' UNION ALL
SELECT 892,'201918','201905',18,5,2,1,2019,'2019-04-29','2019-05-05' UNION ALL
SELECT 893,'201919','201905',19,5,2,1,2019,'2019-05-06','2019-05-12' UNION ALL
SELECT 894,'201920','201905',20,5,2,1,2019,'2019-05-13','2019-05-19' UNION ALL
SELECT 895,'201921','201905',21,5,2,1,2019,'2019-05-20','2019-05-26' UNION ALL
SELECT 896,'201922','201905',22,5,2,1,2019,'2019-05-27','2019-06-02' UNION ALL
SELECT 897,'201923','201906',23,6,2,1,2019,'2019-06-03','2019-06-09' UNION ALL
SELECT 898,'201924','201906',24,6,2,1,2019,'2019-06-10','2019-06-16' UNION ALL
SELECT 899,'201925','201906',25,6,2,1,2019,'2019-06-17','2019-06-23' UNION ALL
SELECT 900,'201926','201906',26,6,2,1,2019,'2019-06-24','2019-06-30' UNION ALL
SELECT 901,'201927','201907',27,7,3,1,2019,'2019-07-01','2019-07-07' UNION ALL
SELECT 902,'201928','201907',28,7,3,1,2019,'2019-07-08','2019-07-14' UNION ALL
SELECT 903,'201929','201907',29,7,3,1,2019,'2019-07-15','2019-07-21' UNION ALL
SELECT 904,'201930','201907',30,7,3,1,2019,'2019-07-22','2019-07-28' UNION ALL
SELECT 905,'201931','201908',31,8,3,1,2019,'2019-07-29','2019-08-04' UNION ALL
SELECT 906,'201932','201908',32,8,3,1,2019,'2019-08-05','2019-08-11' UNION ALL
SELECT 907,'201933','201908',33,8,3,1,2019,'2019-08-12','2019-08-18' UNION ALL
SELECT 908,'201934','201908',34,8,3,1,2019,'2019-08-19','2019-08-25' UNION ALL
SELECT 909,'201935','201908',35,8,3,1,2019,'2019-08-26','2019-09-01' UNION ALL
SELECT 910,'201936','201909',36,9,3,1,2019,'2019-09-02','2019-09-08' UNION ALL
SELECT 911,'201937','201909',37,9,3,1,2019,'2019-09-09','2019-09-15' UNION ALL
SELECT 912,'201938','201909',38,9,3,1,2019,'2019-09-16','2019-09-22' UNION ALL
SELECT 913,'201939','201909',39,9,3,1,2019,'2019-09-23','2019-09-29' UNION ALL
SELECT 914,'201940','201910',40,10,4,2,2019,'2019-09-30','2019-10-06'UNION ALL
SELECT 915,'201941','201910',41,10,4,2,2019,'2019-10-07','2019-10-13'UNION ALL
SELECT 916,'201942','201910',42,10,4,2,2019,'2019-10-14','2019-10-20'UNION ALL
SELECT 917,'201943','201910',43,10,4,2,2019,'2019-10-21','2019-10-27'UNION ALL
SELECT 918,'201944','201910',44,10,4,2,2019,'2019-10-28','2019-11-03'UNION ALL
SELECT 919,'201945','201911',45,11,4,2,2019,'2019-11-04','2019-11-10'UNION ALL
SELECT 920,'201946','201911',46,11,4,2,2019,'2019-11-11','2019-11-17'UNION ALL
SELECT 921,'201947','201911',47,11,4,2,2019,'2019-11-18','2019-11-24'UNION ALL
SELECT 922,'201948','201911',48,11,4,2,2019,'2019-11-25','2019-12-01'UNION ALL
SELECT 923,'201949','201912',49,12,4,2,2019,'2019-12-02','2019-12-08'UNION ALL
SELECT 924,'201950','201912',50,12,4,2,2019,'2019-12-09','2019-12-15'UNION ALL
SELECT 925,'201951','201912',51,12,4,2,2019,'2019-12-16','2019-12-22'UNION ALL
SELECT 926,'201952','201912',52,12,4,2,2019,'2019-12-23','2019-12-29'
SELECT * FROM [#MST_WEEK_TEMP]
--以上数据内容 我想根据当前日期 去 [#MST_WEEK_TEMP] 这个表中找出 当前日期前13周的数据
--例如 我想取 2019-5-27 的前13 周 的 数据为
ID YearWeekCode YearMonthCode Week Month Quarter HalfYear Year StartDate EndDate
883 201909 201902 9 2 1 2 2019 2019-02-25 2019-03-03
取 2019-12-31 的前13 周的数据为
ID YearWeekCode YearMonthCode Week Month Quarter HalfYear Year StartDate EndDate
913 201939 201909 39 9 3 1 2019 2019-09-23 2019-09-29
当前日期必须 在 [#MST_WEEK_TEMP] 表中 StartDate 和EndDate 值的范围内 的周 减13