February 18, 2006 at 7:17 pm
We log data in our store based on datetime. We have three shifts that the workers can work (first, second, third).
Shift 1 (S1) = 7am - 2:59pm
Shift 2 (S2) = 3pm - 10:59pm
Shift 3 (S3) = 11:pm - 6:59am
02/10/06 6:44pm $15 S2
02/10/06 11:23pm $25 S3
02/11/06 2:23am $45 S3
02/11/06 7:38am $10 S1
I want to see the total transactions for 02/10/06 and I need it to include ALL of the third shift data, which happens to run over to the next calendar day until 6:59am. So, the total for 02/10/06 would be $85
How do I group my data, or perform my sum's/totals per day, when the my days are not based on a calendar day?
Thanks!
February 18, 2006 at 10:12 pm
Something like...
declare @shift_factor table
(
shift varchar(2) unique,
count_to_day int
)
insert @shift_factor
select 's1',0
union
select 's2',0
union
select 's3',-1
select * from @shift_factor
print datepart(hour,getdate())
declare @shift_data table
(
some_date datetime,
some_money money,
shift varchar(2)
)
insert @shift_data
select'02/10/06 6:44pm',15,'S2'
union all
select '02/10/06 11:23pm',25,'S3'
union all
select '02/11/06 2:23am',45,'S3'
union all
select '02/11/06 7:38am',10,'S1'
select convert(varchar(12),dateadd(hour,count_to_day*8.00,some_date)),sum(some_money) [money]
from @shift_data d
join @shift_factor s
on d.shift = s.shift
group by convert(varchar(12),dateadd(hour,count_to_day*8.00,some_date))
HTH
Mathew J Kulangara
sqladventures.blogspot.com
February 19, 2006 at 12:28 am
Or if you need to retrieve all dates.
select shift_date, sum(trans_amt)
from
(
select case when trans_dt between dateadd(hour, 7, dateadd(day, 0, datediff(day, 0, trans_dt)))
and dateadd(hour, 7, dateadd(day, 1, datediff(day, 0, trans_dt))) then
dateadd(day, 0, datediff(day, 0, trans_dt))
else
dateadd(day, -1, datediff(day, 0, trans_dt))
end as shift_date,
*
from yourtable
) as a
group by shift_date
February 19, 2006 at 2:44 am
Add computed column "ShiftNo" to you column and calculate shift number from time recorded.
Then you can apply
GROUP BY ShiftNo.
_____________
Code for TallyGenerator
February 19, 2006 at 12:20 pm
Two functions....
Use them in your select and group statements. You can hard code the shifts or make a table for shift, start hour, end hour. I'd put an effective date, so if shifts change you can track past shifts.
DECLARE FUNCTION fn_Shift (@sDTTM smalldatetime)
RETURNS tinyint
AS
BEGIN
RETURN
(
SELECT CASE
WHEN DATEPART(hh,@sDTTM) BETWEEN 7 AND 14
THEN 1
WHEN DATEPART(hh,@sDTTM) BETWEEN 15 AND 22
THEN 2
ELSE 3
END AS shift
)
END
DECLARE FUNCTION fn_SimpleDate (@sDTTM smalldatetime)
RETURNS tinyint
AS
BEGIN
RETURN
(
SELECT CAST(DATEPART(yyyy,sDTTM) + DATEPART(mm,sDTTM) + DATEPART(dd,sDTTM) AS smalldatetime) simpleDate
)
END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply