July 10, 2015 at 2:48 pm
I have hard coded a DAte (6/22/2015 12:00:00 AM) inside DateDiff to get the last friday date. I want to put a Pr.DATE instead. This I think needs to run as sub query because I have multiple dates inside Pay records table and I need to Extract Last friday for each iterations inside Pay records. Once that is achieved I would be able to get the correct SUM(Hours) for rate type SICK. Currently I'm getting all the same sick hours in each row because of the hard coded date.
Thank You!
Select v.accrued_sick, v.accrued_sick_effective_date, x.*
from dbo.Accruals v
INNER JOIN Employee e
ON e.employee_no = v.employee_no
LEFT JOIN (SELECT pv.employee_no,
Sum(sick) AS SICK
FROM
(SELECT pr.pay_period_id AS pp_id,
pr.[date],
[hours],
employee_no,
rate_type
FROM pay_records AS pr) AS p
PIVOT (Sum([hours])
FOR rate_type IN ([SICK]) ) AS pv
WHERE [date] > (select accrued_sick_effective_date from dbo.Accruals Where
accrued_sick_effective_date = Dateadd(day, ( Datediff (day, '19990102', '6/22/2015 12:00:00 AM') / 7 ) * 7,'19990101'))
AND [date] <= Dateadd(week, 1, ( Dateadd(day, ( Datediff (day, '19990102','6/22/2015 12:00:00 AM') / 7 ) * 7,'19990101') ))
GROUP BY pv.employee_no) AS x
ON x.employee_no = e.employee_no
WHERE e.employee_no = '15879'
July 10, 2015 at 3:15 pm
shezi (7/10/2015)
I have hard coded a DAte (6/22/2015 12:00:00 AM) inside DateDiff to get the last friday date. I want to put a Pr.DATE instead. This I think needs to run as sub query because I have multiple dates inside Pay records table and I need to Extract Last friday for each iterations inside Pay records. Once that is achieved I would be able to get the correct SUM(Hours) for rate type SICK. Currently I'm getting all the same sick hours in each row because of the hard coded date.Thank You!
Select v.accrued_sick, v.accrued_sick_effective_date, x.*
from dbo.Accruals v
INNER JOIN Employee e
ON e.employee_no = v.employee_no
LEFT JOIN (SELECT pv.employee_no,
Sum(sick) AS SICK
FROM
(SELECT pr.pay_period_id AS pp_id,
pr.[date],
[hours],
employee_no,
rate_type
FROM pay_records AS pr) AS p
PIVOT (Sum([hours])
FOR rate_type IN ([SICK]) ) AS pv
WHERE [date] > (select accrued_sick_effective_date from dbo.Accruals Where
accrued_sick_effective_date = Dateadd(day, ( Datediff (day, '19990102', '6/22/2015 12:00:00 AM') / 7 ) * 7,'19990101'))
AND [date] <= Dateadd(week, 1, ( Dateadd(day, ( Datediff (day, '19990102','6/22/2015 12:00:00 AM') / 7 ) * 7,'19990101') ))
GROUP BY pv.employee_no) AS x
ON x.employee_no = e.employee_no
WHERE e.employee_no = '15879'
Do you mean like this?
Select v.accrued_sick, v.accrued_sick_effective_date, x.*
from dbo.Accruals v
INNER JOIN Employee e
ON e.employee_no = v.employee_no
LEFT JOIN (SELECT pv.employee_no,
Sum(sick) AS SICK
FROM
(SELECT pr.pay_period_id AS pp_id,
pr.[date],
[hours],
employee_no,
rate_type
FROM pay_records AS pr) AS p
PIVOT (Sum([hours])
FOR rate_type IN ([SICK]) ) AS pv
WHERE [date] > (select accrued_sick_effective_date from dbo.Accruals Where
accrued_sick_effective_date = Dateadd(day, ( Datediff (day, '19990102', PR.Date) / 7 ) * 7,'19990101'))
AND [date] <= Dateadd(week, 1, ( Dateadd(day, ( Datediff (day, '19990102',PR.Date) / 7 ) * 7,'19990101') ))
GROUP BY pv.employee_no) AS x
ON x.employee_no = e.employee_no
WHERE e.employee_no = '15879'
Or am I missing something here?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 10, 2015 at 3:26 pm
Yes that is the idea but its giving me syntax error "The multi-part "Pr.Date" could not be bound."
Thank you!
July 10, 2015 at 3:50 pm
It looks like you aliased this to be "p". Change "PR" to "p" in the above script. (I was basing this solely off of what you had said)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 13, 2015 at 8:06 am
Wayne, I do appreciate your constant assistance.
Unfortunately it is still giving me that error even with P. Also I do need to iterate thought the dates from the pay record table. from each date, I'm getting the date of last friday. The query works if I put a static date but my sum of hours only applies for that date's last friday date.
July 13, 2015 at 8:16 am
You need to change it in the WHERE clause, not in the subquery.
John
July 13, 2015 at 8:26 am
thank you guys, I did make progress based on your suggestions but I removed the aliases names from the date. Here is the updated sql query. Now i"m getting a different error when I run the sub query. Perhaps it's still related? See below
Select v.accrued_sick, v.accrued_sick_effective_date, x.*
from dbo.VacationAccruals v
INNER JOIN Employee e
ON e.employee_no = v.employee_no
LEFT JOIN (SELECT pv.employee_no,
Sum(sick) AS SICK
FROM
(SELECT pr.pay_period_id AS pp_id,
pr.[date],
[hours],
employee_no,
rate_type
FROM pay_records AS pr) AS p
PIVOT (Sum([hours])
FOR rate_type IN ([SICK]) ) AS pv
WHERE [date] > (select accrued_sick_effective_date from dbo.VacationAccruals Where
accrued_sick_effective_date = Dateadd(day, ( Datediff (day, '19990102', [Date]) / 7 ) * 7,'19990101'))
AND [date] <= Dateadd(week, 1, ( Dateadd(day, ( Datediff (day, '19990102',[Date]) / 7 ) * 7,'19990101') ))
GROUP BY pv.employee_no) AS x
ON x.employee_no = e.employee_no
WHERE e.employee_no = '1333'
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Warning: Null value is eliminated by an aggregate or other SET operation.
July 13, 2015 at 8:51 am
In your WHERE clause, you're comparing the values in the date column to the value returned by the subquery. If that subquery returns more than one value, you'll get an error. Maybe you could use MAX or MIN to guarantee the single value?
John
July 13, 2015 at 9:56 am
John Mitchell-245523 (7/13/2015)
In your WHERE clause, you're comparing the values in the date column to the value returned by the subquery. If that subquery returns more than one value, you'll get an error. Maybe you could use MAX or MIN to guarantee the single value?John
As the WHERE clause for that subquery specifies the field being selected, I suspect they need only add the DISTINCT keyword, as it appears that formula in the WHERE clause should only return one value.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 13, 2015 at 3:50 pm
Going back to original Query posted in the question, here is the issue. Notice my sick hours aren't tallying up because I hardcoded ONE date. I want to replace the hard coded date with the Pay record date which will get the effective date (which is last friday) and then it will tally up Total sick Hours for each Effective Date. I have only 4 effective date. Basically I want to iterate through dates found in Pay record, get the last friday date (which is effective) and then tally the sick hours as of each effective date.
accrued Sick effective Date employee_no Sick
2.00 2015-06-12 1255 3
3.00 2015-06-19 1255 3
7.00 2015-07-03 1255 3
5.00 2015-06-26 1255 3
That is why I was doing a sub query to run through all the dates in Pay record, tally the sick hours but I'm not sure how to achieve this.
Thank You guys!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply