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

SQL Server 2005 Forums

Transact-SQL (2005)

Query help

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-09-15 : 04:59:30

Hi,

Just trying to do a query that would fill gaps, for instance

Table1
Product, Info, Value
1, Sell, 400
1, Buy, 300
2, Buy 300

See how product 2 doesnt have 'sell', how would I be able to add the field and the value of that would be the buy value * 1.5

Any ideas?

Thanks

Cipriani

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-09-15 : 05:30:21

Any help???

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-15 : 05:33:45

Don't know if there are better solutions...

declare @Table1 table (Product int,Info varchar(255),value float)
insert @Table1
select 1, 'Sell', 400 union all
select 1, 'Buy', 300 union all
select 2, 'Buy', 300

;with cte as (
select t1.Product,
       t1.Info,
       t1.value,
       isnull(t2.Product,t1.Product) as Product_t2,
       isnull(t2.Info,'Sell') as Info_t2,
       isnull(t2.value,t1.value * 1.5) as value_t2
from @Table1 t1 
left join @Table1 t2
  on t1.Product = t2.Product and t2.Info = 'Sell'
where t1.Info = 'Buy'
)
select Product,Info,value from cte
union all
select Product_t2,Info_t2,value_t2 from cte
order by Product,Info

No, you're never too old to Yak'n'Roll if you're too young to die.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-09-15 : 05:51:06

This is my query but i get the following error:

---
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
--

declare @Table1 table (Product varchar(max), Region varchar(max), Platform varchar(max), InfoType varchar(max), Final float)
insert into @Table1
select Product, Region, Platform, InfoType, Final FROM pg_info

; with cte as (
select t1.Product, t1.Region, t1.Platform, t1.InfoType, t1.Final, isnull(t2.Product, t1.Product) AS Product_t2,
isnull(t2.InfoType, 'PG Sell-Through') AS InfoType_t2,
isnull(t2.Final, t1.Final * 0.7) as Final_t2
FROM @Table1 t1
left join @Table1 t2
on t1.Product = t2.Product and t1.Region = t2.Region AND t1.Platform = t2.Platform
and t2.InfoType = 'PG Sell-Through' WHERE t1.InfoType = 'PG Sell-In')
select Product, Region, Platform, InfoType, Final from cte
union all
select Product_t2, InfoType_t2, Final_t2 from cte
order by Product, InfoType

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-15 : 05:56:52

while using the union all
u should have same noof columns and same datatype

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-15 : 05:59:43

First there is no need for you to use @Table1 I have done that only to have test data.
Replace @Table1 by pg_info in cte statement.

Second the union select needs also Region and Platform because both selects must have same number of columns and data types.

No, you're never too old to Yak'n'Roll if you're too young to die.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-09-15 : 06:02:00

I still get same error when made the changes:

; with cte as (
select t1.Product, t1.Region, t1.Platform, t1.InfoType, t1.Final, isnull(t2.Product, t1.Product) AS Product_t2,
isnull(t2.Region, t1.Region) AS Region_t2,
isnull(t2.Platform, t1.Platform) AS Platform_t2,
isnull(t2.InfoType, 'PG Sell-Through') AS InfoType_t2,
isnull(t2.Final, t1.Final * 0.7) as Final_t2
FROM pg_info t1
left join pg_info t2
on t1.Product = t2.Product and t1.Region = t2.Region AND t1.Platform = t2.Platform
and t2.InfoType = 'PG Sell-Through' WHERE t1.InfoType = 'PG Sell-In')
select Product, Region, Platform, InfoType, Final from cte
union all
select Product_t2, Region_t2, Platform_t2 InfoType_t2, Final_t2 from cte
order by Product, InfoType
------

Any ideas?

quote:
Originally posted by webfred

First there is no need for you to use @Table1 I have done that only to have test data.
Replace @Table1 by pg_info in cte statement.

Second the union select needs also Region and Platform because both selects must have same number of columns and data types.

No, you're never too old to Yak'n'Roll if you're too young to die.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-15 : 06:04:04

example for cte
with cte (id, name)
as
(
select id,name from table

union all
select t.id,t.name from table t
join cte c on c.id = t.parentid
)
select * from cte

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-15 : 06:05:19

There is a comma missing
Platform_t2, InfoType_t2

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources