locked
best possible ways to write the stored procedure to insert the record looping between two date range RRS feed

  • Question

  • I have Three  following table  and I am writing the stored procedure to insert Employee Bank holiday into its attendance Log with its corresponding  hours of that date.  So I have to write the stored procedure by giving two date

    declare @Fromdate  datetime

    declare @ToDate   datetime

    string @EmpID varchar (max)

    Set @Fromdate  = '01-01-2019'

    Set @ToDate = '12-31-2019'

    set @Empid = '1,2,5,6,7'

    I want to insert all bank holidays between that  @fromdate and  @todate  for all employess and its corresponding  working Hrs for each employee in the variable @Empid. 

    Please I am looking for the help . I have given the table structure and record below

    EmpID int EmpName varchar MonHrs decimal (5,2) TueHrs decimal (5,2) WedHrs decimal (5,2) ThuHrs decimal (5,2) BankHoliday DateHoliday datetime Descritption varchar (max) AttendanceLog EmpID int Attdate datetime Hrs decimal (5,2) Descritption varchar (max)

    Insert into Employee

    (id, Name, MonHrs, TueHrs, WedHrs, ThuHrs)

    Values

    (1,'AA',2.3,4.3,5.3,3),

    (2,'BB',3.15,4.3,7.30,2)

    Insert into BankHoliday

    (DateHoliday,Descritption)

    Values

    ('01-01-2019', 'New Year'),

    ('10-01-2019', 'Other Holiday'),

    ('06-05-2019','Holiday'),

    (25-12-2019','Christmas')

    I have to insert the record Each Bank holiday for the given datefrom and dateto and for the

    selected Employee into the table AttendanceLog table

    I have the following record in Employee Master and BankHoliday


    polachan


    • Edited by polachan Friday, April 26, 2019 2:48 PM Improvement
    Friday, April 26, 2019 1:42 PM

All replies

  • Hi,

    It can help if you provide the excel file instead of image which we cannot use.

    Better option is to provide queries to create the tables and insert the sample data

    >> I want to insert all bank holidays between that  @fromdate and  @todate  for all employess and its corresponding  working Hrs for each employee in the variable @Empid. 

    How do you want to insert multiple values from table structure into a single value type NVARCHAR?

    We will need to concatenation all the values from all rows, and for this we need to know which format you want to get the result

    In general, if you are using SQL Server 2017 and above then you can use the function STRING_AGG for the task


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    • Edited by pituachMVP Friday, April 26, 2019 2:42 PM
    Friday, April 26, 2019 2:37 PM
  • If you need more help, please provide:

    1) Queries to CREATE your tables
    2) Queries  to INSERT sample data.
    3) The desired result given the sample, as text or image of excel for example.
    4) Which version of SQL Server you are using (this will help to fit the query to your version).


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, April 26, 2019 2:43 PM
  • Hi

    I hope following script for you need

    create table  employees
    (
    EmpID   int ,
    EmpName varchar(50),
    MonHrs   decimal (5,2),
    TueHrs   decimal (5,2),
    WedHrs   decimal (5,2),
    ThuHrs   decimal (5,2))
     
    
    create table BankHoliday
    (
    DateHoliday   datetime,
    Descritption   varchar (max)
    )
    
    create table
    AttendanceLog
    (
    EmpID    int,
    Attdate datetime,
    Hrs decimal (5,2),
    Descritption   varchar (max))
    
    insert into employees
    values (1,'AA',2.3,4.3,5.3,3),
    (2,'BB',3.15,4.3,7.3,2)
    
    insert into BankHoliday
    values('20190101','New Year'),
    ('20190110','Other Holidays'),
    ('20190506','Holiday'),
    ('20191225','Christmats')
    
    
    insert into AttendanceLog
    select e.EmpID,h.dateholiday,case  left (DATENAME(dw,dateholiday),3) when 'MON' then MonHrs 
    when 'TUE' then Tuehrs 
    when 'WED' then WedHrs
    when 'THU' then THUHRS
    end as HRS,h.Descritption
     from bankholiday  h,Employees e
    
     select * from AttendanceLog
     
     --result
    -- EmpID	Attdate	Hrs	Descritption
    --1	2019-01-01 00:00:00.000	4.30	New Year
    --1	2019-01-10 00:00:00.000	3.00	Other Holidays
    --1	2019-05-06 00:00:00.000	2.30	Holiday
    --1	2019-12-25 00:00:00.000	5.30	Christmats
    --2	2019-01-01 00:00:00.000	4.30	New Year
    --2	2019-01-10 00:00:00.000	2.00	Other Holidays
    --2	2019-05-06 00:00:00.000	3.15	Holiday
    --2	2019-12-25 00:00:00.000	7.30	Christmats
    
    
    

    Hope it can help you.

     

    Best Regards,

    Natig


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. 


    Friday, April 26, 2019 2:46 PM
  • The bank holioday should only be inserted for the selected employees  not for all the employee. How can I give  the insert for the selected employees

    for example  we have employee id 1 to 100. But I want to insert the record only for the employee given in the variable

    set @Empid = '1,2,5,6,7' . but employee id is integer and I have to insert the holiday for the employee 1,2,5,6,7


    polachan

    Friday, April 26, 2019 3:35 PM
  • What language are you programming and? It certainly is not SQL. The only format allowed for dates in ANSI/ISO standard SQL is “yyyy-mm-dd” and there is no string data type (never has been). Nobody in their right mind would ever use the maximum string length. The length of a variable is a vital part of its data integrity.

    BEGIN 
    DECLARE @start_date DATE;
    DECLARE @end_date DATE;
    DECLARE @emp_id CHAR(15);

    SET @start_date = '2019-01-01';
    SET @end_date = '2019-12-31;
    SET @emp_id = '1,2,5,6,7';  -- looks suspect 
    END;

    I hope that you understand that emp_id is not intended to be a list. SQL uses scalar values in such places. That would be so fundamentally wrong. Also, why did you post DDL? Why do we have to do your work for you from your from your narrative?

    >> I want to insert all bank holidays between that @start_date and @end_date for all employees and its corresponding working Hrs for each employee in the variable @emp_id. <<

    >> Please I am looking for the help . I have given the table structure and record below <<

    No, you are very rude and posted only a narrative. We are the people that had to provide the table structure and the data. Why don’t you know what normalization is? Did you know that a table must have a key, by definition? Things like “Monday”, “Tuesday”, etc. are actually units of measure in the temporal scale. They are not attributes and should not be in columns. In short, your data model is completely wrong

    Let’s try and fix everything you’re doing

    CREATE TABLE Personnel
    (emp_id CHAR(10) NOT NULL PRIMARY KEY,
     emp_name VARCHAR(25) NOT NULL);

    CREATE TABLE Timesheets
    (emp_id  CHAR(10) NOT NULL
        REFERENCES Personnel(emp_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
     posting_date DATE NOT NULL,
     working_hrs  DECIMAL (5,2) DEFAULT(0.00) NOT NULL,
    PRIMARY KEY (emp_id,  posting_date));

    CREATE TABLE BankHolidays
    (holiday_date DATE NOT NULL PRIMARY KEY,;
     holiday_description VARCHAR (25) NOT NULL);

    INSERT INTO BankHolidays
    VALUES
    ('2019-01-01’, 'New Year'),
    ('2019-01-10', 'Other Holiday'),
    ('2019-05-06','Holiday'),
    (‘2019-12-25','Christmas');

    You have picked the most ambiguous local date display format on earth. Please try to come up to ISO-8601 standards by changing the settings in your SQL Server. 
    .
    >> I have to insert the record [sic: rows are not records] each bank holiday for the given start_date and end_date and for the selected employee into the Tmesheets table <<

    Google around and find out what a calendar table is. Another part of the ISO standards that might be useful in your situation is a display format based on weeks. It is popular in the Nordic countries. It uses a week and has the format “yyyyWww-d” ; “yyyy” is the year, the letter “W” is a marker followed by the number of the week within the calendar year (01 to 52 or 53), the dash is more punctuation and the day is between 1 (Monday) and 7 (Sunday). You can download calendars from the Internet that have the traditional display format along with this week format. Or it simple enough to write a program to fill in your lookup table

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, April 26, 2019 8:08 PM
  • set @Empid = '1,2,5,6,7' . but employee id is integer and I have to insert the holiday for the employee 1,2,5,6,7

    So you need to split the string into a table. See this short article on my web site for how to do this:
    http://www.sommarskog.se/arrays-in-sql.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, April 26, 2019 10:08 PM
  • The bank holioday should only be inserted for the selected employees  not for all the employee. How can I give  the insert for the selected employees

    for example  we have employee id 1 to 100. But I want to insert the record only for the employee given in the variable

    set @Empid = '1,2,5,6,7' . but employee id is integer and I have to insert the holiday for the employee 1,2,5,6,7


    polachan

    See this?

    CREATE TABLE EmployeeMaster 
    (
    EmpID   int, 
    EmpName varchar(64),
    MonHrs   decimal (5,2),
    TueHrs   decimal (5,2),
    WedHrs   decimal (5,2),
    ThuHrs   decimal (5,2)
    )
     
    CREATE TABLE BankHoliday
    (
    DateHoliday   date,
    Descritption   varchar(max)
    )
    
    Insert into EmployeeMaster(EmpID, EmpName, MonHrs, TueHrs, WedHrs, ThuHrs) Values
    (1,'AA',2.3,4.3,5.3,3),
    (2,'BB',3.15,4.3,7.30,2),
    (3,'BBCC',3.2,4.5,7.40,2)
    
    Insert into BankHoliday(DateHoliday,Descritption)Values
    ('2019-01-01', 'New Year'),
    ('2019-01-10', 'Other Holiday'),
    ('2019-05-06','Holiday'),
    ('2019-12-25','Christmas')
    
    CREATE PROC proc_query
    @m_Fromdate datetime,
    @m_ToDate datetime,
    @m_Empid varchar(max),
    @m_delimer varchar(1)
    as
    begin
    ;WITH CTE AS
    (
    SELECT 
    EmpID,DAY_AT,Hrs
    FROM EmployeeMaster
    UNPIVOT
    (
     Hrs FOR DAY_AT IN ([MonHrs],[TueHrs],[WedHrs],[ThuHrs])
    ) UP
    )
    SELECT 
    T.EmpID,
    T1.DateHoliday as [Date],
    T1.Descritption as [Desc],
    T2.Hrs as [Attendance]
    FROM EmployeeMaster T
    CROSS JOIN BankHoliday T1
    LEFT JOIN CTE T2 ON T.EmpID=t2.EmpID AND LEFT(DATENAME(DW,T1.DateHoliday),3)=LEFT(T2.DAY_AT,3)
    WHERE T1.DateHoliday BETWEEN @m_Fromdate AND @m_ToDate
    AND EXISTS (SELECT 1 FROM string_split(@m_Empid,@m_delimer) WHERE value=T.EmpID)
    end
    
    EXEC proc_query '2019-01-01','2019-12-31','1,2',','

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by pituachMVP Monday, April 29, 2019 11:26 AM
    Monday, April 29, 2019 9:13 AM
  • The bank holioday should only be inserted for the selected employees  not for all the employee. How can I give  the insert for the selected employees

    for example  we have employee id 1 to 100. But I want to insert the record only for the employee given in the variable

    set @Empid = '1,2,5,6,7' . but employee id is integer and I have to insert the holiday for the employee 1,2,5,6,7


    polachan

    Hi,

    There are many ways to do it. One of the easier approach would be to insert the employee ids (1,2,5,6,7) into a table (ex temp) after you split up the employee ids.

    Then you select one id at a time from temp table and insert a row into attendence table for that id. Delete that id from the temp table. You will be done once you have no rows left into temp table. You would need a loop to do it. Good luck.





    Monday, April 29, 2019 9:41 AM
  • polachan

    Hi,

    There are many ways to do it. One of the easier approach would be to insert the employee ids (1,2,5,6,7) into a table (ex temp) after you split up the employee ids.

    Then you select one id at a time from temp table and insert a row into attendence table for that id. Delete that id from the temp table. You will be done once you have no rows left into temp table. You would need a loop to do it. Good luck.

    hi,

    >> Then you select one id at a time

    SQL Server (like other tabular databases) is designed to work with SET of data and not row by row. The entire approach of the way the server works under the scenes is based on parsing the queries and find a good execution plan, based on complex and advance algorithms (some known and some kept in secret), which meant to improve the performance of working with SETs of data. In most cases it is very bad idea to work "one id at a time", using loop, using Cursor, and so on. 


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, April 29, 2019 11:26 AM
  • polachan

    Hi,

    There are many ways to do it. One of the easier approach would be to insert the employee ids (1,2,5,6,7) into a table (ex temp) after you split up the employee ids.

    Then you select one id at a time from temp table and insert a row into attendence table for that id. Delete that id from the temp table. You will be done once you have no rows left into temp table. You would need a loop to do it. Good luck.

    hi,

    >> Then you select one id at a time

    SQL Server (like other tabular databases) is designed to work with SET of data and not row by row. The entire approach of the way the server works under the scenes is based on parsing the queries and find a good execution plan, based on complex and advance algorithms (some known and some kept in secret), which meant to improve the performance of working with SETs of data. In most cases it is very bad idea to work "one id at a time", using loop, using Cursor, and so on. 


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    " In most cases it is very bad idea to work "one id at a time", using loop, using Cursor, and so on."

    In real life situation often set based solution is useless. I am looking for something or whatever works for me. sql server is very limited and I don't want being handicapped by some poor set based approach.

    Monday, April 29, 2019 1:03 PM
  • "In real life situation often set based solution is useless. I am looking for something or whatever works for me. sql server is very limited and I don't want being handicapped by some poor set based approach."

    You can do whatever to solve the problems you are facing but in most cases, set-based solutions offer better performance. Use a loop in a solution of T-SQL query generally implies a non-optimal solution at least. 

    Monday, April 29, 2019 2:16 PM
  • right, most of my problems involve few thousands of records (main table has 50k and all side tables few thousands some even less and working mainly with side tables) and optimazation is never an issue while I spend hours getting a 'correct' solution sometimes if I am lucky its a typo and more insidious are semantics problem that requires looking at data (various sql statements) so investigate and understand. In that scenario last thing I am worried about set based and etc but rest assure sql is such a language no matter how hard I try 99% of sql I write is set based.

    Monday, April 29, 2019 4:36 PM
  • right, most of my problems involve few thousands of records (main table has 50k and all side tables few thousands some even less and working mainly with side tables) and optimazation is never an issue while I spend hours getting a 'correct' solution sometimes if I am lucky its a typo and more insidious are semantics problem that requires looking at data (various sql statements) so investigate and understand. In that scenario last thing I am worried about set based and etc but rest assure sql is such a language no matter how hard I try 99% of sql I write is set based.

    hi,

    This is really bad approach

    You say that "spend hours getting a 'correct' solution" but yet you do not want to implement simple rules and learn how to get fast CORRECT solution (correct without quotation marks instead of fake 'correct'). Maybe the reason it take you longer time than other people is because your way of thinking about the database is wrong and you treat like developers treat arrays? Give us a chance to make you expert which able to solve your issue fast, by taking the first step :-)

    It is not important if you have 10mb database or 100TB...

    Once you will get used to see the database as SETs of data instead of arrays of records, then you will probably find solutions faster and obviously it will be better.

    Anyhow, this is your call and we cannot order you what to do :-)

    "a solution always start with the first step"
    Have a great day


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, April 30, 2019 3:01 AM
  • right, most of my problems involve few thousands of records (main table has 50k and all side tables few thousands some even less and working mainly with side tables) and optimazation is never an issue while I spend hours getting a 'correct' solution sometimes if I am lucky its a typo and more insidious are semantics problem that requires looking at data (various sql statements) so investigate and understand. In that scenario last thing I am worried about set based and etc but rest assure sql is such a language no matter how hard I try 99% of sql I write is set based.

    hi,

    This is really bad approach

    You say that "spend hours getting a 'correct' solution" but yet you do not want to implement simple rules and learn how to get fast CORRECT solution (correct without quotation marks instead of fake 'correct'). Maybe the reason it take you longer time than other people is because your way of thinking about the database is wrong and you treat like developers treat arrays? Give us a chance to make you expert which able to solve your issue fast, by taking the first step :-)

    It is not important if you have 10mb database or 100TB...

    Once you will get used to see the database as SETs of data instead of arrays of records, then you will probably find solutions faster and obviously it will be better.

    Anyhow, this is your call and we cannot order you what to do :-)

    "a solution always start with the first step"
    Have a great day


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    You are thinking ahead and putting words in my mouth. Being 'correct' means when I write a sql I should get the 'right' answer. Most of the people when write a sql statement (select, update whatever) can not tell ahead how many rows it will affect (the statement). Even that, sometimes the statement gives the 'correct' answer and other times (different parameter but with valid records) I get 0 rows affected. Then I need to investigate further. Being 'correct' means the statement does what it is supposed to do and if not finding out why it is not doing it and that requires examining the 'set' of data and finding out why it is not giving the right answer and it takes times time to get to the root of the problem. Finally once I find the solution to the problem and implement the design then presto I am onto the next problem to be tacked on my roster and it is same routine all over again. I have multiple tabs open with numerous sql statements and I just comment in an out various parts of the sql statement and investigating usually two tables at a time probably investigating why the join, update, select involving them is not working out. It takes time and effort to get the 'correct' solution and that's just one of many other solutions I have to fix to get a whole solution I am working on. I hope its clear now. :)  
    Here is one example (easy one). I was updating two tables and using joins on various columns and one was doing a simple string match. table1.string = table2.string (there were additional predicates but lets just focus on the predicate that caused problem that I had to investigate. The statement worked for most of the parameters as I was updating one parameter at a time for the update. It stopped working for one parameter string and it took me some time to figure out that  there was an extra space in the string in one table causing not to match and hence the entire statement was failing. I manually typed in the string without the space, so T.A. becomes T. A. :) It took time to figure out (again it was simple problem).    
    Another one was like I had, where etc ...and statuson between '2019-02-24 12:36:54' and '2019-02-24 12:36:54' for some reason it stopped working (no update for those records) and I had to change 36 second to 37 to get it working. That may even work in sql server but I was using postgress. I usually think in terms of sql server and implement it using postgres just to learn it better. That's a whole new ball game because I am always looking for equivalent statement for sql statement for both platforms but I have found it easier thing to do.





    Tuesday, April 30, 2019 3:17 AM