Too Complex for 2002 SQL ?

[dcavaiani]

How would I do this?

Table with these 2 fields:

Year Week

2016 51
2016 51
2016 52
2016 52
2016 52

I want a SQL to select the highest year, then the highest month in that highest year. Then, if week < 52, add 1 to week, ELSE if the week is eq 52, add 1 to the highest year and set week = 1. Doable ??? Result s/b year=2017 and week=1 in this example.

 

[plog]

The key to things like this is just to take as many queries as you need and work through it one step at a time. First thouh , you have poor field names (Year and Week are reserved words) so I used [YearField] and [WeekField] and you provided no table name, so I used YourTable. Replace those as appropriate in the below code:

SELECT Max(YourTable.YearField) AS MaxYear
FROM YourTable;

Name the above query HighestWeek_sub1. It gets the highest year from your table.

SELECT HighestWeek_sub1.MaxYear, Max(YourTable.WeekField) AS MaxWeek
FROM YourTable INNER JOIN HighestWeek_sub1 ON YourTable.YearField = HighestWeek_sub1.MaxYear
GROUP BY HighestWeek_sub1.MaxYear;

Name the above query HighestWeek_sub2. It gets the highest week in that highest year.

SELECT [MaxYear]+IIf([MaxWeek]=52,1,0) AS HighestYear, IIf([MaxWeek]=52,1,[MaxWeek]+1) AS HighestWeek
FROM HighestWeek_sub2;

That's the query that will produce the results you want. It applies the logic based on the highest week number.

Lastly, your logic wasn't complete:

if week < 52, add 1 to week, ELSE if the week is eq 52

What happens when the week is 53 or 54? I don't know how you are determining your weeks, but years can span 53, sometimes 54 weeks. In the code I posted those weeks would not rollover to the next year week 1.

 

[dcavaiani]

You know, I thought that would be the answer. Break it down into multiple queries. I thank you much for awesome reply.

 

[dcavaiani]

What "feature" is it in Access that allows me to just run the final query rather than all 3 of them in order to get the latest update?

 

[plog]

What "feature" is it in Access that allows me to just run the final query rather than all 3 of them in order to get the latest update?

Huh? You just run the last one--it is built using the first 2. They run when you run the last one.

 

[dcavaiani]

Huh? You just run the last one--it is built using the first 2. They run when you run the last one.

I know, but what "allows" that - does not seem logical. It's 3 separate queries?

 

[arnelgp]

SELECT IIF(VAL(MID(Expr1,5)) < 52, VAL(LEFT(EXPR1,4)), VAL(LEFT(EXPR1,4))+1) AS YEAR,
IIF(VAL(MID(Expr1,5)) < 52, VAL(MID(Expr1,5))+1, 1) AS WEEK FROM
(SELECT TOP 1 F AS Expr1
FROM (SELECT [YEAR] & [WEEK] AS F FROM yourTable ORDER BY [YEAR] & [WEEK] DESC) AS T1);

 

[dcavaiani]

SELECT IIF(VAL(MID(Expr1,5)) < 52, VAL(LEFT(EXPR1,4)), VAL(LEFT(EXPR1,4))+1) AS YEAR,
IIF(VAL(MID(Expr1,5)) < 52, VAL(MID(Expr1,5))+1, 1) AS WEEK FROM
(SELECT TOP 1 F AS Expr1
FROM (SELECT [YEAR] & [WEEK] AS F FROM yourTable ORDER BY [YEAR] & [WEEK] DESC) AS T1);

I know I was close when trying yesterday and that it could be done, and I'm sure this would do it. It is just too hard for me to grasp.

 

[plog]

I know, but what "allows" that - does not seem logical. It's 3 separate queries?

I think of it like this--data is just a series of 1s and 0s on disk. A table is a load of that data into memory in the form of rows. A query is a load of rows into memory as well. Once its in memory the computer doesn't care if it came from a query or a table, it just sees rows of data in memory.

 

[dcavaiani]

So "memory" is the thing. Just defies the logic of Midrange and Mainframe database Systems.

 

[CJ_London]

Why not simply

 SELECT TOP 1 myYear-(myWeek=52) as calcyear, iif(myWeek=52,1,myWeek) as calcweek
 FROM myTable 
 ORDER BY myYear & myWeek Desc