September 14, 2012 at 9:23 am
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?
September 14, 2012 at 9:27 am
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
September 14, 2012 at 9:31 am
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,
September 14, 2012 at 9:49 am
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