Need Stored Procedure.Please Reply Its Very Urgent

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)

Need Stored Procedure.Please Reply Its Very Urgent

Author

Topic

shahid00704
Starting Member

11 Posts

Posted - 2009-12-15 : 08:09:51

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

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-15 : 09:04:13

HI,

declare @Site table(
SiteID int,
SiteTypeID int,
PowerTypeID int,
LocationID int,
SiteCode nvarchar(500))
insert into @Site (
SiteID, SiteTypeID, PowerTypeID, LocationID, SiteCode)
select 1, 10, 3, 100, 'xxx' union all
select 2, 20, 1, 200, 'yyy' union all
select 3, 30, 2, 300, 'zzz' union all
select 4, 30, 1, 100, 'xxx'

declare @Sites_SiteType table(
ID int,
Type nvarchar(500))

insert into @Sites_siteType
(ID, Type)
select 10, 'RoofTop' union all
select 20, 'Transformer' union all
select 30, 'GreenField'

declare @Sites_PowerType table(
ID int,
Type nvarchar(500) )

insert into @Sites_PowerType
(
ID, Type)
select 1, 'Generator' union all
select 2 ,'T.VStation' union all
select 3, 'Transformer'

declare @SiteTypeRenewal table(
RenewalID int,
SiteTypeId int,
SiteID Int )

insert into @SiteTypeRenewal(
RenewalID ,SiteTypeID, SiteID)
select 1, 20, 1 union all
select 2, 30, 2 union all
select 3, 10,3 union all
select 4, 10,2 union all
select 5, 20, 3

declare @PowerTypeRenewal table (
RenewalID int,
PowerTypeId int,
SiteID int)
insert into @PowerTypeRenewal (
RenewalID, PowerTypeID, SiteID)
select 1 ,2 ,1 union all
select 2 ,3 ,2 union all
select 3 ,1 ,3 union all
select 4 ,1, 2 union all
select 5 ,2, 3

DECLARE @siteid INT
SELECT @siteid = 3

SELECT s.siteid,LocationID,SiteCode,ISNULL(st1.type,st.type) AS SiteType,ISNULL(pt1.type,pt.type)
FROM @SITE AS S
INNER JOIN @Sites_siteType AS ST ON s.SiteTypeID = st.ID
INNER JOIN @Sites_PowerType AS pt ON s.powertypeid = pt.id
LEFT JOIN ( SELECT TOP 1 siteid,type FROM @SiteTypeRenewal
INNER JOIN @Sites_siteType on id = SiteTypeID WHERE SITEID = @siteid ORDER BY RenewalID DESC ) AS st1 ON s.siteid = st1.SiteID
LEFT JOIN ( SELECT TOP 1 siteid,type from @PowerTypeRenewal
INNER JOIN @Sites_siteType on id = powerTypeID WHERE SITEID = @siteid ORDER BY RenewalID DESC ) AS pt1 ON s.siteid = pt1.SiteID
WHERE s.siteid = @siteid

Thanks,
vikky.

shahid00704
Starting Member

11 Posts

Posted - 2009-12-15 : 09:09:30

Hi Vikky,

First of all thanks for ur Reply.

Here i am unable to understand what u r doing?? can u explain me what u r doing...

Thank you.

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-15 : 09:22:52

hi,

r u get correct result or not ?

first i declared the @siteid.

1) i select the values form sites and then joined with the @Sites_siteType AND @Sites_PowerType.
2)Then i getted the resent renevalsites and renevalpowers for the given siteid = ( 3 or 4) in subquery.

3) i joined the subquery subquery with siteids.
4) in select isnull(st1.type,st.type) AS SiteType,ISNULL(pt1.type,pt.type)
Gives the if avalable recent reneval other wise gets which entries available in table.

r u understand or not ?

Thanks,
vikky.

shahid00704
Starting Member

11 Posts

Posted - 2009-12-15 : 09:44:22

Hello vicky,
I got ur points. But the thing is i have to manually select data into @Site,@Site_SiteType,@Site_PowerType,@RenewalSite,@RenewalPower... Is there any way by which it automatically selects data into these temp tables... because if i have large amount of data then it is impossible.
Please reply.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-15 : 10:37:24

Those are just table definitions (sample data for testing) that he has built...

use the query alone and replace all the @tables with your actual table names.

If you face any issues with the query, post back.

shahid00704
Starting Member

11 Posts

Posted - 2009-12-15 : 12:27:15

Hello Vijayisonly and Vicky.
I tried using ur code but getting errors

declare @New_Site table(
SiteID int,
SiteType int,
PowerType int,
LocationID int,
SiteCode nvarchar(500))
insert into @New_Site(SiteID, SiteType, PowerType, LocationID, SiteCode)
select SiteID,SiteType,PowerType,LocationID,SiteCode from New_Site

Declare @Site_SiteTypes table(ID int,Type nvarchar(200))

insert into @Site_SiteTypes(ID, Type)
Select ID,Type from Site_SiteTypes

declare @Sites_PowerTypes table(ID int,Type nvarchar(500))

insert into @Sites_PowerTypes(ID, Type)
Select ID,Type from Sites_PowerTypes

declare @Site_Renewal_SiteType table(SiteTypeRenewalID int,SiteTypeID int,SiteID Int )

insert into @Site_Renewal_SiteType(SiteTypeRenewalID ,SiteTypeID, SiteID)
select SiteTypeRenewalID ,SiteTypeID, SiteID from Site_Renewal_SiteType

declare @Site_Renewal_PowerType table (PowerTypeRenewalID int,PowerTypeID int,SiteID int)
insert into @Site_Renewal_PowerType (PowerTypeRenewalID, PowerTypeID, SiteID)
select PowerTypeRenewalID, PowerTypeID, SiteID from Site_Renewal_PowerType

DECLARE @siteid INT

SELECT S.SiteId,S.LocationID,S.SiteCode,ISNULL(st1.type,st.type) AS SiteType,ISNULL(pt1.type,pt.type)
FROM @New_Site AS S
INNER JOIN @Site_SiteTypes AS ST ON S.SiteType = ST.ID
INNER JOIN @Sites_PowerTypes AS PT ON S.PowerType = PT.ID
LEFT JOIN ( SELECT TOP 1 SiteID,SiteTypeID FROM @Site_Renewal_SiteType
INNER JOIN @Site_SiteTypes on ST.ID = SiteTypeID WHERE SITEID = @siteid ORDER BY SiteTypeRenewalID DESC ) AS st1 ON s.SiteId = st1.SiteID
LEFT JOIN ( SELECT TOP 1 SiteID,PowerTypeID from @Site_Renewal_PowerType
INNER JOIN @Sites_PowerTypes on PT.ID = PowerTypeID WHERE SITEID = @siteid ORDER BY PowerTypeRenewalID DESC ) AS pt1 ON s.SiteId = pt1.SiteID
WHERE S.SiteID = @siteid

the errors are
Msg 4104, Level 16, State 1, Line 35
The multi-part identifier "ST.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 35
The multi-part identifier "PT.ID" could not be bound.
Msg 207, Level 16, State 1, Line 35
Invalid column name 'type'.
Msg 207, Level 16, State 1, Line 35
Invalid column name 'type'.

there is no column with the name type in Site_Renewal_SiteType and Site_renewal_PowerType so it gives error. and i dont know y the multipart identifier cannot be bound error.
please reply as soon as possible.

Thanks,
Shahid

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-15 : 15:05:44

Just execute this...and let me know if the results are ok...

Make sure you are using your correct table names and column names (also check for case sensitivity with column names..looks like you have a case sensitive collation.

SELECT a.SiteID, a.LocationID,a.SiteCode,coalesce(st1.Type,st.Type) as SiteType,coalesce(pt1.Type,pt.Type)
FROM [Site] a
inner join Sites_SiteType st on a.SiteTypeID = st.ID
inner join Sites_PowerType pt on a.PowerTypeID = pt.ID
left join (
select t1.SiteID,t2.Type from
(select row_number() over(partition by SiteID order by RenewalID desc) as seq, * from SiteTypeRenewal) t1 
inner join Sites_SiteType t2 on t1.SiteTypeID = t2.ID 
where t1.seq = 1)
st1 on a.SiteID = st1.SiteID
left join (
select t3.SiteID,t4.Type from
(select row_number() over(partition by SiteID order by RenewalID desc) as seq, * from PowerTypeRenewal) t3 
inner join Sites_PowerType t4 on t3.PowerTypeID = t4.ID 
where t3.seq = 1)
pt1 on a.SiteID = pt1.SiteID

shahid00704
Starting Member

11 Posts

Posted - 2009-12-16 : 02:34:29

Hello Vijay,

Thanks Man... I really Thank u from bottom of my heart... u dont know what u did for me.. i was really very much tensed about this query because i dont know sql queries and procedures very well and no body is here to assist me.

I also thank to Mr.Vicky from bottom of my heart who also supported me.

Thanks SqlTeam.
Shahid.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-16 : 09:51:21

Np. You're welcome

shahid00704
Starting Member

11 Posts

Posted - 2010-01-05 : 13:31:05

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources