Stored Procedure is needed but unable to write.

Question

Stored Procedure is needed but unable to write.

shahid00704

SSC Veteran

Points: 272
More actions
December 15, 2009 at 10:59 am

#139990

I have Five tables and i want to get result from these five tables providing some checks.
Site
----------------------------
SiteID int,
SiteTypeID int,
PowerTypeID int,
LocationID int,
SiteCode nvarchar(500)
Sites_SiteType
-----------------------------
ID int,
Type nvarchar(500)
Sites_PowerType
-----------------------------
ID int,
Type nvarchar(500)
SiteTypeRenewal
-----------------------------
RenewalID int,
SiteTypeId int,
SiteID
PowerTypeRenewal
-----------------------------
RenewalID int,
PowerTypeId int,
SiteID
Site Data
----------------
SiteID SiteTypeID PowerTypeID LocationID SiteCode
1 10 3 100 xxx
2 20 1 200 yyy
3 30 2 300 zzz
4 30 1 100 xxx
Sites_siteType Data
----------------------------
ID Type
10 RoofTop
20 Transformer
30 GreenField
SiteTypeRenewal
----------------------------
RenewalID SiteTypeID SiteID
1 20 1
2 30 2
3 10 3
4 10 2
5 20 3
Sites_PowerType Table Data
----------------------------
ID Type
1 Generator
2 T.VStation
3 Transformer
PowerTypeRenewal
----------------------------
RenewalID PowerTypeID SiteID
1 2 1
2 3 2
3 1 3
4 1 2
5 2 3
here i want to check in the renewals(SiteTypeRenewal and PowerTypeRenewal) tables if there are records for a particular (SiteID) then extract maximum(last) record based on SiteID and display Type of Site_siteType and Site_PowerType by inner joining from renewal table. here if SiteID=3, it should give me LocationID=300,SiteCode=zzz SiteType=Transformer and PowerType=T.VStation.
else check in the Site Table whether that SiteID is available or not if available extract that record joining Sites_SiteType and Site_PowerType and display it ex here for this if SiteID=4, it should give me LocationID=100,SiteCode=xxx SiteType=GreenField and PowerType=Generator.
my final result looks like this...
for SiteID=3
SiteID LocationID SiteCode SiteType PowerType
3 300 zzz Transformer T.VStation
for SiteID=4 which is not available in renewals tables
SiteID LocationID SiteCode SiteType PowerType
4 100 xxx GreenField Generator
Please Reply if know how to write.
Thanks in Advance,
Shahid.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

First, the only way you will learn is to do. If you have problems, ask specific questions.

Second, if you really want help, then please read and follow the instructions in the first article I have referenced in my signature block below.

shahid00704 SSC Veteran Points: 272 More actions · Answer 2

Thanks SSChampion... i will follow the instructions u provided.

Thanks.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

A suggestion:

Try writing it out in pseudo-code first. Once you have the pseudo-code, you can start learning the commands that fit the logic you need.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

John Rowan SSC Guru Points: 56440 More actions · Answer 4

This is a repost, please continue thread and responses here:

http://www.sqlservercentral.com/Forums/Topic842291-149-1.aspx?Update=1

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

John Rowan (1/5/2010)
This is a repost, please continue thread and responses here:
http://www.sqlservercentral.com/Forums/Topic842291-149-1.aspx?Update=1

Actually, this is the original thread and the one you've linked is his 'now it's really urgent' repost.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Rowan SSC Guru Points: 56440 More actions · Answer 6

Yep, you are correct. I mis-stated this as it should have read : "This has been re-posted, please.....".

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden