November 19, 2021 at 10:56 am
Please assist me? I would like to calculate:my code:
SELECT * from [dbo].
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
November 19, 2021 at 11:53 am
With >3,000 points, you should know to provide a few things:
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
USE [Skuss]
GO
/****** Object: Table [dbo].
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
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