SQL Server Performance

sql 2005 left join question

Discussion in 'SQL Server 2005 General Developer Questions' started by aaron, Jan 25, 2010.

  1. aaron New Member

    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!
  2. FrankKalis Moderator

    Can you please post the table structures, some sample data and the required result?

Share This Page