Post reply

Calculate HR

  • November 19, 2021 at 10:56 am

    #3954792

    Please assist me? I would like to calculate:my code:

    SELECT * from [dbo].

    where LeaveTypeName Not In ('paid time off')

    BETWEEN date(DateApprovedSupervisor) AND date(2001-08-01)

    Number of team members who have not used a PTO in the last 30, 60, 90 days (include % per 30-day period) - Number of team members who used more than 3 PTO in the last 90 days (include %) -

     

    File attached that i imported into MSSQL

    Attachments:
    You must be logged in to view attached files.

  • November 19, 2021 at 11:53 am

    #3954800

    With >3,000 points, you should know to provide a few things:

    1. Sample DDL for your table
    2. Sample data, in the form of INSERT statements for the table created in (1), suitable for pasting into SSMS and running.
    3. Desired results, based on the sample data.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • November 19, 2021 at 12:45 pm

    #3954819

    USE [Skuss]

    GO

    /****** Object: Table [dbo].

    Script Date: 2021/11/19 14:41:12 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].

    (

    [Name] [varchar](50) NULL,

    [LeaveTypeName] [varchar](50) NULL,

    [DateFrom] [varchar](50) NULL,

    [DateTo] [varchar](50) NULL,

    [WithPayNoOfdays] [varchar](50) NULL,

    [WoutPayNoOfDays] [varchar](50) NULL,

    [LeaveStatus] [varchar](50) NULL,

    [DateApprovedSupervisor] [varchar](50) NULL,

    [DateRejectedSupervisor] [varchar](50) NULL,

    [Column 9] [varchar](50) NULL,

    [Column 10] [varchar](50) NULL,

    [Column 11] [varchar](50) NULL,

    [Column 12] [varchar](50) NULL,

    [Column 13] [varchar](50) NULL,

    [Column 14] [varchar](50) NULL,

    [Column 15] [varchar](50) NULL,

    [Column 16] [varchar](50) NULL,

    [Column 17] [varchar](50) NULL,

    [Column 18] [varchar](50) NULL

    ) ON [PRIMARY]

    GO

    Desired results as follows:

    I would like to calculate ...

    Number of team members who have not used a PTO in the last 30, 60, 90 days (include % per 30-day period) - Number of team members who used more than 3 PTO in the last 90 days (include %) - How many PTOs were cancelled? How many were declined? How many were not actioned on?(include %)

  • November 20, 2021 at 11:15 am

    #3955114

    Like Phil said, you should know better.....

    Your table has no name

    you have provided a table structure but no data

    What is a PTO (I am assuming Period of Time Off)

    Your table has a name but does not indicate a link to another table with a list of team members.  If it did then the person identifier should probably be their employeee ID, not their name as names can be changed and the Primary Key for a table should be immutable.

    That said this would be my approach

    Create a CTE to aggregate the counts of the PTO records by employee for each of the periods you are interested in by running a separate SELECT statement for each period and UNIONing them together.

    PIVOT the CTE to flatten the structure to one record per emoloyee

    LEFT JOIN from Employee to the PIVOT: this will give one one record per employee and the PIVOT data if they have ANY PTOs

    From there it should just be simple aggregation  but you may need to go through several CTEs as you can't use one window function inside another.

    CAST(COUNT(PIVOT.30days) AS DECIMAL(10,2)) / COUNT(EmloyeeID) * 100 = % of employees with a PTO in the last 30 days.  NOTE the CAST() to decimals.  You need to do this because the two counts are INT values and if you divide an INT by an INT the result will be an INT and you lose precision.

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply