Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010
    Posts
    21

    Unanswered: To pick up data based on report date

    Hi all,

    I have a case where I need to pick the right data based on two dates and month.

    Here's the sample of data:

    Code:
     cash_box_id  |  balance |      validfrom      |    validto
      -----------+---------+-----------------+------------------
      cb003        |    950    |    30june2010     |   24may2011
      cb003        |    900    |    25may2011     |   01Jan5999
      cb005        |    800    |    11jan2011       |   01Jan5999
       cb007        |    300    |    24Feb2011      |  10may2011
      cb007        |    385    |    11may2011     |   22may2011
      cb007        |    590    |    23may2011     |   28jun2011
      cb007        |    700    |    29june2011     |   01Jan5999
    ...
    I wanted to pick data that valid on 31 May 2011. So, expected output should be only two records as blue highlight.

    Code:
    cash_box_id  |  balance |      validfrom      |    validto
      -----------+---------+-----------------+------------------
      cb003        |    950    |    30june2010     |   24may2011
      cb003        |    900    |    25may2011     |   01Jan5999
      cb005        |    800    |    11jan2011       |   01Jan5999
       cb007        |    300    |    24Feb2011      |  10may2011
      cb007        |    385    |    11may2011     |   22may2011
      cb007        |    590    |    23may2011     |   28jun2011
      cb007        |    700    |    29june2011     |   01Jan5999
    ...
    They are thousand of records like this.
    Really appreciate if somebody can help me with the query. I have tried many times to get it right, but failed.


    Thank you very much!!

    nbtet

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I assume validfrom AND validto are DATE or DATETIME columns and "31 May 2011" is just a representation of a date and not a string.
    Code:
    SELECT * 
    from aTable
    WHERE '2011.05.15' BETWEEN validfrom AND validto
    Last edited by Wim; 11-17-11 at 12:33.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: To pick up data based on report date

    Another possibility...

    Code:
    select *
    from CASH_BOX cb (nolock)
    where validfrom =
     (select max(validfrom)
      from CASH_BOX (nolock)
      where cash_box_id = cb.cash_box_id
      and validfrom <= '2011-05-31' -- '31 May 2011'
     )
    and validto > '2011-05-31'

Posting Permissions

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