locked
Insert data to another table based on date range RRS feed

  • Question

  • User-582711651 posted

    Hi Friends,

    This is my SQL table (sample),

    Create Table #HolidayMaster_Tbl (
        HolidayTbl_Row_IDT int,
        Holiday_Date Date,);
    
    
    Insert Into #HolidayMaster_Tbl ([HolidayTbl_Row_IDT], [Holiday_Date]) VALUES (1, '2018/01/26' )
    Insert Into #HolidayMaster_Tbl ([HolidayTbl_Row_IDT], [Holiday_Date]) VALUES (2, '2018/01/30' )
    Insert Into #HolidayMaster_Tbl ([HolidayTbl_Row_IDT], [Holiday_Date]) VALUES (3, '2018/02/20' )
    Insert Into #HolidayMaster_Tbl ([HolidayTbl_Row_IDT], [Holiday_Date]) VALUES (4, '2018/03/02' )
    Insert Into #HolidayMaster_Tbl ([HolidayTbl_Row_IDT], [Holiday_Date]) VALUES (5, '2018/05/01' )
    Insert Into #HolidayMaster_Tbl ([HolidayTbl_Row_IDT], [Holiday_Date]) VALUES (6, '2018/07/22' )
    Insert Into #HolidayMaster_Tbl ([HolidayTbl_Row_IDT], [Holiday_Date]) VALUES (7, '2018/08/15' )
    Insert Into #HolidayMaster_Tbl ([HolidayTbl_Row_IDT], [Holiday_Date]) VALUES (8, '2018/09/13' )
    Insert Into #HolidayMaster_Tbl ([HolidayTbl_Row_IDT], [Holiday_Date]) VALUES (9, '2018/10/02' )
    Insert Into #HolidayMaster_Tbl ([HolidayTbl_Row_IDT], [Holiday_Date]) VALUES (10, '2018/10/18' )
    Insert Into #HolidayMaster_Tbl ([HolidayTbl_Row_IDT], [Holiday_Date]) VALUES (11, '2018/11/22' )
    Insert Into #HolidayMaster_Tbl ([HolidayTbl_Row_IDT], [Holiday_Date]) VALUES (12, '2018/12/25' )
    Insert Into #HolidayMaster_Tbl ([HolidayTbl_Row_IDT], [Holiday_Date]) VALUES (13, '2018/12/26' )
    GO

    when the student joins 01.01.2018 then, that student can avail all 13 days leave, when the student joins 01.07.2018, then that student can avail 8 days only, suppose student join 01.12.2018, that student can avail leave for 2 days only. Based on this condition I want to insert the particular rows to another table, 

    I was given an example below, Student joined on 05.12.2018, student availed two days leave, see this example

    Create Table #Stud_Leave_Avail_Data_Txn_Tbl (
        HolidayID int,
        Holiday_Avbl_Date date);
    
    Insert Into #Stud_Leave_Avail_Data_Txn_Tbl ([HolidayID] , [Holiday_Avbl_Date]) VALUES (12, '2018/12/25' )
    Insert Into #Stud_Leave_Avail_Data_Txn_Tbl ([HolidayID] , [Holiday_Avbl_Date]) VALUES (13, '2018/12/30' )
    

    Thanks in advance. 

    Monday, July 16, 2018 10:00 AM

Answers

  • User753101303 posted

    Hi,

    It seems you want just the rows that are past the joining date is something such as:

    Insert Into #Stud_Leave_Avail_Data_Txn_Tbl ([HolidayID],Holiday_Avbl_Date) SELECT [HolidayTbl_Row_IDT],Holiday_Date FROM #HolidayMaster_Tbl WHERE Holiday_Date>@JoiningDate

    @JoiningDate could be also taken directly from a table holding the joining date for the student (and a parameter would be the student id).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 16, 2018 12:45 PM

All replies

  • User753101303 posted

    Hi,

    It seems you want just the rows that are past the joining date is something such as:

    Insert Into #Stud_Leave_Avail_Data_Txn_Tbl ([HolidayID],Holiday_Avbl_Date) SELECT [HolidayTbl_Row_IDT],Holiday_Date FROM #HolidayMaster_Tbl WHERE Holiday_Date>@JoiningDate

    @JoiningDate could be also taken directly from a table holding the joining date for the student (and a parameter would be the student id).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 16, 2018 12:45 PM
  • User-582711651 posted

    Hi <g class="gr_ gr_5 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="5" data-gr-id="5">PatriceSc</g>, 

    Yes, thanks.

    Tuesday, July 17, 2018 1:15 AM