Return Daily Values

Question

Return Daily Values

kabaari

SSCrazy

Points: 2235
More actions
October 5, 2009 at 1:37 pm

#226854

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.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Could you clarify what you are asking and please provide a couple more examples. I am really unclear what you are looking for..

CEWII

kabaari SSCrazy Points: 2235 More actions · Answer 2

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.

kabaari SSCrazy Points: 2235 More actions · Answer 3

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

kabaari SSCrazy Points: 2235 More actions · Answer 4

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)

Mark Marinovic SSCrazy Points: 2472 More actions · Answer 5

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

kabaari SSCrazy Points: 2235 More actions · Answer 6

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

kabaari SSCrazy Points: 2235 More actions · Answer 7

Good afternoon all,

I got the error fixed and data is returning correctly.

Thanks for the help,

DJ Khalif 😀

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kabaari SSCrazy Points: 2235 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 10

Thanks! 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)