Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Sql Stored Procedure

Posted on 2013-12-06
Medium Priority
350 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
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backupâ€¦
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
###### Suggested Courses
Course of the Month8 days, 11 hours left to enroll