July 27, 2011 at 4:31 am
Hi Techies,
Attached is the sql statements to create 3 tables and insert dummy data in all three tables.
Requirement:
Pull all associates information:
1) Associates who Worked less than 8 hrs if there were US PT Hourly
2) Associates who worked less than 8 hrs if they were US FT Hourly
3) Data needed for the past one month.
4) There will be multiple entry in Totals table per employee per day for each punch they make , so needed to consolidate the total hours worked for that day and find if it is less than 6 or 8 (this is where i'm missing)
I tried the following query and it is not giving results as expected.
select distinct a.personnum, a.personfullname,a.Location as store_number,a.REGIONNM as region,
sum(d.durationsecsqty)/3600.00 hours,a.ACCRUALPRFLNAME,b.accrualprofileid,
convert(VARCHAR(11),d.APPLYDTM,101) APPLYDATE
from PERSONAL a, accrualprofile b, TOTALS d WITH (NOLOCK) where
a.employeeid = d.employeeid and
a.ACCRUALPRFLNAME = b.name
and a.REGIONNM = 'MIDSOUTH REGION'
--and b.accrualprofileid = '22'
--and a.ACCRUALPRFLNAME = 'US PT HOURLY'
--and a.ACCRUALPRFLNAME like '%HOURL%'
and d.APPLYDTM > '2011-07-27 00:00:00.000'
--sum(d.durationsecsqty)/3600.00 < 6
group by a.LOCATION,a.personnum, a.personfullname, a.ACCRUALPRFLNAME,
b.accrualprofileid, d.durationsecsqty,d.APPLYDTM,a.REGIONNM
having sum(d.durationsecsqty)/3600.00 < 6
Need result for both US FT and US PT in singe query/output to help the report.
Let me know if the req is no clear.
Thanks in advance.
July 27, 2011 at 4:47 am
Your ACCRUALPROFILE INSERTs violate the primary key constraints on that table.
John
July 27, 2011 at 6:32 am
John,
Correct the SQL insert.
Thanks
July 27, 2011 at 6:43 am
Thanks. Here are the results I get with your query:
personnumpersonfullnamestore_numberregionhoursACCRUALPRFLNAMEaccrualprofileidAPPLYDATE
100242589Will Smith0523MIDSOUTH REGION1.0000000US PT HOURLY207/27/2011
100244250Adam Sandler4759MIDSOUTH REGION4.5000000US FT HOURLY107/27/2011
100325669Daniel Grower0121MIDSOUTH REGION4.3333333US PT HOURLY207/27/2011
100536236Russell Crowe0123MIDSOUTH REGION3.5000000US PT HOURLY207/27/2011
100549823JOHN SMITH0523MIDSOUTH REGION4.3333333US PT HOURLY207/27/2011
101025362Seth Rogan4023MIDSOUTH REGION0.8055555US FT HOURLY107/27/2011
What results do you expect to see?
John
July 28, 2011 at 2:22 am
John,
The requirement is to pull associates who worked less than 6 hrs and 8 hrs if they were PT or FT respectively on a given day.
So the sample data has all in 7/27 day , it means the result should not have associates who worked more than 6 hours. Multiple entries means multiple punches on single day, but collective hours exceeds 6 hrs then he/she should be eliminated in the result set.
Hope I answered your question
Please help on the query to find the list for both FT and PT associates.
Thanks for your time.
July 28, 2011 at 2:31 am
kotteesh (7/28/2011)
Hope I answered your question
Unfortunately not. It's difficult to visualise when you describe it in words. Please will you take the result set I posted, and change it so that it's how you want it to appear. Please highlight the changes in a different colour for extra clarity.
Thanks
John
July 28, 2011 at 5:35 am
Sorry abt that.
* Need to Pull list of associates who worked less than 6 or 8 hrs for given period of time (Could be a month)
* There may two or more entries per associate per day in the totals table as it is directly connected to punch machine.
* When we pull the list we have to look for the entire days hours worked to filter for the mandatory hours worked.
Updated the insert into TOTALS table with more inputs to recreate the problem.
Also attached the latest query and the result as XL. Highlighted the problem result with reason.
select distinct a.personnum, a.personfullname,a.Location as store_number,a.REGIONNM as region,
sum(d.durationsecsqty)/3600.00 hours,a.ACCRUALPRFLNAME,b.accrualprofileid,
convert(VARCHAR(11),d.APPLYDTM,101) APPLYDATE
from PERSONAL a, accrualprofile b, TOTALS d WITH (NOLOCK) where
a.employeeid = d.employeeid and
a.ACCRUALPRFLNAME = b.name
and a.REGIONNM = 'MIDSOUTH REGION'
--and b.accrualprofileid = '22'
and a.ACCRUALPRFLNAME = 'US PT HOURLY'
--and a.ACCRUALPRFLNAME like '%HOURL%'
and d.APPLYDTM > '2011-06-27 00:00:00.000'
--sum(d.durationsecsqty)/3600.00 < 6
group by a.LOCATION,a.personnum, a.personfullname, a.ACCRUALPRFLNAME,
b.accrualprofileid, d.durationsecsqty,d.APPLYDTM,a.REGIONNM
having sum(d.durationsecsqty)/3600.00 < 6
Thanks much!, please help.
July 28, 2011 at 6:29 am
It's because you're grouping on the duration. Quick fix: remove that item from the GROUP BY statement. Proper fix:
(1) Remove NOLOCK, unless you don't mind the possibility of dirty reads returning inaccurate data
(2) Use meaningful aliases for your tables
(3) Use the ANSI join syntax instead of the old-style syntax
(4) Don't group on more columns than necessary. If one column in a table uniquely identifies a row, you don't need to include the other columns in your GROUP BY clause
(5) Lay out your code so that it's easy to read
(6) Add comments so that whoever needs to modify it in future understands it
This is what you end up with. Removing all those columns from the GROUP BY means also removing them from SELECT, but if you want that information, you can join back to the original table. Here's what you end up with:
SELECT DISTINCT
p.EmployeeID
,SUM(t.durationsecsqty)/3600.00 AS hours -- div by 3600 to turn secs to hrs
,CONVERT(varchar(11),t.APPLYDTM,101) AS APPLYDATE -- conversion strips out time portion
FROM
PERSONAL p
JOIN
accrualprofile a
ON -- COLLATE used here to make it work with my database's collation
p.ACCRUALPRFLNAME COLLATE Latin1_General_CI_AS = a.name COLLATE Latin1_General_CI_AS
JOIN
TOTALS t ON p.employeeid = t.employeeid
WHERE
p.REGIONNM COLLATE Latin1_General_CI_AS = 'MIDSOUTH REGION'
AND
p.ACCRUALPRFLNAME = 'US PT HOURLY'
AND
t.APPLYDTM > '2011-06-27 00:00:00.000'
GROUP BY
p.EmployeeID
,t.APPLYDTM
,CONVERT(varchar(11),t.APPLYDTM,101)
HAVING
SUM(t.durationsecsqty)/3600.00 < 6 -- only want where time worked < 6hrs
John
July 28, 2011 at 11:18 pm
John. Thank you so much!
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply