locked
How to find the netHrs in a week of the employees. Please help RRS feed

  • Question

  • I have the following table

    Create table EmpHrs

    (EmpID int, MonIn decimal(5,2), MonOut decimal(5,2),MonBreak decimal(5,2),

    TueIn decimal(5,2), TueOut decimal(5,2),TueBreak decimal(5,2), OffWeek decimal(5,2)). I have the following record in the table. How Can I  get an sql to get the NetHrs  as given below

    (((MonOut - Mon ) - MonBreak))  +  ((TueOut-TueIn) - TueBreak) ) - OffWeek. Please I am looking for the help to find the netHrs of eah employee in a week. Please help


    polachan

    Friday, May 17, 2019 11:13 AM

All replies

  • SELECT EmpId, (MonOut - MonIn - MonBreak + TueOut - TueIn - TueBreak - OffWeek) AS NetHrs

    FROM EmpHrs


    A Fan of SSIS, SSRS and SSAS

    Friday, May 17, 2019 4:51 PM
  • You have so many fundamental mistakes in this posting. I want to use it in a book as a bad example. SQL is a programming language that has temporal datatypes, so modeling it with decimal datatypes is fundamentally wrong. Not a little wrong, fundamentally wrong. One of my favorite T-shirt slogans is "on a scale from 1 to 10, what is your favorite color of a letter of the alphabet?" Because it's so totally absurd and demonstrates the confusion of datatypes.

    But more than that, you understand how to normalize something. The days of the week are units of measure on a temporal scale. They are not unique attributes!

    Finally, you are so rude you didn't even bother to post DDL. Frankly, if you were a student I would flunk you; if you are an employee I would fire you. Let's try and fix your mess.

    First of all, a table must have a key. This is not an option but a fundamental concept that you don't know. Next, the identifier cannot be numeric because you do know computations on it and it must be, by definition, a nominal scale. Is this what you meant? 

    CREATE TABLE  EmpHrs
    (emp_id CHAR(3) NOT NULL PRIMAR KEY,
     emp_start_time DATETIME(0)) NOT NULL,
     emp_end_time DATETIME(0)) NOT NULL,
     CHECK(emp_start_time <= emp_end_time)
    );

    This design is still wrong. All you have done is try to copy into an SQL table the way you would have written a paper form in the 1950s. A day of the week is a unit of measure not an attribute. 

    >> I have the following record [sic] in the table. How Can I  get an SQL to get the NetHrs  as given below <<

    Rows are not records. This is a fundamental concept of RDBMS and SQL.

    Polachan, you keep asking people to do your homework for you yet you show no evidence of having learned anything or doing any basic research. Why should we help you? Are used having people do your homework for you when you were in school? 

    --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, May 17, 2019 5:20 PM
  • Hi polachan,

    Please try

    Create table EmpHrs
    (
    EmpID int, 
    MonIn decimal(5,2), 
    MonOut decimal(5,2),
    MonBreak decimal(5,2),
    TueIn decimal(5,2), 
    TueOut decimal(5,2),
    TueBreak decimal(5,2), 
    OffWeek decimal(5,2)
    )
    
    insert into EmpHrs values
    (1,9,17.3,1,9,17.3,1,2.08),
    (2,8,18,1,8,18,1,2.08),
    (5,8,18,1,8,18,1,2.08),
    (6,8,18,1,8,18,1,1.23),
    (7,9,17,0.3,9,17,0.3,1.23),
    (8,8.3,15,0.3,8.3,15,0.3,1.28),
    (9,8.3,17.3,1,9,17.3,1,2.08)
    --Query
    SELECT 
    EmpID,
    MonIn,
    MonOut,
    MonBreak,
    TueIn,
    TueOut,
    TueBreak,
    OffWeek,
    cast(((FLOOR(MonOut)-FLOOR(MonIn)-FLOOR(MonBreak)+FLOOR(TueOut)-FLOOR(TueIn)-FLOOR(TueBreak)-FLOOR(OffWeek))*60
    +((MonOut-FLOOR(MonOut))-(MonIn-FLOOR(MonIn))-(MonBreak-FLOOR(MonBreak))+(TueOut-FLOOR(TueOut))-(TueIn-FLOOR(TueIn))-(TueBreak-FLOOR(TueBreak))-(OffWeek-FLOOR(OffWeek)))*100) as int)/60
    +
    cast(cast(((FLOOR(MonOut)-FLOOR(MonIn)-FLOOR(MonBreak)+FLOOR(TueOut)-FLOOR(TueIn)-FLOOR(TueBreak)-FLOOR(OffWeek))*60
    +((MonOut-FLOOR(MonOut))-(MonIn-FLOOR(MonIn))-(MonBreak-FLOOR(MonBreak))+(TueOut-FLOOR(TueOut))-(TueIn-FLOOR(TueIn))-(TueBreak-FLOOR(TueBreak))-(OffWeek-FLOOR(OffWeek)))*100) as int)%60*1.0/100 as decimal(5,2)) as NetHrs
    FROM EmpHrs

    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.

    Monday, May 20, 2019 9:01 AM