February 22, 2012 at 7:58 am
Hi,
I'm creating a stored procedure that reports on the number of times employees clock in and out of work per day (breaks and lunches are part of this). The final result for a particular employee group with the way the query is currently set up would look something like this:
Group--------Employee---ClockedIn-----ClockedOut----Date
Deli----------Smith-------3-------------3-------------2012-07-28
Deli----------Smith-------3-------------3-------------2012-07-30
Bakery-------Brown-------3-------------3-------------2012-07-28
Bakery-------Brown-------2-------------3-------------2012-07-29
There will be days when an employee doesn't clock in or clock out (because the employee is not working that day). When I group by the group, employee and date, I still need the recordset to show a record for such days. So the recordset would look like this:
Group--------Employee---ClockedIn-----ClockedOut----Date
Deli----------Smith-------3-------------3-------------2012-07-28
Deli----------Smith-------0-------------0-------------2012-07-29
Deli----------Smith-------3-------------3-------------2012-07-30
Bakery-------Brown-------3-------------3-------------2012-07-28
Bakery-------Brown-------2-------------3-------------2012-07-29
Deli----------Brown-------0-------------0-------------2012-07-30
I've been using a subquery with unions bringing together a query that gets the employees, a query that gets the clockedIn, and a query that gets the ClockedOut. Then the outer query groups this based on the group, employee and date. That produces the first data example I showed you.
My plan was to write another query to union to the others inside the subquery that would just grab each date within my parameter date range regardless of whether or not any data exists for that date. Any ideas on how I might achieve this?
Thank you
February 22, 2012 at 8:02 am
Please post DDL code, a sample data INSERT statement, and what code you're already using to achieve your current solution. That will help us test any advice we give you.
Also, why can't you just insert a placeholder row for every day and just update the times when employees actually clock in?
February 22, 2012 at 11:02 am
Brandie,
Can you explain what you mean by this?
Also, why can't you just insert a placeholder row for every day and just update the times when employees actually clock in?
I would post my stored procedure, but it's very long and complicated, and it makes use of other stored procedures and tables. There is no updating or inserting, it's just a SELECT query.
I'm just wondering if anyone has any ideas on how one might bring in date placeholders when those dates don't contain any data (and thus, don't exist) in the table being queried. It doesn't have to be a query with a UNION to tie it in, just something that will allow me to show records with empty data for dates where nothing happened for a particular employee.
Thanks.
February 22, 2012 at 11:06 am
use a CTE or table that has a column of dates you want to include and then left join it to your select statement including the new column of "possible" dates in the select list. The date will appear in the results with NULL for the empty columns.
February 22, 2012 at 11:20 am
tarr94 (2/22/2012)
Brandie,Can you explain what you mean by this?
Also, why can't you just insert a placeholder row for every day and just update the times when employees actually clock in?
I would cheat. At the start of every day, I would enter a placeholder row for every employee, regardless of whether they come in or not.
Then, as they come in, I would count the number of clock ins and clock outs (which is what it looks like your result set returns with), and update that row for that employee on that date with a +1 for each clock in.
I'm just wondering if anyone has any ideas on how one might bring in date placeholders when those dates don't contain any data (and thus, don't exist) in the table being queried. It doesn't have to be a query with a UNION to tie it in, just something that will allow me to show records with empty data for dates where nothing happened for a particular employee.
Beside the CTE method, you can create a permanent Date table that has all dates to the end of time and start from that table and LEFT OUTER JOIN it to your query. Which prevents the additional CTE processing every time the query runs because it's pulling directly for an existing permanent (and hopefully non-heap, statistics enabled) table.
February 22, 2012 at 11:23 am
tarr94 (2/22/2012)
Brandie,Can you explain what you mean by this?
Also, why can't you just insert a placeholder row for every day and just update the times when employees actually clock in?
I would post my stored procedure, but it's very long and complicated, and it makes use of other stored procedures and tables. There is no updating or inserting, it's just a SELECT query.
I'm just wondering if anyone has any ideas on how one might bring in date placeholders when those dates don't contain any data (and thus, don't exist) in the table being queried. It doesn't have to be a query with a UNION to tie it in, just something that will allow me to show records with empty data for dates where nothing happened for a particular employee.
Thanks.
Take a look at Jeff Moden's articles on Tally tables.
Try something like this:
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), --10E1 (10)
E2(N) AS (SELECT 1 FROM E1 i1 CROSS JOIN E1 i2), --10E2 (100)
E4(N) AS (SELECT 1 FROM E2 i1 CROSS JOIN E2 i2), --10E4 (10,000)
Numbers AS (SELECT Number = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E4)
SELECT
DATEADD(dd, n.number, @StartDate)
--OtherColumns HERE
FROM
Numbers n LEFT OUTER JOIN
dbo.YourQueryOrTable yq ON DATEADD(dd, n.number, @StartDate) = yq.DateColumn
WHERE
DATEADD(dd, n.number, @StartDate) < @EndDate
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply