bebongtheshark - 1 year ago 63

SQL Question

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.

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];
```

Source (Stackoverflow)