January 25, 2010 at 1:43 pm
I am basically new to sql server 2005 and I have a query question to
ask.
I want to create a final query result that shows all the data that
corresponds to gnumber on one line. The final display line needs to
display the following data:
P.gnumber,P.name, P.Kind, P.Userdate2, mths_since_run, Z.MaxReceiveDate
I want to link the following two queries together. I am thinking that I
could left join the two queries together by gnumber, but have not been
successful so.
The two separate queries do run fine separately. The following are the
two separate queries: distinct P.gnumber,P.name, P.Kind, P.Userdate2,
mths_since_run,MthSincereceived,
1.select distinct P.gnumber,P.name, P.Kind, P.Userdate2,
mths_since_run,
DateDiff(Month,
P.workdate,ltrim(rtrim(str(month(dateadd(month,0,getdate()))))) + '/01/' +
ltrim(rtrim(str(year(dateadd(year,0,getdate())))))) as MthSincereceived,
from
(select distinct P.gnumber,P.name
P.Userdate2, P.Kind,
count(distinct
ltrim(rtrim(str(month(dateadd(month,0,Run_Date))))) + ltrim(rtrim(str(
year(dateadd(year,0,Run_Date)))*100))) as mths_since_run
from dbo.tabA85C
where P.Kind in ('A1','C1','xy')
and P.Userdate2 > '2009-03-01 00:00:00.000'
group by P.gnumber,P.name, P.Kind,
) P
2. select distinct Z.gnumber, count(R.*) AS Requests, Z.MaxReceiveDate
from
from dbo.table1 R
LEFT JOIN
(select distinct P.gnumber,
MaxReceiveDate = Max(P.Received_Date),
from dbo.table2 P
group by P.gnumber
) Z
On Z.HNumber = R.Hnumber
where receive_date > Z.MaxReceiveDate
group by z.gnumber, Z.MaxReceiveDate
Thank you in advance!
January 25, 2010 at 1:47 pm
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
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 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply