SQL Server 2008 :: Converting Adjacency List To Hierarchy Table

Feb 10, 2015

I have been trying to convert an existing table that used adjacency list model (parentid,childid) to a table that use hierarchy Id type. So early on, I notice my original data does contains multiple roots. So I took a step to create dummy nodes to ensure all nodes fall into a single root structure. Another important fact is that each child node can have multiple parents.

My original source table contains 22461 records, when running the query below step 2 produces explosive number of records around 175,000+ records. I spent hours study the result and couldn't understand what actually causing this, I ran it against small set of test data I didn't seem the issue caused by child with multiple parents.

select * from SourceTable -- produces 22461 records

--step 1: first, get row number of child records in each parent

SELECT ChildID,ParentID, ROW_NUMBER() OVER (PARTITION BY PARENTID ORDER BY PARENTID) as Num
INTO #RelationshipTmp
FROM SourceTable;

[Code] ....

View 1 Replies


ADVERTISEMENT

Adjacency List From SQL Table To XML File

Nov 19, 2007

I'm having trouble generating an XML file from a sql table. Basically its an HR Dept. table that has departmentID and ParentDepartmentID. I need to create a hierarchal xml file that has a parent node, and all its departments, and if any of the sub departments have any below them, stick those departments in accordingly. I can do this two ways...either through tSQL or through .NET recursive methods. I've tried the recursion but can't wrap my head around it enough to get it working.

Here is the table schema:




Code BlockCREATE TABLE [dbo].[DepartmentTree](
[DepartmentID] [varchar](13) NOT NULL,
[ParentDepartmentID] [varchar](13) NOT NULL,
[Description] [varchar](30) NOT NULL,
[ManagerEmployeeID] [varchar](13) NOT NULL
) ON [PRIMARY]





here is the xml markup I'm looking for (or facsimile thereof...doesnt really matter)




Code Block
<?xml version="1.0" encoding="UTF-8" ?>
<Department ID="1" Name="IT" Manager="12345">

<Department ID="2" Name="IT Info. Sys" Manager="23456" />
<Department ID="3" Name="IT Technical" Manager="34567">

<Department ID="4" Name="IT Technical West" Manager="45678" />
<Department ID="5" Name="IT Technical East" Manager="56789" />
</Department>
</Department>




Here is my test data:





DepartmentID
ParentDepartmentID
Description
ManagerEmployeeID

1
1
IT
12345

2
1
IT Info. Sys
23456

3
1
IT Technical
34567

4
3
IT Technical West
45678

5
3
IT Technical East
56789



Does anyone have a suggestion as to how to do this?

View 5 Replies View Related

Adjacency Matrix In MDS With A Recursive Derived Hierarchy?

Apr 30, 2014

If you've ever worked with CI (UPC barcodes), certain commercially distributed products can be "packaged" in certain ways as defined by suppliers. For example, buying a six-pack of Coca-Cola, each one of the cans have a barcode UPC that indicates that the particular can is to only be distributed as part of a "pack" - they can't be distributed or sold individually.

Certain products can be distributed and sold in more than one way. For example, that same six pack can be distributed and sold as a case bundle, or it can be distributed as a single six pack. Each packaging method has a certain hierarchy.you can't represent the CI model using anything less than an adjacency matrix.Now, this can be represented in SQL pretty easily - there's tons of examples online that go through the basics of how to do it and how to write the recursive CTE function to represent and return data in this format. My problem is with MDS.

In Master Data Services (MDS) you have several hierarchy types to choose from. The hierarchy type that makes the most sense to me is to use the recursive derived hierarchy. It makes sense because I want to define an entity that contains all the "acceptable" adjacencies for particular items, such that when a user looks at the data in MDS or tries to update its value, it will have certain domain-based enforcement (and thusly lookups).I think that I've crossed a bridge too far with MDS - but, perhaps one of you knows something I don't know. It looks like any hierarchy in MDS is only based in a parent - child relationship, so that's not quite what the doctor ordered here.

View 0 Replies View Related

SQL Server 2008 :: Clone Hierarchy To Another Table

Apr 4, 2015

I have a table "t_prod_cat" which contains hierarchical data which is used in production to present data.

CREATE TABLE [dbo].[t_prod_cat](
[cat_node_id] [bigint] IDENTITY(1,1) NOT NULL,
[advertiser_id] [bigint] NOT NULL,
[cat_hid] [hierarchyid] NULL,
[level] AS ([cat_hid].[GetLevel]()) PERSISTED,
CONSTRAINT [PK_t_prod_cat] PRIMARY KEY CLUSTERED
(
[cat_node_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

In order not to impact the production website during the time an advertiser is editing (the editing might take much time and also mainly because at any time during the editing, the advertiser could cancel all the changes he did), I was thinking of transferring all the data linked to that advertiser to another table and let the advertiser apply any modifications up to the moment he will commit the changes.

Therefore, I would like to "CLONE" the hierarchy related to a certain advertiser_id to another table "t_prod_cat_work"

CREATE TABLE [dbo].[t_prod_cat_work](
[temp_cat_node_id] [bigint] NOT NULL,
[temp_cat_hid] [hierarchyid] NOT NULL,
[advertiser_id] [bigint] NOT NULL
) ON [PRIMARY]

What can be the easiest way to clone all the hierarchical data (multi-levels) from 't_prod_cat' to 't_prod_cat_work' for a certain advertiser_id ?

View 2 Replies View Related

SQL Server 2008 :: Does List Of Page IDs Allocated For A Table Change During Index Rebuilds

Jul 16, 2015

We noticed a deadlock 3-4 weeks ago on a table (table1) and deadlock graph was captured.

When I am analyzing the deadlock graph, page number using DBCC PAGE, I am getting the object id for a different table (table2). But deadlock graph shows the name of the object as table1.

Is it possible that subsequent defragmentation of indexes would have caused the respective page id to got re-allocated to a different table? I checked the deadlock graph lately only after 3-4 weeks.

View 1 Replies View Related

Display Date Hierarchy Parameter List

Nov 20, 2007



I have a parameter consisting of a Year-Month hierarchy. The drop down parameter list appears as:

2007
1
2
3
4
5
6
7
8
9
10
11
12
2008
1

I have two questions:

Can I get the month names to appear instead of number?

When the parameter is selected the Parameters!DateShippedYearMonth.Label displays the month ONLY. How can I see the Year AND Month that was selected?


Thanks.

View 6 Replies View Related

Integration Services :: SSIS 2008 / Turning Delimited List Into Table?

Sep 30, 2015

I have list (in an input file) where each row is about 20K in size (so it can't be stored in a sql table). I want to convert the list into a table as shown below:

before:
---------------
pk1,  c1, d1, c2, d2, c3, d3,......
pk2   c1,d1,c2,d2,c3,d3.....

where "pk" denotes a primary key and in a given row (c1,c2.c3,...) are all distinct. ("c" are columns names, "d" is the associated data)

after: (desired conversion)
---------------
pk1,c1,d1
pk1,c2,d2
pk1,c3,d3
...
pk2,c1,d1
pk2,c2,d2
pk2,c3,d3
....

I was planning to have SSIS pull in the "before" data, run a custom C# program in SSIS against it to massage the data to vertical (3-column format), then export the massaged data to a new text file. The new text file would later be imported into a sql table using SSIS. 

View 5 Replies View Related

SQL Server 2008 :: Date Format Not Converting To Dd / Mm / Yy

Sep 1, 2015

I have a date field stored in a character type field ( this field is a spare one in an ERP package )I am trying to validate and convert to a date format using the following;

[code="CASE WHEN isdate( arc.UserField1 )= 1 then
CAST( arc.UserField1 as DateTime )
ELSE
CAST( '01/01/1900' as Datetime )
END"] [/code]

The results are ;

Date Result
29/09/08 1900-01-01 00:00:00.000
09/06/15 2015-09-06 00:00:00.000
18/03/13 1900-01-01 00:00:00.000
09/10/14 2014-09-10 00:00:00.000

as you can see - the date 29/09/08 is a valid UK date format - however it fails the validation. 09/06/15 converts to 6th September 2015 ( mm/dd/yy ) format...What do I need to do to force this to identify as dd/mm/yy date format ?

View 9 Replies View Related

SQL Server 2012 :: Better Way To Query A Hierarchy Table?

Jan 21, 2015

I have a table named 'DepartmentItem' which is designed with hierarchy structure. The column 'ParentId' from table DepartmentItem indicates parent-child relationship and department root among records. I have written and run a user-defined function I use recursive approach, but the function runs slowly.

My question: is there a better way to query that hierarchy table instead of using recursive?

** The current user-defined function that is written using recursive:

CREATE FUNCTION dbo.fnGetDepartmentTree
(
@departmentItemId int
)
RETURNS TABLE
AS
RETURN
with DepartmentItemTree(DepartmentItemId , DepartmentItemTypeId , ParentId, ItemOrder, Level)

[code].....

** And definition of table 'DepartmentItem' :

DepartmentItemId int IDENTITY(1,1) NOT NULL,
ParentId int NULL, -- Each department root starts when this column is NULL or the current row is department root. If it is not NULL then the current row has ParentId whose record has DepartmentItemId = ParentId of the current row (see more below)
IsActive bit NOT NULL DEFAULT ((1)),

[Code] .....

View 2 Replies View Related

SQL Server 2008 :: Converting Datatype Of A Computed Column?

Mar 10, 2015

I have a computed column that I want to cast as decimal.

The two columns it calculates from are both varchar.

Why can I cast the column as INT, but when I try to cast as decimal, I get the following error?

Arithmetic overflow error converting varchar to data type numeric.

what this error means and why I get it only when I want to cast to decimal.

View 8 Replies View Related

SQL Server 2008 :: Float Value Converting Before Concatenate Get Rounded?

Mar 30, 2015

In My table formula(varchar) and value(Float) column.

Example

formula --> (A+B)/C

I am trying to create same expression with value But my below example showing value gets rounded if it is big.

I am trying to do,

Declare @a float = 123456.235
SELECT CONVERT( varchar , @a) Result=>123456

any way to keep same value as It is?

View 5 Replies View Related

SQL Server 2008 :: Conversion Failed When Converting Nvarchar Value To INT

Apr 9, 2015

Declare @specificDB nvarchar(max) = 'AdventureWorksDW2012'
,@tablename nvarchar(max) = 'DimAccount'

declare @localtab INT
SET @localtab = (SELECT Convert(INT,('select count(*) from ' + @specificDB+'.'+'INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = ''BASE TABLE'' AND Table_name = ' + @tablename)))
Print @localtab
Print @localtab

----

Msg 245, Level 16, State 1, Line 8
Conversion failed when converting the nvarchar value 'select count(*) from AdventureWorksDW2012.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE' AND Table_name = DimAccount' to data type int.

View 2 Replies View Related

SQL Server 2008 :: Converting Inline Query Into Join

Oct 16, 2015

I have an inline query that I am trying to convert it into JOIN, results are not coming out the same:

Original query:

SELECT distinct
(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '3') As DcountMy query:

[code]....

View 8 Replies View Related

SQL Server 2008 :: Show Item Only Once In A List?

Jul 30, 2015

write a query that lists item, mfg serial number, and inspection date. The caveat is that, where there is more than one record for an item, he only wants item listed once. For example:

PartNumberInspectionDateManufacturerSerialNumber
1A144878/4/2013.1101VAS073-7
3L115931/28/2014009VAS206-1
1/29/2014009VAS206-1
2/21/2014009VAS206-1
7/22/2014009VAS206-1
2A285631/1/2015010115ND9CV02
1A203921/3/2015010315ND9PV45
1/4/2015010315ND9PV45
3/1/2015010315ND9PV45
2A2794310/20/20120103700P003

View 9 Replies View Related

SQL Server 2008 :: Converting Job Duration From Job History And Use It On Email Notifications?

Apr 20, 2015

I am trying to convert the @runtime to hh/mm/ss format as it's currently printing milliseconds.

Declare @runtime varchar(100)
Select @runtime = sum(run_duration)
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
where j.name = 'Accounts_From_App03'
and run_date = CONVERT(varchar(8), getdate(), 112)
Declare @Body varchar(100)
Set @Body = 'The Job completed successfully. Duration:' + @runtime

exec master.dbo.sp_SQLNotify 'App02@email.com', 'team@email.com', 'Scheduled Job Success' ,@Body

View 9 Replies View Related

SQL Server 2008 :: Conversion Failed When Converting Varchar Value - Error

Sep 24, 2015

I am getting the following message when trying to run the below sql

Msg 245, Level 16, State 1, Line 15

Conversion failed when converting the varchar value 'Select COUNT(*) From Adventures.dbo.tblLibrary' to data type int.

Declare @Datestart as Date
Declare @CntBefore as int
Declare @CntNow as int
Declare @Database as varchar(30)
Declare @TableName as varchar(60)

[Code] ....

View 9 Replies View Related

SQL Server 2008 :: List Store Procedures Contents?

Jul 27, 2015

who i can to list the content of all store procedures and funtions in text file ?

View 3 Replies View Related

SQL Server 2008 :: Converting SAS Dates In A Float Format To Datetime Values

Mar 17, 2015

I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.

View 0 Replies View Related

SQL Server 2008 :: Error Converting Data Type Varchar To Numeric

Jun 17, 2015

As per my requirment using below script to convert from varchar to numeric.

declare @stg varchar(50)
set @stg='12,000,00'
select CONVERT(NUMERIC(17,2),@stg)

Getting error : Error converting data type varchar to numeric

View 6 Replies View Related

SQL Server 2008 :: Filter List Of Employees Where Sum Of VALIDATED Hours Is Less Than 80?

Mar 12, 2015

How do I filter a list of Employees where the Sum of "VALIDATED" hours is less than 80? For example.

Here is the flat table

SELECT EMP_NO, hours, IsValidated, rate_type
FROM Pay_Records
WHERE pay_period_id = 2
Order by EMP_NO

Output will be something like this

12345 | 2 |true |REG
12345 | 15 |false |OVR
12345 | 30 |true |OVER
33334 |2| true |REG

Total Validated hours for the Employee 12345 will be 32 NOT 47. How do I list employees who worked less than 80 validated hours. The hours are validated only when it is true.

View 2 Replies View Related

SQL Server 2008 :: How To Change Query So Apple Does Not Show Up In List

Apr 2, 2015

I am trying to print Companies with less than 100 employees for all dates.Here's my table structure

Create table CompanyEmployeeArchive(
Company varchar(100) not null, Employees int, Dateinserted date)

Insert into CompanyEmployeeArchive values('Microsoft',1001,'2015-01-01')
Insert into CompanyEmployeeArchive values('Microsoft',1050,'2015-02-01')
Insert into CompanyEmployeeArchive values('Microsoft',1600,'2015-03-01')
Insert into CompanyEmployeeArchive values('IBM',10,'2015-01-01')
Insert into CompanyEmployeeArchive values('IBM',80,'2015-02-01')
Insert into CompanyEmployeeArchive values('Apple',90,'2015-01-01')
Insert into CompanyEmployeeArchive values('Apple',900,'2015-02-01')
Insert into CompanyEmployeeArchive values('Apple',1000,'2015-03-01')

I want companies that have employees less than 100 for all dates i.e. Only IBM. Apple has < 100 employees only on one month.Select Company, dateinserted, employees from CompanyEmployeeArchive group by company,dateinserted,employees having employees < 100 order by company, dateinserted this query lists Apple too. How can I change the query so Apple does not show up in the list.

View 4 Replies View Related

SQL Server 2008 :: Display List Of Indexes - Add Database Name In Results

Apr 6, 2015

I written a proc to display the list of Indexes But I needed to print the database where the objects do belong to. How I should write the Dynamic script to add the database Id? I thought to use derived table kind of stuff, but unable to find a solution.

ALTER PROC [dbo].[USP_INDEXCHECK]
AS
DECLARE @sql NVARCHAR(max)
DECLARE @DB VARCHAR(max)
DECLARE databasecursor CURSOR FOR

[Code] .....

View 2 Replies View Related

SQL Server 2008 :: XML Nodes To Retrieve Child And Its List Of Values

Apr 8, 2015

i was trying to use the XML read functionality using t-SQL for XML attached.The column is coming with the token names and token-values in XML format and we are using the XML nodes() functionality to read the token names and token value.I am able to read only the parent token names and its values(using the sql attached) and could not be able to get the child token names and its values.how can i acheive the tokenNames with its values with the SQL query.i am attaching both SQL script which i am using and the XML entity.

View 9 Replies View Related

SQL Server 2008 :: Query Plan Output List (IsBaseRow)

May 6, 2015

Looking at an execution plan today I noticed something I've not seen before. The plan includes a non clustered index seek, followed by a RID lookup on the heap. The output list for the index seek contains the expected "Bmk" column (in this case "Bmk1473"), but also includes "IsBaseRow1475". This isn't a column from the table.

View 3 Replies View Related

SQL Server 2008 :: Find The List Of Servers By Querying At Active Directory?

Mar 3, 2015

Is there anyway,can we find the list of servers by querying at active directory?

View 3 Replies View Related

SQL Server 2008 :: Find List Of Stored Procedure Used By Crystal Reports

Jul 1, 2015

We have more that 500 crystal reports and we would like to find out list of stored procedure used by crystal reports. Can we find out ?

View 4 Replies View Related

SQL Server 2008 :: Turn Field Values Into Comma Separated List

Aug 11, 2015

I have about 100 K records of the form below in Example 1 and I would like to turn them into the form of Example 2, basically turn the entries in field2 into a coma separated list of values sorted by field1.

Example 1:

field1_field2
1_____a
1_____b
1_____c
2_____f
2_____g

and I would like to get it in the form

Example 2:

field1_field3
1_____a,b,c
2_____f,g

View 2 Replies View Related

SQL Server 2012 :: Query To Generate Relationship (Parent Child Hierarchy From A Table)

Jul 18, 2015

I am working on a query to generate parent child hierarchy from a table.

Table has below records.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable

[Code] ...

how to achieve this.l tried with temp tables it doesn't work.

View 5 Replies View Related

SQL Server 2008 :: Arithmetic Overflow Error Converting Nvarchar To Data Type Numeric

Oct 13, 2012

When I run the following sql query:

"update table set price = price * 1.1 "..

I get the following error : "Msg 8115, Level 16, State 8, Line 1.. Arithmetic overflow error converting nvarchar to data type numeric. The statement has been terminated."

The table is set to nvarchar, and i am just trying to make the prices go up 10%.

View 9 Replies View Related

SQL Server 2008 :: SSIS Derived Column Transformation Failing On Converting Blank Rows

Jul 30, 2015

I have flat file source from which data is imported to a Sql table.The target column is int and input column is string .The column has some numeric values and some blank values.when I tried to convert into int values it fails.

View 7 Replies View Related

SQL Server 2008 :: List Of Commands That Require Exclusive Access In Order For Command To Complete

Aug 10, 2015

Any list of commands that require exclusive access in order for the command to complete? I had an instance today where a DBA executed sp_changedbowner command which is the alter database command on a production database and it locked it up.

View 0 Replies View Related

Analysis :: Hierarchy Based On Dimension Table Joined Multiple Times Against A Fact Table?

Aug 11, 2015

I am working on a model where I have a sales fact table. Each fact record has four different customer fields (ship- to, sold-to, payer, and bill-to customer). I have one customer dimension table that joins to the sales fact table four times (once for each of the customer fields above).  When viewing the data in Excel, I would like to have four hierarchies (ship -to, sold-to, payer, and bill-to customer) within Customer. 

Is there a way to build hierarchies within my Customer dimension based on the same Customer table?  What I want is to view the data in Excel and see the Customer dimension.  Within Customer, I want four hierarchies. 

View 2 Replies View Related

SQL Server 2008 :: Insert From Table 1 To Table 2 Only If Record Doesn't Exist In Table 2?

Jul 24, 2015

I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query

INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)

[Code] ....

View 4 Replies View Related







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