quote:

---

Originally posted by JJ297

This query returns six weeks that's selected from StartDate.

I realized I need another field from a different table so I joined a table to get regionname out of that table. When I added that to this query I'm getting 444 rows instead of just six rows that I want. What I've added is in bold. What am I missing?

@StartDate datetime,
@Region char (1)


As
SET NOCOUNT ON
Select
Case
when sort = 'x' then 'Area ' + right(reg,1)
else reg
end AS reg1
,
Sort, Reg, Region, n.regionname, Convert(varchar, WKDate,1) as WKDate, co, cr,
[Perc Received] = Case
when co = 0 then '0'
when cr = 0 then '0'
else convert (varchar,(cr * 100/co * 100)/100) end + '%',

Ans,
[Perc Ans] = Case
when cr = 0 then '0'
when Ans = 0 then '0'
else convert(varchar,(ans * 100/cr * 100)/100) end + '%',

OverFlow,
[Perc Overflow] = Case
when cr = 0 then '0'
when overflow = 0 then '0'
else convert(varchar,(overflow * 100/cr * 100)/100) end + '%',

Aband,
[Perc Aband] = Case
when cr = 0 then '0'
when Aband = 0 then '0'
else convert(varchar,(Aband * 100/cr * 100)/100) end + '%',

Busy,
[Perc Busy] = Case
when co = 0 then '0'
when Busy = 0 then '0'
else convert(varchar,(Busy * 100/co * 100)/100) end + '%'

from [dbo].[TSRPTotals] as t
inner join (select distinct region_ltr,regionname from natdocfile) n on t.region = n.region_ltr
where(sort = @Region) and (WKDate > DATEADD(wk, -6, @StartDate) AND WKDate <=@StartDate)
order by sort, reg, Region, WKDate desc

---