August 16, 2007 at 12:36 pm
Hi, I am trying to write a query that joins 3 tables and produces counts on multiple columns within the three tables. Below is my code and I am getting a syntax error message near the key word 'in'. Can anyone help modify the query so that I can get the counts I need out of the join. I can create a view with the join and then apply the counts query on the view, but I have a web form that allows the user to change the date on the view. So if multiple users are selecting dates they are constantly changing the data in the view so it can create problems if two users hit submit at the same time ( I think ) so I was hoping to create one dataset result per user to display to the web page. Hopefully this makes sense. Thank you in advance.
select
--Toals By Area Code
sum(case when a.NPA = 'metro' then 1 else 0 end) as '416',
sum
(case when NPA = 'West' then 1 else 0 end) as '519',
sum
(case when NPA = '613 Region' then 1 else 0 end) as '613',
sum
(case when NPA in ('705 North', '705 South') then 1 else 0 end) as '705',
sum
(case when NPA in ('South') then 1 else 0 end) as '905',
--Totals By Zones Sector
sum
(case when sector = '416' then 1 else 0 end) as '416 Telco1',
sum
(case when sector = '519 Telco2' then 1 else 0 end) as '519 Telco2',
sum
(case when sector = '519 Telco1' then 1 else 0 end) as '519 Telco1',
sum
(case when sector = '613 Telco1' then 1 else 0 end) as '613 Telco1',
sum
(case when sector = '613 Telco2' then 1 else 0 end) as '613 Telco2',
sum
(case when sector = '705 S Telco1' then 1 else 0 end) as '705 S Telco1',
sum
(case when sector = '705 S Telco2' then 1 else 0 end) as '705 S Telco2',
sum
(case when sector = '705 N Telco2' then 1 else 0 end) as '705 N Telco1',
sum
(case when sector = '905' then 1 else 0 end) as 'Telco1',
--Totals by NPA/Market
sum
(case when market = 'Bus' and sector = '416' then 1 else 0 end) as '416 Bus',
sum
(case when market = 'Res' and sector = '416' then 1 else 0 end) as '416 Res',
sum
(case when market = 'Bus DSL' and sector = '416' then 1 else 0 end) as '416 Bus HS',
sum
(case when market = 'DSL' and sector = '416' then 1 else 0 end) as '416 HS',
sum
(case when market = 'Cable' and sector = '416' then 1 else 0 end) as '416 Cable',
sum
(case when market = 'VDSL' and sector = '416' then 1 else 0 end) as '416 VDSL',
sum
(case when market = 'Bus' and sector = '519 Telco2' then 1 else 0 end) as '519 Telco1 Bus',
sum
(case when market = 'Res' and sector = '519 Telco2' then 1 else 0 end) as '519 Telco1 Res',
sum
(case when market = 'Bus DSL' and sector = '519 Telco2' then 1 else 0 end) as '519 Telco2 Bus HS',
sum
(case when market = 'DSL' and sector = '519 Telco2' then 1 else 0 end) as '519 Telco1 HS',
sum
(case when market = 'Cable' and sector = '519 Telco2' then 1 else 0 end) as '519 Telco1 Cable',
sum
(case when market = 'VDSL' and sector = '519 Telco2' then 1 else 0 end) as '519 Telco1 VDSL',
sum
(case when market = 'Bus' and sector = '519 Telco1' then 1 else 0 end) as '519 Telco2 Bus',
sum
(case when market = 'Res' and sector = '519 Telco1' then 1 else 0 end) as '519 Telco1 Res',
sum
(case when market = 'Bus DSL' and sector = '519 Telco1' then 1 else 0 end) as '519 Telco1 Bus HS',
sum
(case when market = 'DSL' and sector = '519 Telco1' then 1 else 0 end) as '519 Telco1 HS',
sum
(case when market = 'Cable' and sector = '519 Telco1' then 1 else 0 end) as '519 Telco1 Cable',
sum
(case when market = 'VDSL' and sector = '519 Telco1' then 1 else 0 end) as '519 Telco1 VDSL',
sum
(case when market = 'Bus' and sector = '613 Telco1' then 1 else 0 end) as '613 Telco1 Bus',
sum
(case when market = 'Res' and sector = '613 Telco1' then 1 else 0 end) as '613 Telco1 Res',
sum
(case when market = 'Bus DSL' and sector = '613 Telco1' then 1 else 0 end) as '613 Telco1 Bus HS',
sum
(case when market = 'DSL' and sector = '613 Telco1' then 1 else 0 end) as '613 Telco1 HS',
sum
(case when market = 'Cable' and sector = '613 Telco1' then 1 else 0 end) as '613 Telco1 Cable',
sum
(case when market = 'VDSL' and sector = '613 Telco1' then 1 else 0 end) as '613 Telco1 VDSL',
sum
(case when market = 'Bus' and sector = '613 Telco2' then 1 else 0 end) as '613 Telco2 Bus',
sum
(case when market = 'Res' and sector = '613 Telco2' then 1 else 0 end) as '613 Telco2 Res',
sum
(case when market = 'Bus DSL' and sector = '613 Telco2' then 1 else 0 end) as '613 Telco2 Bus HS',
sum
(case when market = 'DSL' and sector = '613 Telco2' then 1 else 0 end) as '613 Telco2 HS',
sum
(case when market = 'Cable' and sector = '613 Telco2' then 1 else 0 end) as '613 Telco2 Cable',
sum
(case when market = 'VDSL' and sector = '613 Telco2' then 1 else 0 end) as '613 Telco2 VDSL',
sum
(case when market = 'Bus' and sector = '705 N Telco2' then 1 else 0 end) as '705 N Telco2 Bus',
sum
(case when market = 'Res' and sector = '705 N Telco2' then 1 else 0 end) as '705 N Telco2 Res',
sum
(case when market = 'Bus DSL' and sector = '705 N Telco2' then 1 else 0 end) as '705 N Telco2 Bus HS',
sum
(case when market = 'DSL' and sector = '705 N Telco2' then 1 else 0 end) as '705 N Telco2 HS',
sum
(case when market = 'Cable' and sector = '705 N Telco2' then 1 else 0 end) as '705 N Telco2 Cable',
sum
(case when market = 'VDSL' and sector = '705 N Telco2' then 1 else 0 end) as '705 N Telco2 VDSL',
sum
(case when market = 'Bus' and sector = '705 S Telco2' then 1 else 0 end) as '705 S Telco2 Bus',
sum
(case when market = 'Res' and sector = '705 S Telco2' then 1 else 0 end) as '705 S Telco2 Res',
sum
(case when market = 'Bus DSL' and sector = '705 S Telco2' then 1 else 0 end) as '705 S Telco2 Bus HS',
sum
(case when market = 'DSL' and sector = '705 S Telco2' then 1 else 0 end) as '705 S Telco2 HS',
sum
(case when market = 'Cable' and sector = '705 S Telco2' then 1 else 0 end) as '705 S Telco2 Cable',
sum
(case when market = 'VDSL' and sector = '705 S Telco2' then 1 else 0 end) as '705 S Telco2 VDSL',
sum
(case when market = 'Bus' and sector = '905' then 1 else 0 end) as '905 Bus',
sum
(case when market = 'Res' and sector = '905' then 1 else 0 end) as '905 Res',
sum
(case when market = 'Bus DSL' and sector = '905' then 1 else 0 end) as '905 Bus HS',
sum
(case when market = 'DSL' and sector = '905' then 1 else 0 end) as '905 HS',
sum
(case when market = 'Cable' and sector = '905' then 1 else 0 end) as '905 Cable',
sum
(case when market = 'VDSL' and sector = '905' then 1 else 0 end) as '905 VDSL'
where
in (
select
a.id, a.npa, a.dispatchareaid, a.jobtypeid,
b
.jobtype, b.market, c.Dispatchareas,
c
.Dispatchregions, c.Sector
from
MCommPLOrders as a
left join jobtypes as b
on a.jobtypeid = b.jobtype COLLATE Latin1_General_CI_AS
left outer join ZONES as c
on a.dispatchareaid = c.dispatchareas COLLATE Latin1_General_CI_AS
where
datepart(dd, getdate()) = datepart(dd, timeofcode)
and
datepart(mm, getdate()) = datepart(mm, TimeOfCode)
and
datepart(yy, getdate()) = datepart(yy, TimeOfCode)
and
a.reasoncode IN ('pl', 'pl2', 'r2', 't2')
)
August 16, 2007 at 1:05 pm
You have no field in between the WHERE and the IN.
where in (
There is also a problem with your subselect you an only have 1 field. Look into pivot transforms. Not sure exactly what you are trying to do, but I think that is what you need.
August 16, 2007 at 1:05 pm
Your subquery would acts like a virtual table. Tables need to be referenced using the FROM clause in this case. You'll need to not use WHERE IN but FROM to reference your subquery.
August 17, 2007 at 7:18 am
Thanks Aaron and Bob,
here was my final query that worked. A Pivot would have been nice, but I am still trying to wrap my head around how to put one together especially with a three table join. Thank you for taking the time to respond.
select
sum
(case when NPA = 'metro' then 1 else 0 end) as '416',
sum
(case when NPA = 'West' then 1 else 0 end) as '519',
sum
(case when NPA = '613 Region' then 1 else 0 end) as '613',
sum
(case when NPA in ('705 North', '705 South') then 1 else 0 end) as '705',
sum
(case when NPA in ('South') then 1 else 0 end) as '905',
......
from
from
(
select distinct(a.id), a.npa, a.dispatchareaid, a.jobtypeid, b.jobtype, b.market, c.Dispatchareas, c.Dispatchregions, c.Sector
from
MCommPLOrders as a
left
join jobtypes as b
on
a.jobtypeid = b.jobtype COLLATE Latin1_General_CI_AS
left
outer join ZONES as c
on
a.dispatchareaid = c.dispatchareas COLLATE Latin1_General_CI_AS
where
datepart(dd, '8/17/2007') = datepart(dd, timeofcode)
and
datepart(mm, '8/17/2007') = datepart(mm, TimeOfCode)
and
datepart(yy, '8/17/2007') = datepart(yy, TimeOfCode)
and
a.reasoncode IN ('pl', 'pl2', 'r2', 't2')) as subquery
August 17, 2007 at 8:50 pm
FYI, pivot (not near SQL Server, to can't test syntax)
However because you are grouping some together as 1 like 705 north and south it won't work as you did it,
but your method works just as well.
select [metro] as [416],[west] as [519],[613 Region] as [613],...
from
(select distinct(a.id), a.npa, a.dispatchareaid, a.jobtypeid, b.jobtype, b.market, c.Dispatchareas, c.Dispatchregions, c.Sector
from MCommPLOrders as a
left join jobtypes as b
on a.jobtypeid = b.jobtype COLLATE Latin1_General_CI_AS
left outer join ZONES as c
on a.dispatchareaid = c.dispatchareas COLLATE Latin1_General_CI_AS
where datepart(dd, '8/17/2007') = datepart(dd, timeofcode)
and datepart(mm, '8/17/2007') = datepart(mm, TimeOfCode)
and datepart(yy, '8/17/2007') = datepart(yy, TimeOfCode)
and a.reasoncode IN ('pl', 'pl2', 'r2', 't2')) as subquery
pivot
count(*)
FOR ([metro],[West],[613 Region],...)
)AS thePivot
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply