January 9, 2010 at 11:11 am
shahid00704 (1/8/2010)
@Lynn PettisWow... what a great job u did... by scaring me.... thats really grate....
First, let me make sure you understand something. With the exception of a Steve Jones, we are volunteers who are willing to help people who need help. We have jobs of our own, so we help when we can and where we can. You were asked to read an article on forum etiquette that showed you in a step by step fashion what and how to post information that would give you the best help. What you originally posted would require US to do work YOU should do up front before posting. The more work you do up front, the more responsive you are to questions the better help you will get.
My *sarcastic* response was in relation to the fact that you failed to respond to anything initially posted after your original post.
You want help? You will get it here, but you will need to work at helping us to help you.
January 9, 2010 at 11:29 am
shahid00704 (1/8/2010)
i asks the moderator to please delete this post... i think i made a mistake by posting here my problem....
I disagree that a mistake was made in posting the problem at SSC. The professionals at SSC are plenty willing to give you assistance on their free time. That free time, as we all know, is highly valuable - and they are just giving it away. When a volunteer asks for you to be more specific, or provide more information - it means they are ready to help and need you help them help you. If you are unwilling to help them help you, then they can do little to help you.
The professionals at this site are giving freely to the community to help any who really want it. When you answer the questions a volunteer asks of you - you are showing how important the topic is to you and how important it is to participate in solving the very question you asked.
Rather than blast the volunteers and get mad about not having a solution - help us 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 9, 2010 at 11:12 pm
I have five Tables as @Site,@SitesSiteType,@SitePowerType,@SiteTypeRenewal and @PowerTypeRenewal
declare @site table
(
SiteId int,
SiteTypeId int,
PowerTypeId int,
LocationId int,
SiteCode nvarchar(100),
CreationDate Datetime,
)
insert into @Site
select 1,10,3,100,'xxx','01/01/2010' union all
select 2,10,1,200,'yyy','5/01/2010' union all
select 3,20,1,300,'zzz','6/01/2010' union all
declare @SitesSiteType table
(
ID int,
[Type] nvarchar(100)
)
insert into @SitesSiteType
select 10,'Roof Top' union all
select 20,'Transformer' union all
select 30,'GreenField'
declare @SitesPowerType table
(
ID int,
[Type] nvarchar(100)
)
insert into @SitesPowerType
select 1,'Generator' union all
select 2,'T.V.Station' union all
select 3,'D.C'
declare @SiteTypeRenewal table
(
RenewalId int,
SiteTypeID int,
SiteId int,
CreationDate DateTime
)
insert into @SiteTypeRenewal
select 1,20,1,'05/01/2010' union all
select 2,30,1,'08/01/2010' union all
select 3,10,1,'09/01/2010' union all
select 4,10,1,'15/01/2010' union all
select 5,20,2,'04/01/2010' union all,
select 5,20,3,'10/01/2010'
declare @PowerTypeRenewal table
(
RenewalId int,
PowerTypeId int,
SiteId int,
CreationDate Datetime
)
insert into @PowerTypeRenewal
select 1,2,1,'05/01/2010' union all
select 2,3,1,'08/01/2010' union all
select 3,1,1,'09/01/2010' union all
select 4,1,1,'15/01/2010' union all
select 5,2,2,'04/01/2010' union all
select 6,3,3,'10/01/2010'
Here I have a scenario where i need to check first the siteID which is from Site table present in the @SiteTypeRenewal Table
and @PowerTypeRenewal Table. If the SiteID which is in @Site Table Not Present int renewals Table then get the Type from the
@SitesSiteType and @SitesPowertype based on SiteTypeID and PowerTypeID of @Site Table and If the SiteID Present in the
Renewals Tables then check for the Date, Here the criteria for date is it should check with current date. if there is a
creationdate in the renewals table which matches current date then get that Type from the @SitesSiteType table based on
SiteTypeID and @SitesPowerType based on PowerTypeID and if any row does not matches to the current date in the renewals
tables then go for the last date which is max of all old dates means get the date which is immediately less to the current
date and get that Type from the @SitesSiteType table based on SiteTypeID and @SitesPowerType based on PowerTypeID.
Here SiteTypeID and PowerTypeID are ForeignKeys to
@SitesSiteType and @SitesPowerType Tables ID'S.
for example if i check for siteID=1,
it should first check in the renewals table whether the SiteID is present or not in the renewals table. Here in my scenario
siteID=1 is present in @SitesSiteType and @SitePowerType Table. Now i want to have a check for Current Date. Let us Say Current Date is 09/01/2010.
Now it should Give me SiteID=1,SiteType="Roof Top" since the SiteTypeID whose date is current date in @SiteTypeRenewal Table is 10, PowerType="Generator"
since the PowerTypeID whose date is current date in @PowerTypeRenewal Table is 1, Location=100,SiteCode=xxx.
if SiteID=2, Since There is a SiteID in Renewals Tables. Now Go for Date Check. now we dont have a CreationDate which is equal to current Date so it should
go for the immediately less to current date here we have date which is less to current date is 04/01/2010, i may have creationdates less than to this
04/01/2010 date, so it should go for 04/01/2010 coz it is the latest date which is less to current date. siteID=2,SiteType="Transformer",
PowerType="T.V.Station",Location="200",SiteCode="yyy".
If SiteID=3, Since There is a SiteID=3 in renewals Tables, Now go for date Check. Since the date is greater than current date, i sould go for the date which
is immediately less to current date. here i dont have any more SiteID=3 to check for CreationDate Less to Current Date So i must get the
SiteType and PowerType Which is present in My @Site Table.
This was my question and the answer to this is here....
;with STypeRenewal as
(
select RenewalId,SiteId,SiteTypeId,row_number() over (partition by siteid order by creationdate desc) as rn from @SiteTypeRenewal
),
PTypeRenewal as
(
select RenewalId,SiteId,PowerTypeId,row_number() over (partition by siteid order by creationdate desc) as rn from @PowerTypeRenewal
),
Details as
(
SELECT a.SiteID, a.LocationID,a.SiteCode,coalesce(st1.SiteTypeId,a.SiteTypeId) as SiteType,coalesce(pt1.PowerTypeId,a.PowerTypeId) as PType
FROM @Site a
left join ( select RenewalId,SiteId,SiteTypeId from STypeRenewal where rn = 1)st1 on a.SiteID = st1.SiteID
left join ( select RenewalId,SiteId,PowerTypeId from PTypeRenewal where rn = 1)pt1 on a.SiteID = pt1.SiteID
)
select SiteId,LocationId,SiteCode,SiteType,Ptype,S.[Type] as SiteTypeName,P.[Type] as PowerTypeName from Details D
inner join @SitesSiteType S on D.SiteType = S.Id
inner join @SitesPowerType P on D.PType = P.Id
January 9, 2010 at 11:24 pm
Sorry EveryBody....
Actually i was so tensed that i was not able to understand anything....
once again Sorry.....
January 9, 2010 at 11:33 pm
Good, you provided much more information and this is what we needed to help you. One more thing I would add, however, is the expected results based on your sample data. You explained how the data should be processed, but showing us adds clarity, especially for someone like me that is visually oriented when solving problems. The explaination helps, but seeing what needs to happen aids me more.
January 10, 2010 at 6:34 am
Just for the record of it:
the solution shahid posted has been provided by ramireddy at social.msdn .
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply