January 26, 2010 at 8:50 pm
I have several sqls scripts that I would like to combine. I would like to know how to calculate the max(receivedate) in one place and use it every where else.
Basically I want to use the results for the folliowing query and apply the results in the lower reference to max(receivedate).
select P.region, P.Gnum, P.Gname,
P.effectivedate,
MaxReceiveDate = Max(received_Date), p.Status
from dbo.plan_table P
group by P.region, P.Gnum, P.Gname, P.effectivedate,
received_Date, p.Status.
Basically the max(receivedate) from the above query returns the value for all records searched.
Basically how would I change the following query to only calculate Max(received_Date) in one place.
The sql currently looks like the following:
select P.region, P.Gnum, P.Gname,
P.effectivedate,
MaxReceiveDate = Max(received_Date), p.Status
from dbo.plan_table P
group by P.region, P.Gnum, P.Gname, P.effectivedate,
received_Date, p.Status
left join
(select distinct Z.Gnum, R.Requests, Z.MaxReceiveDate
from
(select distinct Gnum, count(*) as Requests
from dbo.table1
where receive_date > Z.MaxReceiveDate
group by Gnum
) R
left join
(select distinct P.gnumber,
MaxReceiveDate = Max(Received_Date),
from dbo.table2
group by P.gnumber
) Z
On Z.GNum = R.Gnum
) as J2
on P.Gnum =J2.Gnum
Thanks!
January 26, 2010 at 9:02 pm
Any reason you don't use say
declare @MaxRecieveDate datetime
select @maxRecievedDate = max(recieveDate)
from table
where col1 = 123
By doing this you set the maxRecieveDate to the variable @MaxRecieveDate. Now you can use that variable anywhere else in the the following queries.
Fraggle
January 26, 2010 at 9:08 pm
Fraggle is spot on. Just assign your date to the variable and then re-use the variable throughout the code.
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 28, 2010 at 8:51 am
thank you very much!
January 28, 2010 at 11:06 am
dianerstein (1/28/2010)
thank you very much!
You're welcome
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 29, 2010 at 4:13 am
Glad to help danerstein
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply