Transact SQL :: Recursive Query To Assign Values

Jul 30, 2015

I'm trying to find it difficult to use recursice CTEs or better solution for a special request below. There are two tables 1) @sizes serves as a lookup or reference for right drive and 2) @test is sample data with different sizes. Assume that I want to evenly distribute the drive letters from table1 to table2 by checking the size available.

E.g.: for the first record in  @test; id = 1 where the size is 50 and it fits in Y: drive -- left over space in Y: = 50

id=2, size 2.5, space available from left over = 50 - 2.5 = 47.5 which again fits into Y:
id = 3, size 51, cannot in fit in Y: drive as there is enough space in Y: to allocate (51 > 47.5)

so pick the next drive check on availability again

DECLARE @sizes TABLE (id TINYINT, size DECIMAL(5,2), drive VARCHAR(3))
INSERT INTO @Sizes
SELECT 1,100.00,'Y:'
UNION ALL
SELECT 2,80.85,'Z:'

[Code] ....

-- My output should look like

col1  ,  val ,   path
A          50      Y:
B           2.5    Y:
C          51     Z:
D          2.6    Y:
E          52      Z:
F           2.7    Y:

View 5 Replies


ADVERTISEMENT

Transact SQL :: Types Don't Match Between Anchor And Recursive Part In Column ParentID Of Recursive Query

Aug 25, 2015

Msg 240, Level 16, State 1, Line 14

Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,

DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),

[code]....

View 2 Replies View Related

Transact SQL :: Recursive CTE Parent Child Query

Oct 31, 2015

I have a Recursive CTE for TFS database which gives me below results:

Parent (User Story) level 0
             -------------.Task(Dev) and ask(QA) --

level  1
    BUG level 2
                ---------------Task (Dev) and Task(QA)
level2 

So, My ParentID column keeps two ParentId one for User story which keeps the child tasks and child bugs and another for Bug Child tasks

I need to update this results and want to see all of the tasks under User story so the result I want is:

User Story:

 Task(dev and QA)
 Task (Dev and QA ) but its should know that these tasks are the bug tasks.

View 10 Replies View Related

Transact SQL :: Recursive Query To Find Child Of A Parent Until Last Leaf

Oct 22, 2015

I need to write recursive query to find child of a parent until the last leaf. Below is my code. 

;WITH Parent AS(
SELECT [ParentID],Value
FROM[DynamicColsValues_TP1]
WHEREValue IS null
UNION ALL
SELECT t1.[ParentID],T1.Value, FROM DynamicColsValues_TP1 t1 INNER JOIN Parent t2
ON t1.[ParentID]=t2.[ParentID]
)
SELECT * FROM Parent option (maxrecursion 0)

When I execute this code. It is returning me millions of rows. Whereas  i have only 20 rows in a table max 40 rows it should return.

View 7 Replies View Related

Transact SQL :: Get Not Existing Values In Query

Jun 23, 2015

I have a table like this:

ID Order Date .....
1 Bla 2015-01-13 12:00:45.2713558 .
2 Mio 2015-01-13 12:05:45.2713558 .
3 Tala 2015-01-13 14:00:45.2713558 .
4 Bla 2015-01-13 15:00:45.2713558 .
5 Mio 2015-01-13 20:00:45.2713558 .
6 Bla 2015-01-15 17:00:45.2713558 .
7 Bla 2015-01-15 19:00:45.2713558 .

[code]...

I tryed several things with CASE, IF, EXISTS,... but I had no success.

View 13 Replies View Related

Transact SQL :: How To Query A Table With Hidden Values

Aug 31, 2015

I have a table called USERS, some of its records are marked as  hiddenRcord, I want to load those records in a custom page in my asp.net webpage with paging enabled, each page contains 10 records. 

I use the statement "SELECT Top 10 tableID,userName FROM USERS WHERE (hiddenRecord=0 AND tableID>@tableID)"

The pagination has 5 links (First,2,3,4,Last), I can of course put the last tableID in link number 2, but I don't know how to do it for the links (3,4,Last).

View 3 Replies View Related

Transact SQL :: How To Use Multiple Values In Where Condition In Query

Jun 17, 2015

I have a query where i would calculate the counts of three Servers 

declare @Managed float
declare @Active float
DECLARE @Values varchar(1000)
SET @Values = 'WE,EE,CO'
select @Managed = count(Name0) from v_R_System where Operating_System_Name_and0 like '%Workstation%' 

[Code] ...

Here i need output like below

Workgroup Managed Active
'WE' 255 ,400
'EE' 300 ,450
'CO' 155, 600

So how to use these three values in the where condition when i use the where clause i have put in condition it will give me the subquery returns more than one value,so how should i use this scenario to accomplish this output?

View 3 Replies View Related

Transact SQL :: Recursive Top 100 Rows

Nov 6, 2015

I need limit on recursion. I have a recursion query. which gives me a parent child record set. I want my application to fetch only top 100 rows. In those 100 rows the parent child set should not get break. Even if it gives 105 rows also fine but it should have entire parent child structure.

If you see my below table. where null is the table which dont have parents but have the child.

ITEMID parentItemid
6 NULL
7 NULL
8 6
9 7
12 NULL
13 12
14 8
16 6
17 16

And it is temp table #test1and my recursive query.

;WITH Cte AS
(
SELECT ItemID,ParentItemID,CAST(ItemID AS VARCHAR(200)) [path] FROM #Test1 WHERE ParentItemID IS NULL
UNION ALL
SELECT t.ItemID,t.ParentItemID, CAST([path]+'.'+CAST(t.ItemID AS VARCHAR(20)) AS VARCHAR(200)) FROM Cte c JOIN #Test1 t ON C.ItemID=t.ParentItemID
)
SELECT * FROM Cte ORDER BY path ASC

Here how can i get top 100 parent child set. 

View 4 Replies View Related

Transact SQL :: Repeatedly Assign Set Of Dates To Each ID?

Nov 18, 2015

I have a table with 3 columns , let say (PatientID  int, AppointmentDate date, PatientName varchar(30))

My source data looks in below way..

PatientID        AppointmentDate      PatientName
  1                 01/01/2012          Tom
  2                 01/10/2012          Sam
  3                 02/15/2012          John

I need output in below way..

PatientID        AppointmentDate      PatientName
  1                 01/01/2012          Tom    (actual patient record)
  null              01/10/2012          Tom
  null              02/15/2012          Tom
  null              01/01/2012          Sam
  2                 01/10/2012          Sam     (actual patient record)
  null              02/15/2012          Sam
    null              01/01/2012          John
  null              01/10/2012          John
  3                 02/15/2012          John     (actual patient record)

I need t-sql to get above output. Basically the appointment dates are repeatedly assigned to each patient but only difference is patientid will be not null for actual patient record.
 
Create table sample (PatientID  int null, AppointmentDate date null, PatientName varchar(30) null)

Insert sample  values (1,'01/01/2012' ,'Tom'),
(2,'01/10/2012','Sam'),
(3,'02/15/2012','John')

View 2 Replies View Related

T-SQL (SS2K8) :: Assign Next Available Column Values?

Sep 29, 2014

find below object and data:

create table #StuDetails(City varchar(25),StuStatus varchar(25), currentValue int,Week1 int,week2 int,week3 int,week4 int)
insert into #StuDetails values('A','new',13,10,0,0,12)
insert into #StuDetails values('B','Old',10,10,41,0,12)
insert into #StuDetails values('C','Fail',10,9,0,0,5)
select * from #StuDetails

Output of above is display as:

CityStuStatuscurrentValueWeek1week2week3week4
Anew 13 10 0 0 12
BOld 10 10 41 0 12
CFail 10 9 0 0 5

Now for columns Week1 to week3 if value is 0 then i want to display by searching next week value, if it is also 0 then go for next week and if value found there then display instead of zero. so my output would be as below instead of above.

CityStuStatuscurrentValueWeek1week2week3week4
Anew 13 10 12 12 12
BOld 10 10 41 12 12
CFail 10 9 5 5 5

