sql server 2005 query

  • 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!

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply