June 12, 2014 at 3:05 pm
Hi,
This is a problem I have been working on for some time, but Im still unsure how to proceed.
I have a simple table with the following columns
Tabel Name - TblHHWorkedHours
ID int,
PersonelNo int,
WeekEnding date,
HoursWorked float,
ContractHours float
Each date in WeekEnding is a Sunday, the complex part is Grouping the WeekEndings into a 4 weekly time period with the date being highest date and PersonnelNo, and sum(HoursWorked) and Sum(ContractHours).
I want it to be a view that I can then easily query by Personnel No.
SO if an employee had 52 weeks data in the table the view shoudl return 13 rows (of 4 weeks data)for the employee. But I am not sure howmany rows each employee will have in the table.
Sounds straight forward but I am not experienced with SQL enough...
All help appreciated.
June 12, 2014 at 3:31 pm
Tallboy (6/12/2014)
Hi,This is a problem I have been working on for some time, but Im still unsure how to proceed.
I have a simple table with the following columns
Tabel Name - TblHHWorkedHours
ID int,
PersonelNo int,
WeekEnding date,
HoursWorked float,
ContractHours float
Each date in WeekEnding is a Sunday, the complex part is Grouping the WeekEndings into a 4 weekly time period with the date being highest date and PersonnelNo, and sum(HoursWorked) and Sum(ContractHours).
I want it to be a view that I can then easily query by Personnel No.
SO if an employee had 52 weeks data in the table the view shoudl return 13 rows (of 4 weeks data)for the employee. But I am not sure howmany rows each employee will have in the table.
Sounds straight forward but I am not experienced with SQL enough...
All help appreciated.
What about gaps? Say in week 9 there is no data. Should the first column be NULL or do the columns "shift" to the left?
If you could post some sample data along with the desired output based on that sample data we can do this pretty easily. I am thinking all you need is ROW_NUMBER and some modulus.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2014 at 4:23 pm
That would be great...
Sample data would be
ID, PersonnelNo,WeekEnding, WorkedHours, ContractHours
4, 1234, 1/10/14, 10.50, 20.00
3, 1234, 23/9/14, 11.00, 20.00
2, 1234, 16/9/14, 10.75, 22.00
1, 1234, 9/9/14, 10.50, 22.00
0, 1234, 2/9/14, 11.25, 12.00
So each employee may have 52 weeks data per year, but they may start late in the year or leave befor ethe year is up. But all data has WeekEnding dates ie Sundays.
Final Out out for each employee would be 1 row dor each 4 week period as follows;
PersonnelNo, 4WkEnding, TotalWorkedHours, TotalContractHours
1234, 1/10/14, 42.75, 82.00
1234, 2/9/14, 34.50, 82.00
4343, 1/10/14, 34.00, 74.00
Hope that helps...
June 12, 2014 at 6:51 pm
Does this work for you? (Seems not).
Set up some tables/data for folks to use...
USE TempDB;
GO
CREATE TABLE #WorkHours (
ID int
, PersonnelNo int
, WeekEnding date
, WorkedHours decimal(4,2)
, ContractHours decimal(4,2)
);
Insert some records...
INSERT INTO #WorkHours (ID, PersonnelNo, WeekEnding, WorkedHours, ContractHours)
(SELECT 4, 1234, '10/1/14', 10.50, 20.00
UNION ALL
SELECT 3, 1234, '9/23/14', 11.00, 20.00
UNION ALL
SELECT 2, 1234, '9/16/14', 10.75, 22.00
UNION ALL
SELECT 1, 1234, '9/9/14', 10.50, 22.00
UNION ALL
SELECT 0, 1234, '9/2/14', 11.25, 12.00
UNION ALL
SELECT 5, 1111, '10/1/14', 10.50, 23.00
);
This is close, I think...
SELECT PersonnelNo
, LunarMo
, SUM(WorkedHours) AS LunarWorkedTotal
, SUM(ContractHours) AS LunarContractTotal
FROM
(
SELECT PersonnelNo
, ID
, WeekEnding
, 1+DATEDIFF(wk,'1-Jan-2014',WeekEnding)/4 AS LunarMo
, WorkedHours
, ContractHours
FROM #WorkHours
WHERE PersonnelNo=1234
) x
GROUP BY PersonnelNo, LunarMo
ORDER BY PersonnelNo, LunarMo;
June 13, 2014 at 7:48 am
Let's use the excellent sample data and ddl that pietlinden posted. That is how you should post stuff in the future.
What should be the output from the sample data? The desired output you posted doesn't seem to work with the data you posted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 13, 2014 at 8:28 am
I figured that since he was new, I would be nice and show him how to set up the problem so that people could help...
to the OP - if you set up like that, i'ts infinitely easier for folks to help you because they don't have to do much setup at all. In my experience, it helps me to better understand and think through the problem, which sometimes gets me all the way to the solution... just something to think about.
June 13, 2014 at 8:36 am
What's the rule about the 52 weeks? Do they start at the first of the year, or is there some other rule for that?
June 13, 2014 at 8:36 am
pietlinden (6/13/2014)
I figured that since he was new, I would be nice and show him how to set up the problem so that people could help...
And much appreciated by anybody who wants to help. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 13, 2014 at 9:51 am
Hi guys, thanks for all those kind words.
I suppose I am not articulating what the problem is, so here goes again.
I have a table with PersNo, WeekEnding, WorkedHours and ContractHours.
I want to group by PersNo and Between the first (max Weekending date and Weekeding date less 23 dates.
The query/view should return 1 row for each 4 weeks of summed data for each Staff member.
How many rows are in the starting table for each client is variable, there may be 52 rows from some staff, so there shoudl be 13 row in the outout view, there may be a lot less rows for some staff and possible weeks missing, so it need to handle that too!
Does that make it any clearer
June 13, 2014 at 9:53 am
Just for clarity its not lunar month, its a four week month...
June 13, 2014 at 9:55 am
Again...using the sample data from pietlinden what is the expected output? Not a written explanation but the actual values as you want to see them.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 13, 2014 at 10:00 am
I think it should be something like
SELECT PersNo, 4WeekEnd, TotalWorkedHours, TotalContractHours
From
WITH TblWorkedHours @WeekEnd
(Select
PersNo,
Max(WeekEnding) As 4 WeekEnd,
Sum(WorkedHours) As TotalWOrkedHours,
Sum(ContractHours) As TotalContractHours,
From TblWorkedHours
Where WeekEnding Between @Weekend and @WeekEnd-23days
Group By PersNo, WorkedHours
)Where PersNo = @PersNo
But I need to iterate over the complete table for each 4 week period !
June 13, 2014 at 10:05 am
Tallboy (6/13/2014)
I think it should be something likeSELECT PersNo, 4WeekEnd, TotalWorkedHours, TotalContractHours
From
WITH TblWorkedHours @WeekEnd
(Select
PersNo,
Max(WeekEnding) As 4 WeekEnd,
Sum(WorkedHours) As TotalWOrkedHours,
Sum(ContractHours) As TotalContractHours,
From TblWorkedHours
Where WeekEnding Between @Weekend and @WeekEnd-23days
Group By PersNo, WorkedHours
)Where PersNo = @PersNo
But I need to iterate over the complete table for each 4 week period !
Obviously you need help with the sql or you wouldn't have posted. I will try one last time. Don't post sql, don't post a text explanation. Post the ACTUAL data you want as output from the sample data posted by pietlinden.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 13, 2014 at 1:02 pm
output should be...
Output for each employee would be 1 row for each 4 week period as follows;
PersonnelNo, 4WeekEnd, TotalWorkedHours, TotalContractHours
1234, 1/10/14, 42.75, 82.00
1234, 2/9/14, 34.50, 82.00
4343, 1/10/14, 34.00, 74.00
June 13, 2014 at 1:22 pm
Tallboy (6/13/2014)
output should be...Output for each employee would be 1 row for each 4 week period as follows;
PersonnelNo, 4WeekEnd, TotalWorkedHours, TotalContractHours
1234, 1/10/14, 42.75, 82.00
1234, 2/9/14, 34.50, 82.00
4343, 1/10/14, 34.00, 74.00
This is not the same data that pietlinden posted. I have no interest in trying to chase this around. You are obviously still basing on your sample data. Notice that your sample data doesn't have a personnelno of 4343 yet it is in your output. I am pretty darn good at sql but my mind reading abilities are terrible. I really do want to help you but you have got to put in the effort to make your question clear.
Let's start again by me reposting the sample data assembled from your posts.
if OBJECT_ID('tempdb..#WorkHours') is not null
drop table #WorkHours
CREATE TABLE #WorkHours (
ID int
, PersonnelNo int
, WeekEnding date
, WorkedHours decimal(4,2)
, ContractHours decimal(4,2)
);
INSERT INTO #WorkHours (ID, PersonnelNo, WeekEnding, WorkedHours, ContractHours)
(SELECT 4, 1234, '10/1/14', 10.50, 20.00
UNION ALL
SELECT 3, 1234, '9/23/14', 11.00, 20.00
UNION ALL
SELECT 2, 1234, '9/16/14', 10.75, 22.00
UNION ALL
SELECT 1, 1234, '9/9/14', 10.50, 22.00
UNION ALL
SELECT 0, 1234, '9/2/14', 11.25, 12.00
UNION ALL
SELECT 5, 1111, '10/1/14', 10.50, 23.00
);
select *
from #WorkHours
order by PersonnelNo, WeekEnding
With the above as our data what should be the output?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply