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
2
Medium Priority
?
350 Views
Last Modified: 2013-12-06
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
Comment
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
  • Learn & ask questions
2 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
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

by:
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

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

596 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question