Interesting SELECT

  • Hello,

    I have this situation. I have a table #T1

    CREATE TABLE #T1 (

    cislo int,

    datod smalldatetime,

    datdo smalldatetime,

    utvar varchar(24)

    )

    and here are these data

    cislo datod datdo utvar

    5152002-04-01 00:00:002003-01-31 00:00:004004

    5152003-02-01 00:00:002006-07-17 00:00:007003

    5152006-07-18 00:00:002007-12-31 00:00:000000

    5152008-01-01 00:00:002008-10-27 12:15:007003

    I have a storedProcedure with Input parameters:

    @DatumOd as SmallDateTime

    @DatumDo as SmallDateTime

    and They have values

    @DatumOd='20021201'

    @DatumDo='20021231'

    I have to select records , which are in this range. For this example, it have to select this record

    5152002-04-01 00:00:002003-01-31 00:00:004004

    I try this:

    select * from #T1r

    where cislo=515 and

    ((datod between '20021201' AND '20021231') OR (datdo between '20021201' AND '20021231'))

    or

    select * from #T1

    where cislo=515 and

    ((datod>='20021201' AND datdo<='20021231')) AND

    (datdo '20021231'))

    Could you help me with correct select command or some udf function?

    Thank you

  • I think your sql is correct - the reason no rows are being returned is because there is no data that falls within the date range of your parameters.

    If you change the sql to

    ((datod between '20020101' AND '20021231') OR (datdo between '20021201' AND '20021231')) you will see you get 1 row returned.

    In your data set there are no rows that have datod or datdo between 01-Dec-2002 and 31-Dec-2002 which is what you have your variables set to.

  • It looks like you are swaping the Variable and the columns

    Based on your parameter and expected result.....

    Declare

    @DatumOd SmallDateTime

    ,@DatumDo SmallDateTime

    Select

    @DatumOd='20021201'

    ,@DatumDo='20021231'

    Select *

    From #T1

    Where

    ( @DatumOD >= Datod And @DatumOD <= Datdo )

    And

    ( @DatumDo >= Datod And @DatumDo <= Datdo )

  • Martin,

    Your question has nothing to do with T-SQL.

    It's a task from school math.

    To find the correct answer you need:

    - stop browsing Internet for it;

    - take a piece of paper;

    - draw a time line on it;

    - mark all time intervals from the table on the time line;

    - mark the search interval on the same line.

    Draw several different variations of the picture: with overlapping intervals, with one search interval covering several table intervals, etc.

    Allocate 5 minutes of your time to analyze the pictures and figure out what are the criteria for selecting the right intervals.

    I'm pretty sure you can find the answer.

    _____________
    Code for TallyGenerator

  • Hello,

    To Sergiy:

    You are right. The first what I did was an analyze and developed diagram. Then I decided that I have to check every date from input range if it is between datod AND datdo.

    May be I described my problem wrong. What I needed was join "utvar" from #T1 with every date from #T2. Table #T2 is created by input date range.

    I have source data #T1:

    cislo datod datdo utvar

    37792002-04-01 00:00:002002-11-30 00:00:007003

    37792002-12-01 00:00:002003-04-30 00:00:008007

    37792003-05-01 00:00:002008-10-28 08:17:007003

    Input date range:

    @DatumOd='20030420'

    @DatumDo='20030512'

    Source data #T2:

    cislo datum kodds hodin

    37792003-04-23 00:00:00100495

    37792003-04-24 00:00:00100450

    37792003-04-25 00:00:00100585

    37792003-04-28 00:00:00100495

    37792003-04-29 00:00:00100495

    37792003-04-30 00:00:00100450

    37792003-05-02 00:00:00100450

    37792003-05-05 00:00:00100435

    37792003-05-06 00:00:00100495

    37792003-05-07 00:00:00100495

    37792003-05-09 00:00:00100435

    37792003-05-12 00:00:00100495

    This is result what I need:

    cislo datum kodds hodin utvar

    37792003-04-23 00:00:001004958007

    37792003-04-24 00:00:001004508007

    37792003-04-25 00:00:001005858007

    37792003-04-28 00:00:001004958007

    37792003-04-29 00:00:001004958007

    37792003-04-30 00:00:001004508007

    37792003-05-02 00:00:001004507003

    37792003-05-05 00:00:001004357003

    37792003-05-06 00:00:001004957003

    37792003-05-07 00:00:001004957003

    37792003-05-09 00:00:001004357003

    37792003-05-12 00:00:001004957003

    I used storedprocedure and "utvar" I got from:

    SET @utvar2=(select utvar from #T1 where cislo=@cislo1 AND (@datum between datod and datdo))

    On finally it looks very simply 🙂 .

    Thank you for your help and advice

Viewing 5 posts - 1 through 4 (of 4 total)

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