Transact SQL :: How To Update ItemOrder With Ascending Numbers Starts With 1 For Child Items

Jul 23, 2015

I have a below table,

DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ParentItemName, ItemOrder, ReportId)
VALUES('Item1', NULL, 1, 5),('Item1-Child1', 'Item1', 0, 5),('Item1-Child2', 'Item1', 0, 5),('Item2', NULL, 2, 5),
('Item11', NULL, 1, 6),('Item12', NULL, 2, 6),('Item12-Child1', 'Item12', 0, 6),('Item13', NULL, 3, 6)
SELECT * FROM @TBL

Here,
1. for all ReportId, child items's ItemOrder  = 0
2. example, for ReportId = 5, both child items ("Item1-Child1" & "Item1-Child1") of parent "Item1" has ItemOrder = 0

I need to,
1. update all child items with ascending numbers starts with 1 against each parent and each report.
2. for each different parent or different report, order by should starts with 1 again.

View 2 Replies


ADVERTISEMENT

Transact SQL :: How To Update ItemOrder For Multiple ItemID

Aug 25, 2015

I have a table with some below dummy data,

DECLARE @TBL TABLE (QId INT, ItemName NVARCHAR(5), ItemParentName NVARCHAR(5) NULL, ItemId INT, ItemOrder DECIMAL(18,2) NULL)
INSERT INTO @TBL VALUES
(1, 'A', NULL, 10, 1.00),
(2, 'A1', 'A', 10, NULL),
(3, 'A1', 'A', 10, NULL),

[Code] ....

1. I have multiple "ItemId" and have their parent/child data.
2. Need to update only child's "ItemOrder". Example, if parent has order "1.00" then their child will be 1.01 & 1.02... and so on.

View 6 Replies View Related

Transact SQL :: To Get Parent / Child / Grand Child Row On Various Order?

Jun 26, 2015

I have a table with below kind of data,

DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ItemDate DATE, ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ItemDate, ParentItemName, ItemOrder, ReportId)
VALUES ('Plan', '2015-06-01', NULL, 1, 20),('Design', '2015-06-01', NULL, 2, 20),('Test', '2015-06-20', NULL, 3, 20),('Complete', '2015-06-30', NULL, 4, 20),
('Design child A', '2015-06-02', 'Design', 1, 20), ('Design child B', '2015-06-01', 'Design', 2, 20),
('Test child A', '2015-06-10', 'Test', 1, 20), ('Test child B', '2015-06-09', 'Test', 2, 20), ('Test child C', '2015-06-08', 'Test', 3, 20),
('Test grand child A', '2015-06-08', 'Test child B', 1, 20), ('Test grand child B', '2015-06-08', 'Test child B', 2, 20)
select * from @TBL

Here I want,

1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate)
2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)

Looking for below output ...

View 3 Replies View Related

Reconcile Pairs Of Parent-Child Items. Ideas Please?

Feb 15, 2008

I need ideas on how to achieve the following:

A parent can have many children - always has at least one.

A parent always has an 'anti-parent' with an equal number of 'anti-children' to the normal parent

Each child and anti child has a unique ID


I need to reconcile each Parent/antiParent Child/antichild and highlight the differences before.

Normally, reconciliation reports are fairly straightforward due to the fixed number of items, I'm not quite sure of the best way to do this.


How it should look in the report

PARENT A field1,field2,...,field n
PARENT B field1,field2,...,field n
CHILD A1 childfield1,cf2,....cf n (child A1 and B1 share a unique ID)
CHILD B1 childfield1,cf2.....cf n
CHILD A2
CHILD B2
...
CHILD An
CHILD Bn


So, compare PARENT A with PARENT B and highlight any differences
compare CHILDA1 with CHILD B1 and highlight any differences
...
compare CHILDAn with CHILD Bn and highlight any differences

Thoughts?

View 2 Replies View Related

I Need To Update A Table With Random Numbers Or Sequential Numbers

Mar 11, 2008



I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.

Thanks for any help

View 6 Replies View Related

Transact SQL :: Parent / Child Tables - Pivot Child Data To Parent Row

May 19, 2015

Given the sample data and query below, I would like to know if it is possible to have the outcome be a single row, with the ChildTypeId, c.StartDate, c.EndDate being contained in the parent row.  So, the outcome I'm hoping for based on the data below for ParentId = 1 would be:

1 2015-01-01 2015-12-31 AA 2015-01-01 2015-03-31 BB 2016-01-01 2016-03-31 CC 2017-01-01 2017-03-31 DD 2017-01-01 2017-03-31

declare @parent table (Id int not null primary key, StartDate date, EndDate date)
declare @child table (Id int not null primary key, ParentId int not null, ChildTypeId char(2) not null, StartDate date, EndDate date)
insert @parent select 1, '1/1/2015', '12/31/2015'
insert @child select 1, 1, 'AA', '1/1/2015', '3/31/2015'

[Code] .....

View 6 Replies View Related

Transact SQL :: Match Items Between Two Tables

Jul 6, 2015

I have a table variable @tbl with one column (user) with example Laura, Scott, and Kevin.

Then I have a table usergroups with two columns (groupid, user) with example:

1, Laura
1, Scott
2, Laura
2, Scott
2, Kevin
3, Laura
3, Kevin
3, Scott
3, Jim
4, Laura
4, Kevin
4, Scott

I want to find groupid 2 and 4 because they exactly match the content of @tbl.

View 8 Replies View Related

Transact SQL :: Syntax To Group Items In One Line?

Nov 20, 2015

I have records that I get in this format:

ID                       Customer               Type                 TypeNUm
100                      Tiger                    Item                   T100  
100                      Tiger                     Item                   T200
100                      Tiger                     Item                   T300
100                      Tiger                     Shiper                  SAAA 
100                       Tiger                    PO                       POAAA
200                       Panera                  GL                   WE
200                       Panera                  PO                   POBBB

The reftypes are not always the same, what I need is to get it in this form

ID                       Customer               Type              TypeNUm
100                      Tiger                     Item                   T100,T200, T300 
100                      Tiger                     Shiper                  SAAA 
100                       Tiger                    PO                       POAAA
200                       Panera                  GL                      WE
200                       Panera                  PO                     POBBB

View 6 Replies View Related

Transact SQL :: NVARCHAR SortOrder Breaks For More Than 9 Items?

Jul 22, 2015

I have below sample data table,

DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ItemDate DATE, ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ItemDate, ParentItemName, ItemOrder, ReportId)
VALUES ('Plan', '2015-06-01', NULL, 1, 20),('Design', '2015-06-01', NULL, 2, 20),('Test', '2015-06-20', NULL, 3, 20),('Complete', '2015-06-30', NULL, 4, 20),
('Design child A', '2015-06-02', 'Design', 1, 20), ('Design child B', '2015-06-01', 'Design', 2, 20),

[Code] ....

Here I want,

1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate)

2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)

3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)

Below Query works perfect, when count of child or grand child count less than 10, as SortOrder column here is

NVARCHAR,
;With cte As
(Select t.ItemId, t.ItemName, t.ItemDate, t.ParentItemName, t.ItemOrder, t.ReportId,
Cast(t.ItemOrder As nvarchar(max)) As SortOrder
From @TBL t
Where t.ParentItemName Is Null

[Code] ....

The output rows with ItemOrder's 10, 11, 12 (test grand child 10, test grand child 11, test grand child 12) should display after ItemOrder = 9 (test grand child 9). I know this is happening due to varchar sort order?

View 4 Replies View Related

Transact SQL :: Concatenate Numbers (not Add Them)

Nov 6, 2010

Let’s say in one field there is the "year" as an integer 2010, and in another field is the "month" as an integer 11. How can you concatenate them and not add them?

Essentially the result I'm looking for based on the example would be this: 201011 but I still want this to be an integer and not a string.

View 16 Replies View Related

Transact SQL :: Adding Numbers Together

May 18, 2015

I am trying to create the following in SQL automatically. 

Step needs to double itself each time.  How would I write that one out ? Yes I would have the first number as 1. 

ID
Step 

1
1

2
2

3
4

4
8

5
16

6
32

7
64

8
128

9
256

10
512

View 6 Replies View Related

Transact SQL :: Query That Returns Material (items) That Are Used In Event On Certain Day - RIGHT JOIN

Nov 29, 2015

I have a query that returns material(items) that are used in an event on a certain day.

SELECT C.categoryName, count(I.itemID) AS InMission
from items as I
RIGHT JOIN Categories AS C on I.categoryID = C.categoryID
INNER JOIN LinkMissionItem as LM on I.itemID = LM.itemID
INNER JOIN Missions as M on LM.missionID = M.MissionID
where '2015/12/19' BETWEEN M.freightLeave and M.freightReturn AND isReturned = 0
GROUP BY C.categoryName, C.categoryID
ORDER BY C.categoryID

There are a total of 20 categories and I would like all the categories listed in the result even though there are no items booked in a mission. At the moment, I can only get the categories that have items in that category booked in a mission. I hoped that the RIGHT JOIN on the categories table would do the trick but it doesn't.

View 4 Replies View Related

Transact SQL :: Parent Child Grouping

Nov 4, 2015

I have a table as below

ItemID ParentID
6 NULL
7 NULL
8 7
11 7
12 8

Here ParentID null means it does not have the parent its the master. I want result in below format.

ItemID ParentID
6 NULL
--No parent no child
7 NULL
--No parent but has child
8 7
--7 is the parent of 8
12 8
--8 is the parent
11 7
--7 is the parent

Basically it should be in parent id then child id after that child's childID , In a recursive way.

View 5 Replies View Related

Transact SQL :: Need To Get Parent Child Data

Jun 24, 2015

I have a table with parent child relationship data.

DECLARE @DATA TABLE (U_ID INT, U_NM NVARCHAR(20), U_DT DATE, U_ORD INT, P_U_NM NVARCHAR(20) NULL)
INSERT INTO @DATA VALUES (1, 'Design', '06/15/2015', 2, NULL), (1, 'Plan', '06/01/2015', 1, NULL), (1, 'Cust Plan 1', '06/10/2015', 0, 'Plan'), (1, 'Cust Plan 2', '06/05/2015', 0, 'Plan'),
(2, 'Design', '06/25/2015', 2, NULL), (2, 'Plan', '06/20/2015', 1, NULL)
SELECT * FROM @DATA

1. For U_ID = 1, we have two diffrent U_NM and for one U_NM we have 2 child data.  Need to show parent data order by U_ORD and need to show child data within their parent order by U_DT

2. For U_ID = 2, we don't have child data, hence need to show data order by U_ORD only

SELECT 1 AS U_ID, 'Plan' AS U_NM, '06/01/2015' AS U_DT, 1 AS U_ORD, NULL AS P_U_NM
UNION ALL SELECT 1, 'Cust Plan 2', '06/05/2015', 0, 'Plan' UNION ALL SELECT 1, 'Cust Plan 1', '06/10/2015', 0, 'Plan'
UNION ALL SELECT 1, 'Design', '06/15/2015', 2, NULL UNION ALL SELECT 2, 'Plan', '06/20/2015', 1, NULL UNION ALL SELECT 2, 'Design', '06/25/2015', 2, NULL

View 3 Replies View Related

Transact SQL :: Sum Of Distinct Service Numbers?

Jun 17, 2015

I have data in the below format 

Service Numbers    start datetime     enddate time        part number     Calc(in hrs (endate - startdate) 
223344                  2014-05-05          2014-05-07       12345               48
223344                  2014-05-05          2014-05-07       56789               48
223355                  2014-05-06          2014-05-07       17865               24
223355                  2014-05-06          2014-05-07       76543               24
345627                  2014-05-09         2014-05-013      76543               72

I want measure like  sum of  hours of distinct service numbers.I want to use it in a reporting tool.

it means my sum will be 144 and my Average of per time closed will be 144/3 =  48.

How to create a measure like that ?

View 4 Replies View Related

How To Update 1000s Of Items In 50 Tables

Sep 20, 2005

Hi all,I'm looking for a way to re-number inventory items. The items exist in 50+tables, hundreds of fields and there are several thousand items. Maybe onetable could hold the list of old & new items, another table holds the listof tables/fields to update? How can this be done without needing a millionindividual update statements?A brute force method may look like:update table1 set field1 = newitem where field1 = olditemupdate table1 set field2 = newitem where field2 = olditemupdate table2 set field1 = newitem where field1 = olditemThanks for any input.

View 5 Replies View Related

Transact SQL :: Display Child Data Under Parent Row

Jun 25, 2015

I would like to display child row right after parent row with ORDER BY DataDate for below set of data.

DECLARE @DATA TABLE (DataUID INT PRIMARY KEY IDENTITY(1,1), DataId INT, DataName NVARCHAR(20), DataDate DATE, ParentDataName NVARCHAR(20))
INSERT INTO @DATA (DataId, DataName, DataDate, ParentDataName)
VALUES (1, 'child plan 1', '2015-06-09', 'Plan'), (1, 'child plan 2', '2015-06-08', 'Plan'),
(1, 'Design', '2015-06-01', NULL), (1, 'Implement', '2015-06-01', NULL),
(1, 'child Implement 1', '2015-06-09', 'Implement'), (1, 'child Implement 2', '2015-06-10', 'Implement'),
(1, 'Plan', '2015-06-01', NULL), (1, 'Operate', '2015-06-01', NULL)
select * from @DATA

1. as a example the child row 'child implement 1' & 'child implement 1' show correctly under parent 'Implement' with Order BY DataDate.

2. I'm looking for a SELECT query which should display 'child plan 1' & 'child plan 2' under parent 'Plan' with Order BY DataDate clause.

Below is the expected output I'm looking for,

View 6 Replies View Related

Transact SQL :: Select Records From Two Child Tables

Jun 18, 2015

i have 3 tables names parent, child1, child2 parent has 1 record, child1 has 2 record and child 3 has 3 records the script

select Parent.*,child1.f1,child2.f2  from child1 inner join Parent on parent.id =child1.id 
inner join child2 on child1.id  =child2.id 

running above query gives me sixes rows but i want only all rows of childs but not their Cartesian products

Object: Table [dbo].[Parent] Script Date: 06/18/2015 17:33:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Parent](
[id] [int] NOT NULL,

[code]....

View 8 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 :: How To Remove Numbers Zero From The Left Only In Column

Jul 1, 2015

example:

column: ID  numbercar      before         ID    numbercar         after

        1      00122011                      1        122011                                           
       2      00042010                       2         42010
       3      03102012                          3       3102012                                          

View 5 Replies View Related

Transact SQL :: Lottery Winning Numbers Matching

May 6, 2015

I'm developing a Lottery system. Here are some background information:

- Total 48 numbers
- Draw 6 numbers + 1 extra number
- each Bet select 6 numbers

And the Prize:

Prize
Selected Matched
Extra number Matched

1st
6
No

2nd
5
Yes

[Code] ...

Below is my proposed table design (simplified):

Draw table
Column
Datatype

DrawDate
date
Primary Key

ResultNumber1
tinyint

[Code] ....

There will be millions of Bet for a Draw. I need to write a stored procedure to check which bets won the 1st ~ 7th prizes. How to write a query to match the bets with the draw result? The query should be run within 1 minute. And should I change my table design?

View 5 Replies View Related

Transact SQL :: Find / Replace Numbers In Server

Aug 12, 2015

I have a separate list of calendar years with radiocarbon year equivalents in SQL server but no conversion equation. Most but not all of the data I have is in radiocarbon years. I thought at first I could just link the tables but I don't want the data that is already in calendar years to be linked to this conversion table. Is there any way I can either link the two tables with criteria for which data is linked (Only ages that are in radiocarbon years). Or possibly a way to query all ages that are in radiocarbon years and do something similar to a find and replace with a large list of numbers to change?

View 14 Replies View Related

Transact SQL :: Limit A Query Results When All Of Line Items Under Group Meet Certain Condition

Oct 1, 2015

I have a query that returns the data about test cases.  Each test case can have multiple bugs associated to it.  I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data

TestCaseID TestCaseDescription  BugID BugStatus
1                TestCase1                       1      Closed
2                TestCase1                       2      Open
3                TestCase2                      11     Closed
4                TestCase2                      12     Closed
5                TestCase2                      13     Closed

How can I limit this to only return TestCase2 data since all of that test case's bugs have a status of closed.

View 3 Replies View Related

Transact SQL :: Get List Of Items Present In Order Based On Confidentiality Code Of Product

Sep 29, 2015

I want to get the list of items present in that order based on the confidentiality code of that product or Item and confidentiality code of the user.

I display the list of orders in first grid, by selecting the order in first grid I display the Items present in that order based on the confidentiality code of that item.

whenever order in 1st grid is selected i want to display the items that the item code should be less than or equal to the confidentiality code of the logged-in user other items should not display.

If the all the items present in the order having confidentiality code greater than Logged-in user at that time the order no# should not display in the first grid.

Table 1:Order

Order_Id Order_No Customer_Id

2401 1234567 23
2402 1246001 24
2403 1246002 25

Table 2 : OrderedItems

OrderItem_Id Order_Id Item_Id Sequence

1567 2401 1001 1
1568 2401 1003 2
1569 2402 1005 1
1570 2402 1007 2
1571 2403 1010 1

Table 3: ItemMaster

Item_Id Item_Name confidentCode

1001 Rice Null
1003 Wheet 7
1005 Badham Null
1007 Oil 6
1010 Pista 8

Out put for 1st grid 

**Note :** Logged-in user have confidentiality code 6

Order No Customer
1234567 23
1246001 24

3rd order is not displayed in the grid

After user selects the 1st order in the grid then the items present in that 1st order should be displayed as 

1001     Rice

the second item not displayed because that having confidentiality code greater than user.

After user selects the 2nd order in the grid then the items present in that order should displays

1005 Badham
1007 Oil

I need the query to display the order details in 1st grid.

View 3 Replies View Related

Transact SQL :: Parent Child Data With Different Order By Clause?

Jun 24, 2015

I'm asking my previous question in this new thread with more specific data and condition.I have below sample data,

DECLARE @DATA TABLE (U_ID INT, U_NM NVARCHAR(20), U_DT DATE, U_ORD INT, P_U_NM NVARCHAR(20) NULL)
INSERT INTO @DATA VALUES (1, 'Design', '06/15/2015', 2, NULL), (1, 'Plan', '07/01/2015', 1, NULL), (1, 'Cust Plan 1', '06/10/2015', 0, 'Plan'), (1, 'Cust Plan 2', '06/05/2015', 0, 'Plan'),
(2, 'Design', '06/25/2015', 2, NULL), (2, 'Plan', '06/20/2015', 1, NULL)

We have 2 different U_ID (1, 2) and I want a SELECT query to display,

1. For U_ID = 1, we have 2 parent U_NM (Design & Plan) and Plan having 2 child (Cust Plan 1 & Cust Plan 2).
2. I want to display parent U_NM ORDER BY U_ORD
3. If any parent having child element, then need to show immediately under that parent and ORDER BY U_DT
4. For U_ID = 2, we don't have any child, hence display ORDER BY U_ORD

View 10 Replies View Related

Transact SQL :: Make Tracks Or Paths Of Parent Has Many Child

Nov 19, 2015

I have a  tblActivity table :

ActivityID ActivityName
-----------------------------
1 A1
2 A2
3 A3
4 A4
5 A5
6 A6
7 A7
8 A8
9 A9
10 A10

and related it in tblParentActivity table with parent child relationship as:

ParentActivityID ChildActivityID
-------------------------------------
1 2
1 3
2 4
2 5
3 6
4 7
5 7
6 8
7 9
8 9
9 10

I want to write a SQL query which will make it in tracks depend on how many childActivity in one parentActivity like

ActivityID TrackGroup
-------------------------
1 1
2 1
4 1
7 1
9 1
10 1

[code]....

View 8 Replies View Related

Transact SQL :: Convert Numbers To ASCII Code In Server

Jul 29, 2015

This is a string i am getting from sql server.

00000006637120150522187449637100  34                                                              
10-000000003444378351108502007                                                                  
  01016800002413                                                                    
10-000000091541378538466562009                                                                  
  01016800002420                                                                    
                                      
[Code] .....

View 9 Replies View Related

Creating Query To Update A Field For Multiple Items

May 5, 2015

Looking to write an query that will update a field for multiple items, like 1,500.

something like:

UPDATE INMAST
SET FPRICE = 111.11

WHERE
INMAST.FPARTNO = 'xxx'

only issue I'm having is a need to do a JOIN because there's one more condition that must be met from another table, i've tried this:

SET FPRICE = 111.11
JOIN INVCUR
ON
(inmast.fpartno + inmast.frev)= (invcur.fcpartno + invcur.fcpartrev)

WHERE
INMAST.FPARTNO = 'NRE'
AND
invcur.flanycur = 'TRUE'

but that is giving me an error around the JOIN

View 11 Replies View Related

How To Update Child Table Using The PK And A Seq #

Jun 17, 2002

Good morning,
I have two tables PARENTTBL which is a parent table and CHILDTBL which is a
Child table . For each insert in the Parent table i want to insert values in the child Table but (when inserting in child table) i want the Field FILED2 which is a varchar one will receive the value of the PKEY int cancatenated with a sequnetial value for each new PKEY
FILED2 = Rtrim(LTreim(Str(PKEY))+ Sequential # . which is a
Sequential # by PKEY value .

Exemple : If i insert
100,A,B in PARENTTBL (100 is a PK value)
i want to insert in CHILDTBL the following data :
125,100-1
126,100-2
300,100-3
452,100-4
The first column is the Child PK and the second column is the FILED2
I want to do this in one insert instead of insert then Update.

I don't find the equivalent of Number(*) of SYBASE or ROWNUM of Oracle in order to do This .

Thinks to help me

View 3 Replies View Related

Update Child Records Help

Oct 5, 2004

Please help

I have table1 which has many unique ID numbers and table2 that has many records for each ID. some of the ID numbers in table1 have changed and I have created a translation table (table3) that links the old and new ID numbers.

What I need to do is some sort of update sql statement that updates all the records in table2 changing all the oldID numbers to the new ones using the translation table.

Table1 and table2 are not linked...can anyone help me with the sql statement

example

Table 1
IDNUM NAME
12345 Joe
12346 Mary
12347 David

Table2
IDNUM FIELD1
12345 hello
12345 goodbye
12346 hello
12347 goodbye
12346 hello
12346 goodbye

Table3
OLDID NEW ID
12345 54321
12347 74321

need to change the IDNUM in Table2 to 54321 where IDNUM = 12345 and same with 12347..Need to do this for many many IDs but not all.

Thanks very much

View 1 Replies View Related

Transact SQL :: Delete Query Based On Count Of Child Records?

Oct 12, 2015

I've been able to get this select query to work, but I'm not sure how to modify it to turn it into a DELETE query:
  
USE QSCTestENG
select p.[testid], COUNT(c.[testid])
FROM [dbo].[tblTestHeader] p
left outer join [dbo].[tblTestMeasurements] c ON p.[testid]=c.[testid]
where p.[model] = 'XPPowerCLC125US12'
group by p.[testid]
having COUNT(c.[testid]) <>48;

View 2 Replies View Related

Transact SQL :: Order Change In Parent To Its Child Tables Using FK Relations?

Apr 20, 2015

I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.

Link: [URL]

--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child

--When I am deleting a grand child table, it should be linked to child table first followed by Parent

--- Query 1

DELETE Top(100000) FROM [dbo].[ChartInstanceCase]
FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID]
INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId]
WHERE [Patient].PracticeID = '55';

--Query 2

DELETE Top(100000) [dbo].[ChartInstanceCase]
FROM  [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID] 
INNER JOIN
[dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID]
WHERE [Patient].PracticeID = '55';

how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2

View 15 Replies View Related

Transact SQL :: Use FOR XML To Return Multiple Child Records Within Each Parent Record

Aug 7, 2015

I have a single complex query.

SELECT 
Col1, -- Header, 
Col2, -- Header, 
Col3, -- Detail
Col4, -- Detail 
Col5, -- Detail
FROM 
TableName;

The query repeats the Header row value for all children associated with the header.I need the output of the query in XML format such that..For every Header element in the XML, all its children should come under that header element//I am using - 

SELECT 
Cols 
FROM 
Table Names 
FOR XML PATH ('Header'), root('root') , ELEMENTS XSINIL 

This still repeats the header for each detail (in the XML) , but I need all children for a header under it.I basically want my output in this format - 

<Header >
  <detail 1>
   </detail 1>
  <Detail 2>
  </Detail 2>
  <detail 3>
  </detail 3>
</Header>

View 2 Replies View Related







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