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

SQL Query help please

Author

Topic

Poppy
Starting Member

5 Posts

Posted - 2007-01-31 : 08:34:30

Hi

I have two tables in a sql db.
Table one has the the following fields:
rim, size, code, design and brand

Table two:
code, ddate and dprice

I need to find out the number of distinct dates for each brand and use those dates in this query:


select size, a.code, design, brand,
sum( case when convert(varchar,Ddates,112) = 20060110 then dprice else 0 end  ),
sum( case when convert(varchar,Ddates,112) = 20060105 then dprice else 0 end  ),
sum( case when convert(varchar,Ddates,112) = 20061112 then dprice else 0 end  )
from brand_size a left outer join brand_price b
on a.code = b.code
group by size, a.code, design, brand

The problem is that I don't know the number of dates each brand can have. Brand X can have 5, brand Y maybe 3 and brand Z maybe 1.

My results should be something like this


                         Brand 1                       Brand 2 etc...
Size, Code, Design, Date1 date2 Date3 etc..   Date1 date2 Date3 etc..

Kind Regards

Never Argue with an idiot. They drag you down to their level and beat you with experience.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-31 : 08:39:53

this should give you number of distinct date per brand


select brand, count(distinct ddate)
from   table1 t1 inner join table2 t2
       on t1.code = t2.code
group by brand

Poppy
Starting Member

5 Posts

Posted - 2007-01-31 : 09:39:50

Thanks khtan, now if only i could get help with my main problem. How do I use the distict dates for all the brand to find my final query.

Never Argue with an idiot. They drag you down to their level and beat you with experience.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-31 : 10:00:01

You will have to resort to Dynamic SQL. And the query will not be pretty.

Why do you want to do this ?

Poppy
Starting Member

5 Posts

Posted - 2007-02-01 : 02:48:16

quote:
Originally posted by khtan

You will have to resort to Dynamic SQL. And the query will not be pretty.

Why do you want to do this ?

KH

Dynamic SQl? I'm actually running my queries through vb6. I'm writing a program that needs to export and format data from sql to excel using vb.

Never Argue with an idiot. They drag you down to their level and beat you with experience.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-01 : 03:02:06

then why not just return the result in normal format and let vb / excel do the pivot / cross tab job ?

size, code, design, brand, date, price

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources