none
SQL SERVER How to combine two tables in a certain order

    Question

  • Hi:

    I have a store procedure where I’m trying to combine two tables in a certain order.  I have results from one table:

    EMP_ID HOL_ID HOLIDAY_TITLE            HOLIDAY_DATE          DATE_HOLIDAY_SUBMIT    HOLIDAY_REQUESTED
    NULL     NULL     Labor Day, 2013           2013-09-02                NULL                               NULL
    NULL     NULL    Election Day, 2013          2013-11-05               NULL                               NULL
    NULL     NULL    News Years Day, 2014     2014-01-01               NULL                               NULL
    NULL     NULL    Martin Luther King,          2014-01-20               NULL                               NULL
                           Jr. Birthday, 2014
    NULL    NULL     Presidents Day,  2014     2014-02-17                NULL                               NULL
    NULL    NULL     4th of July, 2014            2014-07-04                NULL                               NULL
    NULL    NULL     Labor Day, 2014           2014-09-01                  NULL                                NULL
    NULL    NULL     Election Day, 2014        2014-11-04                  NULL                                NULL
    NULL    NULL     Christmas,2014            2014-12-25                   NULL                               NULL
    100015 608     Thanksgiving Day, 2013 2013-11-28                2014-08-16                      2014-08-04
    100015 609     Christmas, 2013           2013-12-25                 2014-08-16                       2014-08-05
    100015 614     Memorial Day,  2014     2014-05-26                  2014-08-16                      2014-08-06
    100015 613     Easter, 2014                 2014-04-20                   2014-08-16                    2014-08-07
    100015 600     News Years Day, 2013 2013-01-01                    2014-08-16                      2014-09-22
    100015 601    Martin Luther King,       2013-01-21                     2014-08-16                    2014-09-23 
                      Jr. Birthday, 2013
    100015 604     Memorial Day, 2013     2013-05-27                   2014-08-16                        2014-11-19
    100015 605    4th of July, 2013           2013-07-04                       2014-08-16                  2014-11-20
    100015 618    Thanksgiving Day, 2014 2014-11-27                       2014-08-16                 2014-12-03
    100015 602     Presidents Day, 2013    2013-02-18                      2014-08-16                2014-12-09
    100015 603     Easter, 2013                2013-03-31                       2014-08-16                2014-12-10

    when I run this part of the stored procedure:

    SELECT HO.EMP_ID_NUM,  HO.HOLIDAY_ID,CH.HOLIDAY_TITLE, CH.HOLIDAY_DATE, DATE_HOLIDAY_SUBMIT, HOLIDAY_REQUESTED,
      HOLIDAY_APPROVED, HOL_APPROVAL_DATE, HOLIDAY_CANCEL, HOLIDAY_CANCEL_DATE
      FROM COMPANY_HOLIDAY CH LEFT OUTER JOIN HOLIDAY_OFF HO
      ON HO.HOLIDAY_ID = CH.HOLIDAY_ID
      AND HO.EMP_ID_NUM = 100015
      ORDER BY HO.HOLIDAY_REQUESTED

    I get these results:

    HOLIDAY_ID HOLIDAY_TITLE             HOLIDAY_DATE
    600  News Years Day                       2013-01-01
    601  Martin Luther King, Jr. Birthday 2013-01-21
    602  Presidents Day                       2013-02-18
    603  Easter                                2013-03-31
    604  Memorial Day                      2013-05-27
    605  4th of July                         2013-07-04
    606  Labor Day                          2013-09-02
    607  Election Day                         2013-11-05
    608  Thanksgiving Day                2013-11-28
    609  Christmas                           2013-12-25
    610  News Years Day                  2014-01-01
    611  Martin Luther King, Jr. Birthday 2014-01-20
    612  Presidents Day                      2014-02-17
    613  Easter                                2014-04-20
    614  Memorial Day                           2014-05-26
    615  4th of July                             2014-07-04
    616  Labor Day                        2014-09-01
    617  Election Day                      2014-11-04
    618  Thanksgiving Day                  2014-11-27
    619  Christmas                          2014-12-25

    When I run this part of the stored procedure:

     SELECT HOLIDAY_ID,  HOLIDAY_TITLE, HOLIDAY_DATE
      FROM COMPANY_HOLIDAY
      ORDER BY HOLIDAY_DATE

    The result that I want to get is this:

    HOLIDAY_ID HOLIDAY_TITLE            HOLIDAY_DATE    DATE_HOLIDAY_SUBMIT    HOLIDAY_REQUESTED
    600  News Years Day                       2013-01-01            2014-08-16                     2014-08-04 
    601  Martin Luther King, Jr. Birthday 2013-01-21               2014-08-16                2014-08-05 
    602  Presidents Day                        2013-02-18                 2014-08-16                2014-08-06 
    603  Easter                                   2013-03-31                 2014-08-16                     2014-08-07 
    604  Memorial Day                          2013-05-27               2014-08-16                   2014-09-22 
    605  4th of July                             2013-07-04                 2014-08-16                  2014-09-23 
    606  Labor Day                             2013-09-02                 2014-08-16                    2014-11-19 
    607  Election Day                          2013-11-05                 2014-08-16                  2014-11-20 
    608  Thanksgiving Day                    2013-11-28                 2014-08-16                   2014-12-03 
    609  Christmas                              2013-12-25                 2014-08-16                    2014-12-09 
    610  News Years Day                      2014-01-01                2014-08-16                        2014-12-10 
    611  Martin Luther King, Jr. Birthday 2014-01-20                   NULL                               NULL
    612  Presidents Day                         2014-02-17                  NULL                               NULL
    613  Easter                                    2014-04-20                   NULL                                 NULL
    614  Memorial Day                        2014-05-26                    NULL                                  NULL
    615  4th of July                                2014-07-04                  NULL                                    NULL
    616  Labor Day                                2014-09-01                    NULL                                 NULL
    617  Election Day                            2014-11-04                        NULL                           NULL
    618  Thanksgiving Day                      2014-11-27                        NULL                             NULL
    619  Christmas                                2014-12-25                       NULL                             NULL

    Here is my store procedure that won't the way I want it to work:

    GO

    CREATE PROCEDURE  proc_MoveEmOnUp(@employeeID INTEGER)
    AS
    BEGIN
    DECLARE @holidayChosen TABLE
    (
    employID   INTEGER,
    holidayID   INTEGER,
    holidayTitle   VARCHAR (40),
    holidayDate   DATE,
    holidaySubmitDate  DATE,
    holidayDateRequest  DATE,


    PRIMARY KEY (employID, holidayID)
    );

    DECLARE @employID   INTEGER,
      @holidayID  INTEGER,
      @holidayTitle  VARCHAR(40),
      @holidayDate  DATE,
      @holidaySubmitDate DATE,
      @holidayDateRequest DATE,
      
      
    DECLARE @holidayList TABLE
    (
    holiday_ID   INTEGER,
    holiday_Title   VARCHAR(40),
    holiday_Date   DATE


    PRIMARY KEY (holiday_ID)
    );

    DECLARE
      @holiday_ID INTEGER,
      @holiday_Title VARCHAR(40),
      @holiday_Date DATE
      
      
      
    DECLARE @holidays TABLE
    (
    empID   INTEGER,
    holID   INTEGER,
    holTitle  VARCHAR(40),
    holDate   DATE,
    holSubmitDate  DATE,
    holDateRequest  DATE,


    PRIMARY KEY (empID, holID)
    );

    DECLARE @empID   INTEGER,
     @holID   INTEGER,
     @holTitle  VARCHAR(40),
     @holDate  DATE,
     @holSubmitDate  DATE,
     @holDateRequest  DATE,
     
     
     LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
     
      SELECT HO.EMP_ID_NUM,  HO.HOLIDAY_ID,CH.HOLIDAY_TITLE, CH.HOLIDAY_DATE, DATE_HOLIDAY_SUBMIT, HOLIDAY_REQUESTED
      FROM COMPANY_HOLIDAY CH LEFT OUTER JOIN HOLIDAY_OFF HO
      ON HO.HOLIDAY_ID = CH.HOLIDAY_ID
      AND HO.EMP_ID_NUM = 100015
      ORDER BY HO.HOLIDAY_REQUESTED
      
    OPEN c;
     FETCH NEXT FROM c INTO @employID, @holidayID, @holidayTitle, @holidayDate, @holidaySubmitDate,  @holidayDateRequest
     
    WHILE @@FETCH_STATUS = 0
    BEGIN
       
     INSERT @holidayChosen(employID, holidayID, holidayTitle, holidayDate, holidaySubmitDate, holidayDateRequest)
     SELECT @employID, @holidayID, @holidayTitle, @holidayDate, @holidaySubmitDate,  @holidayDateRequest
     FETCH NEXT FROM c INTO @employID, @holidayID, @holidayTitle, @holidayDate, @holidaySubmitDate,  @holidayDateRequest
     
     END;
     
     CLOSE c;
    DEALLOCATE c;

    DECLARE c_Cursor CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
     SELECT HOLIDAY_ID,  HOLIDAY_TITLE, HOLIDAY_DATE
      FROM COMPANY_HOLIDAY
      ORDER BY HOLIDAY_DATE

     
    OPEN c_Cursor

    FETCH NEXT FROM c_Cursor INTO @holiday_ID, @holiday_Title, @holiday_Date;

    WHILE @@FETCH_STATUS = 0
    BEGIN

    INSERT  @holidayList(holiday_ID, holiday_Title, holiday_Date)

     SELECT @holiday_ID, @holiday_Title, @holiday_Date;
     FETCH NEXT FROM c_Cursor INTO @holiday_ID, @holiday_Title, @holiday_Date;
     END;
     
     CLOSE c_Cursor;
    DEALLOCATE c_Cursor

    DECLARE d CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR

    SELECT HC.employID, HL.holiday_ID, HL.holiday_Title, HL.holiday_Date, HC.holidaySubmitDate, HC.holidayDateRequest,
           FROM @holidayList HL, @holidayChosen HC
           WHERE HC.holidayDateRequest IS NOT NULL
     
     
    OPEN d

    FETCH NEXT FROM d INTO @empID, @holID, @holTitle, @holDate, @holSubmitDate, @holDateRequest

    WHILE @@FETCH_STATUS = 0
    BEGIN


    INSERT  @holidays(empID, holID, holTitle,  holDate, holSubmitDate, holDateRequest)

     SELECT @empID, @holID, @holTitle, @holDate, @holSubmitDate, @holDateRequest)
     FETCH NEXT FROM d INTO @empID, @holID, @holTitle, @holDate, @holSubmitDate, @holDateRequest)
     
     END;
     
     CLOSE d;
     
    SELECT empID, holID, holTitle, holDate, holSubmitDate, holDateRequest
    FROM @holidays
    WHERE empID = @employeeID
    END;
    GO

    Here are my tables:


    CREATE TABLE COMPANY_HOLIDAY(
    HOLIDAY_ID  INTEGER NOT NULL,
    HOLIDAY_TITLE VARCHAR(40),
    HOLIDAY_DATE DATETIME2(0)

    PRIMARY KEY(HOLIDAY_ID),
    );

    CREATE TABLE HOLIDAY_OFF(
    EMP_ID_NUM    INTEGER NOT NULL,
    HOLIDAY_ID    INTEGER NOT NULL,
    DATE_HOLIDAY_SUBMIT  DATETIME2(0) DEFAULT GETDATE(),
    HOLIDAY_REQUESTED  DATETIME2(0)


    PRIMARY KEY(EMP_ID_NUM, HOLIDAY_ID),
    FOREIGN KEY(HOLIDAY_ID) REFERENCES  COMPANY_HOLIDAY(HOLIDAY_ID),

    );

    Here's some data:


    -- 2013 Company Holidays
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(600, 'News Years Day', '01-Jan-2013');
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(601, 'Martin Luther King, Jr. Birthday','21-Jan-2013');
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(602, 'Presidents Day','18-Feb-2013');
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(603, 'Easter', '31-Mar-2013');
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(604, 'Memorial Day','27-May-2013');
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(605, '4th of July', '04-Jul-2013');
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(606, 'Labor Day','02-Sep-2013');
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE)VALUES(607, 'Election Day', '05-Nov-2013');
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(608, 'Thanksgiving Day','28-Nov-2013');
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(609, 'Christmas','25-Dec-2013');


    -- 2014
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(610, 'News Years Day','01-Jan-2014');
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(611, 'Martin Luther King, Jr. Birthday', '20-Jan-2014');
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(612, 'Presidents Day','17-Feb-2014');
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(613, 'Easter', '20-Apr-2014');
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(614, 'Memorial Day','26-May-2014');
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(615, '4th of July', '04-Jul-2014');
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(616, 'Labor Day', '01-Sep-2014');
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(617, 'Election Day','04-Nov-2014');
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(618, 'Thanksgiving Day', '27-Nov-2014');
    INSERT INTO COMPANY_HOLIDAY(HOLIDAY_ID, HOLIDAY_TITLE, HOLIDAY_DATE) VALUES(619, 'Christmas','25-Dec-2014');

    INSERT INTO HOLIDAY_OFF(EMP_ID_NUM, HOLIDAY_ID, DATE_HOLIDAY_SUBMIT, HOLIDAY_REQUESTED) VALUES (100015, 608, 'Thanksgiving Day, 2013', '2013-11-28', GETDATE(), '2014-08-04');
    INSERT INTO HOLIDAY_OFF(EMP_ID_NUM, HOLIDAY_ID, DATE_HOLIDAY_SUBMIT, HOLIDAY_REQUESTED) VALUES (100015, 609, 'Christmas, 2013', '2013-12-25', GETDATE(), '2014-08-05');
    INSERT INTO HOLIDAY_OFF(EMP_ID_NUM, HOLIDAY_ID, DATE_HOLIDAY_SUBMIT, HOLIDAY_REQUESTED) VALUES (100015, 614, 'Memorial Day, 2014', '2014-05-26', GETDATE(), '2014-08-06');

    INSERT INTO HOLIDAY_OFF(EMP_ID_NUM, HOLIDAY_ID, DATE_HOLIDAY_SUBMIT, HOLIDAY_REQUESTED) VALUES (100015, 613, 'Easter, 2014', '2014-04-20', GETDATE(), '2014-08-07');
    INSERT INTO HOLIDAY_OFF(EMP_ID_NUM, HOLIDAY_ID, DATE_HOLIDAY_SUBMIT, HOLIDAY_REQUESTED) VALUES (100015, 600, 'News Years Day, 2013', '2013-01-01', GETDATE(), '2014-09-22');
    INSERT INTO HOLIDAY_OFF(EMP_ID_NUM, HOLIDAY_ID, DATE_HOLIDAY_SUBMIT, HOLIDAY_REQUESTED) VALUES (100015, 601, 'Martin Luther King,Jr.Birthday, 2013', '2013-01-21', GETDATE(), '2014-09-23');

    INSERT INTO HOLIDAY_OFF(EMP_ID_NUM, HOLIDAY_ID, DATE_HOLIDAY_SUBMIT, HOLIDAY_REQUESTED) VALUES (100015, 604, 'Memorial Day, 2013', '2013-05-27', GETDATE(), '2014-11-19');
    INSERT INTO HOLIDAY_OFF(EMP_ID_NUM, HOLIDAY_ID, DATE_HOLIDAY_SUBMIT, HOLIDAY_REQUESTED) VALUES (100015, 605, '4th of July, 2013', '2013-07-04', GETDATE(), '2014-11-20');
    INSERT INTO HOLIDAY_OFF(EMP_ID_NUM, HOLIDAY_ID, DATE_HOLIDAY_SUBMIT, HOLIDAY_REQUESTED) VALUES (100015, 618, 'Thanksgiving Day, 2014', '2014-11-27', GETDATE(), '2014-12-03');
    INSERT INTO HOLIDAY_OFF(EMP_ID_NUM, HOLIDAY_ID, DATE_HOLIDAY_SUBMIT, HOLIDAY_REQUESTED) VALUES (100015, 602, 'Presidents Day, 2013', '2013-02-18', GETDATE(), '2014-12-09');
    INSERT INTO HOLIDAY_OFF(EMP_ID_NUM, HOLIDAY_ID, DATE_HOLIDAY_SUBMIT, HOLIDAY_REQUESTED) VALUES (100015, 603, 'Easter, 2013', '2013-03-31', GETDATE(), '2014-12-10');

    Thank you very much for your time and expertise.

    Sincerely,

    Bosco Dog

    Saturday, August 16, 2014 11:50 PM

Answers

All replies

  • Sorry your required output has no relation to your posted data. For example record with Holiday_ID 600 has the 2014-09-22 as Requested Date but in your output you show it as 2014-08-04  etc. Can you explain this discrepancy?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, August 17, 2014 5:35 AM
  • Thank you for responding.

    Yes, that is the output I want to get.  As you notice, in that output, the values in the requested Date are in order by date.  Earliest date first.  That's what I want to get.  At first, if you notice in the original output, I have a person requesting  2014-08-04 to use as their floating holiday for Thanksgiving Day. 

    100015 608     Thanksgiving Day, 2013 2013-11-28                2014-08-16                      2014-08-04

    and

    100015 600     News Years Day, 2013 2013-01-01                    2014-08-16                      2014-09-22

    You can see that for News Years Day, 2013  I have 2014-09-22 as their requested floating holiday. What happens sometimes is that a person may lose their floating holiday if they don't take that particular holiday (New Years Day, 2013 in this case) before a certain day.  So, if I swap the dates by putting the 2014-08-04 for New Year's Day, 2013 the person will not lose their floating holiday. 

    As you can see, originally, the output shows

    100015 601    Martin Luther King,       2013-01-21                     2014-08-16                    2014-09-23 
                      Jr. Birthday, 2013

    It is the second holiday in the year 2013.  I want it to be the next date, which is  2014-08-05. In other words, I want the dates to be in ascending order to align with the holidays of the year.  First holiday, first date.  Second holiday, second date.  Third holiday, third date . . and so forth.  I know it's confusing to look at all that data. Sorry about that. 

    If you have any more question, please ask.  Thank you again for your responding to my question.

    Bosco


    • Edited by bosco dog Sunday, August 17, 2014 2:14 PM
    Sunday, August 17, 2014 2:12 PM
  • Visakh16,

    Thank you but I figured it out.  I just did it in C# and it works.

    Thanks again.

    Bosco

    Thursday, August 21, 2014 3:28 AM
  • Visakh16,

    Thank you but I figured it out.  I just did it in C# and it works.

    Thanks again.

    Bosco

    Hmm..That would be an overhead isntit?

    Did you iterate through recordset for that?

    Why not do it set based way?


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, August 21, 2014 4:40 AM