Displaying Records in a Hierarchy

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

Transact-SQL (2000)

Displaying Records in a Hierarchy

Author

Topic

alikazim
Starting Member

1 Post

Posted - 2004-10-09 : 21:18:26

Hi,

I need to display data (items purchased in a month) in a

hierarchy like below:

2004 10
Dec 05
Nov 03
Jul 02
2003 07
Nov 04
Aug 03

I want to return it in a single query and I'm using TreeView web

control to display it on webpage for this tree like structure.

Here is the DDL:

CREATE TABLE Books
(
BookID INT,
BookName VARCHAR2(50),
PurchaseDate DATE
);

INSERT INTO Books VALUES (1, 'XYZ', '11/10/2004');
INSERT INTO Books VALUES (2, 'XYZ', '12/10/2004');
.
.
.

I'm able to return data in following manner:

2004 Dec 05
2004 Nov 03
2004 Jul 02
2003 Nov 04
2003 Aug 03

Using this query:

SELECT YEAR(PurchaseDate), MONTH(PurchaseDate), COUNT(*)
FROM PRPressRms
GROUP BY YEAR(PRPressRmDate), MONTH(PRPressRmDate)

But I need to return data in following manner:

ID ITEM TOTAL PARENT
1 2004 10
2 Dec 05 1
3 Nov 03 1
4 Jul 02 2
5 2005 07
6 Dec 04 5
7 Nov 03 5

Am I doing the right way? Or do I need to change my approach to

this problem?

Any help will be highly appriciated.

Regards

Ali

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-10 : 11:44:41

well u could use union

select for year union all
select for months union all
...

have you looked at rollup, cube or compute by in BOL?

Go with the flow & have fun! Else fight the flow

imughal
Posting Yak Master

192 Posts

Posted - 2004-10-11 : 06:38:06

instead using count(*) apply select query. i had used this sort of query. below script will help u out it is much simillar that you are looking.

CREATE PROCEDURE sp_qbprocessdateslatest

@monthno int,
@year varchar(5)

As
Begin

SET NOCOUNT ON

select caclientsloginid,upload_date as 'Processed Send', noofmonths as 'No. of Months',
(
select top 1 upload_date
from fileinfo as B
where right(rtrim(filename),3)= 'qbw' or right(rtrim(filename),3)= 'qbb' or right(rtrim(filename),3)= 'qbx' and
filename <> 'NULL' and
b.caclientsloginid=c.caclientsloginid and
b.upload_date < c.upload_date and
month(b.upload_date) = month(c.upload_date) and
year(b.upload_date) = year(c.upload_date)
order by upload_date desc

) as 'Un-Processed'

from backpdfinfo as c
where filename <> 'NULL' and
right(rtrim(filename),3)= 'qbw' or right(rtrim(filename),3)= 'qbb' or right(rtrim(filename),3)= 'qbx' and
month(upload_date) = @monthno and
year(upload_date) = @year
order by caclientsloginid

end
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources