Help with SQL 2008 Stored Procedure
I have the following subquery that calculates the variance in PTO that you have accrued versus the amount that has been approved. Currently if you haven't taken any PTO the varience displays a 0. What I want to display is if the varience = 0 then DATEPART(M, GETDATE( )) * ( PL.Accrual ) = Variance
,ISNULL(( SELECT DATEPART(M, GETDATE( )) * ( PL.Accrual )
- SUM(TotalHoursUsed)
FROM PTORequest PR
LEFT OUTER JOIN PTOBeginningBalances PB ON PR.EmployeeUsername = PB.EmployeeUsername
LEFT OUTER JOIN PTOLevelsAccrualHours PL ON PB.Level = PL.Level
WHERE PBAL.EmployeeUsername = PR.EmployeeUsername
AND NOT SUBSTRING(PTOType, 4, LEN(PTOType) - 3) = 'Carryover'
AND ( YEAR(PB.Year) = YEAR(GETDATE()) )
AND SUBSTRING(PR.Status, 4, LEN(PR.Status) - 3) = 'Approved'
AND StartDateAndTime > DATEADD(yy,
DATEDIFF(yy, 0,
GETDATE()), 0)
AND StartDateAndTime < DATEADD(m,
DATEDIFF(m, -1,
GETDATE()), 0)
GROUP BY PL.Accrual
), 0) AS [Variance]
,ISNULL(( SELECT DATEPART(M, GETDATE( )) * ( PL.Accrual )
- SUM(TotalHoursUsed)
FROM PTORequest PR
LEFT OUTER JOIN PTOBeginningBalances PB ON PR.EmployeeUsername = PB.EmployeeUsername
LEFT OUTER JOIN PTOLevelsAccrualHours PL ON PB.Level = PL.Level
WHERE PBAL.EmployeeUsername = PR.EmployeeUsername
AND NOT SUBSTRING(PTOType, 4, LEN(PTOType) - 3) = 'Carryover'
AND ( YEAR(PB.Year) = YEAR(GETDATE()) )
AND SUBSTRING(PR.Status, 4, LEN(PR.Status) - 3) = 'Approved'
AND StartDateAndTime > DATEADD(yy,
DATEDIFF(yy, 0,
GETDATE()), 0)
AND StartDateAndTime < DATEADD(m,
DATEDIFF(m, -1,
GETDATE()), 0)
GROUP BY PL.Accrual
), 0) AS [Variance]
ASKER
No. let me explain, for instance here is what the code is doing now. For employee NXA778 she has 28 hours of approved PTO from Jan 1st until the EOM or February 29th in this case. She accrues 14.56 hours of pto every month. So my code above says DATEPART(M, GETDATE( )) or get current month in this case 2 multiplied by her accrual rate 14.56 subtract her approved PTO = Variance or in this case would = 1.1.
Now lets say this user didn't take any PTO yet this year. the variance displays "0" because that is how the code is set up. What I want is if the user doesn't have any approved PTO from jan 1t to current EOM then just show me how much they accrued or in code terms DATEPART(M, GETDATE( )) * ( PL.Accrual ) = Variance instead of a 0
Make better sense?
Now lets say this user didn't take any PTO yet this year. the variance displays "0" because that is how the code is set up. What I want is if the user doesn't have any approved PTO from jan 1t to current EOM then just show me how much they accrued or in code terms DATEPART(M, GETDATE( )) * ( PL.Accrual ) = Variance instead of a 0
Make better sense?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was able to solve this issue using code from an earlier question on Experts-Exchange that was provided by user angelIII.
,COALESCE(( SELECT DATEPART(M, GETDATE( )) * ( PL.Accrual )
FROM PTOBeginningBalances PB
LEFT OUTER JOIN PTOLevelsAccrualHours PL ON PB.Level = PL.Level
WHERE PBAL.EmployeeUsername = PB.EmployeeUsername
AND ( YEAR(PB.Year) = YEAR(GETDATE()) )
), 0)
- COALESCE(( SELECT SUM(PR.TotalHoursUsed)
FROM PTORequest PR
WHERE PBAL.EmployeeUsername = PR.EmployeeUsername
AND NOT SUBSTRING(PTOType, 4,
LEN(PTOType) - 3) = 'Carryover'
AND SUBSTRING(PR.Status, 4,
LEN(PR.Status) - 3) = 'Approved'
AND StartDateAndTime > DATEADD(yy,
DATEDIFF(yy, 0,
GETDATE()), 0)
AND StartDateAndTime < DATEADD(m,
DATEDIFF(m, -1,
GETDATE()), 0)
), 0) AS [Variance]
,COALESCE(( SELECT DATEPART(M, GETDATE( )) * ( PL.Accrual )
FROM PTOBeginningBalances PB
LEFT OUTER JOIN PTOLevelsAccrualHours PL ON PB.Level = PL.Level
WHERE PBAL.EmployeeUsername = PB.EmployeeUsername
AND ( YEAR(PB.Year) = YEAR(GETDATE()) )
), 0)
- COALESCE(( SELECT SUM(PR.TotalHoursUsed)
FROM PTORequest PR
WHERE PBAL.EmployeeUsername = PR.EmployeeUsername
AND NOT SUBSTRING(PTOType, 4,
LEN(PTOType) - 3) = 'Carryover'
AND SUBSTRING(PR.Status, 4,
LEN(PR.Status) - 3) = 'Approved'
AND StartDateAndTime > DATEADD(yy,
DATEDIFF(yy, 0,
GETDATE()), 0)
AND StartDateAndTime < DATEADD(m,
DATEDIFF(m, -1,
GETDATE()), 0)
), 0) AS [Variance]
You want to set the month to 0??