"JDP@Work" <JPGMTNoSpam@sbcglobal.net> wrote in message
news:O1SdvrUkDHA.2000@TK2MSFTNGP12.phx.gbl...
> I want a single row per sales rep with counts
>
> How do I make a multi conditional case with a single row result set. ?
>
> select c1.key4 [Sales Rep]
> ,c1.key3 [Market Center]
>
> -- this errors: line n bad syntax near 'not'
> ,count(case c1.accID when not null then 1 end) as [Goldmine Record Count]
>
> -- this errors: line n bad syntax near '>'
> ,count(case c2.accID when (c2.urental > '') and (isnull(c2.uwp 0) > 0)
> and (isnull(c2.ucxd,'01/01/1951') > getdate())
> and (isnull(c2.uawr,0) > 25) then 1 end) as [Qualified Rental Count]
>
> from Central..contact1 c1
> join contact2 c2 on c1.accID = c2.accID
> where rtrim(c1.key4) = "Aaron Apple"
> group by c1.key3, c1.key4 ,c1.accID
>
>

wrote in message news:uOJFmaVkDHA.4008@TK2MSFTNGP11.phx.gbl...
> Not sure about the logic, but syntactically I guess that your first
example should be:
>
> ,count(case WHEN c1.accID IS not null then 1 end) as [Goldmine Record
Count]
>
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>
> "JDP@Work" <JPGMTNoSpam@sbcglobal.net> wrote in message
> news:O1SdvrUkDHA.2000@TK2MSFTNGP12.phx.gbl...
> > I want a single row per sales rep with counts
> >
> > How do I make a multi conditional case with a single row result set. ?
> >
> > select c1.key4 [Sales Rep]
> > ,c1.key3 [Market Center]
> >
> > -- this errors: line n bad syntax near 'not'
> > ,count(case c1.accID when not null then 1 end) as [Goldmine Record
Count]
> >
> > -- this errors: line n bad syntax near '>'
> > ,count(case c2.accID when (c2.urental > '') and (isnull(c2.uwp 0) > 0)
> > and (isnull(c2.ucxd,'01/01/1951') > getdate())
> > and (isnull(c2.uawr,0) > 25) then 1 end) as [Qualified Rental Count]
> >
> > from Central..contact1 c1
> > join contact2 c2 on c1.accID = c2.accID
> > where rtrim(c1.key4) = "Aaron Apple"
> > group by c1.key3, c1.key4 ,c1.accID
> >
> >
>
>