count question

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,> 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|||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 whe
re
> 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 tha
t
> 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,|||you da man. thanks.
"Alejandro Mesa" wrote:
> Try not filtering in the where clause, and instead use a case expression t
o
> 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:
>|||seem to give enough info for the above responder to answer the question
"Aaron Bertrand [SQL Server MVP]" wrote:

> 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
>
>|||> 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?