'How to pass variable of adding 270 days to the date?

Below is my query:

select 
  facility_lob as FACILITY_LOB,
  TO_DATE(REPLACE(posting_d_date_sk, ',', ''), 'YYYYMMDD') as PostingDate,
  count(distinct encounter_num ||cast(date_of_service as varchar(20) )) as ENCOUNTER_VOLUME,
  SUM(charge_amt) as Gross_Charges 
from sources.Table 
where 1=1 
and REPLACE(posting_d_date_sk, ',', '') >= '20210101' 
and REPLACE(posting_d_date_sk, ',', '') <= '20210928'
and posting_d_date_sk <> '-1' 
and posting_d_date_sk  is not NULL 
group by facility_lob, posting_d_date_sk 
order by REPLACE(posting_d_date_sk, ',', '')

For now, I have hard coded the dates from Jan 1, 2021 to Sep 28, 2021 ( which is 270 days from Jan 1, 2021).

My requirement is the query should should pull data from and greater than year 2021 (>= 2021 of the posting date), and I need to pull the data from Jan 1, 2021, to 270 days. If I run the query today, I need to get the data from Jan 1, 2021 to Sep 28 2021 ( which is 270 days from jan 1 2021). If I execute this query tomorrow, I need to get the data from Jan 2 2021 to Sep 29 2021. jan 3, 2021 to sep 30 2021 etc.. If I run the query on April 3, 2022 then the calculation of 270 days is from Feb 1, 2021, to Oct 29, 2021.

Could you please help how do I fix this?

sqlpostgresql


Solution 1:[1]

Why not using a simple dateadd based on getdate and then format it into your format ?

Declare @to varchar(10), @from varchar(10), @mydate smalldatetime 

Set @mydate = Getdate()
Set @from = convert(varchar,year(@mydate)) + right('0' + convert(varchar,month(@mydate)),2) + right('0' + convert(varchar,day(@mydate)),2)

Set @mydate = dateadd(dd,270,getdate())
Set @to = convert(varchar,year(@mydate)) + right('0' + convert(varchar,month(@mydate)),2) + right('0' + convert(varchar,day(@mydate)),2)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 ChrisFerreira

Related Questions