count error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

count error

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2008-03-13 : 11:33:43

I have table #allcall
contains two columns
clid and date

For every record I want to find how many time clid appears in previous 30 days.

I use following code

select a.clid, a.date, (select sum(case when datediff(d, x.date, a.date)<=30
then 1 else 0 end) from #allcall as x
where x.clid=a.clid ) as nbr

from #allcall as a

but get error msg:
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

How to fix this problem?
Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-13 : 11:42:29

you are greatly over-complicating what you need to do.

select a.clid, count(*) as nbr
from #allcall
where a.date >= dateadd(d, -30, getdate())
group by a.clid

- Jeff
http://weblogs.sqlteam.com/JeffS

jeff06
Posting Yak Master

166 Posts

Posted - 2008-03-13 : 11:52:15

Thanks but this is not what I want to get.
for exampe
clid date
1 1/1/2008
1 1/3/2008
1 1/28/2008
1 2/4/2008
1 3/8/2008
2-----
2

the result should look like

clid date count
1 1/1/2008 0
1 1/3/2008 1 because only first one is inside 30 days
1 1/28/2008 2 first and second
1 2/4/2008 1 third
1 3/8/2008 0 none

Thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-13 : 12:00:20

[code]select a.clid,
a.date,
sum(case when datediff(day, a.date, getdate()) <= 30 then 1 else 0 end) as nbr
from #allcall as a[/code]

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-13 : 12:00:30

You need to explain better what you need. That doesn't match what you asked for originally:

quote:

For every record I want to find how many time clid appears in previous 30 days.

Do you want:

"For each clid and date, I want to return a count of how many dates exist for that clid within the previous 30 days"

?? that is just a guess -- only you can clearly describe exactly and precisely what you want.

Also, you need to tell us what the primary key of this table is. What happens if there are two rows with the same clid/date ??

- Jeff
http://weblogs.sqlteam.com/JeffS

jeff06
Posting Yak Master

166 Posts

Posted - 2008-03-13 : 12:11:42

Jeff,
The table just have two column and combination of clid and date is unique.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-13 : 12:23:12

Try:-

SELECT a.clid, a.date,t.RecCount
FROM #allcall a
CROSS APPLY (SELECT COUNT(*) As RecCount
             FROM #allcall 
             WHERE datediff(d, date, a.date)<=30 )t

talleyrand
Starting Member

35 Posts

Posted - 2008-03-13 : 12:27:11

[edit]Nm, as I got back to hitting submit the scenario changed [/edit]

jeff06
Posting Yak Master

166 Posts

Posted - 2008-03-13 : 12:55:33

MY own soluton:

select clid, date, sum(p) as nbr
from
(
select a.clid, a.date, x.clid as xclid, x.date as xdate,
case when datediff(d, x.date, a.date)<=30 and datediff(s, x.date, a.date)>0
then 1 else 0 end as p
from #allcall as a, #allcall as x
where a.clid=x.clid
) as b
group by clid, date
order by clid, date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources