July 13, 2012 at 8:28 am
I am running SQL Server Express 2008.
I have the following query. I know this is a very silly question, but I am baffled.
The query returns results, but only for January in both years. What am I doing wrong?
SELECT * FROM [AllData] WHERE
(([MonthEnd] >= 1
AND ([YearEnd] >= 2011)
AND ([MonthEnd] <= 1)
AND [YearEnd] <= 2012))
AND
= 'PROD_1'
Thanks.
July 13, 2012 at 8:32 am
Its your month end where clauses
What you have is >= 1 and a <=1 which results in = 1
If you remove the month_end where clauses it should work as you intend.
July 13, 2012 at 8:34 am
i'm guessing if you wanted all months, it should be like this instead:
SELECT * FROM [AllData] WHERE
(([MonthEnd] >= 1
AND ([YearEnd] >= 2011)
--here is the fix!
AND ([MonthEnd] <= 12)
AND [YearEnd] <= 2012))
AND
= 'PROD_1'
it would be MUCH better if you simply stored the dates as a datetime/datetime2, instead of chopping the date into integers for the months and years;
if you have the option of changing the data, r adding a column that is datetime, you could make your query simpler and faster.
Lowell
July 13, 2012 at 8:38 am
Thanks - I only want records from Jan. 2011 thru Jan. 2012. So not sure your solution would work.
The data is from a linked server over which I have no control, so cannot change data type.
July 13, 2012 at 8:43 am
Jan to Jan inclusive, so Jan 2011 to Dec 2011 plus Jan 2012 or just Jan 2011 to Dec 2011
For Jan to Dec use
SELECT * FROM AllData
WHERE
YearEnd = 2011
AND
DIV = 'PROD_1'
If inclusive add the below to the above
UNION
SELECT * FROM AllData
WHERE
YearEnd = 2012
AND
MonthEnd = 1
AND
DIV = 'Prod_1'
July 13, 2012 at 8:46 am
so you want everything from 2011 plus just january of 2012?
i'd think this will do what you are asking:
SELECT
*
FROM [AllData]
WHERE [YearEnd] = 2011
AND
= 'PROD_1'
UNION ALL
SELECT
*
FROM [AllData]
WHERE [MonthEnd] = 1
AND [YearEnd] = 2012
AND
= 'PROD_1'
Lowell
July 13, 2012 at 8:47 am
anthony and i are on the same page 😀
Lowell
July 13, 2012 at 10:00 am
Thanks. I did not think this was necessary info, so I did not mention it earlier.
But this query is an ASP.NET app where the user will enter from and to month and year and I get the data accordingly for that date range.
If user requests,
from month =1
from year = 2011
to month = 1
to year =2012
The get the data for a range of date from Jan 2011 thru Jan 2012.
Thanks again.
July 13, 2012 at 10:54 am
tinausa (7/13/2012)
Thanks. I did not think this was necessary info, so I did not mention it earlier.But this query is an ASP.NET app where the user will enter from and to month and year and I get the data accordingly for that date range.
If user requests,
from month =1
from year = 2011
to month = 1
to year =2012
The get the data for a range of date from Jan 2011 thru Jan 2012.
Thanks again.
That's where having the data with actual datetime helps enormously.
then you could use two datepicker controls, and use
a WHERE stateement like WHERE DateColumn BETWEEN '2011-01-01' AND '2012-01-01'
to really help you correctly, we need to see the DDL (CREATE TABLE) of the table.
if it's all integers, it's a lot harder to do.
Lowell
July 13, 2012 at 11:39 am
tinausa (7/13/2012)
Thanks. I did not think this was necessary info, so I did not mention it earlier.But this query is an ASP.NET app where the user will enter from and to month and year and I get the data accordingly for that date range.
If user requests,
from month =1
from year = 2011
to month = 1
to year =2012
The get the data for a range of date from Jan 2011 thru Jan 2012.
Thanks again.
Lowell is correct, having date compatible datatypes makes things much easier.
In your specific example however, I would only compare months when the years are equal, otherwise we can just compare years.
Sort of like:
-- assuming @YearStart, @YearEnd, @MonthStart, @MonthEnd are parameters
SELECT * FROM [AllData] WHERE
(
([YearData] > @YearStart) OR
([YearData] = @YearStart AND [MonthData] >= @MonthStart)
)
AND
(
([YearData] < @YearEnd) OR
([YearData] = @YearEnd AND [MonthData] <= @MonthEnd)
)
July 13, 2012 at 1:05 pm
I did it as below and that seems to work.
strSql = "SELECT * FROM [AllData] WHERE "
strSql = strSql + " convert(date,cast( [yearend] as varchar(4)) + right('00'+convert(varchar,[monthend]),2) +'01') "
strSql = strSql + " >= '" + fromDate
strSql = strSql + "' AND "
strSql = strSql + " convert(date,cast( [yearend] as varchar(4)) + right('00'+convert(varchar,[monthend]),2) +'01') "
strSql = strSql + " <= '" + toDate
strSql = strSql + "' AND [Div] = 'Prod1'"
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply