October 5, 2009 at 1:37 pm
Good afternoon all,
I need to return daily values. For instance, for the week of 9/14/2009
Monday 3
Tuesday 19
And so on and so on.
The farthest I get is counting the values but returning one day.
October 5, 2009 at 1:41 pm
Could you clarify what you are asking and please provide a couple more examples. I am really unclear what you are looking for..
CEWII
October 5, 2009 at 1:56 pm
Elliot,
I got this to work but I need to add shift so that I can tally by "shift per day".
select datename(dw,record_date), count(defect_id) pinhole
from tbl_assembly_production
join tbl_assembly_holds on tbl_assembly_production.workorder = tbl_assembly_holds.workorder
join tlkp_item on tbl_assembly_production.item = tlkp_item.item
JOIN tbl_Assembly_hold_defects on tbl_assembly_holds.tagnumber = tbl_assembly_hold_defects.tagnumber
WHERE tbl_Assembly_hold_defects.defect_id = 19 and tbl_assembly_holds.record_date between '9/13/2009' and '9/30/2009'
group by datename(dw,record_date)
order by datename(dw,record_date)
Thanks.
October 5, 2009 at 2:05 pm
Ok,
I added "shift" but I know this is wrong. I would like for it to tally the "shift" totals. For instance,
Day of Week Shift 1 Shift 2 Shift 3
Monday 26 5 15
Tuesday 0 19 44
This is what I did:
select datename(dw,record_date), count(defect_id) pinhole,
(case when shift = 1 then 1 else 0 end) Shift_1,
(case when shift = 2 then 2 else 0 end) Shift_2,
(case when shift = 3 then 3 else 0 end) Shift_3
from tbl_assembly_production
join tbl_assembly_holds on tbl_assembly_production.workorder = tbl_assembly_holds.workorder
join tlkp_item on tbl_assembly_production.item = tlkp_item.item
JOIN tbl_Assembly_hold_defects on tbl_assembly_holds.tagnumber = tbl_assembly_hold_defects.tagnumber
WHERE tbl_Assembly_hold_defects.defect_id = 19 and tbl_assembly_holds.record_date between '9/13/2009' and '9/20/2009'
group by datename(dw,record_date), shift
order by datename(dw,record_date), shift
October 5, 2009 at 2:13 pm
Finally,
I believe this is it:
select datename(dw,record_date),
sum(case when shift = 1 then 1 else 0 end) Shift_1,
sum(case when shift = 2 then 1 else 0 end) Shift_2,
sum(case when shift = 3 then 1 else 0 end) Shift_3
from tbl_assembly_production
join tbl_assembly_holds on tbl_assembly_production.workorder = tbl_assembly_holds.workorder
join tlkp_item on tbl_assembly_production.item = tlkp_item.item
JOIN tbl_Assembly_hold_defects on tbl_assembly_holds.tagnumber = tbl_assembly_hold_defects.tagnumber
WHERE tbl_Assembly_hold_defects.defect_id = 19 and tbl_assembly_holds.record_date between '9/14/2009' and '9/18/2009'
group by datename(dw,record_date)
October 5, 2009 at 3:24 pm
select datename(dw,record_date),
sum(case when shift = 1 then 1 else 0 end) Shift_1,
sum(case when shift = 2 then 1 else 0 end) Shift_2,
sum(case when shift = 3 then 1 else 0 end) Shift_3
<snip...>
group by datename(dw,record_date)
You'll need to also GROUP BY record_date itself (or it's week number in the year or other representation of it), otherwise you're just going to return all shifts that are split out by day of the week for all dates in your FROM/WHERE clause. I think what you are looking for as a result is:
Week of 09.14.2009:
Day Shift_1 Shift_2 Shift_3
M 12 14 8
T 9 17 4
...
Week of 09.21.2009:
M 13 11 17
T 11 3 16
...
Whereas your query (strictly grouping by the date part) will give the following, given my made-up numbers:
M 25 25 25
T 20 20 20
...
Am I on the right track for what you're after?
MJM
EDIT: I see now that you are restricting your recordset in your WHERE clause, so my post may not be relevant. If your business wants a breakout by day by week for an entire month in one report though...:-D
October 6, 2009 at 10:17 am
MJM,
I am going to try your code but, I found something funky. The dates run through 9/21/2009 to 10/5/2009 in this case. My problem is, SQL is not returning data for 10-1 and 10-2, I can verify data exists for those dates through SSMS.
select record_date,
sum(case when shift = 1 then 1 else 0 end) Shift_1,
sum(case when shift = 2 then 1 else 0 end) Shift_2,
sum(case when shift = 3 then 1 else 0 end) Shift_3
from tbl_assembly_production
join tbl_assembly_holds on tbl_assembly_production.workorder = tbl_assembly_holds.workorder
join tlkp_item on tbl_assembly_production.item = tlkp_item.item
JOIN tbl_Assembly_hold_defects on tbl_assembly_holds.tagnumber = tbl_assembly_hold_defects.tagnumber
WHERE tbl_Assembly_hold_defects.defect_id = 19 and tbl_assembly_holds.record_date Between '9/21/2009' and '10/5/2009'
group by record_date
October 6, 2009 at 11:51 am
Good afternoon all,
I got the error fixed and data is returning correctly.
Thanks for the help,
DJ Khalif 😀
October 17, 2009 at 8:59 pm
kabaari (10/6/2009)
Good afternoon all,I got the error fixed and data is returning correctly.
Thanks for the help,
DJ Khalif 😀
Any chance of seeing your final code?
--Jeff Moden
October 19, 2009 at 7:11 am
This is the code that got the results I wanted.
select tbl_Assembly_holds.record_date,
sum(case when shift = 1 then 1 else 0 end) Shift_1,
sum(case when shift = 2 then 1 else 0 end) Shift_2,
sum(case when shift = 3 then 1 else 0 end) Shift_3
from tbl_assembly_production
join tbl_assembly_holds on tbl_assembly_production.workorder = tbl_assembly_holds.workorder
join tlkp_item on tbl_assembly_production.item = tlkp_item.item
JOIN tbl_Assembly_hold_defects on tbl_assembly_holds.tagnumber = tbl_assembly_hold_defects.tagnumber
WHERE tbl_Assembly_hold_defects.defect_id = #FORM.defects# and tbl_Assembly_holds.Record_Date BETWEEN '#FORM.dateFrom#' AND '#FORM.dateTo#'
group by tbl_Assembly_holds.record_date
October 21, 2009 at 6:46 pm
Thanks! 🙂
--Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply