Wicked select - 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)

Wicked select

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-05-13 : 13:19:20

Is it possible to do a select on this DDL:

  DECLARE @myTable table (
    Price decimal(10, 2),
    Volume decimal(10, 2),
    Active bit,
    UserID int)
  
  INSERT INTO @myTable
  SELECT 2.5, 200, 0, 11 UNION ALL SELECT 3, 150, 1, 11 UNION ALL 
  SELECT 2.5, 100, 0, 12 UNION ALL SELECT 2, 150, 1, 12 UNION ALL 
  SELECT 2.5, 100, 0, 12 UNION ALL SELECT 2, 150, 1, 12 UNION ALL 
  SELECT 1.5, 200, 1, 11 UNION ALL SELECT 2, 150, 0, 11 UNION ALL 
  SELECT 1.5, 200, 1, 11 UNION ALL SELECT 2, 150, 0, 11 UNION ALL 
  SELECT 1.5, 200, 0, 11 UNION ALL SELECT 2, 150, 1, 11 UNION ALL 
  SELECT 1.5, 200, 0, 12 UNION ALL SELECT 3, 150, 1, 12

and get this result:

---------------------------------------------------------------------------
| Price | Volume_active | Active_users | Volume_inactive | Inactive_Users |
---------------------------------------------------------------------------
| 1.50  |           400 |           11 |             400 |          11;12 |
| 2.00  |           450 |        11;12 |             300 |             11 |
| 2.50  |          NULL |         NULL |             400 |          11;12 |
| 3.00  |           300 |        11;12 |            NULL |           NULL |
---------------------------------------------------------------------------

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-13 : 13:21:43

Could you explain the result set?

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-13 : 13:35:27

I can't think of any way to do it. You can get Price, Volume_active, and Volume_inactive really easy. Select this into a temp table with blank columns for Active_users and Inactive_Users. Then loop through the table and build your string of Active_users and Inactive_Users.

If anyone has a brilliant idea to do this in one SELECT, that would be really interesting.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-05-13 : 13:37:06

Of course, here goes:

Price: the distinct price of an item

Volume_active: the sum of all volumes with matching price where active = 1 (there are two volumes of 200 => 400 with price 1.5 and Active = 1)
Active_users: the userid's of those that "own" a volume in Volume_active (one user => 11 has two volumes of 200 each with price 1.5 and active = 1)

Volume_inactive: the sum of all volumes with matching price where active = 0 (there are two volumes of 100 and one of 200 => 400 with price 2.5 and Active = 0)
Inactive_users: the userid's of those that "own" a volume in Volume_inactive (user 12 has 100 + 100, 11 has 200)

Hmm, not sure if this made it any better...let me know if it was unclear.

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-13 : 14:16:59

Here you go...



USE Northwind

CREATE TABLE myTable99 (
    Price decimal(10, 2),
    Volume decimal(10, 2),
    Active bit,
    UserID int)
GO
  
  INSERT INTO myTable99 (Price, Volume, Active, UserID)
  SELECT 2.5, 200, 0, 11 UNION ALL SELECT 3, 150, 1, 11 UNION ALL 
  SELECT 2.5, 100, 0, 12 UNION ALL SELECT 2, 150, 1, 12 UNION ALL 
  SELECT 2.5, 100, 0, 12 UNION ALL SELECT 2, 150, 1, 12 UNION ALL 
  SELECT 1.5, 200, 1, 11 UNION ALL SELECT 2, 150, 0, 11 UNION ALL 
  SELECT 1.5, 200, 1, 11 UNION ALL SELECT 2, 150, 0, 11 UNION ALL 
  SELECT 1.5, 200, 0, 11 UNION ALL SELECT 2, 150, 1, 11 UNION ALL 
  SELECT 1.5, 200, 0, 12 UNION ALL SELECT 3, 150, 1, 12
GO

SET NOCOUNT ON
GO

DECLARE @Price decimal(10, 2), @MIN_Price decimal(10, 2), @List varchar(100)
DECLARE @x TABLE (Price decimal(10, 2), ActiveUsers varchar(100), InActiveUsers varchar(100))

SELECT @List = Null
SELECT @Price = Max(Price), @MIN_Price = MIN(Price) FROM myTable99

WHILE @MIN_Price <= @Price
  BEGIN
	SELECT @List = COALESCE(@List + ', ', '') + CONVERT(varchar(3),UserId)
	  FROM (SELECT DISTINCT UserId FROM myTable99 WHERE Active = 1 AND Price = @Price) AS xxx
	INSERT INTO @x(Price, ActiveUsers) SELECT @Price, @List
	SELECT @List = Null
	SELECT @List = COALESCE(@List + ', ', '') + CONVERT(varchar(3),UserId)
	  FROM (SELECT DISTINCT UserId FROM myTable99 WHERE Active = 0 AND Price = @Price) AS xxx
	UPDATE @x SET InActiveUsers = @List WHERE Price = @Price
	SELECT @Price = Max(Price) FROM myTable99 WHERE Price < @Price
	SELECT @List = Null
  END

    SELECT a.Price, Volume_active, Activeusers, Volume_inactive, InactiveUsers 
      FROM (SELECT DISTINCT Price FROM myTable99) AS a
 LEFT JOIN (SELECT Price, SUM(Volume) AS Volume_Active FROM myTable99 WHERE Active = 1 GROUP BY Price) AS b
	ON a.Price = b.Price
 LEFT JOIN (SELECT Price, SUM(Volume) AS Volume_InActive FROM myTable99 WHERE Active = 0 GROUP BY Price) AS c
	ON a.Price = c.Price
 LEFT JOIN @x d
	ON a.Price = d.Price
