Forum Discussion

Chris_Evans's avatar
Chris_Evans
Frequent Visitor
6 years ago

Conditional column referencing another table

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...
Need Help
v-yuta-msft's avatar
v-yuta-msft
Community Support
6 years ago

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's avatar
    Chris_Evans
    Frequent Visitor
    6 years ago

    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.