时间戳

首页 > 解决方案 > SQL SERVER中如何根据MonthStart_Date和MonthEnd_date多次复制表记录

sql-server - SQL SERVER中如何根据MonthStart_Date和MonthEnd_date多次复制表记录

问题描述

如何在monthStartDateand的基础上在同一个表中多次复制表记录MonthEndDate，假设我有一个只包含一行和monthStartDate and的表MonthEndDate。月份开始日期是，01-Jan-2018现在我想将这条记录复制到 31 次。MonthendDate31-jan-2018

下面是表结构。

tbl_Employee 

EmployeeID  Name            TeamManagerEmpID    TeamManagerName OpsManagerID    OpsManagerName  Department  FromDate    ToDate
210000      Peter Anderson  110000              Alex Broad      100000          Steve Anderson  BI          01-Jan      31-Jan
210000      Peter Anderson  110001              Smith Jones     100000          Steve Anderson  BI          01-Feb      31-Mar

现在我期待下面的输出。

MetricDate  EmployeeID  Name            TeamManagerEmpID    TeamManagerName OpsManagerID    OpsManagerName  Department
01-Jan      210000      Peter Anderson  110000              Alex Broad      100000          Steve Anderson  BI
02-Jan      210000      Peter Anderson  110000              Alex Broad      100000          Steve Anderson  BI
03-Jan      210000      Peter Anderson  110000              Alex Broad      100000          Steve Anderson  BI
04-Jan      210000      Peter Anderson  110000              Alex Broad      100000          Steve Anderson  BI

请你帮助我好吗。

标签： sql-server

解决方案

首先，您需要为 FromDate 和 ToDate 定义正确的 DATE 数据类型。然后，您应该有一个单独的日期表来生成 FromDate 和 ToDate 之间的日期。我在下面给出了示例代码供您参考。

CREATE TABLE #employee
(
empid int,
empname varchar(30),
empStartDate datetime,
empEndDate datetime);

CREATE TABLE #dimdate
(
datevalue datetime
);

INSERT INTO #employee 
values(1,'venkat','20180101','20180131');

DECLARE @startDate datetime = '20180101'
DECLARE @cutoffDate datetime = '20180131'

INSERT #dimdate(datevalue) 
SELECT d
FROM
(
  SELECT d = DATEADD(DAY, rn - 1, @StartDate)
  FROM 
  (
    SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate)) 
      rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    -- on my system this would support > 5 million days
    ORDER BY s1.[object_id]
  ) AS x
) AS y;

SELECT [datevalue],e.empid, e.empname, e.empStartDate
FROM #dimDate as d
CROSS JOIN #employee as e
WHERE d.datevalue IN ( select d.datevalue FROM #dimdate WHERE d.datevalue >= e.empStartDate and d.datevalue  <= e.empEndDate) ;

推荐阅读