RE: Re: UKHA Classified update - SQL Help!

["BUTLER, Tony, FM" <tony.butler@xxxxxxx>]

Graham,

I have run out of time on this, as I left the office about 20 minutes ago
(!) but run the stuff below in I/SQL which should help you.
Basicaly, #sp_prod_list retrieves a list of all products under a given
product type, all the way down the tree (well, depends on your version of
SQL - earlier ones can only go 15 levels deep).
It's not _quite_ the answer, but a starting point.
You just need to roll up the data to the appropriate level.

Note: I am using temporary procs and table for convienience - obviousy you
would have permanent ones in the final results.

Back in tomorrow so feel free to ask any questions if more details
required.

HTH,

Tony



set nocount on

create table #tblProductsType
(
ProductTypeID int,
Description varchar (30),
ParentProductTypeID int,
DeletedFlag int)
go
create table

#tblProducts (
ProductID int,
Description varchar (100),
ProductTypeID int,
StatusID int)
go
create table

#tblStatus (
StatusID int,
Description varchar (30),
ArchivedFlag int,
DeletedFlag int,
PublishedFlag int)

insert #tblProductsType values(1,"Computer",0,0)
insert #tblProductsType values(2,"Hardware",1,0)
insert #tblProductsType values(3,"Software",1,0)
insert #tblProductsType values(4,"Type2",0,0)
insert #tblProductsType values(5,"Type2 SubType 1",4,0)
insert #tblProductsType values(6,"Microsoft Products",3,0)
insert #tblProductsType values(7,"Lotus Products",3,0)
insert #tblProductsType values(8,"Software requiring a PhD",3,0)
insert #tblProductsType values(9,"Office Applications",6,0)
insert #tblProductsType values(10,"Back Office Applications",6,0)
insert #tblProductsType values(11,"Office Applications",7,0)

insert #tblproducts values (1,"Excel",9,0)
insert #tblproducts values (2,"Word",9,0)
insert #tblproducts values (3,"Access",9,2)
insert #tblproducts values (4,"Exchange",10,0)
insert #tblproducts values (5,"SQL Server",10,0)
insert #tblproducts values (6,"XP Home",6,0)
insert #tblproducts values (7,"Notes",7,0)
insert #tblproducts values (8,"1-2-3",11,0)
insert #tblproducts values (9,"AmiPro",11,0)
insert #tblproducts values (10,"Linux",8,0)
insert #tblproducts values (10,"Some other flavour of Linux",8,0)
insert #tblproducts values (10,"Yet another type of Linux",8,0)
insert #tblproducts values (10,"The aren't standards wonderful edition
of
Linux",8,1)

insert #tblStatus values(0,"Status 0",0,0,0)
insert #tblStatus values(1,"Status 1 (Deleted)",0,1,0)
insert #tblStatus values(2,"Status 2",0,0,0)

set nocount off

go
create table #Prod (
ProductID int NOT NULL,
ProductTypeID int NOT NULL,
ParentProductTypeID int NOT NULL
)

drop procedure #sp_prod_list
GO
CREATE PROCEDURE #sp_prod_list
(
@ProdTypeID	int
)
AS
BEGIN


DECLARE @NodeID int

SET NOCOUNT ON

INSERT #Prod
SELECT p.ProductID, p.ProductTypeID, pt.ParentProductTypeID
>from
INNER  JOIN #tblproducts p
ON     pt.producttypeid=p.producttypeid
INNER  JOIN #tblstatus s
ON     p.statusid=s.statusid
WHERE  s.DeletedFlag=0
AND    pt.ProductTypeID=@ProdTypeID

DECLARE pt_cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT producttypeid
>from
WHERE  ParentProductTypeID=@ProdTypeID

OPEN pt_cursor

FETCH NEXT
>from
INTO  @NodeID

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC #sp_prod_list @NodeID

FETCH NEXT
>from
INTO  @NodeID
END

CLOSE  pt_cursor
DEALLOCATE pt_cursor
RETURN (SELECT COUNT(*) FROM #Prod)

SET NOCOUNT OFF

END

GO


#sp_prod_list 3
go
Select pt.Description, count(*) Number
>from
inner join #prod p
on pt.producttypeid=p.producttypeid
group by pt.description
go

set nocount on
drop table #prod
drop table #tblStatus
drop table #tblproducts
drop table #tblproductstype
set nocount off


********************************************************************
Visit our Internet site at http://www.rbsmarkets.com

This e-mail is intended only for the addressee named above.
As this e-mail may contain confidential or privileged information,
if you are not the named addressee, you are not authorised to
retain, read, copy or disseminate this message or any part of it.
********************************************************************