bebongtheshark bebongtheshark - 5 months ago 23
SQL Question

If Then Else in WHERE clause in MS Access SQL Query

I'm trying out MS Access SQL Query. My Data is structured like this
Rent Table

The idea is I want to split the table to collect the latest start up to 12 months back using [Year_Start] and [Month_Start] as basis. So my rough code will be:

SELECT [Renter_Name], [Amount]
FROM RentTable1
WHERE [Year_Start] = Max([Year_Start]) AND [Month_Start] = Max([Month_Start])
ORDER BY [Renter_Name];


Subsequently, other month tables will conceptually be coded like this:

SELECT [Renter_Name], [Amount]
FROM RentTable1
WHERE [Year_Start] = Max([Year_Start]) AND [Month_Start] = Max([Month_Start]) - 1
ORDER BY [Renter_Name];


And then subsequent months will be adjusted using the minus sign.

SELECT [Renter_Name], [Amount]
FROM RentTable1
WHERE [Year_Start] = Max([Year_Start]) AND [Month_Start] = Max([Month_Start]) - 2
ORDER BY [Renter_Name];


I'm also considering a case where [Month_Start] = Max([Month_Start]) - x will be zero (0) or a negative number so a theoretical code will be:

SELECT [Renter_Name], [Amount]
FROM RentTable1
IF Max([Month_Start]) - X <= 0 THEN
WHERE [Year_Start] = Max([Year_Start]) - 1 AND [Month_Start] = Max([Month_Start]) - X
ELSE
WHERE [Year_Start] = Max([Year_Start]) AND [Month_Start] = Max([Month_Start]) - X
END IF
ORDER BY [Renter_Name];


*** X being the months backward from the latest start month and year.

Clearly, you see my SQL coding skills are really weak. Pardon me as I'm really a beginner. So there are some touches other standard programming like If-Then-Else statements.

I was hoping someone could propose to correct the above codes.

Thanks! Appreciate everyone who stumble upon this question.

EDIT 1:

Just to clarify, this is the expected thought:

In the example the latest period is 2016 and 4. So it should pick it up for TABLE1.

A subsequent query is to be made to minus one month from the latest period so the result should be 2016 and 3. This goes on until 2016 and 1.

When 4 - 4 happens which equals 0, the query should be able to skip through this illogical step and go through (2016 - 1) and the get the max month using the result of (2016 - 1) which is 2015 and 12.

cha cha
Answer

First of all, here is my advise: when dealing with dates use the DATE data type. You can specify the StartDate as 2016-04-01 and the EndDate as 2017-04-31. Even better: specify the EndDate as 2017-05-01 and always remember that you need to use >= for the StartDate and < for the EndDate.

Now, to your problem. You need to convert the columns to the proper date using the [DateSerial()][1] function, like this:

SELECT [Renter_Name], [Amount]
FROM RentTable1
WHERE DateSerial([Year_Start], [Month_Start], 1) = 
    (SELECT Max(DateSerial([Year_Start], [Month_Start], 1) as dt FROM RentTable1)
ORDER BY [Renter_Name];

To get the details for the previous month use DateAdd() function. Here is the example for the previous month:

SELECT [Renter_Name], [Amount]
FROM RentTable1
WHERE DateSerial([Year_Start], [Month_Start], 1) = 
    (SELECT DateAdd('m', -1, Max(DateSerial([Year_Start], [Month_Start], 1)) as dt FROM RentTable1)
ORDER BY [Renter_Name];

And here is the universal query to get the details for the X months ago:

SELECT [Renter_Name], [Amount]
FROM RentTable1
WHERE DateSerial([Year_Start], [Month_Start], 1) = 
    (SELECT DateAdd('m', [X] * (-1), Max(DateSerial([Year_Start], [Month_Start], 1)) as dt FROM RentTable1)
ORDER BY [Renter_Name];
Comments