Identify Weeks

[strom99]

I need to create a column that identifies which week the data fell into. For example: The top row is a yield % and showing count by week.

Week 100 99-92 91-85

09-07-2003 - 09-13-2003 13 14 5
09-14-2003 - 09-20-2003 12 10 8
09-21-2003 - 09-27-2003 20 11 4

Here is the SQL I have now, which gives it to me by month, I would like to add the week column as well. Thanks for the help...

create procedure pr_rpt_up_select_mvitoolyieldrpts
as

select
test_date as 'MVI Tool Yield',


sum(case when mvi_tool_yield = 100 then 1 else 0 end) as '100',
sum(case when mvi_tool_yield between 92 and 99 then 1 else 0 end) as '99-92',
sum(case when mvi_tool_yield between 85 and 91 then 1 else 0 end) as '91-85',
sum(case when mvi_tool_yield between 78 and 84 then 1 else 0 end) as '84-78',
sum(case when mvi_tool_yield between 71 and 77 then 1 else 0 end) as '77-71',
sum(case when mvi_tool_yield < 71 then 1 else 0 end) as '71-0'
from
(select
v1.*,
v2.mvi_tool_yield,
v2.estimated_yield as mvi_estimated_yield,
left(convert(varchar,v1.start_create_datetime,120),7) as test_date,
v1.start_create_datetime as startdate
from

--get run and roll nbrs for first jumbos tested per tool
(select
u1.run_nbr,
u1.roll_nbr,
u1.unit_nbr as tool_nbr,
u1.mmm_id_nbr,
u1.start_create_datetime
from

(select
run_nbr,
batch_nbr as roll_nbr,
unit_nbr,
mmm_id_nbr,
start_create_datetime

from
imdb.dbo.t_material) u1

inner join

-- get first manufacture date for tested tool and jumbo
(select
t2.unit_nbr,
min(start_create_datetime) as start_create_datetime

from

(select
run_nbr,
roll_nbr,
mvi_tool_yield,
estimated_yield,
case when estimated_yield is not null then 'tested' end as tested

from
bart_main.dbo.verification_summary

where
estimated_yield is not null) t1

inner join

(select
run_nbr,
batch_nbr as roll_nbr,
unit_nbr,mmm_id_nbr,
start_create_datetime

from
imdb.dbo.t_material ) t2

/*where
mmm_id_nbr = '42001592155' or
mmm_id_nbr = '42001836826' and
len(unit_nbr) = 5) t2
*/

on
t1.run_nbr = t2.run_nbr
and t1.roll_nbr = t2.roll_nbr

group by
unit_nbr) u2


on
u1.unit_nbr = u2.unit_nbr and u1.start_create_datetime = u2.start_create_datetime) v1

inner join

(select
run_nbr,
roll_nbr,
mvi_tool_yield,
estimated_yield

from
bart_main.dbo.verification_summary) v2 on v1.run_nbr = v2.run_nbr and v1.roll_nbr = v2.roll_nbr


where
left(convert(varchar,v1.start_create_datetime,120),7) between '2003-01' and '2003-12') w1


group by
test_date,startdate
GO

 

[rac2]

Overwhelming amount of code there.

The number of the week in a year for a date value such as startdate is provided by DATEPART(week, startdate). This number could be used to GROUP BY.


You could generate a table that associates the dates on Sunday and Saturday with these week numbers for any particular year.

This table could be JOINED to other tables with dates to obtain a range of dates for display given that you have a date or a week-number.