Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Sql Stored Procedure

Posted on 2013-12-06
Medium Priority
351 Views
Hi Experts

I would like to build a sp in mssql for the attached file with question and target answer

That's all

Thanks
Book1.xlsx
0
Question by:beckyng

LVL 34

Expert Comment

ID: 39701221
SELECT Parent, Child, Level, Hierachy, Usage,
Usage * CASE WHEN Level = 1 THEN 1 ELSE (SELECT Usage FROM MyTable WHERE Level = 1) END AS [New Usage]
FROM myTable AS T
0

LVL 11

Accepted Solution

John_Vidmar earned 1400 total points
ID: 39701462
``````-- test data
select	Parent	= 'P001'
,	Child	= 'C002'
,	Usage	= 2.000
into	#temp
union all
select	Parent	= 'C002'
,	Child	= 'K003'
,	Usage	= 1.000
union all
select	Parent	= 'K003'
,	Child	= 'T001'
,	Usage	= 0.027
go

-- recursive call
WITH
recursive_CTE
(	Parent
,	Child
,	Hierarchy
,	Usage
,	Level
)
AS
(
SELECT	a.Parent
,	a.Child
,	CAST(a.Parent +'.'+ a.Child as varchar(50))
,	CAST(a.Usage as numeric(9,3))
,	1
FROM	#temp	a
LEFT
JOIN	#temp	b	ON	a.Parent = b.Child
WHERE	b.Child IS NULL

UNION ALL

SELECT	b.Parent
,	b.Child
,	CAST(a.Hierarchy + '.' + b.Child as varchar(50))
,	CAST(a.usage * b.usage as numeric(9,3))
,	a.Level + 1
FROM	recursive_CTE	a
JOIN	#temp		b	ON	a.Child = b.Parent

)
SELECT	Parent
,	Child
,	Level
,	Hierarchy
,	Usage
FROM	recursive_CTE
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
###### Suggested Courses
Course of the Month11 days, 19 hours left to enroll