I want to assign a specific date to a variable based on the output of a query and then use that variable in another query. I am doing this because using sql pivot tables requires a defined element/date in order to utilize. I can write the dates in, but i am hoping to automate this process. Also, i would prefer not to create a temporary table or a standalone table to reference, i would like to keep this as dynamic as possible. Can a CTE or dual table be used? I have seen many examples, but i cannot tie all of them together to solve this issue.
Variables:
Variable 1: SELECT Period from Period_tbl WHERE order_id in '1' -- 1 = current month
Variable 2: SELECT Period from Period_tbl WHERE order_id in '2' -- 2 = previous month
code i want to apply this to:
SELECT *
From(
SELECT Period, Team, Description, dollars
from table_1)
PIVOT(
SUM(dollars) as Period for Period in (
to_date(variable 1, 'mm/dd/yyyy') current_mo
,to_date(variable 2, 'mm/dd/yyyy') prior_mo));
