April 28, 2015 at 8:02 pm
Hello Everyone,
I have a table that contain (besides other columns) 2 fields Year (2014, 2015, etc.) and Month ( 1, 2, 10, etc.) I want to be able to query this table as follows. For example, give me all the records starting at year 2014 month 9 until year 2015 month 10 inclusive. How can I construct the WHERE clouse to accomplish this in a SELECT statement? It looks very simple but it is quite challenging.
I've tried to create the statement in multiple ways combining ands, ors, etc between both fileds, but I cannot manage to get the correct range.
Can anybody has an idea how to accomplish this?
Thanks.
April 28, 2015 at 9:02 pm
Save yourself the headache. Store the dates as dates, not as two columns with numbers. Then you can do date math really easily, index columns and all that. Otherwise, if you're stuck with this design, you could use something like this:
SELECT YearNumber,MonthNumber,DATEFROMPARTS(YearNumber,MonthNumber,1) AS SomeDate
FROM FakeDates
ORDER BY YearNumber,MonthNumber;
then you could do date math on the resulting "SomeDate" field. Kinda hideous because you can't index etc, but if you're stuck you're stuck.
April 28, 2015 at 9:10 pm
I think you are looking for (I'm calling your Year column "yearCol" and month column "monthCol":
WHERE
(yearCol = 2014 AND monthCol >= 9) -- All dates in year 2014 with month greater than/equal to 9
OR
(yearCol = 2015 AND monthCol <= 10) -- All dates in year 2015 and Month less than/equal to 10
Let's say you needed Year 2012 through 2015 month 10 you could do this:
(just focus on the WHERE clause, I added sample columns to make this easier to understand)
SELECT
mo.N AS monthCol,
yr.N,
CAST(CAST(mo.N AS varchar(2))+'/1/'+CAST(yr.N AS char(4)) AS date)
FROM
(
SELECT 1 AS N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
) mo
CROSS JOIN
(
SELECT 2012 AS N UNION ALL SELECT 2013 UNION ALL
SELECT 2014 UNION ALL SELECT 2015
) yr
WHERE (yr.N = 2012 AND mo.N >= 9)
OR (yr.N IN (2013,2014))
OR (yr.N = 2015 AND mo.N <= 10)
Please forgive me BTW... I don't have access to a SQL 7 or 2000 instance.
-- Itzik Ben-Gan 2001
April 28, 2015 at 9:12 pm
pietlinden (4/28/2015)
Save yourself the headache. Store the dates as dates, not as two columns with numbers. Then you can do date math really easily, index columns and all that. Otherwise, if you're stuck with this design, you could use something like this:SELECT YearNumber,MonthNumber,DATEFROMPARTS(YearNumber,MonthNumber,1) AS SomeDate
FROM FakeDates
ORDER BY YearNumber,MonthNumber;
then you could do date math on the resulting "SomeDate" field. Kinda hideous because you can't index etc, but if you're stuck you're stuck.
This is a SQL 7/2000 post :rolleyes:
-- Itzik Ben-Gan 2001
April 28, 2015 at 9:46 pm
<zing!>
Sorry, missed that COMPLETELY.
April 29, 2015 at 12:25 am
san1901 (4/28/2015)
Hello Everyone,I have a table that contain (besides other columns) 2 fields Year (2014, 2015, etc.) and Month ( 1, 2, 10, etc.) I want to be able to query this table as follows. For example, give me all the records starting at year 2014 month 9 until year 2015 month 10 inclusive. How can I construct the WHERE clouse to accomplish this in a SELECT statement? It looks very simple but it is quite challenging.
I've tried to create the statement in multiple ways combining ands, ors, etc between both fileds, but I cannot manage to get the correct range.
Can anybody has an idea how to accomplish this?
Thanks.
If it were me, I'd petition to have them add a persisted computed column that would at least add the two columns together to form a 6 digit integer in the format of yyyymm and then add an index to that column. Unless they also lost their minds when it came to how they wrote the GUIs, it shouldn't break anything. If it does, you could drop the column.
If they refuse, then you'll always have the same necessary complexity that Eirikur posted or just put up with scans by writing a simpler non-sargable WHERE clause. It won't be much different than having all those ORs.
If push comes to shove for performance, adding the column to the table would be the best bet but, if that does break stuff, you could always add a sister table and maintain that with a very well written trigger that will also be quite fast.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2015 at 4:17 am
All are excellent solutions. But the easiest one is to concatenate the two columns into a date field and do the math from there. Since the day is not important, I can use any. It worked like a champ.
Thanks everyone for the help.
April 29, 2015 at 7:37 am
san1901 (4/29/2015)
All are excellent solutions. But the easiest one is to concatenate the two columns into a date field and do the math from there. Since the day is not important, I can use any. It worked like a champ.Thanks everyone for the help.
Just remember that has no chance of doing an index seek. It will always do at least an index scan. Could make quite a difference as the table grows. Adding the persisted computed column would be the next thing to do if performance does become a problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 9, 2015 at 10:32 am
This is the simplest where clause that works now mater how many years are between the dates.
If as in this example they are only 1 year apart, or the same year, the middle statement returns nothing,
otherwise it returns all the intermediate years.
WHERE (yearCOL = 2014 AND monthCOL >= 9) --Everything for Low year, above or equal to Month
OR (yearCOL > 2014 AND yearCOL < 2015) --Everything for the years in between
OR (yearCOL = 2015 AND monthCOL <= 10) --Everything for High year, below or equal to Month
June 11, 2015 at 7:59 am
Well my previous post didn't actually work for all options, hopefully this does.
I find it's a very common problem with accounting tables, which often only store the year and period.
WHERE
--Everything for Low year, above or equal to Month (except if same year entered)
(yearCOL = @yearA AND monthCOL >= @monthA AND @yearA < @yearB)
--Everything for the years in between (excludes itself if the years are the same)
OR (yearCOL > @yearA AND yearCOL < @yearB)
--Everything for High year, below or equal to Month (except if same year entered)
OR (yearCOL = @yearB AND monthCOL <= @monthB AND @yearA < @yearB)
-- Months between if the selection is the same year
OR (yearCOL = @yearA AND monthCOL >= @monthA AND monthCOL <= @monthB AND @yearA = @yearB)
June 11, 2015 at 8:41 am
waynes 78578 (6/11/2015)
Well my previous post didn't actually work for all options, hopefully this does.I find it's a very common problem with accounting tables, which often only store the year and period.
WHERE
--Everything for Low year, above or equal to Month (except if same year entered)
(yearCOL = @yearA AND monthCOL >= @monthA AND @yearA < @yearB)
--Everything for the years in between (excludes itself if the years are the same)
OR (yearCOL > @yearA AND yearCOL < @yearB)
--Everything for High year, below or equal to Month (except if same year entered)
OR (yearCOL = @yearB AND monthCOL <= @monthB AND @yearA < @yearB)
-- Months between if the selection is the same year
OR (yearCOL = @yearA AND monthCOL >= @monthA AND monthCOL <= @monthB AND @yearA = @yearB)
That's good but that also brings us to an old point. The separation of month and year in data is counter productive and should be limited to display purposes or Calendar tables only. If you need to store notations by month, then do it as a proper date or datetime or datetime2 column using "1" as the day. That way, you don't have to use such relatively complicated criteria and you don't disable the ability to do date math without having to do conversions.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 11, 2015 at 9:35 am
Yes, it would be nice to have dates to work with, but not all accounting years start in January.
A lot of companies have April as period 1, or 13 x 4 week periods or the terrible old 4-5-4 week periods.
Then there's always the end of year period +1 and +2, supported by some accounting systems to allow final adjustments.
There's common sense, and then there's accounting software
June 11, 2015 at 9:40 am
waynes 78578 (6/11/2015)
Yes, it would be nice to have dates to work with, but not all accounting years start in January.A lot of companies have April as period 1, or 13 x 4 week periods or the terrible old 4-5-4 week periods.
Then there's always the end of year period +1 and +2, supported by some accounting systems to allow final adjustments.
There's common sense, and then there's accounting software
Nothing you've said would interfere with anything that I said about keeping years and months together in a single properly datatyped column. In fact, doing so would lend itself much easier to the use of a Calendar table to accommodate fiscal years and true-up periods.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 11, 2015 at 10:25 am
Can't see how the adjustment periods/true-up periods can be incorporated into a calendar.
Surely they'd conflict with the end date of the old year or the start date of the new year ?
31/03/2015 Yr 2014 Perd 12
31/03/2015 Yr 2014 Perd 12+1
31/03/2015 Yr 2014 Perd 12+2
01/04/2015 Yr 2015 Perd 01
At some point you're going to have to filter using the Period for those 'What was the balance before the adjustments..' reports,
but hopefully there are actual and effective posting dates included on the table as well.
Maybe even a table with the dates and years/periods all listed so to make it all too easy
At lot of the old software was written on 3rd Gen software, with developers trying to save space and open table limits.
As tools get more and more enhanced a lot of the old problems go away (6 digit dates for a start).
But there are still legacy databases out there, in fact I still help out on some systems where you can't even do a sub-query, let alone a CASE or CAST
Regards
Wayne
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply