Prev: Get a list of subscribers
Next: Indexing Errors with Reporting Service Excel Exports
From: aaron on 25 Jan 2010 15:22
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!
From: Plamen Ratchev on 25 Jan 2010 16:45
Try something like this:

SELECT <columns>
FROM (<query #1>) AS A
LEFT OUTER JOIN (<query #2>) AS B
ON A.gnumber = B.gnumber;

Also, there is no need to use DISTINCT in your queries when you use GROUP BY.

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Get a list of subscribers
Next: Indexing Errors with Reporting Service Excel Exports