Conditional column referencing another table

[Chris_Evans]

I have one table that is a SQL source and has a date on each line and another custom table that has set date ranges. I am using the date as a means to color code data in a table visual. Current Month, Previous Month, 2 Months ago, All Previous.

I am currently using a SQL query to add a column that uses the current actual date and generates a color code based on that date. 0 for Current, 1 for Previous and so on. The problem is that our dates for current is not actually the calendar month but overlaps both so for instance the November month is 10/9 - 11/13. So what is happening with my current setup is that when the new month starts, the color code changes in my SQL query since it is based on the actual physical date which in turn changes the visuals attached to it. I dont want to have to edit my SQL queries every month . I have a custom table that I use with those dates in it that I would like to reference to make a custom conditional column to replace the color code from my query so that the color coding in my charts reflects the correct monthly period and not the calendar month. So 0 would be between current and last, 1 between last and 2 months, 2 between 2 months and all previous, and all previous before all previous.

[v-yuta-msft]

@Chris_Evans ,

I'm confused on your description. Could you please share the expected result and show the logic of your SQL statement?

Regards,

Jimmy Tao

[Chris_Evans]

@v-yuta-msft 

I am using a case statement with datediff

Case When datediff(month, ui.dateposted, getdate()) > 3 Then 3 Else datediff(month, ui.dateposted, getdate()) End as "Color Code"

If the datediff returned is greater than 3 it returns 3 otherwise the datediff. However that method uses the current physical date and I want to use a date range to create this color code. The reason for this is that our current period of time is not related to the calendar month but spreads between 2 seperate months. For example, the November cycle is between 10/9 and 11/13.

For example, if the date in the table is 11/12 and the current physical date is 12/1, the datediff query will set the color code field to 1 that corresponds to previous month but it is actually in our current cycle.

What i would like to do is use a conditional column that pulls the date ranges from a custom table that I provided a screencapture of in the OP. "0" would be a date between Current Month and Last Month, "1" would be between Last Month and 2 Months, "2" would be between 2 Months and All Previous, "3" all greater than All Previous.

Thanks for any help you can provide.