Count - 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 2000 Forums

Transact-SQL (2000)

Count

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-18 : 09:39:35

Mala writes "Hi,

In a table i have to derive three values for the speificed date range alongwith date.

ex. i have a table as below

date status trans
2002-06-15 'NEW' ' '
2002-06-15 'NEW' 'C '
2002-06-14 'OLD' ' '
2002-06-14 ' ' 'C '..
2002-06-14 'NEW' ' '
2002-06-14 'NEW' ' '

Something like this.

Now for the speficied range, i have to give count of "NEW","OLD" and "CANCEL"

So my output should be as below:
date new old cancel
2002-06-15 1 0 1
2002-06-14 2 1 1

For this i am creating temp table :

create table #tmp_dt
(date varchar(10),
new int,
old int,
cancel int)

Then inserting values as

insert into #tmp_dt
select distint date,count(status),0,0 from my_table where date between '2002-06-14' and '2002-06-15'
and status='NEW' and trans= ' '
group by date

insert into #tmp_dt
select distint date,0,count(status),0 from my_table where date between '2002-06-14' and '2002-06-15'
and status='OLD' and trans= ' '
group by date

insert into #tmp_dt
select distint date,0,0,count(status) from my_table where date between '2002-06-14' and '2002-06-15'
and status=' ' and trans= ' C'
group by date

All thes things will be done with dynamic SQL as many of the parameters will passed dynamically.

So to produce the report I will select as below:

select date,sum(new) as new,sum(old) as old,sum(cancel) as cancel
from #tmp_dt
group by date

Which this i am getting desired result. But is there any other way i can simplify this , as our database is very huge and it may give time out error from browser window.

Thanks in advance.

Bye,
Mala"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-18 : 09:46:01

Generally, when posting to message boards, rather than giving use prose about your table desing, you should just provide the CREATE TABLE statements that are relevant to your problem so that someone who wants to help can just cut & paste rather than have to translate your personal psuedo-code into SQL....


select
    date,
    sum(case trans when 'new' then 1 else o end) as new,
    sum(case trans when 'old' then 1 else 0 end) as old,
    sum(case trans when 'cancel' then 1 else 0 end) as cancel
from
    mala
group by
    date

I beleive that will work...

<O>

Mala
Starting Member

26 Posts

Posted - 2002-06-23 : 12:45:42

Hi,

This may not work as i have to check different columns also when I sum

For example for New I have to check two columns as status='NEW' and cancel=' '
for another option I have to check status in ('OLD','REN') and cancel=' '
again for cancel i have to refer only cancel column for values 'C'

If possible all these i have to do it in single statement [right now i am using three statments] , as data is very huge and i may get time out errror from ASP when this stored procedure is called. If possible i want to avoid even self join also.

Thanks

Bye,
Mala

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources