troubleshooting Question

subquery???

Avatar of marcus72
marcus72 asked on
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2005
5 Comments3 Solutions264 ViewsLast Modified:
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')

)
ASKER CERTIFIED SOLUTION
lahousden

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros