where clause not working for some values

  • I have a table that holds all models with a reporting period such as

    Model TimeFrame

    A1 201210

    A1 201209

    A1 201208

    A1 201101

    A1 201105

    A1 201104

    and so on

    I have my query that looks like this:

    select CAST(LEFT(TimeFrame, 4) as int), CAST(right(TimeFrame, 2) as int)

    from TimePeriod

    where model= 'A1'

    and CAST(LEFT(TimeFrame, 4) as int) = (select (CAST(LEFT(min(TimeFrame), 4) as int)) from TimePeriod

    with this query I should see

    2011 01

    2011 05

    2011 04

    however, I see nothing

    when I run this query:

    select CAST(LEFT(TimeFrame, 4) as int), CAST(right(TimeFrame, 2) as int)

    from TimePeriod

    where model= 'A1' and cast(left(TimeFrame, 4) as int) = '2011'

    I see the months for the year 2011

    right now I'm only having this issue with the model of A1. I have several other models types in the table and I'm able to view the min year for each model. AND if I change it from min to MAX, I'm able to see 2012 for the model A1.

    am I missing something?

  • What is the data that is contained within the TimePeriod table in your WHERE clause?

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • Andy Hyslop (9/14/2012)


    What is the data that is contained within the TimePeriod table in your WHERE clause?

    Huh? The data is the model and the timeFrame.

    the data is stored in the table as

    Model varchar

    TimeFrame numeric (this comes from oracle)

    Model looks like

    A1

    B22

    B-9098

    TimeFrame

    201101

    201110

    201109

    201209

    201210

    and so on,

  • SELECT

    TimeFrame / 100, CAST(TimeFrame AS int) % 100

    FROM dbo.TimePeriod

    WHERE

    model= 'A1' AND

    TimeFrame / 100 = (

    SELECT MIN(TimeFrame) / 100

    FROM dbo.TimePeriod

    )

    Or, more generically:

    SELECT

    model, TimeFrame / 100, CAST(TimeFrame AS int) % 100

    FROM dbo.TimePeriod tp1

    WHERE

    TimeFrame / 100 = (

    SELECT MIN(TimeFrame) / 100

    FROM dbo.TimePeriod tp2

    WHERE

    tp2.model = tp1.model

    )

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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