Tricky little problem - 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

SQL Server Development (2000)

Tricky little problem

Author

Topic

OMB
Yak Posting Veteran

88 Posts

Posted - 2002-05-23 : 11:49:55

I am trying to create a view on the following table:


LimitID ID      Date         Quantity price  DealType

7	1VENT	2002-05-17 	5	2	1	
8	1VENT	2002-05-16 	200	5	2		
9	1VENT	2002-05-15 	200	5	1		
14	1VENT	2002-05-21 	5500	18	1		
15	ADNAM	2002-05-22 	1000	20	2		
17	ADNAM	2002-05-22      1000	20	2		
18	1VENT	2002-05-15 	5000	5	1

Basically I want a view showing the following information


ID       Date          Quantity     Price     Number
1VENT    2002-05-15     5200          5         2
ADNAM    2002-05-22     2000          20        1
1VENT	 2002-05-21 	5500	      18	1

and so on.....

The quantity is the sum of quntities where the price and date are equal for a given ID.

Number is the number (count) of records that have the same ID,price,date at a given price and ID.

I hope this makes sense, i am having real problems with this and would appreciate any help. remeber this is going to be a view so the normal restrictions apply.

Thank You

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-23 : 12:05:15

create table #Deals
(LimitID int,
ID1 char(5),
Date1 datetime,
Quantity int,
price int,
DealType int)

insert #Deals values(7,'1VENT','2002-05-17', 5, 2, 1)
insert #Deals values(8, '1VENT','2002-05-16', 200, 5, 2)
insert #Deals values(9, '1VENT' ,'2002-05-15', 200, 5, 1)
insert #Deals values(14, '1VENT' ,'2002-05-21', 5500, 18, 1)
insert #Deals values(15, 'ADNAM' ,'2002-05-22', 1000, 20, 2)
insert #Deals values(17, 'ADNAM' ,'2002-05-22', 1000, 20, 2)
insert #Deals values(18,'1VENT' ,'2002-05-15', 5000, 5, 1)

select ID1, Date1, sum(Quantity) as SumOfQuant , count(*) as Number
from #Deals
group by ID1, Date1 , price

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources