SQL存储过程插入当前月份的所有日期不在

I have 2 tables

我有2张桌子

• Table Person with a column PersonID (integer)

具有列PersonID(整数)的表人员

• Table CarePlanReport with columns PersonID (integer), Shift (nvarchar(5) - will hold the text values ‘day’ or ‘night’), CarePlanDate (datetime), Details (nvarchar(50))

表CarePlanReport与列PersonID(整数),Shift(nvarchar(5) - 将保存文本值'day'或'night'),CarePlanDate(datetime),Details(nvarchar(50))

I need a stored procedure that will for all dates in the current month, insert 2 records into the CarePlanReport table for each Person.PersonID (a day shift and a night shift) if they don't already exist. This is because each person requires both a day and night shift record for each day in the current month. The problem is a new person could be added during any day in the current month and the missing records will need to be added - so this procedure will need to be run multiple times in any given month. Existing records will remain unchanged because they could already store important details.

我需要一个存储过程,该过程将在当月的所有日期中,为CarePlanReport表中的每个Person.PersonID(白班和夜班)插入2条记录(如果它们尚不存在)。这是因为每个人都需要当月的每一天的白天和夜班记录。问题是在当月的任何一天都可以添加新人,并且需要添加缺失的记录 - 因此这个过程需要在任何给定的月份中多次运行。现有记录将保持不变,因为它们可能已存储重要细节。

EXAMPLE

Person table holds:

人员表持有:

PersonId
--------
   1
   2

CarePlanReport holds for current month

CarePlanReport持有当月

PersonID   Shirt    CarePlanDate
----------------------------------
   1       day      2015/03/01
   2       day      2015/03/01
   1       night    2015/03/01
   2       night    2015/03/01
   .. and so on for each day in the month ....
   1       day      2015/03/31
   2       day      2015/03/31
   1       night    2015/03/31
   2       night    2015/03/31

I used this to return all dates for the current month (not sure if it’s the most efficient method)

我用它来返回当月的所有日期(不确定它是否是最有效的方法)

declare @date datetime
set @date =  cast(YEAR(GETDATE()) as nvarchar(4)) +  RIGHT('00' +     cast(Month(GETDATE()) as nvarchar(2)), 2) + '01';

with d as (
    select @date as Date
    union all
    select dateadd(dd,1,Date)
    from d
    where month(date) = month(@Date)
)
select d.date 
from d 
where month(date) = month(@Date)

1 个解决方案

#1

@tfa

It would be good if you write a trigger and that should get invoked as a new record inserted in the Person Table. Let me know if any need any help for this. You have to have few set of validations in your trigger Use the below procedure which will insert "day" and "night" record for each personid. Just you have to invoke once in a month.

如果你编写一个触发器并且应该作为插入Person Table中的新记录调用它会很好。如果有任何需要,请告诉我。您必须在触发器中设置几组验证使用以下过程,该过程将为每个personid插入“day”和“night”记录。只需要在一个月内调用一次。

create procedure [dbo].[InsertsRecords]
as
begin
DECLARE @DATE DATETIME
declare @NoOfDays int
declare @lv_count int =1
declare @LastDateOfMonth date 
declare @getdates date
declare @lv_personid  int
declare @FirstDateOfMonth date
SET @DATE=convert(date,getdate())
set @LastDateOfMonth =(select DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@date)+1,0)))
set @FirstDateOfMonth=(SELECT convert(date,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)))
declare rcds_cur cursor  for
select personid from dbo.person where personid not in (select personid from dbo.CarePlanReport between 
@FirstDateOfMonth and @LastDateOfMonth )

open rcds_cur
FETCH NEXT FROM rcds_cur into @lv_personid
while @@FETCH_STATUS=0
begin
set @NoOfDays=( Select Day(EOMONTH(@DATE)) AS [Current Month])
set @NoOfDays=@NoOfDays-1
     while (@lv_count<=@NoOfDays)
     begin   
     set @getdates=(select DATEADD(dd,-@lv_count , @LastDateOfMonth))
     insert into dbo.CarePlanReport values (@lv_personid,'day',@getdates,'day shift')
     insert into dbo.CarePlanReport values (@lv_personid,'night',@getdates,'night shift')
     set @lv_count=@lv_count+1

     end
FETCH NEXT FROM rcds_cur into @lv_personid
end
close rcds_cur
deallocate rcds_cur
end