holiday's

hi

i want to write some sort of query which will return all saturday and sunday of year 2007 with date...

thanx a lot

here you go..

Code Snippet

Declare @.Year as Int;

Declare @.Weekends Table(Date DateTime);

Declare @.Date as DateTime;

Declare @.EndDate as DateTime;

Select @.Year = 2000;

Select @.Date = Cast(Cast(@.Year as Varchar) + '-01-01' as Datetime) ,

@.EndDate = Cast(Cast(@.Year as Varchar) + '-12-31' as Datetime);

Select @.Date = DateAdd(DD,7-Datepart(w,@.Date),@.Date);

While @.Date <= @.EndDate

Begin

Insert Into @.Weekends

Select @.Date

Union All

Select DateAdd(DD,1,@.Date);

Select @.Date = DateAdd(DD,7,@.Date);

End

Select * From @.Weekends

|||

Hi,

I hope the below query may help you

declare @.date as datetime

select @.date = dateadd(dd, -1 * datepart(dy, getdate()) + 1, getdate())

while year(@.date)=2007

begin

if DATEPART(dw, @.date) in (1,7)

print cast(@.date as varchar(10))

set @.date = dateadd(dd,1,@.date)

end

Eralper

http://www.kodyaz.com

|||

hey mani thanx for reply...

i want date with day...i mean the corresponding date is saturday or sunday .......how we can do?

|||

Here it is with day..

Code Snippet

Declare @.Year as Int;

Declare @.Weekends Table(Date DateTime,Day varchar(10));

Declare @.Date as DateTime;

Declare @.EndDate as DateTime;

Select @.Year = 2006;

Select @.Date = Cast(Cast(@.Year as Varchar) + '-01-01' as Datetime) ,

@.EndDate = Cast(Cast(@.Year as Varchar) + '-12-31' as Datetime);

Select @.Date = DateAdd(DD,1-Datepart(w,@.Date),@.Date);

While @.Date <= @.EndDate

Begin

Insert Into @.Weekends

Select DateAdd(DD,-1,@.Date), Datename(dw,DateAdd(DD,-1,@.Date)) Where Year(DateAdd(DD,-1,@.Date)) = @.Year

Union

Select @.Date, Datename(dw,@.Date) Where Year(@.Date) = @.Year

Select @.Date = DateAdd(DD,7,@.Date);

End

Select * From @.Weekends

|||

yes mani this is my need thanx a lot....but here i m getting one entry of 2006-12-31 00:00:00.000......

Select @.Year = 2007;

here i set @.year 2007 but i m getting one entry of 2006...i don't want that entry....

sorry for trouble...

|||opps yes.. Fixed it..|||thanx a lot|||welcome.. |||

declare @.date as datetime
declare @.year as int
set @.year=2007
Declare @.Weekends Table(Date varchar(12),[Day] varchar(10));
select @.date = dateadd(dd, -1 * datepart(dy, getdate()) + 1, getdate())
while year(@.date)=@.year
begin
if DATEPART(dw, @.date) in (1,7)
begin
Insert Into @.Weekends
select cast(@.date as varchar(12)),cast(DATENAME(dw,@.Date) as varchar(10))
end
set @.date = dateadd(dd,1,@.date)
end
Select * From @.Weekends

holiday's

hi

i want to write some sort of query which will return all saturday and sunday of year 2007 with date...

thanx a lot

here you go..

Code Snippet

Declare @.Year as Int;

Declare @.Weekends Table(Date DateTime);

Declare @.Date as DateTime;

Declare @.EndDate as DateTime;

Select @.Year = 2000;

Select @.Date = Cast(Cast(@.Year as Varchar) + '-01-01' as Datetime) ,

@.EndDate = Cast(Cast(@.Year as Varchar) + '-12-31' as Datetime);

Select @.Date = DateAdd(DD,7-Datepart(w,@.Date),@.Date);

While @.Date <= @.EndDate

Begin

Insert Into @.Weekends

Select @.Date

Union All

Select DateAdd(DD,1,@.Date);

Select @.Date = DateAdd(DD,7,@.Date);

End

Select * From @.Weekends

|||

Hi,

I hope the below query may help you

declare @.date as datetime

select @.date = dateadd(dd, -1 * datepart(dy, getdate()) + 1, getdate())

while year(@.date)=2007

begin

if DATEPART(dw, @.date) in (1,7)

print cast(@.date as varchar(10))

set @.date = dateadd(dd,1,@.date)

end

Eralper

http://www.kodyaz.com

|||

hey mani thanx for reply...

i want date with day...i mean the corresponding date is saturday or sunday .......how we can do?

|||

Here it is with day..

Code Snippet

Declare @.Year as Int;

Declare @.Weekends Table(Date DateTime,Day varchar(10));

Declare @.Date as DateTime;

Declare @.EndDate as DateTime;

Select @.Year = 2006;

Select @.Date = Cast(Cast(@.Year as Varchar) + '-01-01' as Datetime) ,

@.EndDate = Cast(Cast(@.Year as Varchar) + '-12-31' as Datetime);

Select @.Date = DateAdd(DD,1-Datepart(w,@.Date),@.Date);

While @.Date <= @.EndDate

Begin

Insert Into @.Weekends

Select DateAdd(DD,-1,@.Date), Datename(dw,DateAdd(DD,-1,@.Date)) Where Year(DateAdd(DD,-1,@.Date)) = @.Year

Union

Select @.Date, Datename(dw,@.Date) Where Year(@.Date) = @.Year

Select @.Date = DateAdd(DD,7,@.Date);

End

Select * From @.Weekends

|||

yes mani this is my need thanx a lot....but here i m getting one entry of 2006-12-31 00:00:00.000......

Select @.Year = 2007;

here i set @.year 2007 but i m getting one entry of 2006...i don't want that entry....

sorry for trouble...

|||opps yes.. Fixed it..|||thanx a lot|||welcome.. |||

declare @.date as datetime
declare @.year as int
set @.year=2007
Declare @.Weekends Table(Date varchar(12),[Day] varchar(10));
select @.date = dateadd(dd, -1 * datepart(dy, getdate()) + 1, getdate())
while year(@.date)=@.year
begin
if DATEPART(dw, @.date) in (1,7)
begin
Insert Into @.Weekends
select cast(@.date as varchar(12)),cast(DATENAME(dw,@.Date) as varchar(10))
end
set @.date = dateadd(dd,1,@.date)
end
Select * From @.Weekends

sql

Hmm - unique id is in Binary data - how do I query against it?

Hi - I've got a table in SQL server that has its unique ID field as binary data. I have a gridview displaying data from this table and I have set the datakey of this gridview as that binary data field.

I have a 'Select' ciolumn in my gridview that, when selected, will display more details from the selected record. The problem is, how do I pass the selected id back to sql server bearing in mind that it's binary data? Here's what I'm doing at the moment:

I have a function in my data accsess class that queries the database:

Public Function getDetailsById(ByVal Id As System.Byte) As DataSet

Dim con As New SqlConnection(conStr)
Dim cmd As New SqlCommand("SELECT * FROM tableName WHERE Id=@.Id", con)

cmd.Parameters.Add("@.Id", SqlDbType.Binary)
cmd.Parameters("@.Id").Value = Id

Dim ds As New DataSet
Dim adp As New SqlDataAdapter(cmd)
adp.Fill(ds)

Return ds

End Function

And then I call this function from my page with the gridview using:

Dim da As New myDataAccess
Dim ds As New DataSet
ds = da.getDetailsById(GridView1.SelectedDataKey.Value)

But I get the error:

Conversion from type 'Byte()' to type 'Byte' is not valid.

Any ideas where my data/code is going wrong? I'm not sure how to pass this sort of data around?

Thanks

loydall:

Hi - I've got a table in SQL server that has its unique ID field as binary data.

Not trying to be funny... but making your primary key a binary is where you are going wrong.

I know I've queried off of binary fields before, but I don't have any of that code infront of me now.

|||

Thanks but it's not my db so I have no control over how the ID is stored...

I've fixed it anyway - I just need to use system.byte() rather than system.byte as a parameter in my function - it obviously needed to treat the data as an array...

|||

Make sure you're getting the correct records back... I seem to remember that SQL will store a binary as 0x00000047 where .Net will see the same value as 0x47000000.

|||

You are using the wrong ADO.NET command object, you should use ExecuteScalar. Try the link below for complete sample code from Jeff Prosise Programming .NET. The code is in C# but it shows you the correct way to use ExecuteScalar. Hope this helps.

http://kurinjikumaravel.tripod.com/Asp.Net/AspWintellect.txt

Historical tables, partitioning or what?

I have about 45000 records in a CSV file, which I am using as HTTP request parameters to query a website and store some results in a database. This is the kind of application which runs 24/7, so database grows really quickly. Every insert fires up a trigger, which has to look for some old records based on some criteria and modify the last inserted record. My client is crazy about performance on this one and suggested to move the old records into another table, which has exactly the same structure, but would serve as a historical table only (used to generate reports, statistics, etc.), whilst the original table would store only the latest rows (so no more than 45k at a given time, whereas the historical table may grow to millions of records). Is this a good idea? Having the performance in mind and the fact that there's that trigger - it has to run as quickly as possible - I might second that idea. Is it good or bad? What do you think?

I read a similar post here, which mentioned SQL Server 2005 partitioning, I might as well try this, although I never used it before.

I think you should use the archive table and partition it with 2005s new capabilities. This will allow you to easily administer the table(s). Heres a good link on 2005 partitioning. http://msdn2.microsoft.com/en-us/library/ms345146.aspx If you use a seperate archive table, you can create good indexes that will really speed queries. The only bad thing is that if you insert new records into it often, it could be slow. So, you might want to only insert new archive records during off-peak time if possible.
Tim|||I would caution to have the absolute minimal indexes on the archive table. In some situations, it may be efficient to create an index just for a report, and then remove the index as soon as the report is complete.|||Thanks for your comments. I read about SQL Server 2005's partitioning feature and find it very interesting (and useful, I think), but the application in question runs on the Express Edition currently with possible switch to a higher version, but doubtfuly the Enterprise one, so partitioning is out of the question, unfortunately. I will hopefuly use it when I will work on some enterprise level application in the future.

historical lookup query

I'm having a dickins of a time with a particular query and am hoping
someone here can help me.
Using the following example;
declare @.SearchDate datetime
set @.SearchDate = '30 Nov 2005'
declare @.t1 table (t1id int, t1desc varchar(10))
insert into @.t1 (t1id, t1desc) values (1, 'Ed')
insert into @.t1 (t1id, t1desc) values (2, 'Bill')
insert into @.t1 (t1id, t1desc) values (3, 'Bob')
insert into @.t1 (t1id, t1desc) values (4, 'Fred')
insert into @.t1 (t1id, t1desc) values (5, 'John')
declare @.t1history table (t1id int, t1desc varchar(10), created
datetime)
insert into @.t1history (t1id, t1desc, created) values (1, 'James', '01
Jan 2005')
insert into @.t1history (t1id, t1desc, created) values (1, 'Frank', '05
Jan 2005')
insert into @.t1history (t1id, t1desc, created) values (1, 'Henry', '10
May 2005')
insert into @.t1history (t1id, t1desc, created) values (1, 'Joe', '28
Nov 2005')
insert into @.t1history (t1id, t1desc, created) values (4, 'Toby', '21
Oct 2005')
insert into @.t1history (t1id, t1desc, created) values (4, 'Brian', '25
Oct 2005')
insert into @.t1history (t1id, t1desc, created) values (4, 'Horace', '28
Nov 2005')
insert into @.t1history (t1id, t1desc, created) values (5, 'Ben', '21
Oct 2005')
declare @.lookup table (val varchar(10))
insert into @.lookup (val) values ('Ben')
insert into @.lookup (val) values ('Frank')
insert into @.lookup (val) values ('Bill')
--example query
select *,
(select top 1 t1desc from @.t1history as Table1History where
Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
created desc) as t1deschistory
from @.t1 as Table1
I want to filter the results returned from @.t1 against those contained
in @.lookup
I also need to be able to filter the results based on what the value
for t1desc could have been in the past using @.t1history and @.SearchDate
For example, with the date of '30 Nov 2005' I would expect the
following;
t1id t1desc t1deschistory
----
2 'Bill' null
5 'John' 'Ben'
Changing the date to '15 Oct 2005' I would expect;
t1id t1desc t1deschistory
----
2 'Bill' null
and changing it again to '15 Jan 2005' I would expect;
t1id t1desc t1deschistory
----
1 'Ed' 'Frank'
2 'Bill' null
What I basically want to do is this;
select *,
(select top 1 t1desc from @.t1history as Table1History where
Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
created desc) as t1deschistory
from @.t1 as Table1
where t1desc in (select val from @.lookup) or t1deschistory in (select
val from @.lookup)
This gives the following error as expected;
Server: Msg 207, Level 16, State 3, Line 28
Invalid column name 't1deschistory'.
Moving the sub-query into a join doesn't work either;
select *
from @.t1 as Table1
left join (select top 1 * from @.t1history as t1history where
t1history.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
created desc) Table1History on Table1.t1id = Table1History.t1Id
where Table1.t1desc in (select val from @.lookup) or
Table1History.t1desc in (select val from @.lookup)
This gives the following error;
Server: Msg 107, Level 16, State 2, Line 28
The column prefix 'Table1' does not match with a table name or alias
name used in the query.
Can anyone help?
Many thanks in advance,
Edone way: make it a derived table before applying the where e.g.
select * from (
select *,
(select top 1 t1desc
from @.t1history as Table1History
where Table1History.t1id = Table1.t1Id
and created <= @.SearchDate +1
order by created desc) as t1deschistory
from @.t1 as Table1
) x
where t1desc in (select val from @.lookup) or t1deschistory in (select
val from @.lookup)
ThievingScouser wrote:
> I'm having a dickins of a time with a particular query and am hoping
> someone here can help me.
> Using the following example;
> declare @.SearchDate datetime
> set @.SearchDate = '30 Nov 2005'
> declare @.t1 table (t1id int, t1desc varchar(10))
> insert into @.t1 (t1id, t1desc) values (1, 'Ed')
> insert into @.t1 (t1id, t1desc) values (2, 'Bill')
> insert into @.t1 (t1id, t1desc) values (3, 'Bob')
> insert into @.t1 (t1id, t1desc) values (4, 'Fred')
> insert into @.t1 (t1id, t1desc) values (5, 'John')
> declare @.t1history table (t1id int, t1desc varchar(10), created
> datetime)
> insert into @.t1history (t1id, t1desc, created) values (1, 'James', '01
> Jan 2005')
> insert into @.t1history (t1id, t1desc, created) values (1, 'Frank', '05
> Jan 2005')
> insert into @.t1history (t1id, t1desc, created) values (1, 'Henry', '10
> May 2005')
> insert into @.t1history (t1id, t1desc, created) values (1, 'Joe', '28
> Nov 2005')
> insert into @.t1history (t1id, t1desc, created) values (4, 'Toby', '21
> Oct 2005')
> insert into @.t1history (t1id, t1desc, created) values (4, 'Brian', '25
> Oct 2005')
> insert into @.t1history (t1id, t1desc, created) values (4, 'Horace', '28
> Nov 2005')
> insert into @.t1history (t1id, t1desc, created) values (5, 'Ben', '21
> Oct 2005')
> declare @.lookup table (val varchar(10))
> insert into @.lookup (val) values ('Ben')
> insert into @.lookup (val) values ('Frank')
> insert into @.lookup (val) values ('Bill')
> --example query
> select *,
> (select top 1 t1desc from @.t1history as Table1History where
> Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
> created desc) as t1deschistory
> from @.t1 as Table1
>
> I want to filter the results returned from @.t1 against those contained
> in @.lookup
> I also need to be able to filter the results based on what the value
> for t1desc could have been in the past using @.t1history and @.SearchDate
> For example, with the date of '30 Nov 2005' I would expect the
> following;
> t1id t1desc t1deschistory
> ----
> 2 'Bill' null
> 5 'John' 'Ben'
> Changing the date to '15 Oct 2005' I would expect;
> t1id t1desc t1deschistory
> ----
> 2 'Bill' null
> and changing it again to '15 Jan 2005' I would expect;
> t1id t1desc t1deschistory
> ----
> 1 'Ed' 'Frank'
> 2 'Bill' null
>
> What I basically want to do is this;
> select *,
> (select top 1 t1desc from @.t1history as Table1History where
> Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
> created desc) as t1deschistory
> from @.t1 as Table1
> where t1desc in (select val from @.lookup) or t1deschistory in (select
> val from @.lookup)
> This gives the following error as expected;
> Server: Msg 207, Level 16, State 3, Line 28
> Invalid column name 't1deschistory'.
> Moving the sub-query into a join doesn't work either;
> select *
> from @.t1 as Table1
> left join (select top 1 * from @.t1history as t1history where
> t1history.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
> created desc) Table1History on Table1.t1id = Table1History.t1Id
> where Table1.t1desc in (select val from @.lookup) or
> Table1History.t1desc in (select val from @.lookup)
> This gives the following error;
> Server: Msg 107, Level 16, State 2, Line 28
> The column prefix 'Table1' does not match with a table name or alias
> name used in the query.
>
> Can anyone help?
> Many thanks in advance,
> Ed
>

Highlight Results from search

>From what I have so far, Sql Server does not provide a way to return
its query results hits in a highlighted format. I was able to
programmatically get this working for simple searches, where I simply
do a find and replace based on the search criteria. But this will not
work for plural results or irregular type finds. For example, if I do
a search for "mouse", and sql server returns "mice", I have no way of
knowing to highlight "mice". I read that this can be done using
lemmatizer and stemmer algorithms. Is there an easier way of doing
this? If not, does anyone know of any .net api's or code I can get
started with?
Thanks
You have to implement your own version of Porter stemmer algorithm for this.
You can use Indexing Services for this, here is a link describing how to do
this.
http://www.indexserverfaq.com/sqlhithighlighting.htm
"guate911" <guatemala911@.gmail.com> wrote in message
news:1179421330.863970.28850@.p77g2000hsh.googlegro ups.com...
> its query results hits in a highlighted format. I was able to
> programmatically get this working for simple searches, where I simply
> do a find and replace based on the search criteria. But this will not
> work for plural results or irregular type finds. For example, if I do
> a search for "mouse", and sql server returns "mice", I have no way of
> knowing to highlight "mice". I read that this can be done using
> lemmatizer and stemmer algorithms. Is there an easier way of doing
> this? If not, does anyone know of any .net api's or code I can get
> started with?
> Thanks
>
sql

Highest balance

Hi
I have table with all customers transactions
I am trying to create a query that can show me the balance of our customers.
I am trying to see when customer had the highest balance. How can I do this?
My customers transactions
Record number
Date.
Customer
Amount
21850
1.1.2004
1111
-1.699,85
21851
1.1.2004
1111
-638,71
21852
1.1.2004
1111
-2.795,87
21853
1.1.2004
1111
144,21
21854
1.1.2004
1111
25.472,30
21855
1.1.2004
2222
2.501,91
21856
1.1.2004
2222
19.942,04
21857
1.1.2004
2222
1.518,95
SELECT Customer, Date, Amount
FROM YourTable
WHERE Amount =
(SELECT MAX(Amount)
FROM YourTable Y1
WHERE Y1.Customer = YourTable.Customer)
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
>
> I have table with all customers transactions
> I am trying to create a query that can show me the balance of our
customers.
> I am trying to see when customer had the highest balance. How can I do
this?
>
> My customers transactions
>
> Record number
> Date.
> Customer
> Amount
> 21850
> 1.1.2004
> 1111
> -1.699,85
> 21851
> 1.1.2004
> 1111
> -638,71
> 21852
> 1.1.2004
> 1111
> -2.795,87
> 21853
> 1.1.2004
> 1111
> 144,21
> 21854
> 1.1.2004
> 1111
> 25.472,30
> 21855
> 1.1.2004
> 2222
> 2.501,91
> 21856
> 1.1.2004
> 2222
> 19.942,04
> 21857
> 1.1.2004
> 2222
> 1.518,95
>
>
>
|||Thank you for your answer Adam, but this did not work for me. Maybe I didn't
explain this right. I am not trying to get the highest amount from the
Column "Amount". I am trying to get the highest balance. Maybe I need to
create Column balance and calculate from Amount. Is that possible?
Example:
In this example i am trying to get the amount of 7000 that is the highest
balance for this customer
Record Date Customer Amount
1 01.01.04 3344 5000
2 01.01.04 3344 2000
3 01.01.04 3344 -1000
4 01.01.04 3344 -500
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
>
> I have table with all customers transactions
> I am trying to create a query that can show me the balance of our
customers.
> I am trying to see when customer had the highest balance. How can I do
this?
>
> My customers transactions
>
> Record number
> Date.
> Customer
> Amount
> 21850
> 1.1.2004
> 1111
> -1.699,85
> 21851
> 1.1.2004
> 1111
> -638,71
> 21852
> 1.1.2004
> 1111
> -2.795,87
> 21853
> 1.1.2004
> 1111
> 144,21
> 21854
> 1.1.2004
> 1111
> 25.472,30
> 21855
> 1.1.2004
> 2222
> 2.501,91
> 21856
> 1.1.2004
> 2222
> 19.942,04
> 21857
> 1.1.2004
> 2222
> 1.518,95
>
>
>
|||Ahh, now I understand...
The first step is to calculate a running balance:
SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
GROUP BY Tbl1.Customer, Tbl1.Record
Then we can use this as a derived table in an outer query to get the max per
customer... I've also added the date to the outer part of the query in case
you want that:
SELECT YourTable.Customer, YourTable.Date, MAX(Tbl0.Balance) As Balance
FROM YourTable
JOIN
(SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
GROUP BY Tbl1.Customer, Tbl1.Record
) Tbl0(Balance, Customer, Record) ON Tbl0.Record = YourTable.Record
GROUP BY YourTable.Customer, YourTable.Date
Note, I've used Tbl2.Record in order to determine the order of transactions;
if possible, you should use the date instead. I didn't, as the dates you
provided were non-unique.
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23bG$jZiUEHA.2580@.TK2MSFTNGP12.phx.gbl...
> Thank you for your answer Adam, but this did not work for me. Maybe I
didn't
> explain this right. I am not trying to get the highest amount from the
> Column "Amount". I am trying to get the highest balance. Maybe I need to
> create Column balance and calculate from Amount. Is that possible?
> Example:
> In this example i am trying to get the amount of 7000 that is the highest
> balance for this customer
> Record Date Customer Amount
> 1 01.01.04 3344 5000
> 2 01.01.04 3344 2000
> 3 01.01.04 3344 -1000
> 4 01.01.04 3344 -500
>
>
> "Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
> news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> customers.
> this?
>
|||Thank you Adam this works great you saved my day. Your first query "Running
balance" can I save this balance into my Customers transaction table? I did
create column called "running balance" in my Customers transaction table.
regards
Benedikt Fridbjornsson
Computer department
SIF Iceland
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OsGd0piUEHA.1172@.TK2MSFTNGP10.phx.gbl...
> Ahh, now I understand...
> The first step is to calculate a running balance:
> SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
> FROM YourTable Tbl1
> JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
> AND Tbl2.Record <= Tbl1.Record
> GROUP BY Tbl1.Customer, Tbl1.Record
>
> Then we can use this as a derived table in an outer query to get the max
per
> customer... I've also added the date to the outer part of the query in
case
> you want that:
> SELECT YourTable.Customer, YourTable.Date, MAX(Tbl0.Balance) As Balance
> FROM YourTable
> JOIN
> (SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
> FROM YourTable Tbl1
> JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
> AND Tbl2.Record <= Tbl1.Record
> GROUP BY Tbl1.Customer, Tbl1.Record
> ) Tbl0(Balance, Customer, Record) ON Tbl0.Record = YourTable.Record
> GROUP BY YourTable.Customer, YourTable.Date
>
> Note, I've used Tbl2.Record in order to determine the order of
transactions;[vbcol=seagreen]
> if possible, you should use the date instead. I didn't, as the dates you
> provided were non-unique.
>
> "Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
> news:%23bG$jZiUEHA.2580@.TK2MSFTNGP12.phx.gbl...
> didn't
highest[vbcol=seagreen]
5000[vbcol=seagreen]
2000[vbcol=seagreen]
-1000[vbcol=seagreen]
-500
>
|||Of course...
UPDATE YourTable
SET RunningBalance =
(SELECT SUM(Tbl2.Amount)
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
AND Tbl1.Record = YourTable.Record
GROUP BY Tbl1.Customer, Tbl1.Record)
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:uBVKsurUEHA.2668@.TK2MSFTNGP10.phx.gbl...
> Thank you Adam this works great you saved my day. Your first query
"Running
> balance" can I save this balance into my Customers transaction table? I
did[vbcol=seagreen]
> create column called "running balance" in my Customers transaction table.
> regards
> Benedikt Fridbjornsson
> Computer department
> SIF Iceland
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:OsGd0piUEHA.1172@.TK2MSFTNGP10.phx.gbl...
> per
> case
> transactions;
you[vbcol=seagreen]
to[vbcol=seagreen]
> highest
> 5000
> 2000
> -1000
> -500
do
>

Highest balance

Hi
I have table with all customers transactions
I am trying to create a query that can show me the balance of our customers.
I am trying to see when customer had the highest balance. How can I do this?
My customers transactions
Record number
Date.
Customer
Amount
21850
1.1.2004
1111
-1.699,85
21851
1.1.2004
1111
-638,71
21852
1.1.2004
1111
-2.795,87
21853
1.1.2004
1111
144,21
21854
1.1.2004
1111
25.472,30
21855
1.1.2004
2222
2.501,91
21856
1.1.2004
2222
19.942,04
21857
1.1.2004
2222
1.518,95SELECT Customer, Date, Amount
FROM YourTable
WHERE Amount =
(SELECT MAX(Amount)
FROM YourTable Y1
WHERE Y1.Customer = YourTable.Customer)
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
>
> I have table with all customers transactions
> I am trying to create a query that can show me the balance of our
customers.
> I am trying to see when customer had the highest balance. How can I do
this?
>
> My customers transactions
>
> Record number
> Date.
> Customer
> Amount
> 21850
> 1.1.2004
> 1111
> -1.699,85
> 21851
> 1.1.2004
> 1111
> -638,71
> 21852
> 1.1.2004
> 1111
> -2.795,87
> 21853
> 1.1.2004
> 1111
> 144,21
> 21854
> 1.1.2004
> 1111
> 25.472,30
> 21855
> 1.1.2004
> 2222
> 2.501,91
> 21856
> 1.1.2004
> 2222
> 19.942,04
> 21857
> 1.1.2004
> 2222
> 1.518,95
>
>
>|||Thank you for your answer Adam, but this did not work for me. Maybe I didn't
explain this right. I am not trying to get the highest amount from the
Column "Amount". I am trying to get the highest balance. Maybe I need to
create Column balance and calculate from Amount. Is that possible?
Example:
In this example i am trying to get the amount of 7000 that is the highest
balance for this customer
Record Date Customer Amount
1 01.01.04 3344 5000
2 01.01.04 3344 2000
3 01.01.04 3344 -1000
4 01.01.04 3344 -500
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
>
> I have table with all customers transactions
> I am trying to create a query that can show me the balance of our
customers.
> I am trying to see when customer had the highest balance. How can I do
this?
>
> My customers transactions
>
> Record number
> Date.
> Customer
> Amount
> 21850
> 1.1.2004
> 1111
> -1.699,85
> 21851
> 1.1.2004
> 1111
> -638,71
> 21852
> 1.1.2004
> 1111
> -2.795,87
> 21853
> 1.1.2004
> 1111
> 144,21
> 21854
> 1.1.2004
> 1111
> 25.472,30
> 21855
> 1.1.2004
> 2222
> 2.501,91
> 21856
> 1.1.2004
> 2222
> 19.942,04
> 21857
> 1.1.2004
> 2222
> 1.518,95
>
>
>|||Ahh, now I understand...
The first step is to calculate a running balance:
SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
GROUP BY Tbl1.Customer, Tbl1.Record
Then we can use this as a derived table in an outer query to get the max per
customer... I've also added the date to the outer part of the query in case
you want that:
SELECT YourTable.Customer, YourTable.Date, MAX(Tbl0.Balance) As Balance
FROM YourTable
JOIN
(SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
GROUP BY Tbl1.Customer, Tbl1.Record
) Tbl0(Balance, Customer, Record) ON Tbl0.Record = YourTable.Record
GROUP BY YourTable.Customer, YourTable.Date
Note, I've used Tbl2.Record in order to determine the order of transactions;
if possible, you should use the date instead. I didn't, as the dates you
provided were non-unique.
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23bG$jZiUEHA.2580@.TK2MSFTNGP12.phx.gbl...
> Thank you for your answer Adam, but this did not work for me. Maybe I
didn't
> explain this right. I am not trying to get the highest amount from the
> Column "Amount". I am trying to get the highest balance. Maybe I need to
> create Column balance and calculate from Amount. Is that possible?
> Example:
> In this example i am trying to get the amount of 7000 that is the highest
> balance for this customer
> Record Date Customer Amount
> 1 01.01.04 3344 5000
> 2 01.01.04 3344 2000
> 3 01.01.04 3344 -1000
> 4 01.01.04 3344 -500
>
>
> "Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
> news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> customers.
> this?
>|||Thank you Adam this works great you saved my day. Your first query "Running
balance" can I save this balance into my Customers transaction table? I did
create column called "running balance" in my Customers transaction table.
regards
Benedikt Fridbjornsson
Computer department
SIF Iceland
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OsGd0piUEHA.1172@.TK2MSFTNGP10.phx.gbl...
> Ahh, now I understand...
> The first step is to calculate a running balance:
> SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
> FROM YourTable Tbl1
> JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
> AND Tbl2.Record <= Tbl1.Record
> GROUP BY Tbl1.Customer, Tbl1.Record
>
> Then we can use this as a derived table in an outer query to get the max
per
> customer... I've also added the date to the outer part of the query in
case
> you want that:
> SELECT YourTable.Customer, YourTable.Date, MAX(Tbl0.Balance) As Balance
> FROM YourTable
> JOIN
> (SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
> FROM YourTable Tbl1
> JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
> AND Tbl2.Record <= Tbl1.Record
> GROUP BY Tbl1.Customer, Tbl1.Record
> ) Tbl0(Balance, Customer, Record) ON Tbl0.Record = YourTable.Record
> GROUP BY YourTable.Customer, YourTable.Date
>
> Note, I've used Tbl2.Record in order to determine the order of
transactions;
> if possible, you should use the date instead. I didn't, as the dates you
> provided were non-unique.
>
> "Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
> news:%23bG$jZiUEHA.2580@.TK2MSFTNGP12.phx.gbl...
> didn't
highest[vbcol=seagreen]
5000[vbcol=seagreen]
2000[vbcol=seagreen]
-1000[vbcol=seagreen]
-500[vbcol=seagreen]
>|||Of course...
UPDATE YourTable
SET RunningBalance =
(SELECT SUM(Tbl2.Amount)
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
AND Tbl1.Record = YourTable.Record
GROUP BY Tbl1.Customer, Tbl1.Record)
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:uBVKsurUEHA.2668@.TK2MSFTNGP10.phx.gbl...
> Thank you Adam this works great you saved my day. Your first query
"Running
> balance" can I save this balance into my Customers transaction table? I
did
> create column called "running balance" in my Customers transaction table.
> regards
> Benedikt Fridbjornsson
> Computer department
> SIF Iceland
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:OsGd0piUEHA.1172@.TK2MSFTNGP10.phx.gbl...
> per
> case
> transactions;
you[vbcol=seagreen]
to[vbcol=seagreen]
> highest
> 5000
> 2000
> -1000
> -500
do[vbcol=seagreen]
>sql

High Light Words in Query

I creating an SQL script that will query a database and return the results to an html page. The script searches for a keyword and then displays the results. What I want to do is when the result is returned, I want to highlight the word that was being searched in the result set. I would like the word to be bolded in the query. How can this be done?please, don't cross-post

see http://www.dbforums.com/t1100603.html

High CPU -- identify which query ties up cpu

Hello,
I'm running SQL2K enterprise, recently faced constant high cpu usage by
sqlsrvr process.
How can I identify which query or queries tie up cpu? I mean something more
precise and comprehensible than cpu column in sp_who2.
thanks for any help,
Gary.Gary
I'd run SQL Server profiler to identify long running queries , there are
some events liek CPU,Duration and then try to speed them up
http://www.sql-server-performance.c...or_counters.asp
http://www.sql-server-performance.c...counters_io.asp
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:5079326B-B881-4728-B042-0448C1105D71@.microsoft.com...
> Hello,
> I'm running SQL2K enterprise, recently faced constant high cpu usage by
> sqlsrvr process.
> How can I identify which query or queries tie up cpu? I mean something
> more
> precise and comprehensible than cpu column in sp_who2.
> thanks for any help,
> Gary.

High CPU -- identify which query ties up cpu

Hello,
I'm running SQL2K enterprise, recently faced constant high cpu usage by
sqlsrvr process.
How can I identify which query or queries tie up cpu? I mean something more
precise and comprehensible than cpu column in sp_who2.
thanks for any help,
Gary.Gary
I'd run SQL Server profiler to identify long running queries , there are
some events liek CPU,Duration and then try to speed them up
http://www.sql-server-performance.com/performance_monitor_counters.asp
http://www.sql-server-performance.com/performance_monitor_counters_io.asp
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:5079326B-B881-4728-B042-0448C1105D71@.microsoft.com...
> Hello,
> I'm running SQL2K enterprise, recently faced constant high cpu usage by
> sqlsrvr process.
> How can I identify which query or queries tie up cpu? I mean something
> more
> precise and comprehensible than cpu column in sp_who2.
> thanks for any help,
> Gary.

Hierchical data for xml

Hi
I am trying to build a query that will give me hierarchical data but am
having little success.
How can I change the query below to give me xml data in a hierchical format,
nesting multiple customer addresses under one customer id?
Thanks for your asistance
Regards
Habib
---
use adventureworks
select c.CustomerID, co.FirstName, co.LastName,
a.AddressID, pa.AddressLine1, pa.City, pa.PostalCode
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
join Sales.CustomerAddress a
on c.CustomerID = a.CustomerID
join Person.Address pa
on a.AddressID = pa.AddressID
where c.CustomerID in( 254, 11532)
for xml pathUse a nested query
select c.CustomerID,
(select co.FirstName,
co.LastName,
a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Person.Contact co
inner join Sales.CustomerAddress a on c.CustomerID =
a.CustomerID
inner join Person.Address pa on a.AddressID = pa.AddressID
where c.CustomerID = co.ContactID
for xml path,type)
from Sales.Customer c
where c.CustomerID in( 254, 11532)
for xml path|||Mark,
That was very helpful. Thanks.
The key was to use the Type directive in the nested query. I missed that
when reading BOL.
Regards
Habib
<markc600@.hotmail.com> wrote in message
news:1148646040.698060.201590@.i40g2000cwc.googlegroups.com...
> Use a nested query
> select c.CustomerID,
> (select co.FirstName,
> co.LastName,
> a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Person.Contact co
> inner join Sales.CustomerAddress a on c.CustomerID =
> a.CustomerID
> inner join Person.Address pa on a.AddressID = pa.AddressID
> where c.CustomerID = co.ContactID
> for xml path,type)
> from Sales.Customer c
> where c.CustomerID in( 254, 11532)
> for xml path
>|||Again thanks for your help.
I have a nice little query as below:
How can I wrap this with two additional tags
<request>
<addOrUpdate>
<Customers>
..
</Customers>
</addOrUpdate>
</request>
--
select c.CustomerID, co.FirstName, co.LastName,
(select a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Sales.CustomerAddress a
inner join Person.Address pa on a.AddressID = pa.AddressID
and c.CustomerID = a.CustomerID
for xml path ('address'),type)
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
where c.CustomerID in( 254, 11532)
for xml path ('Customers')
www.DynExtra.com
A resource for the Microsoft Dynamics Community
Featuring FAQs, File Exchange and more
Current member count: 21
---
Share your knowledge. Add your favorite questions and answers
Help add questions to this site! We want Your input.
<markc600@.hotmail.com> wrote in message
news:1148646040.698060.201590@.i40g2000cwc.googlegroups.com...
> Use a nested query
> select c.CustomerID,
> (select co.FirstName,
> co.LastName,
> a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Person.Contact co
> inner join Sales.CustomerAddress a on c.CustomerID =
> a.CustomerID
> inner join Person.Address pa on a.AddressID = pa.AddressID
> where c.CustomerID = co.ContactID
> for xml path,type)
> from Sales.Customer c
> where c.CustomerID in( 254, 11532)
> for xml path
>|||This should work
select (
select c.CustomerID, co.FirstName, co.LastName,
(select a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Sales.CustomerAddress a
inner join Person.Address pa on a.AddressID = pa.AddressID
and c.CustomerID = a.CustomerID
for xml path ('address'),type)
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
where c.CustomerID in( 254, 11532)
for xml path ('Customers') ,root('addOrUpdate'),type)
for xml path ('request')|||
Mark,
I discovered something similar when I was tinkering with the query.
select ( select ...
for XML PATH ('Customer'),type) as [addOrUpdate]
for xml path ('request'),type
But try as i might, I cant seem to get end tag to appear after each
</Customers>
I expect the difference is that the record is committed after each
</addOrUpdate>, so without it wrapping each record, the entire XML file is
either committed or entirely rolled back.
One might argue the relative merits of either approach but the specification
requires the end tags after each record.
How can I get that in?
Thanks for the assistance so far. It has been very helpful.
Regards
Habib
<markc600@.hotmail.com> wrote in message
news:1148715862.851001.69320@.j33g2000cwa.googlegroups.com...
> This should work
> select (
> select c.CustomerID, co.FirstName, co.LastName,
> (select a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Sales.CustomerAddress a
> inner join Person.Address pa on a.AddressID = pa.AddressID
> and c.CustomerID = a.CustomerID
> for xml path ('address'),type)
> from Sales.Customer c
> join Person.Contact co
> on c.CustomerID = co.ContactID
> where c.CustomerID in( 254, 11532)
> for xml path ('Customers') ,root('addOrUpdate'),type)
> for xml path ('request')
>|||Is this what you're after?
select
(select c.CustomerID, co.FirstName, co.LastName,
(select a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Sales.CustomerAddress a
inner join Person.Address pa on a.AddressID = pa.AddressID
and c.CustomerID = a.CustomerID
for xml path ('address'),type)
for xml path ('Customers'),type)
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
where c.CustomerID in( 254, 11532)
for xml path ('addOrUpdate') ,root('request'),type|||Mark,
No, I should have been clearer with what I meant
I have pasted what I want below.
I have tried tinkering with the SQL statement but it seems impossible to me.
Thanks again and regards
Habib
<request>
<addOrUpdate>
<Customers>
<CustomerID>254</CustomerID>
<FirstName>Helen</FirstName>
<LastName>Dennis</LastName>
<address>
<AddressID>185</AddressID>
<AddressLine1>2681 Eagle Peak</AddressLine1>
<City>Bellevue</City>
<PostalCode>98004</PostalCode>
</address>
<address>
<AddressID>861</AddressID>
<AddressLine1>25915 140th Ave Ne</AddressLine1>
<City>Bellevue</City>
<PostalCode>98004</PostalCode>
</address>
</Customers>
</addOrUpdate>
</request>
<request>
<addOrUpdate>
<Customers>
<CustomerID>11532</CustomerID>
<FirstName>Madison</FirstName>
<LastName>White</LastName>
<address>
<AddressID>201</AddressID>
<AddressLine1>6202 Seeno St.</AddressLine1>
<City>Sammamish</City>
<PostalCode>98074</PostalCode>
</address>
<address>
<AddressID>20692</AddressID>
<AddressLine1>6437 Brookview Dr.</AddressLine1>
<City>Redmond</City>
<PostalCode>98052</PostalCode>
</address>
</Customers>
</addOrUpdate>
</request>
<markc600@.hotmail.com> wrote in message
news:1148762909.570472.310590@.38g2000cwa.googlegroups.com...
> Is this what you're after?
>
> select
> (select c.CustomerID, co.FirstName, co.LastName,
> (select a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Sales.CustomerAddress a
> inner join Person.Address pa on a.AddressID = pa.AddressID
> and c.CustomerID = a.CustomerID
> for xml path ('address'),type)
> for xml path ('Customers'),type)
> from Sales.Customer c
> join Person.Contact co
> on c.CustomerID = co.ContactID
> where c.CustomerID in( 254, 11532)
> for xml path ('addOrUpdate') ,root('request'),type
>|||I believe this will work
select
(select
(select c.CustomerID, co.FirstName, co.LastName,
(select a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Sales.CustomerAddress a
inner join Person.Address pa on a.AddressID = pa.AddressID
and c.CustomerID = a.CustomerID
for xml path ('address'),type)
for xml path ('Customers'),type)
for xml path ('addOrUpdate'),type)
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
where c.CustomerID in( 254, 11532)
for xml path ('request')|||YeeHaw! That worked.
Thank you very much for your assistance. You are a genius.
I tried various other combinations such as adOrUpdate/Request and
@.addorUpdate but did not know think of using the FOR XML PATH before the
final from clause. Of course now it makes sense.
Regards
Habib
<markc600@.hotmail.com> wrote in message
news:1148799893.889247.42490@.38g2000cwa.googlegroups.com...
>I believe this will work
> select
> (select
> (select c.CustomerID, co.FirstName, co.LastName,
> (select a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Sales.CustomerAddress a
> inner join Person.Address pa on a.AddressID = pa.AddressID
> and c.CustomerID = a.CustomerID
> for xml path ('address'),type)
> for xml path ('Customers'),type)
> for xml path ('addOrUpdate'),type)
> from Sales.Customer c
> join Person.Contact co
> on c.CustomerID = co.ContactID
> where c.CustomerID in( 254, 11532)
> for xml path ('request')
>

Hierarchichal query in SQL Server 2000

Hi All,

How to implement a hierarchical query in SQL Server 2000.

Example ; I have an Oracle Query as below

SELECT LEVEL,employee_id, manager_id, first_name, last_name
FROM employee
START WITH employee_id = 1
CONNECT BY prior employee_id = manager_id;

I need to get the equivalent of this query in SQL Server 2000 .

In SQL Server 2005 i can achieve this using COMMON TABLE EXPRESSION .

Is there any way to implement this in SQL Server 2000Is there any way to implement this in SQL Server 2000not easily, no

if there is some maximum number of levels to the hierarchy (e.g. never more than eleven levels deep, from top executive to lowliest peon), then you can write a query with that number of LEFT OUTER JOINs|||You can do this efficiently using a loop. Check out this link, and then I will answer any other questions you have:
http://sqlblindman.googlepages.com/returningchildrecords|||Hi Blindman,

I am unable to open the link u provided .|||http://sqlblindman.googlepages.com/returningchildrecords
Are you getting an error?|||no error, url works just fine|||Here is a little tutorial i wrote some time ago that gives you what you want.

http://vbforums.com/showthread.php?t=366078

Hierarchical Top Level BOM Query

The following query gives me some top level and some subassembly levels
returned because they are at the same level in the hierarchy. How can I get
it to keep selecting the subassembly levels until all returns are top level?
SELECT DISTINCT TOP 100 PERCENT Parent_Pfl.PflNumber AS ParentPfl,
Child_Pfl.PflNumber AS ChildProfile
FROM (SELECT ParentPflHierarchy.ParentPflID,
ChildPflHierarchy.ChildPflID
FROM dbo.PflHierarchy ChildPflHierarchy INNER JOIN
dbo.PflHierarchy ParentPflHierarchy ON
ChildPflHierarchy.ParentPflID = ParentPflHierarchy.ChildPflID) DERIVEDTBL
INNER JOIN
dbo.Pfl Parent_Pfl ON DERIVEDTBL.ParentPflID =
Parent_Pfl.PflID INNER JOIN
dbo.Pfl Child_Pfl ON DERIVEDTBL.ChildPflID =
Child_Pfl.PflID
WHERE (Child_Pfl.PflNumber = N'28699')
ORDER BY Parent_Pfl.PflNumber
PflHierarchy Table
PflHierarchyID ParentPflID ChildPflID
1 1 2
2 1 3
3 1 4
4 1 5
5 2 6
6 2 7
7 6 8
8 6 9
9 6 10
Pfl Table
PflID Description
1 This is a top level assy
2 This is a Sub assy
3 This is a child level for a part
4 This is a child level for a part
5 This is a child level for a part
6 This is a Sub assy
7 This is a child level for a part
8 This is a child level for a part
9 This is a child level for a part
10 This is a child level for a partGet a copy of TREES & HIERARCHIES IN SQL. I worked out a BOM using the
nested sets model. It will run at least an order of magnitude faster
and let you do hierarchical summaries in a single query.|||I ordered TREES & HIERARCHIES IN SQL, along with Joe Celko's SQL for
Smarties: Advanced SQL Programming, but they won't be here until Tuesday fro
m
Amazon.com. There's none available in any local stores.
One problem is I'm working with data imported from an SQL Anywhere database,
and I can't make any chages to the tables because they will be running both
databases concurrently.
I'm an application programmer, but I've had to deal with SQL due to lack of
support from our DB administrator.
I'll hopefully have this worked out by then, but I'll let you know if I get
any further information from it.
Thank you
"--CELKO--" wrote:

> Get a copy of TREES & HIERARCHIES IN SQL. I worked out a BOM using the
> nested sets model. It will run at least an order of magnitude faster
> and let you do hierarchical summaries in a single query.
>|||I ordered TREES & HIERARCHIES IN SQL along with SQL FOR SMARTIES from
Amazon.com and they'll be here Tuesday, there are none available from any
local bookstores.
One problem I'm dealing with is the database is imported from SQL Anywhere
and no changes can be made to the structure so they can be moved back and
forth.
I'm primarily an application programmer, but I've had to deal with SQL due
to lack of support from our DB Administrator.
I'll hopefully have this worked out before the books come Tuesday, but I'll
let you know if I get any usefull information from them.
Thanks
"--CELKO--" wrote:

> Get a copy of TREES & HIERARCHIES IN SQL. I worked out a BOM using the
> nested sets model. It will run at least an order of magnitude faster
> and let you do hierarchical summaries in a single query.
>|||Check out solutions to BOM in the following whitepaper:
(URL may wrap)
http://msdn.microsoft.com/library/d...TSQLEnhance.asp
Even though the solutions in the paper use Recursive CTEs in SQL Server
2005, they can all be easily converted to UDFs in SQL Server 2000 applying
the same algorithms. If you have any trouble converting to UDFs, let me
know, and I can give you examples.
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"CapeCAD" <CapeCAD@.discussions.microsoft.com> wrote in message
news:0C850AAC-50A8-4BBF-A60A-366D510B7FA9@.microsoft.com...
> The following query gives me some top level and some subassembly levels
> returned because they are at the same level in the hierarchy. How can I
> get
> it to keep selecting the subassembly levels until all returns are top
> level?
> SELECT DISTINCT TOP 100 PERCENT Parent_Pfl.PflNumber AS ParentPfl,
> Child_Pfl.PflNumber AS ChildProfile
> FROM (SELECT ParentPflHierarchy.ParentPflID,
> ChildPflHierarchy.ChildPflID
> FROM dbo.PflHierarchy ChildPflHierarchy INNER JOIN
> dbo.PflHierarchy ParentPflHierarchy ON
> ChildPflHierarchy.ParentPflID = ParentPflHierarchy.ChildPflID) DERIVEDTBL
> INNER JOIN
> dbo.Pfl Parent_Pfl ON DERIVEDTBL.ParentPflID =
> Parent_Pfl.PflID INNER JOIN
> dbo.Pfl Child_Pfl ON DERIVEDTBL.ChildPflID =
> Child_Pfl.PflID
> WHERE (Child_Pfl.PflNumber = N'28699')
> ORDER BY Parent_Pfl.PflNumber
> PflHierarchy Table
> PflHierarchyID ParentPflID ChildPflID
> 1 1 2
> 2 1 3
> 3 1 4
> 4 1 5
> 5 2 6
> 6 2 7
> 7 6 8
> 8 6 9
> 9 6 10
> Pfl Table
> PflID Description
> 1 This is a top level assy
> 2 This is a Sub assy
> 3 This is a child level for a part
> 4 This is a child level for a part
> 5 This is a child level for a part
> 6 This is a Sub assy
> 7 This is a child level for a part
> 8 This is a child level for a part
> 9 This is a child level for a part
> 10 This is a child level for a part
>|||I would appreciate seeing some examples, I think that may help.
I've taken 15 different approaches now and the results are all the same. If
an item is 3 levels deep in one BOM and 7 levels deep in another: I get the
top level from the 3 level deep, and the 4th level from the 7 level deep.
Thank you
"Itzik Ben-Gan" wrote:

> Check out solutions to BOM in the following whitepaper:
> (URL may wrap)
> http://msdn.microsoft.com/library/d...TSQLEnhance.asp
> Even though the solutions in the paper use Recursive CTEs in SQL Server
> 2005, they can all be easily converted to UDFs in SQL Server 2000 applying
> the same algorithms. If you have any trouble converting to UDFs, let me
> know, and I can give you examples.
> Cheers,
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "CapeCAD" <CapeCAD@.discussions.microsoft.com> wrote in message
> news:0C850AAC-50A8-4BBF-A60A-366D510B7FA9@.microsoft.com...
>
>|||Sure,
Here are solutions to "Assemblies containing a certain sub-item" using both
Recursive CTEs in SQL Server 2005 and using UDFs in SQL Server 2000:
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('BOM') IS NOT NULL
DROP TABLE BOM;
GO
IF OBJECT_ID('Items') IS NOT NULL
DROP TABLE Items;
GO
CREATE TABLE Items
(
itemid VARCHAR(5) NOT NULL PRIMARY KEY,
itemname VARCHAR(25) NOT NULL,
/* other columns, e.g., unit_price, measurement_unit */
);
CREATE TABLE BOM
(
itemid VARCHAR(5) NOT NULL REFERENCES Items,
assemblyid VARCHAR(5) NULL REFERENCES Items,
qty INT NOT NULL
/* other columns, e.g., quantity */
UNIQUE CLUSTERED(itemid, assemblyid),
CHECK (itemid <> assemblyid)
);
INSERT INTO Items(itemid, itemname) VALUES('A', 'Item A');
INSERT INTO Items(itemid, itemname) VALUES('B', 'Item B');
INSERT INTO Items(itemid, itemname) VALUES('C', 'Item C');
INSERT INTO Items(itemid, itemname) VALUES('D', 'Item D');
INSERT INTO Items(itemid, itemname) VALUES('E', 'Item E');
INSERT INTO Items(itemid, itemname) VALUES('F', 'Item F');
INSERT INTO Items(itemid, itemname) VALUES('G', 'Item G');
INSERT INTO Items(itemid, itemname) VALUES('H', 'Item H');
INSERT INTO Items(itemid, itemname) VALUES('I', 'Item I');
INSERT INTO Items(itemid, itemname) VALUES('J', 'Item J');
INSERT INTO Items(itemid, itemname) VALUES('K', 'Item K');
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'A', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'B', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'C', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'D', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'E', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'F', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'G', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'H', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'I', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'J', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'K', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('E', 'J', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('C', 'E', 3);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('A', 'C', 2);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('H', 'C', 4);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('C', 'B', 2);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('B', 'F', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('B', 'G', 3);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('A', 'B', 2);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('A', 'D', 2);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('H', 'I', 1);
GO
-- SQL Server 2005 Solution
DECLARE @.itemid AS VARCHAR(5);
SET @.itemid = 'F';
WITH BOMCTE
AS
(
SELECT itemid, assemblyid, qty,
0 AS lvl, CAST('.' + itemid + '.' AS VARCHAR(900)) AS path
FROM BOM
WHERE itemid = @.itemid
UNION ALL
SELECT BOM.itemid, BOM.assemblyid, BOMCTE.qty * BOM.qty,
BOMCTE.lvl + 1, CAST('.' + BOM.itemid + BOMCTE.path AS VARCHAR(900))
FROM BOMCTE
JOIN BOM
ON BOM.itemid = BOMCTE.assemblyid
)
SELECT itemid, qty, lvl, path
FROM BOMCTE
WHERE assemblyid IS NULL
AND lvl > 0;
Output:
itemid qty lvl path
-- -- -- --
B 1 1 .B.F.
C 2 2 .C.B.F.
H 8 3 .H.C.B.F.
A 4 3 .A.C.B.F.
A 2 2 .A.B.F.
-- SQL Server 2000 Solution
IF OBJECT_ID('fn_AssembliesContainingItem')
IS NOT NULL
DROP FUNCTION fn_AssembliesContainingItem;
GO
CREATE FUNCTION fn_AssembliesContainingItem(@.itemid AS VARCHAR(5))
RETURNS @.T TABLE
(
itemid VARCHAR(5) NOT NULL,
assemblyid VARCHAR(5) NULL,
qty INT NOT NULL,
lvl INT NOT NULL,
path VARCHAR(900) NOT NULL,
UNIQUE CLUSTERED(lvl, assemblyid, itemid)
)
AS
BEGIN
DECLARE @.lvl AS INT;
SET @.lvl = 0;
INSERT INTO @.T
SELECT itemid, assemblyid, qty, @.lvl, '.' + itemid + '.'
FROM BOM
WHERE itemid = @.itemid;
WHILE @.@.rowcount > 0
BEGIN
SET @.lvl = @.lvl + 1;
INSERT INTO @.T
SELECT BOM.itemid, BOM.assemblyid, T.qty * BOM.qty,
@.lvl, '.' + BOM.itemid + T.path
FROM @.T AS T JOIN BOM
ON T.lvl = @.lvl - 1
AND BOM.itemid = T.assemblyid;
END
DELETE FROM @.T WHERE lvl = 0 OR assemblyid IS NOT NULL;
RETURN
END
GO
SELECT itemid, qty, lvl, path FROM fn_AssembliesContainingItem('F');
Output:
itemid qty lvl path
-- -- -- --
B 1 1 .B.F.
A 2 2 .A.B.F.
C 2 2 .C.B.F.
A 4 3 .A.C.B.F.
H 8 3 .H.C.B.F.
BG, SQL Server MVP
www.SolidQualityLearning.com
"CapeCAD" <CapeCAD@.discussions.microsoft.com> wrote in message
news:A9C665B8-2CE1-4A88-8008-F29AB2D0B7B0@.microsoft.com...
>I would appreciate seeing some examples, I think that may help.
> I've taken 15 different approaches now and the results are all the same.
> If
> an item is 3 levels deep in one BOM and 7 levels deep in another: I get
> the
> top level from the 3 level deep, and the 4th level from the 7 level deep.
> Thank you
> "Itzik Ben-Gan" wrote:
>|||I'm not sure yet what I might have transposed wrong, but when I tried the
following:
CREATE FUNCTION [dbo].[fn_AssembliesContainingItem]
(@.itemid AS VARCHAR(14))
RETURNS @.T TABLE
(
ChildPflID VARCHAR(14) NOT NULL,
ParentPflID VARCHAR(14) NULL,
lvl INT NOT NULL,
UNIQUE CLUSTERED(lvl, ParentPflID, ChildPflID)
)
AS
BEGIN
DECLARE @.lvl AS INT;
SET @.lvl = 0;
INSERT INTO @.T
SELECT PflHierarchy.ChildPflID, PflHierarchy.ParentPflID, @.lvl
FROM PflHierarchy
WHERE PflHierarchy.ChildPflID = @.itemid;
WHILE @.@.rowcount > 0
BEGIN
SET @.lvl = @.lvl + 1;
INSERT INTO @.T
SELECT PflHierarchy.ChildPflID, PflHierarchy.ParentPflID, @.lvl
FROM @.T AS T JOIN PflHierarchy
ON T.lvl = @.lvl - 1
AND PflHierarchy.ChildPflID = T.ParentPflID;
END
DELETE FROM @.T WHERE lvl = 0 OR ParentPflID IS NOT NULL;
RETURN
END
I get the following error message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of UNIQUE KEY
constraint 'UQ__@.T__20A07422'. Cannot insert duplicate key in object
'#1FAC4FE9'.
My books by Joe Celko just came in, so I'll be reading them for more ideas
to try.
"Itzik Ben-Gan" wrote:

> Sure,
> Here are solutions to "Assemblies containing a certain sub-item" using bot
h
> Recursive CTEs in SQL Server 2005 and using UDFs in SQL Server 2000:
> SET NOCOUNT ON;
> USE tempdb;
> GO
> IF OBJECT_ID('BOM') IS NOT NULL
> DROP TABLE BOM;
> GO
> IF OBJECT_ID('Items') IS NOT NULL
> DROP TABLE Items;
> GO
> CREATE TABLE Items
> (
> itemid VARCHAR(5) NOT NULL PRIMARY KEY,
> itemname VARCHAR(25) NOT NULL,
> /* other columns, e.g., unit_price, measurement_unit */
> );
> CREATE TABLE BOM
> (
> itemid VARCHAR(5) NOT NULL REFERENCES Items,
> assemblyid VARCHAR(5) NULL REFERENCES Items,
> qty INT NOT NULL
> /* other columns, e.g., quantity */
> UNIQUE CLUSTERED(itemid, assemblyid),
> CHECK (itemid <> assemblyid)
> );
> INSERT INTO Items(itemid, itemname) VALUES('A', 'Item A');
> INSERT INTO Items(itemid, itemname) VALUES('B', 'Item B');
> INSERT INTO Items(itemid, itemname) VALUES('C', 'Item C');
> INSERT INTO Items(itemid, itemname) VALUES('D', 'Item D');
> INSERT INTO Items(itemid, itemname) VALUES('E', 'Item E');
> INSERT INTO Items(itemid, itemname) VALUES('F', 'Item F');
> INSERT INTO Items(itemid, itemname) VALUES('G', 'Item G');
> INSERT INTO Items(itemid, itemname) VALUES('H', 'Item H');
> INSERT INTO Items(itemid, itemname) VALUES('I', 'Item I');
> INSERT INTO Items(itemid, itemname) VALUES('J', 'Item J');
> INSERT INTO Items(itemid, itemname) VALUES('K', 'Item K');
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'A', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'B', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'C', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'D', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'E', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'F', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'G', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'H', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'I', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'J', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'K', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('E', 'J', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('C', 'E', 3);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('A', 'C', 2);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('H', 'C', 4);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('C', 'B', 2);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('B', 'F', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('B', 'G', 3);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('A', 'B', 2);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('A', 'D', 2);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('H', 'I', 1);
> GO
> -- SQL Server 2005 Solution
> DECLARE @.itemid AS VARCHAR(5);
> SET @.itemid = 'F';
> WITH BOMCTE
> AS
> (
> SELECT itemid, assemblyid, qty,
> 0 AS lvl, CAST('.' + itemid + '.' AS VARCHAR(900)) AS path
> FROM BOM
> WHERE itemid = @.itemid
> UNION ALL
> SELECT BOM.itemid, BOM.assemblyid, BOMCTE.qty * BOM.qty,
> BOMCTE.lvl + 1, CAST('.' + BOM.itemid + BOMCTE.path AS VARCHAR(900))
> FROM BOMCTE
> JOIN BOM
> ON BOM.itemid = BOMCTE.assemblyid
> )
> SELECT itemid, qty, lvl, path
> FROM BOMCTE
> WHERE assemblyid IS NULL
> AND lvl > 0;
> Output:
> itemid qty lvl path
> -- -- -- --
> B 1 1 .B.F.
> C 2 2 .C.B.F.
> H 8 3 .H.C.B.F.
> A 4 3 .A.C.B.F.
> A 2 2 .A.B.F.
> -- SQL Server 2000 Solution
> IF OBJECT_ID('fn_AssembliesContainingItem')
IS NOT NULL
> DROP FUNCTION fn_AssembliesContainingItem;
> GO
> CREATE FUNCTION fn_AssembliesContainingItem(@.itemid AS VARCHAR(5))
> RETURNS @.T TABLE
> (
> itemid VARCHAR(5) NOT NULL,
> assemblyid VARCHAR(5) NULL,
> qty INT NOT NULL,
> lvl INT NOT NULL,
> path VARCHAR(900) NOT NULL,
> UNIQUE CLUSTERED(lvl, assemblyid, itemid)
> )
> AS
> BEGIN
> DECLARE @.lvl AS INT;
> SET @.lvl = 0;
> INSERT INTO @.T
> SELECT itemid, assemblyid, qty, @.lvl, '.' + itemid + '.'
> FROM BOM
> WHERE itemid = @.itemid;
> WHILE @.@.rowcount > 0
> BEGIN
> SET @.lvl = @.lvl + 1;
> INSERT INTO @.T
> SELECT BOM.itemid, BOM.assemblyid, T.qty * BOM.qty,
> @.lvl, '.' + BOM.itemid + T.path
> FROM @.T AS T JOIN BOM
> ON T.lvl = @.lvl - 1
> AND BOM.itemid = T.assemblyid;
> END
> DELETE FROM @.T WHERE lvl = 0 OR assemblyid IS NOT NULL;
> RETURN
> END
> GO
> SELECT itemid, qty, lvl, path FROM fn_AssembliesContainingItem('F');
> Output:
> itemid qty lvl path
> -- -- -- --
> B 1 1 .B.F.
> A 2 2 .A.B.F.
> C 2 2 .C.B.F.
> A 4 3 .A.C.B.F.
> H 8 3 .H.C.B.F.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "CapeCAD" <CapeCAD@.discussions.microsoft.com> wrote in message
> news:A9C665B8-2CE1-4A88-8008-F29AB2D0B7B0@.microsoft.com...
>
>|||Joe,
I've read your books, and perhaps I was not clear on what my initial problem
is:
1. I am working with existing tables and existing data
2. I am working with multiple parent Bills Of Materials
3. I am searching for All top level items when given a part item
The following demonstrates the problem:
CREATE TABLE [Hierarchy] (
[PflHierarchyID] [int] NOT NULL ,
[ParentPflID] [int] NOT NULL ,
[ChildPflID] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Part] (
[PflID] [int] NOT NULL ,
[PflNumber] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
INSERT INTO [Hierarchy] VALUES(1,1,2)
INSERT INTO [Hierarchy] VALUES(2,1,3)
INSERT INTO [Hierarchy] VALUES(3,3,2)
INSERT INTO [Hierarchy] VALUES(4,3,4)
INSERT INTO [Hierarchy] VALUES(5,5,2)
INSERT INTO [Hierarchy] VALUES(6,5,4)
INSERT INTO [Part] VALUES(1,'1234','PIII Motherboard')
INSERT INTO [Part] VALUES(2,'341','Res 1K 0805')
INSERT INTO [Part] VALUES(3,'2345','Video Card')
INSERT INTO [Part] VALUES(4,'421','Cap 1 pF 0805')
INSERT INTO [Part] VALUES(5,'12345','P4 Motherboard')
This view should return '1234','2345', and '12345': Returns '1234'
SELECT DISTINCT
TOP 100 PERCENT Parent_Pfl.PflNumber AS ParentPfl,
Parent_Pfl.Description AS ParentDescription, Child_Pfl.PflNumber AS
ChildProfile,
Child_Pfl.Description AS ChildDescription
FROM (SELECT ParentPflHierarchy.ParentPflID,
ChildPflHierarchy.ChildPflID
FROM Hierarchy ChildPflHierarchy INNER JOIN
Hierarchy ParentPflHierarchy
ON ParentPflHierarchy.ChildPflID = ChildPflHierarchy.ParentPflID)
HierarchyTBL INNER JOIN
dbo.Part Child_Pfl ON HierarchyTBL.ChildPflID =
Child_Pfl.PflID INNER JOIN
dbo.Part Parent_Pfl ON HierarchyTBL.ParentPflID =
Parent_Pfl.PflID
WHERE (Child_Pfl.PflNumber = N'421')
Any help would be appreciated
"--CELKO--" wrote:

> Get a copy of TREES & HIERARCHIES IN SQL. I worked out a BOM using the
> nested sets model. It will run at least an order of magnitude faster
> and let you do hierarchical summaries in a single query.
>|||I created a UNIQUE constraint to allow filtering using an index, but
multiple paths leading to the same node might end up with dups (which are
valid).
So either remove the constraint, or add an identity column at the end if you
want to allow using an index to filter the previous level:
CREATE FUNCTION fn_AssembliesContainingItem(@.itemid AS VARCHAR(5))
RETURNS @.T TABLE
(
itemid VARCHAR(5) NOT NULL,
assemblyid VARCHAR(5) NULL,
qty INT NOT NULL,
lvl INT NOT NULL,
path VARCHAR(900) NOT NULL,
surkey INT NOT NULL IDENTITY,
UNIQUE CLUSTERED(lvl, assemblyid, itemid, surkey)
)
AS
BEGIN
DECLARE @.lvl AS INT;
SET @.lvl = 0;
INSERT INTO @.T
SELECT itemid, assemblyid, qty, @.lvl, '.' + itemid + '.'
FROM BOM
WHERE itemid = @.itemid;
WHILE @.@.rowcount > 0
BEGIN
SET @.lvl = @.lvl + 1;
INSERT INTO @.T
SELECT BOM.itemid, BOM.assemblyid, T.qty * BOM.qty,
@.lvl, '.' + BOM.itemid + T.path
FROM @.T AS T JOIN BOM
ON T.lvl = @.lvl - 1
AND BOM.itemid = T.assemblyid;
END
DELETE FROM @.T WHERE lvl = 0 OR assemblyid IS NOT NULL;
RETURN
END
GO
BG, SQL Server MVP
www.SolidQualityLearning.com
"CapeCAD" <CapeCAD@.discussions.microsoft.com> wrote in message
news:8E1A0DAE-D71A-4849-8FA3-75D3043AD644@.microsoft.com...
> I'm not sure yet what I might have transposed wrong, but when I tried the
> following:
> CREATE FUNCTION [dbo].[fn_AssembliesContainingItem]
> (@.itemid AS VARCHAR(14))
> RETURNS @.T TABLE
> (
> ChildPflID VARCHAR(14) NOT NULL,
> ParentPflID VARCHAR(14) NULL,
> lvl INT NOT NULL,
> UNIQUE CLUSTERED(lvl, ParentPflID, ChildPflID)
> )
> AS
> BEGIN
> DECLARE @.lvl AS INT;
> SET @.lvl = 0;
> INSERT INTO @.T
> SELECT PflHierarchy.ChildPflID, PflHierarchy.ParentPflID, @.lvl
> FROM PflHierarchy
> WHERE PflHierarchy.ChildPflID = @.itemid;
> WHILE @.@.rowcount > 0
> BEGIN
> SET @.lvl = @.lvl + 1;
> INSERT INTO @.T
> SELECT PflHierarchy.ChildPflID, PflHierarchy.ParentPflID, @.lvl
> FROM @.T AS T JOIN PflHierarchy
> ON T.lvl = @.lvl - 1
> AND PflHierarchy.ChildPflID = T.ParentPflID;
> END
> DELETE FROM @.T WHERE lvl = 0 OR ParentPflID IS NOT NULL;
> RETURN
> END
> I get the following error message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of UNIQUE KEY
> constraint 'UQ__@.T__20A07422'. Cannot insert duplicate key in object
> '#1FAC4FE9'.
> My books by Joe Celko just came in, so I'll be reading them for more ideas
> to try.
> "Itzik Ben-Gan" wrote:
>

Hierarchical Resultset Sorting

I have a query like this

with TempCTE(id, Name, level, sortcol)
As
(
Select id, Name, 0 as level,
cast(cast( id AS BINARY(4)) as varbinary(100)) sortcol
from Table1
where id = 1

union all

Select id, Name, 0 as level,
cast(sortcol + cast( id AS BINARY(4)) as varbinary(100)) sortcol

from Table1 inner join TempCTE on TempCTE.id = Table1.parentid
)

select * from TempCTE order by sortcol

My problem is I want to sort this hierarchical resultset further on name like

aaa
--aaaa
--bbbb
--cccc
aaaaa
bbbbb
--dddd
aaaaa
bbbbb
bbb
--aaaa
--bbbb

Thanks

You need to basically add the appropriate columns in the ORDER BY clause. Based on your query above, you need to probably do ORDER BY sortcol, name. See below example based on a query using AdventureWorks sample tables:

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT replicate('.', EmployeeLevel* 10) + right(replicate('0', 10) + cast(coalesce(ManagerID, 0) as varchar), 10) as Mgr
FROM DirectReports
ORDER BY EmployeeLevel, ManagerID, EmployeeID;
GO

Hierarchical Query

I have a table representing a tree structure - self referencing table. For
any node, I need to traverse upwards until root and retreives all the
traversed nodes.
With SQL only, how could I do this? In Oracle, there is START WITH, CONNECT
BY clause to help me. What is the equivalence in sql server?Here is an alternative method
http://toponewithties.blogspot.com/
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"as" <none@.asgmeail.com> wrote in message
news:42928048$1_1@.rain.i-cable.com...
>I have a table representing a tree structure - self referencing table. For
> any node, I need to traverse upwards until root and retreives all the
> traversed nodes.
> With SQL only, how could I do this? In Oracle, there is START WITH,
> CONNECT
> BY clause to help me. What is the equivalence in sql server?
>|||See Itzik Ben-Gan's example
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
"as" <none@.asgmeail.com> wrote in message
news:42928048$1_1@.rain.i-cable.com...
> I have a table representing a tree structure - self referencing table.
For
> any node, I need to traverse upwards until root and retreives all the
> traversed nodes.
> With SQL only, how could I do this? In Oracle, there is START WITH,
CONNECT
> BY clause to help me. What is the equivalence in sql server?
>|||Hi as,
Can you try the something like following which i implemented while working
on Hierarchical query (I converted this to a stored proc. This code example
is given in Books online, with little modification it worked fine for me.)
--Begin--
--
Accessing and Changing Relational Data
Expanding Hierarchies
Databases often store hierarchical information. For example, the following
data is a hierarchical representation of regions of the world. This
representation does not clearly show the structure implied by the data.
Parent Child
-- --
World Europe
World North America
Europe France
France Paris
North America United States
North America Canada
United States New York
United States Washington
New York New York City
Washington Redmond
This example is easier to interpret:
World
North America
Canada
United States
Washington
Redmond
New York
New York City
Europe
France
Paris
The following Transact-SQL procedure expands an encoded hierarchy to any
arbitrary depth. Although Transact-SQL supports recursion, it is more
efficient to use a temporary table as a stack to keep track of all of the
items for which processing has begun but is not complete. When processing is
complete for a particular item, it is removed from the stack. New items are
added to the stack as they are identified.
CREATE PROCEDURE expand (@.current char(20)) as
SET NOCOUNT ON
DECLARE @.level int, @.line char(20)
CREATE TABLE #stack (item char(20), level int)
INSERT INTO #stack VALUES (@.current, 1)
SELECT @.level = 1
WHILE @.level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @.level)
BEGIN
SELECT @.current = item
FROM #stack
WHERE level = @.level
SELECT @.line = space(@.level - 1) + @.current
PRINT @.line
DELETE FROM #stack
WHERE level = @.level
AND item = @.current
INSERT #stack
SELECT child, @.level + 1
FROM hierarchy
WHERE parent = @.current
IF @.@.ROWCOUNT > 0
SELECT @.level = @.level + 1
END
ELSE
SELECT @.level = @.level - 1
END -- WHILE
The input parameter (@.current) indicates the place in the hierarchy to
start. It also keeps track of the current item in the main loop.
The local variables used are @.level, which keeps track of the current level
in the hierarchy, and @.line, which is a work area used to construct the
indented line.
The SET NOCOUNT ON statement avoids cluttering the output with ROWCOUNT
messages from each SELECT.
The temporary table, #stack, is created and primed with the item identifier
of the starting point in the hierarchy, and @.level is set to match. The leve
l
column in #stack allows the same item to appear at multiple levels in the
database. Although this situation does not apply to the geographic data in
the example, it can apply in other examples.
In this example, when @.level is greater than 0, the procedure follows these
steps:
If there are any items in the stack at the current level (@.level), the
procedure chooses one and calls it @.current.
Indents the item @.level spaces, and then prints the item.
Deletes the item from the stack so it will not be processed again, and then
adds all its child items to the stack at the next level (@.level + 1). This i
s
the only place where the hierarchy table (#stack) is used.
With a conventional programming language, you would have to find each child
item and add it to the stack individually. With Transact-SQL, you can find
all child items and add them with a single statement, avoiding another neste
d
loop.
If there are child items (IF @.@.ROWCOUNT > 0), descends one level to process
them (@.level = @.level + 1); otherwise, continues processing at the current
level.
If there are no items on the stack awaiting processing at the current level,
goes back one level to see if there are any awaiting processing at the
previous level (@.level = @.level - 1). When there is no previous level, the
expansion is complete.
--End---
--
Regards,
Siva
"as" wrote:

> I have a table representing a tree structure - self referencing table. Fo
r
> any node, I need to traverse upwards until root and retreives all the
> traversed nodes.
> With SQL only, how could I do this? In Oracle, there is START WITH, CONNE
CT
> BY clause to help me. What is the equivalence in sql server?
>
>|||>> What is the equivalent in sql server? <<
The equivalent is a cursor becasue that what Oracle does under the
covers in their proprietary syntax. Instead:
1) Get a copy of TREES & HIERACHIES IN SQL from Amazon.com. There are
much better ways of modeling a tree.
2) Google for "Nested set model" in this newsgroup. There is no need
for procedural code.l|||Joe,
I would like to know your opinion about the approach discussed in
http://toponewithties.blogspot.com/ (Path Enumeration using Prime number
Products)
Regards
Roji
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116960769.637939.154330@.z14g2000cwz.googlegroups.com...
> The equivalent is a cursor becasue that what Oracle does under the
> covers in their proprietary syntax. Instead:
> 1) Get a copy of TREES & HIERACHIES IN SQL from Amazon.com. There are
> much better ways of modeling a tree.
> 2) Google for "Nested set model" in this newsgroup. There is no need
> for procedural code.l
>|||I wish I had seen this before finishing TREES & HIERARCHIES IN SQL.
The only problem I can see is the size of the primes as the trees get
larger, but we are living in a 64-bit world now. Since the math is
simple integer division and multiplication, the speed is probably
pretty good.
Random thought: if we give each node a prime in a general graph, then a
cycle|||Thanks Joe,
I am a happy man.
You can consider this for the next version of TREES & HIERARCHIES IN SQL :)
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1117037337.540731.73370@.g14g2000cwa.googlegroups.com...
>I wish I had seen this before finishing TREES & HIERARCHIES IN SQL.
> The only problem I can see is the size of the primes as the trees get
> larger, but we are living in a 64-bit world now. Since the math is
> simple integer division and multiplication, the speed is probably
> pretty good.
> Random thought: if we give each node a prime in a general graph, then a
> cycle
>|||--CELKO-- wrote:
> I wish I had seen this before finishing TREES & HIERARCHIES IN SQL.
> The only problem I can see is the size of the primes as the trees get
> larger, but we are living in a 64-bit world now. Since the math is
> simple integer division and multiplication, the speed is probably
> pretty good.
Kendall Willets suggested this method on comp.databases.theory some
time ago.

> Random thought: if we give each node a prime in a general graph, then a
> cycle
Prime numbers set with "divided by" binary relation is a partial order.
More specifically it is a lattice. An arbitrary DAG can be embedded
into a lattice. However, the prime numbers encoding for graphs is
volatile. Adding a node into a graph would force to recalculate
encodings for large graph fragment. Plus, how to handle acyclic graphs?|||Mikito Harakiri wrote:
> --CELKO-- wrote:
> Kendall Willets suggested this method on comp.databases.theory some
> time ago.
>
> Prime numbers set with "divided by" binary relation is a partial order.
> More specifically it is a lattice. An arbitrary DAG can be embedded
> into a lattice. However, the prime numbers encoding for graphs is
> volatile. Adding a node into a graph would force to recalculate
> encodings for large graph fragment. Plus, how to handle acyclic graphs?
Could the access path be indexed? Imagine that we don't have this silly
64 bit limit and you are presented with a node encoded with a number
3107418240490043721350750035888567930037
3460228427
2754572016194882320644051808150455634682
9671723286
7824379162728380334154710731085019195485
2900733772
4822783525742386454014691736602477652346
609
(RSA-640) and are asked what are the ancestors. Can you answer that in
a reasonable amount of time.
I'm cheating of course, if the primes are small you can factor the
number reasonably fast. Still, what about the access path? For
comparison, in case of nested sets we are talking about index range
scan (at least one way).