View 2 Replies View Related

How To Assign Identity Key Values In Replication

Sep 28, 2006

This is a very basic question on replication.

I'm having a central Server with SQL Server 2005 Standard Edition and Other sites with Sql Express Server 2005.

Other sites will also be adding New records and data will be replicated to Central server and from there it will be distributed to all sites.

Question is that if Other sites are also adding Records how i can assing Identity values in those databases. There are few restricitons on this :-

1. I don't want to use GUID.

2. Numbers should be sequential that is after 1000, 1001, 1002 etc. should come.

i thought of adding Negative Values in the primary key on other sites and then when data is replicated on central server then replace it with sequential key but i'm not clear on how to accomplish this.

any help will be highly appreciable.

View 3 Replies View Related

How To Assign Values To A Variable From A Xls Sheet?

Sep 28, 2006

I've got this query inside a Sql Task against a Excel connection and I'd like to insert that value into a user variable called "Proyecto". How do I such thing?

select Proyecto from [Carga$]

TIA,

View 1 Replies View Related

Transact SQL :: Recursive CTE Work Flow

May 7, 2015

how recursive CTE works...Synthesise table with non-recursive CTE

;WITH Employee (ID, Name, MgrID) AS
(
SELECT 1, 'Keith', NULL UNION ALL
SELECT 2, 'Josh', 1 UNION ALL
SELECT 3, 'Robin', 1 UNION ALL
SELECT 4, 'Raja', 2 UNION ALL

[code]....

the first part query will return one row
IDNameMgrIDnLevelFamily
1KeithNULL11

then this result set using after union all query .how the level + 1 condition is working ?  and how its return values .

View 7 Replies View Related

Transact SQL :: Recursive CTE To Fetch Parent

May 20, 2015

I am using the following code to get the next immediate parent of a customer in the hierarchy and it works fine. However, it works only for one customer at a time (i made this a scalar function and I pass the customer id as shown below).

How can I modify this so that i can get each customer and its next immediate parent (all of them in one shot in a single data set)?

WITH my_cte (CustomerID, ParentID, Level)
AS
(
SELECT CustomerID, ParentID, 0 AS Level
FROM [dbo].MyTable
WHERE CustomerID = @CustomerID

UNION ALL
SELECT CustomerID, ParentID, Level + 1
FROM [dbo].MyTable e
INNER JOIN my_cte AS cte ON e.CustomerID = cte.ParentID
)

select CustomerID from my_cte WHERE Level <> 0 AND Level = (SELECT MIN(Level) FROM my_cte WHERE Level <> 0)

View 14 Replies View Related

Transact SQL :: Assign Unique Number To Records?

Nov 15, 2015

Lets say I have a table - tblProducts

View 4 Replies View Related

Transact SQL :: How To Assign Random Number Between 0 And 1 To A Record

Nov 17, 2015

I need to assign a random number between 0 and 1 to all eligible cases of cancer. 

I have a file of records like:
patientid
tumid
site

How can I assign a random number to each record?

View 10 Replies View Related

How To Assign Distinct Data Values For Attribute

Nov 17, 2015

I need the Rate to be of a value from 1-5 to indicate the stars for Rating attribute in my database.

View 2 Replies View Related

Transact SQL :: How To Convert Row Specific Values Into Columns In Join Query

Aug 18, 2015

I am using stored procedure to load gridview,i want to show row specific values in coloumns , as i an working on daily timetable of college and There are three tables Week_Day,Daily_Timetable & Subject.Daily_Timetable has data which has week_day,class_id,Subject_id,Period_No.

Each day has 6 periods and each period is mapped with subject in daily timetable.From below sql i am getting 6 rows of monday.

But i want to show in a row weekname,period1_subject_id(Period_No=1),period2_subject_id(Period_No=2),period3_subject_id.......upto
period6_subject_id.

Please see my query below:-

SELECT     Week_Day.Week_Day_name, Subject.Subject_Code,  Daily_Timetable.Period_No
FROM         Week_Day LEFT JOIN
                      Daily_Timetable ON Week_Day.Week_Day_Id = Daily_Timetable.Week_Day_Id and Daily_Timetable.Class_Id=6  LEFT JOIN
                      Subject ON Daily_Timetable.Subject_Id = Subject.Subject_Id order by  Week_Day.Week_Day_Id ,Daily_Timetable.Period_No

View 4 Replies View Related

Transact SQL :: Creating Report - Query With Parameters Based Off Values

Sep 1, 2015

I have the following report I need to create with 2 parameters. An equal OR not equal. I need the report to have a drop down that has equal to  '1024' or a drop down option that IS NOT equal to '1024'. I also need the WHERE clause to return the equal or not equal based on the user selection inside of SSRS.

SELECT user1 AS [Company], reference AS [PAI_REF], statenumber,
LEFT(user4, 7) AS [Supplier Code],
user4 AS [Company Information], 
user8 AS [Transaction Type], user2 AS[Invoice Number], 
--CONVERT(VARCHAR,CONVERT(Date, user3, 103),101) AS [Invoice Date],
[routeName] AS [Route], username AS [User Name]

[Code] ....

View 2 Replies View Related

Transact SQL :: Query To Return Greater Than Zero Values To Sort Up And Zeros Go Down

Sep 17, 2015

I am using a table to store different size numbers for example:

Look at the picture below:

But I want this type of output look at the picture below:

How to sort the query to return greater than zero values to sort up and zeros go down. 

I am using sql server 2008.

View 14 Replies View Related

How To Assign Dataset Values In Header In RDLC Report

Apr 25, 2008

I am using RDLC report with Microsoft visual studio 2005. In the first page of rdlc i have two text boxes and one table in body section. In the second and subsequent pages i want to repeat the data from textbox1 and textbox2 along with table data continuation of page1.



Currently the continuation of table data from page1 to page2 is working properly. But the textbox1 and textbox2 data also needs to be repeated in every pages.



I tried the following steps, but fails to work.

1. added two text boxes in header section and another two text boxes in Body section.

2. Assigns the dataset value to textboxes in body section.

(Ex: =first(Fields!Address.Value)

3. Assigns the textboxes value from Body section to the corresponding text boxes in header section.

(Ex : =first(ReportItems!textbox1.Value))



Result:

The header text box value displayed in the first page only and not repeated in the subsequent pages.



Expected:

Whatever assigned to the header section should be repeated in the subsequent pages. But only page number, date... is reflecting in other pages and not the text box values in header section.



Kindly give me the solution.

Thanks in advance.

View 7 Replies View Related

Integration Services :: Assign Values To Variables Dynamically In 2012 SSIS Package

Jul 16, 2015

Can I assign values to variables in 2012 using below command? I have used the same command in 2008 and it works fine.

DTEXEC
/SERVER"XXXXXXXXSQLSERVER2012"/SQL"Mypackage.dtsx"/SETPackage.Variables[FilePath].Value;"C:Test estvariable.csv"

Wondering is there a different way in 2012 to pass values to variables dynamically.

View 2 Replies View Related

How To Convert Recursive Function Into Recursive Stored Procedure

Jul 23, 2005

I am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod

View 4 Replies View Related

Transact SQL :: Convert Comma Separated String Values Into Integer Values

Jul 28, 2015

I have a string variable

string str1="1,2,3,4,5";

I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :

declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)

But this query throws following error message:

Conversion failed when converting the varchar value '3,4,6,7' to data type int.

View 4 Replies View Related

Transact SQL :: CTE To Peg Char Values To Int Values In Radio-style Chart

Aug 26, 2015

I developed the following T-SQL query that runs successfully, but I was looking for a more efficient and concise way to do this. Is there a CTE that can replace all of these case statements?  I've updated my query as below. Although this sample query works, it's not working for my real data.  Instead, I get an error.  At the bottom is the error part of my real query.I copied all of the tables from the first query block below.  But when I wrote the bottom query block, it underlined in red the words "answer" and "question."  It says "Invalid column name". 

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#hard_values')
) DROP TABLE #hard_values;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#dummy_data')
) DROP TABLE #dummy_data;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#temp')

[code]...

View 13 Replies View Related

Transact SQL :: Query To Display Avg Values For Each Timestamp And Count Of Timestamp

Jun 23, 2015

date        time         s-sitename TimeTaken(Seconds)
6/8/2015 10:56:26 TestSite 100
6/8/2015 10:56:26 TestSite 500
6/8/2015 10:56:26 TestSite 800
6/9/2015 11:56:26 TestSite 700
6/9/2015 11:56:26 TestSite 200
6/12/2015 12:56:26 TestSite 700

I have a table with above values, I am looking for a sql query to find AvgTimeTaken at different time stamps and total count of each time stamp

Output
date        time         s-sitename TimeTaken(Seconds) Count_of_Request
6/8/2015 10:56:26 TestSite 1400                  3
6/9/2015 11:56:26 TestSite 900                   2
6/12/2015 12:56:26 TestSite 700                   1

View 5 Replies View Related

How To Query The Using Recursive?

Jun 10, 2007

 
Hello,
I have the following tables:

Article(articleID,CategoryID,ArticleTitle)
Categories(categoryID,ParentID,CategoryTitle)
I am trying to retrieve the main category ID for a specific article ID.
For example lets say I have this data:
Article: 

1, 10 , "some title"
2,10,"some title"
3,11,"some title"
Categories:
1, NULL , "some title"
2, 1, "some title"
10, 2, "some title"
11, 10 , "some title"
 
In this example I want to know who is the main category of article 3.
The query should return the answer: 1
Thats because:

The article ID 3 is inside category 11.
Parent for category 11 is 10.
Parent for category 10 is 2.  
Parent for category 2 is 1
and Parent for category 1 is NULL, which means category 1 has no parents and it is the main category.
Query will return article id, category id, main_category_id, ArticleTitle, CategoryTitle (some union between 2 tables)
Do you have any suggestions for such query?
Thanks all.
 

View 1 Replies View Related

Recursive Query ..

Jan 18, 2008

Recursive quey to show products with "custom defines fields" related by Classifications, instead of per product Hello, I’m working on a project ..  .



I’m desperating due to the
complex (for me and also I think for some others) sql query that I need to
write, to show the products with his “custom defined fields� that are inside a ProductsFieldsByClassification
table that holds this mentioned “custom defined fieds� according to the Classifications
table, on where the Products can be found trought the productsClassifications
table.


CustomFields can be defined
and set for the products, trought his Classifications (instead of define a
custom field for each product (that consume a lot of data), I decide to use it as
I explain)

 

I will to know the properly
SQL QUERY to show a list of products with the ProductsFieldsByClassifications and ProductsFieldsValuesByClassifications:

 


As example on
a Requested ID_Classification = 16 (Torents/Games/Dreamcast/PAL), the products must be show with the
ProductsFields and Values that has the DBA for the:

·        
requested
ID_Classification

o       
PAL (ID_Classification: 16)

·        
AND all
the Classifications that belongs above (trought ID_ParentClassification) that are :

o       
Torrents (ID_Classification:
1) that will show the products values for the “Size�

o       
Games (ID_Class..:4) ß this classification has no CustomFields so none from this one.

o       
Dreamcast (ID_Class..:14 )
that will show his ID_Classification(14) product field “Levels� value (but not “AllowSave� as not have value for any product)

 




Hmnn i show a graphic that i design for (feel to click over to see at correct resolution) 

 
 
 

I also write asp.net tutorials. For those interested see my blog at http://abmartin.wordpress.com 
 

 

View 2 Replies View Related

Recursive SQL Query

Sep 21, 2004

I have a tough issue with a query.

I have the following structure


Table: Users

UserId ParentUserId
1 1
2 1
3 2
4 1
5 4
6 5


I need to write a stored procedure that takes in UserId as parameter and returns everyone under him/her.

So if Param=1 it would return result set of 2,3,4,5,6

Anyone done this before or have any ideas?

Thanks,
ScAndal

View 1 Replies View Related

Recursive Query Help

Jul 20, 2005

Hi,

Does anyone know how to do an sql recursion queries?

I believe it involves a view with a union.

I have a User Table and in that table i have a employee_id and a
boss_id. What i'd like to do is to find all employees under a certain
boss.
For example,

Employee_ID Boss_ID
1
2                     1
3                    
4                     3
5                     2

So if i'd like to know who are under the employee_id = 1 it will return
employee_id 2 and 5 since employee 2 also is the boss of employee_id =
5.

To do that i'd have to have recursion query.

Thanks,

View 2 Replies View Related

Recursive Sql Query

Sep 29, 2004

Hi,

i have the following table structure:

id | parentID
1 | NULL
2 | 1
3 | 2
4 | 3

im having trouble in creating a recursive query to return all the parents of a particular id. Have anyone ever done this?

thanks in advance,

View 7 Replies View Related

Can A Recursive Query Do This?

Dec 23, 2004

I am wondering if there is some type of recursive query to return the values I want from the following database.

Here is the setup:

The client builds reptile cages.

Each cage consists of aluminum framing, connectors to connect the aluminum frame, and panels to enclose the cages. In the example below, we are not leaving panels out to simplify things. We are also not concerned with the dimensions of the cage.

The PRODUCT table contains all parts in inventory. A finished cage is also considered a PRODUCT. The PRODUCT table is recursively joined to itself through the ASSEMBLY table.

PRODUCTS that consist of a number of PRODUCTS are called an ASSEMBLY. The ASSEMBLY table tracks what PRODUCTS are required for the ASSEMBLY.

Sample database can be downloaded from http://www.handlerassociates.com/cage_configurator.mdb

Here is a quick schema:

Table: PRODUCT
--------------------------
PRODUCTIDPK
PRODUCTNAMEnVarChar(30)


Table: ASSEMBLY
--------------------------
PRODUCTIDPK(FK to PRODUCT.PRODUCTID)
COMPONENTIDPK(FK to PRODUCT.PRODUCTID)
QTYINT


I can write a query that takes the PRODUCTID, and returns all



PRODUCT
=======
PRODUCTIDPRODUCTNAME
--------------------
1Cage Assembly - Solid Sides
2Cage Assembly - Split Back
3Cage Assembly - Split Sides
4Cage Assembly - Split Top/Bottom
5Cage Assembly - Split Back and Sides
6Cage Assembly - Split Back and Top/Bottom
7Cage Assembly - Split Back and Sides and Top/Bottom
833S - Aluminum Divider
933C - Aluminum Frame
10T3C - Door Frame
11Connector Kit
12Connector Socket
13Connector Screws



ASSEMBLY
=========
PRODUCTIDCOMPONENTQTY
---------------------
198
1104
1111
211
281
311
381
411
481
511
582
611
682
711
783
11128
11138



I need a query that will give me all parts for each PRODUCT.

Example: I want all parts for the PRODUCT "Cage Assembly - Split Back"

The results would be:


PRODUCTIDPRODUCTNAME
--------------------
2Cage Assembly - Split Back
1Cage Assemble - Solid Back
933C - Aluminum Frame
10T3C - Door Frame
11Connector Kit
833S - Aluminum Divider
12Connector Socket
13Connector Screws

Is it possible to write such a query or stored procedure?

View 4 Replies View Related

Recursive Query

Dec 6, 2007

Can you please help me to write a recursive query in sql server 2000?

I have got a table WORKORDER. wonum and parent are the two columns in the table. A wonum can have children.Those children can have children, so on. if i am giving a wonum,it should display all the children ,their children and so on.

Sample data in the table is as follows

wonum parent
7792 NULL
7793 7792
165305 7793
7794 7792
7795 7792

eg:
7792 is a workorder,which has got children and grand children

7793 is a child of 7792
165305 is a child of 7793
7794 is a child of 7792 and
7795 is a child of 7792

When I give the 7792 in the query,it should fetch all the children and grand children,etc.
output should be :
7793
165305
7794
7795

How can we do that?

View 16 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved