September 26, 2017 at 11:06 am
I have business data (simplified) that has a table with columns: Year, WeekNo and Amount.
e.g. (all amounts are examples only)
2017, 4, 10.00
2017, 5, 20.00
2017, 6, 15.00
2017 ....
2017, 51, 23.00
2017, 52, 24.00
2018, 1, 25.00
2018, 2, 30.00
2018, 3, 44.00
2018, 4, 43.00
2018, 5, 23.00
2018 ....
I can retrieve single year data with no problem with e.g. where year >=2017 and year <=2017 and weekno >=1 and weekno <=52 - but this does not work when I wish to retrieve data spanning a year(s)
Q. How could I get all rows from above test data only between year=2017, weekno=6 and year=2018, weekno=3 i.e. across a year
September 26, 2017 at 11:11 am
office 6161 - Tuesday, September 26, 2017 11:06 AMI have business data (simplified) that has a table with columns: Year, WeekNo and Amount.
e.g. (all amounts are examples only)
2017, 4, 10.00
2017, 5, 20.00
2017, 6, 15.00
2017 ....
2017, 51, 23.00
2017, 52, 24.00
2018, 1, 25.00
2018, 2, 30.00
2018, 3, 44.00
2018, 4, 43.00
2018, 5, 23.00
2018 ....I can retrieve single year data with no problem with e.g. where year >=2017 and year <=2017 and weekno >=1 and weekno <=52 - but this does not work when I wish to retrieve data spanning a year(s)
Q. How could I get all rows from above test data only between year=2017, weekno=6 and year=2018, weekno=3 i.e. across a year
The problem is largely that you're not stored data as actual date values. As this looks like data warehouse type info, do you have a calendar dimension that this data can be joined to? If so, you can look at actual dates that are associated with those week number values, and use an internal key field value range in the calendar table to derive all the valid year and week number combinations in a CTE that you join to on year and week number.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 26, 2017 at 11:16 am
I do have an actual Date field within each row, which "should" (it's user data) fall in the data range of the corresponding weekno.
Would that be useful?
September 26, 2017 at 11:23 am
office 6161 - Tuesday, September 26, 2017 11:16 AMI do have an actual Date field within each row, which "should" (it's user data) fall in the data range of the corresponding weekno.
Would that be useful?
That might work. Something on the order of a CTE or temp table with a SELECT DISTINCT Year, WeekNo FROM YourTable AS YT WHERE YT.DateField BETWEEN @START_DATE AND @END_DATE, in a stored procedure, and then that CTE or temp table is joined to YourTable on Year and WeekNo. That would at least translate your year and week numbers into dates and provide a somewhat "loose" translation of dates into week numbers. As long as your dates are fully contiguous (meaning there's data for every calendar day in the entire period, it can work reasonably well. Alternatively, you'd need to set up a calendar table that performs the same functionality, and extends it out maybe 20 to 30 years into the future, along with enough of the past to handle existing data.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 26, 2017 at 11:25 am
office 6161 - Tuesday, September 26, 2017 11:16 AMI do have an actual Date field within each row, which "should" (it's user data) fall in the data range of the corresponding weekno.
Would that be useful?
If you have a date column, why not use it ? Otherwise, convert your year/week info into a single int and use that in your WHERE clause (= YEAR * 100 + WEEK)
September 26, 2017 at 12:41 pm
Cheers for really quick and helpful responses, will give them a go and let you know
September 27, 2017 at 7:43 am
office 6161 - Tuesday, September 26, 2017 11:06 AMQ. How could I get all rows from above test data only between year=2017, weekno=6 and year=2018, weekno=3 i.e. across a year
Combine the necessary WHERE conditions with an OR:
WHERE ((year = 2017 AND weekno >= 6) OR (year = 2018 AND weekno <= 3))
If you retrieve data by year and weekno, then leave the data indexed by [Edit: clustered by, if possible] year and weekno.
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".
September 28, 2017 at 11:17 am
Thanks again chaps that lat SQL snippet did the trick for cross year, will still need to use and AND for in-year though
Cheers
Mike
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply