Professional Web Applications Themes

Help in formulating a Query..... - Microsoft SQL / MS SQL Server

Ricky, Try this Select * from PIReportTemplateColumns A where BeginDate= ( select max(BeginDate) from PIReportTemplateColumns where RptTempID=A.RptTempID and ColNum=A.ColNum group by RptTempID,ColNum ) HTH, Praveen Maddali MCSD, MCDBA "Ricky" <state.mn.us> wrote in message news:0dec01c368b0$44ce2800$gbl... ...

  1. #1

    Default Re: Help in formulating a Query.....

    Ricky,

    Try this

    Select * from PIReportTemplateColumns A
    where BeginDate=
    (
    select max(BeginDate) from PIReportTemplateColumns
    where RptTempID=A.RptTempID and ColNum=A.ColNum
    group by RptTempID,ColNum
    )



    HTH,
    Praveen Maddali
    MCSD, MCDBA

    "Ricky" <state.mn.us> wrote in message
    news:0dec01c368b0$44ce2800$gbl... 


    praveen Guest

  2. #2

    Default Re: Help in formulating a Query.....

    Thanks Praveen

    It works great, But it always gives me the record for each
    colNum for which BeginDate is max as you mentioned in your
    select subquery.

    I want comparison of BeginDate with a date variable, so if
    datevariable has value '2004-03-02 00:00:00' I should get

    1,58,1,'ABC','2003-01-01 00:00:00'
    2,58,2,'DEF','2003-01-01 00:00:00'
    3,58,2,'XYZ','2003-06-01 00:00:00' -- Not This
    4,58,2,'QWE','2003-08-01 00:00:00' -- Not This
    5,58,3,'GHI','2003-01-01 00:00:00'
    6,58,3,'ERT','2003-06-01 00:00:00' -- Not This
    7,58,4,'ERT','2003-01-01 00:00:00'

    If date variable has value '2003-06-02 00:00:00' I should
    get

    1,58,1,'ABC','2003-01-01 00:00:00'
    2,58,2,'DEF','2003-01-01 00:00:00' -- Not This
    3,58,2,'XYZ','2003-06-01 00:00:00'
    4,58,2,'QWE','2003-08-01 00:00:00' -- Not This
    5,58,3,'GHI','2003-01-01 00:00:00' -- Not This
    6,58,3,'ERT','2003-06-01 00:00:00'
    7,58,4,'ERT','2003-01-01 00:00:00'

    So somehow I want this comparison added to the subquery??

    Any thoughts??

    Thanks so much for taking time.

    Ricky


     [/ref]
    lets [/ref]
    ColNum = 
    >
    >
    >.
    >[/ref]
    Ricky Guest

  3. #3

    Default Re: Help in formulating a Query.....

    Ohhhh...

    I got it, Thanks for your help Praveen ...

    Select * from PIReportTemplateColumns A
    where BeginDate=
    (
    select Top 1 BeginDate from PIReportTemplateColumns
    where RptTempID=A.RptTempID and ColNum=A.ColNum And
    BeginDate <= Anydatevariable
    order by BeginDate desc)

    Thanks

    Ricky

     
    each 
    your 
    if [/ref][/ref]
    a [/ref]
    >lets [/ref]
    >ColNum = [/ref][/ref]
    01 [/ref][/ref]

    >>
    >>
    >>.
    >>[/ref]
    >.
    >[/ref]
    Ricky Guest

Similar Threads

  1. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  2. Replies: 1
    Last Post: July 2nd, 09:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139