January 5, 2010 at 11:47 am
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.
January 5, 2010 at 11:57 am
What have you tried so far?
Are you getting any errors?
If you could provide code that you have tried with the point where you get hung-up on, we can better assist.
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
January 5, 2010 at 12:14 pm
By the way, most of us are volunteers with paying jobs of our own, and saying this: Its very Urgent. i may lose my job if dont get this., doesn't necessarily mean you will get help faster. Plus, if you really want faster help, read the first article I have referenced below in my signature block regarding asking for help. The more work you do up fornt for us, the more likely you will get help quicker.
I really don't have time right now to convert your posted info into a usable format from which I can work.
Back to installing SQL Server 2008 and restoring some databases for a major system upgrade.
January 5, 2010 at 12:20 pm
Lynn Pettis (1/5/2010)
By the way, most of us are volunteers with paying jobs of our own, and saying this: Its very Urgent. i may lose my job if dont get this., doesn't necessarily mean you will get help faster. Plus, if you really want faster help, read the first article I have referenced below in my signature block regarding asking for help. The more work you do up fornt for us, the more likely you will get help quicker.I really don't have time right now to convert your posted info into a usable format from which I can work.
Back to installing SQL Server 2008 and restoring some databases for a major system upgrade.
In agreement here. Trying to fix generic errors related to a failed job run. Thus, having as much detail as possible really helps us to help you.
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
January 5, 2010 at 12:27 pm
By the way, this is the exact same response you got the last time you posted this very question.
http://www.sqlservercentral.com/Forums/Topic834653-338-1.aspx
Please do not double post. Most of the regular members here on SSC are going to give you the same response as you got here so reposting will not get your better help.
You need to follow the instructions in the link that Lynn referenced. If you can't help us help you, this must not be too urgent.
January 5, 2010 at 12:32 pm
I can see why it is urgent now. It has been three weeks since you initially asked for help. Obviously it must not have been to important when you first asked for help as you never bothered to follow through with what was requested.
January 5, 2010 at 12:34 pm
Based on the prior thread, you posted you would try the things we suggested. If that has been done, then please show us what you have done.
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
January 5, 2010 at 1:28 pm
You haven't posted enough details for me to be sure this is correct, but here's what I can come up with:
;
WITH CTE
AS (SELECT
SiteID,
LocationID,
SiteCode,
SiteType,
PowerType,
SiteTypeRenewal AS RenewalDate
FROM
dbo.Site
INNER JOIN dbo.Sites_SiteType
ON Site.SiteSiteTypeID = Sites_SiteType.ID
INNER JOIN dbo.Sites_PowerType
ON Sites.PowerTypeID = Sites_PowerType.ID
INNER JOIN dbo.SiteTypeRenewal
ON Sites.SiteID = SiteTypeRenewal.SiteID
AND Sites.SiteTypeID = SiteTypeRenewal.SiteTypeID
WHERE
Site.SiteID = @SiteID
AND SiteTypeRenewal <= @RenewalDate
UNION ALL
SELECT
SiteID,
LocationID,
SiteCode,
SiteType,
PowerType,
PowerTypeRenewal AS RenewalDate
FROM
dbo.Site
INNER JOIN dbo.Sites_SiteType
ON Site.SiteSiteTypeID = Sites_SiteType.ID
INNER JOIN dbo.Sites_PowerType
ON Sites.PowerTypeID = Sites_PowerType.ID
INNER JOIN dbo.PowerTypeRenewal
ON Sites.PowerTypeID = PowerTypeRenewal.PowerTypeID
AND Sites.SiteID = PowerTypeRenewal.SiteID
WHERE
Site.SiteID = @SiteID
AND PowerTypeRenewal <= @RenewalDate)
SELECT TOP 1
*
FROM
CTE
ORDER BY
RenewalDate DESC ;
You'll need to convert it to a stored procedure and declare the input parameters, but it should do what I think you need.
If not, please post the necessary data to clarify what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2010 at 10:47 am
Shahid,
Out of curiosity, what did you expect to get from this foruim that you didn't already get from SQLTeam three weeks ago? See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=137201.
January 8, 2010 at 7:22 am
As expected, this has turned into Boot Hill as far as postings from the OP go.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2010 at 7:29 am
Scared him off, I think we did.
January 8, 2010 at 11:49 pm
i think you have not studied the scenario clearly.... in sqlteam there was similar question but not the same... once again read it carefully then u will get... u can check in my old post it was based on max of ID but now it is completely based on Date....
January 8, 2010 at 11:57 pm
i asks the moderator to please delete this post... i think i made a mistake by posting here my problem....
January 9, 2010 at 3:12 am
shahid00704 (1/8/2010)
i think you have not studied the scenario clearly.... in sqlteam there was similar question but not the same... once again read it carefully then u will get... u can check in my old post it was based on max of ID but now it is completely based on Date....
Did you look at the code GSquared gave you? Is it correct? Does it work? If not, what's wrong?
We're happy to help, but it goes both ways, if we're going to be able to help you, you need to provide enough information and feedback that we can help you.
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
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply