Please I need a stored Procedure. Please Reply.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Please I need a stored Procedure. Please Reply.

Author

Topic

shahid00704
Starting Member

11 Posts

Posted - 2010-01-05 : 13:28:46

Hello All I want a procedure based on some criteria.

Its very Urgent. i may lose my job if dont get this.

I have Five Table. here are the details.

Site
----------------------------
SiteID int,
SiteTypeID int,
PowerTypeID int,
LocationID int,
SiteCode nvarchar(500),
creationdate datetime

Sites_SiteType
-----------------------------
ID int,
Type nvarchar(500)

Sites_PowerType
-----------------------------
ID int,
Type nvarchar(500)

SiteTypeRenewal
-----------------------------
RenewalID int,
SiteTypeId int,
SiteID,
creationdate

PowerTypeRenewal
-----------------------------
RenewalID int,
PowerTypeId int,
SiteID,
creationdate

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 creationdate
1 20 1 31/12/2009
2 30 1 02/1/2010
3 10 1 05/1/2010
4 10 1 15/1/2010
5 20 2 05/1/2010

Sites_PowerType Table Data
----------------------------
ID Type
1 Generator
2 T.VStation
3 Transformer

PowerTypeRenewal
----------------------------
RenewalID PowerTypeID SiteID creationdate
1 2 1 31/12/2009
2 3 1 02/1/2010
3 1 1 05/1/2010
4 1 1 15/1/2010
5 2 2 05/1/2010

here i want to check in the renewals(SiteTypeRenewal and PowerTypeRenewal) tables if there are records for a particular

(SiteID) if SiteID is available in the renewals table then go for check for date column, if the current date matches any

column of creationdate then extract that record from the renewals tables else extract the creationdate column record which is

just less than the currentdate and max of all old dates. here if i say for siteID=1 and creationdate=5/1/2010, it should give

the siteID=1,LocationID=100,SiteCode=xxx,SiteType=RoofTop and powertype=Generator.

assume if siteid=1 and currentdate=04/1/2010 then it should go for first siteID check and if available in renewals tables

then check for dates. here in renewals we dont have creationdate=04/1/2010 then it should extract the record

siteid=1,LocationID=100,SiteCode=xxx,SiteType=GreenField and powertype=Transformer.All checking in Renewals tables only.
and if there is no record present in the renewals table based on SiteID then extract the record which is in Site Table.
for example if i say siteID=3
siteid=3,LocationID=300,SiteCode=zzz,SiteType=GreenField and powertype=T.VStation

my final result looks like this...

for SiteID=1
SiteID LocationID SiteCode SiteType PowerType

1 100 xxx RoofTop Generator

for SiteID=3 which is not available in renewals tables

SiteID LocationID SiteCode SiteType PowerType

3 300 zzz GreenField T.VStation

Hope to get a postitive reply.

Thanks in advance. Please Help.

I need it very urgently. Please.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-05 : 14:50:02

I will try and work on an answer in the spare time...but to give you hints you are going to need to join your main table with the dates to both RenewalType tables and the TypeNames. Join them on RenewalID and CreationDate, make sure these are all LEFT joins.

Add a column using COALESCE( Sites_siteType.Type, Sites_PowerType.Type) to get the correct power type to be filled in.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-05 : 16:01:13

Also asked here: http://www.sqlservercentral.com/Forums/Topic842291-149-1.aspx
and (3 weeks ago) here: http://www.sqlservercentral.com/Forums/Topic834653-338-1.aspx

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-05 : 16:08:02

And a duplicate of a previous post from SQLTeam: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=137201

--
Gail Shaw
SQL Server MVP

shahid00704
Starting Member

11 Posts

Posted - 2010-01-06 : 05:21:05

please somebody reply.... i will not post any more after i get this.... please help me...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-06 : 05:33:23

To make it a bit more clear...
On a fast look it looks like shahid00704 is coming up with the same question he has ask 3 weeks ago.

But have a closer look and you will see that his question is similar but not identic.
Maybe someone has the time...?

No, you're never too old to Yak'n'Roll if you're too young to die.

shahid00704
Starting Member

11 Posts

Posted - 2010-01-09 : 01:33:28

please..... may be today is my last and final day in company..... sorry for disturbing but try to understand...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-09 : 11:18:17

[code]
SELECT s.SiteID,
s.LocationID,
s.SiteCode,
COALESCE(st.Type,stm.Type) AS SiteType,
COALESCE(pt.Type,ptm.Type) AS powertype
FROM Site s
CROSS APPLY( SELECT Type
FROM Sites_SiteType
WHERE ID=s.SiteTypeID)stm
CROSS APPLY( SELECT Type
FROM Sites_PowerType
WHERE ID=s.PowerTypeId)ptm
OUTER APPLY (SELECT TOP 1 sst.Type
FROM SiteTypeRenewal str
INNER JOIN Sites_SiteType sst
ON sst.ID = str.SiteTypeID
WHERE str.SiteID=s.SiteID
AND str.creationdate < GETDATE()
ORDER BY str.creationdate DESC) st
OUTER APPLY (SELECT TOP 1 spt.Type
FROM PowerTypeRenewal ptr
INNER JOIN Sites_PowerType spt
ON spt.ID = ptr.PowerTypeId
WHERE ptr.SiteID=s.SiteID
AND ptr.creationdate < GETDATE()
ORDER BY ptr.creationdate DESC) pt
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources