Query help - 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

General SQL Server Forums

New to SQL Server Programming

Query help

Author

Topic

kodumudisadha
Starting Member

33 Posts

Posted - 2009-01-22 : 09:52:43

Hi
in below query iwant to retrive data based on amount range like
1 to 500,500 to 1000....9500 to 10000....
instead of running for every 500 i want run a single time with range.

select StoreId,count(cm_number),sum(cm_amt) from POSSALESTRNHEADER
where cm_amt between 1500.01 and 2000
and cm_date between '01-dec-07' and '31-dec-07'
group by StoreId

please anybody help

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-22 : 10:13:07

Can you post some sample data and expected output.

kodumudisadha
Starting Member

33 Posts

Posted - 2009-01-22 : 10:19:55

now i am running same query with changing range manually(cm_amt between o and 500 ,500 to 1000)
storeid TIC Inv Cnt TIC Inv Amt
1 1283 323587.3473
4 9044 2380873.073
5 11039 2517606.49
8 9478 2414176.261
10 7699 1921380.518
12 7913 2097996.724
expected output
--------------
Range storeid TIC Inv Cnt TIC Inv Amt
0 to 500 1 1283 323587.3473
0 to 500 4 9044 2380873.073
0 to 500 5 11039 2517606.49
0 to 500 8 9478 2414176.261
0 to 500 10 7699 1921380.518
0 to 500 12 7913 2097996.724
500 to 1000 1 1283 323587.3473
500 to 1000 4 9044 2380873.073
500 to 1000 5 11039 2517606.49
500 to 1000 8 9478 2414176.261
500 to 1000 10 7699 1921380.518
500 to 1000 12 7913 2097996.724
-----
9500 to 10000

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-01-22 : 10:27:42

Create another table (table variable or temp table) for storing range boundaries and then join your query with the range table.

declare @t table
(
from int,
to int)

insert @t
select 0, 500 union all
select 500, 1000 union all
select 1000, 1500 union all
....

select
t2.from, t2.to, t1.StoreId, t1.cm_number, t1.cm_amt
from
(
select 
 StoreId,count(cm_number) cm_number,sum(cm_amt) cm_amt
from POSSALESTRNHEADER
where cm_date between '20071201' and '20071231'
group by StoreId
) t1
join @t t2
on t1.cm_amt between t2.from and t2.to

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-22 : 10:36:07

Or, You could use something like this and avoid creating another table.

select 
	convert(varchar(10),floor(sum(cm_amt)/500.0)*500)+' TO '+convert(varchar(10),ceiling(sum(cm_amt)/500.0)*500),
	StoreId,
	count(cm_number),
	sum(cm_amt) from POSSALESTRNHEADER
where 
	cm_amt between 1500.01 and 2000 
	and cm_date between '01-dec-07' and '31-dec-07'
group by 
	StoreId,
	convert(varchar(10),floor(a/500.0)*500)+' TO '+convert(varchar(10),ceiling(a/500.0)*500)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 10:42:22

or simply add a computed column


select 
 StoreId,count(cm_number) cm_number,sum(cm_amt) cm_amt,
case when sum(cm_amt) BETWEEN 0 and 500 then '0 to 500'
when sum(cm_amt) BETWEEN 500 and 1000 then '500 to 1000'
when sum(cm_amt) BETWEEN 1000 and 1500 then '1000 to 1500'
...
end as range
from POSSALESTRNHEADER
where cm_date between '20071201' and '20071231'
group by StoreId

kodumudisadha
Starting Member

33 Posts

Posted - 2009-01-24 : 02:43:56

hi in my case i want to check cm_amt 0 to 500 after sum for that records ,i tried without sum
select
StoreId,count(cm_number) cm_number,sum(cm_amt) cm_amt,
case cm_amt BETWEEN 0 and 500 then '0 to 500'
...
end as range
from POSSALESTRNHEADER
where cm_date between '20071201' and '20071231'
group by StoreId

its showing cm_amount not in group function error

group by StoreId,cm_amt

its grouping based on each cm_amt
how to solve this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-24 : 08:40:06

didnt understand that. you mean to check if how many cm_amt is in range 0 to 500? then just use


select 
StoreId,count(cm_number) cm_number,sum(case when cm_amt between 0 and 500 then 1 else 0 end) as noin1strange,
sum(case when cm_amt between 500 and 1000 then 1 else 0 end) as noin2ndrange
...
end as range
from POSSALESTRNHEADER
where cm_date between '20071201' and '20071231'
group by StoreId

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources