"Aaron Bertrand [SQL Server MVP]" wrote:

> > This gives me two columns, but I also need a third column with a count
> > that
> > meets this criteria
>
> How about giving us proper DDL and some sample data that we can correlate to
> the desired results, instead of a word problem?
> http://www.aspfaq.com/5006
>
>
>

"Aaron Bertrand [SQL Server MVP]" wrote:

> > seem to give enough info for the above responder to answer the question
>
> Great, congratulations!  Alejandro is more willing than the rest of us to
> make guesses and potentially do a bunch of work for nothing.  Do you think
> http://www.aspfaq.com/5006 was written just so we can be bullies?  Or do you
> not comprehend the point of it all?
>
>
>

"cheilig" wrote:

> have a table (A)  that contains fields:
> [OPENDATE] [datetime] NULL
> [CLSDDATE] [datetime] NULL
> [DUEDATE] [datetime] NULL
> [PRIORITY] [varchar] (30
> [USERID] [int] NULL
>
> and table (B) that contains fields:
> [USERID] [int] NOT NULL
> [DEPT_NUM] [varchar] (30)
> [DEPT] [varchar] (30)
>
> -- the variables ae declared and set
>
> select B.dept_num, count(*) from A inner join B on B.userid = A.userid where
> A.opendate between @startdate and @enddate
> and A.clsddate > A.duedate and B.dept = @dept group by B.dept_num order by
> B.dept_num
>
> This gives me two columns, but I also need a third column with a count that
> meets this criteria
>
>  where As.opendate between @startdate and @enddate
> and B.dept = @dept
>
> Wanting result to be:
> dept A    23     47
> dept B    44     89
> deptcC   28     17
> etc...
> thanks,

"Alejandro Mesa" wrote:

> Try not filtering in the where clause, and instead use a case expression to
> calculate both columns.
>
> select
>    B.dept_num,
>    sum(
>    case when A.opendate between @startdate and @enddate
>    and A.clsddate > A.duedate and B.dept = @dept then 1 else 0 end
>    ) as c1,
>    sum(
>    case when As.opendate between @startdate and @enddate
>    and B.dept = @dept then 1 else 0 end
>    ) as c2
> from
>    A inner join B on B.userid = A.userid
> group by
>    B.dept_num
> order by
>    B.dept_num
> go
>
>
> AMB
>
> "cheilig" wrote:
>
> > have a table (A)  that contains fields:
> > [OPENDATE] [datetime] NULL
> > [CLSDDATE] [datetime] NULL
> > [DUEDATE] [datetime] NULL
> > [PRIORITY] [varchar] (30
> > [USERID] [int] NULL
> >
> > and table (B) that contains fields:
> > [USERID] [int] NOT NULL
> > [DEPT_NUM] [varchar] (30)
> > [DEPT] [varchar] (30)
> >
> > -- the variables ae declared and set
> >
> > select B.dept_num, count(*) from A inner join B on B.userid = A.userid where
> > A.opendate between @startdate and @enddate
> > and A.clsddate > A.duedate and B.dept = @dept group by B.dept_num order by
> > B.dept_num
> >
> > This gives me two columns, but I also need a third column with a count that
> > meets this criteria
> >
> >  where As.opendate between @startdate and @enddate
> > and B.dept = @dept
> >
> > Wanting result to be:
> > dept A    23     47
> > dept B    44     89
> > deptcC   28     17
> > etc...
> > thanks,