GO

SET NOCOUNT OFF
GO

DROP TABLE myTable99
GO

Brett

8-)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-05-13 : 14:41:42

Wow! It works great that's for sure...I have so far only made one modification and that is to the while-loop:

WHILE @MIN_Price < @Price
=>
WHILE @MIN_Price <= @Price

This removes the set of COALESCE you have after the WHILE. It looks totally awesome Brett, thanx alot

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-13 : 14:46:10

<=

oye, what a scrub.....

Glad you fixed that....

As far as doing it in 1 query...you can't....

unless you were using mySQL...then maybe....

EDIT: Fixed it

Brett

8-)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-05-13 : 15:17:47

quote:
As far as doing it in 1 query...you can't....

True, But it can be set-based...

select identity(int,0,1) id,*,replicate(' ',255) Active_users,replicate(' ',255) Inactive_users
into #t 
from @mytable
order by price,UserID

declare 
	@last_price decimal(10,2),
	@last_user varchar(20),
	@active_users varchar(255),
	@inactive_users varchar(255)

select 
	@last_price = min(price),
	@last_user = ''
from #t
group by id
order by id

update
    #t
set
    @active_users = active_users = 
	case
		when @last_price <> price  then convert(varchar(20),UserID)
		when @last_price = price and UserID <> @last_user and UserID <> '' then @active_users + ';' + convert(varchar(20),userid)
		else @active_users
	end,
    @last_price = price,
	@last_user = case when UserID <> '' then UserID else '' end
where active = 1

select 
	@last_price = min(price),
	@last_user = ''
from #t
group by id
order by id

update
    #t
set
    @inactive_users = inactive_users = 
	case
		when @last_price <> price  then convert(varchar(20),UserID)
		when @last_price = price and UserID <> @last_user and UserID <> '' then @inactive_users + ';' + convert(varchar(20),UserID)
		else @inactive_users
	end,
    @last_price = price,
	@last_user = case when UserID <> '' then UserID else '' end
where active = 0

select
	price,
	sum(volume_active) active_volume,
	max(active_users) active_users,
	sum(volume_inactive) inactive_volume,
	max(inactive_users) inactive_users
from	
(
	select top 100 percent
		id, 
		price, 
		max(case when active = 1 then volume else 0 end) volume_active,
		max(case when active = 1 then active_users else '' end) active_users,
		max(case when active = 0 then volume else 0 end) volume_inactive,
		max(case when active = 0 then inactive_users else '' end) inactive_users
	from #t
	group by id,price
	order by id
) d
group by price

drop table #t

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-13 : 16:05:11

Never forget the power of the UDF.


create function UserList(@Price decimal(10,2), @Active bit)
returns varchar(20)
as
begin
        declare @s varchar(20)
        set @s= ''
        select  @s = @s + ';' + convert(varchar(20), UserID) from MyTable 
        where Price =@Price and Active=@Active group by UserID
        return (substring(@s,2,20))
end

GO

select price, 
        suM(case when Active=1 then Volume else 0 end) as Volume_Active,
        dbo.UserList(Price,1) as Active_Users,
        suM(Case when Active=1 then Volume else 0 end ) as Volume_Inactive,
        dbo.UserList(Price,0) as Inactive_Users
from MyTable
group by price

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-13 : 16:09:59

by the way --

THANK YOU !!!!!! for the create table and insert statements !

it makes helping out so much easier !

- Jeff

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-05-13 : 16:16:50

quote:
Originally posted by jsmith8858

Never forget the power of the UDF.

Very nice!!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-05-13 : 16:53:06

>> Never forget the power of the UDF.

Hehe, it just amazes me what a few brilliant minds can come up with. So simple, yet so very powerful

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-14 : 08:50:27

quote:
Originally posted by jsmith8858

Never forget the power of the UDF.

Very Nice Dr!

quote:

Hehe, it just amazes me what a few brilliant minds can come up with. So simple, yet so very powerful

Or simple minds...like mine....

Think I'll be doing udf's for this kind of thing all the time in the future...

Brett

8-)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-05-14 : 09:17:00

For those interested, Page47 and Byrmol have done a nice job outlining these types of functions:

http://www.sqlteam.com/item.asp?ItemID=11021
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19647

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-14 : 10:59:33

YES -- byrmol's post is the first time I'd seen this done, and it's one of my favorite UDF techniques of all time.

And Jay's article is great as well, espcially for pre-2000 versions of SQL Server in which UDF's are not available.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources