How to count last 30 days?

Transact-SQL
1

hi ,
I have a table with 3 columns:
Date 2. User_id 3. Flag_1 (0/1)
I want to create 2 new columns that will count the users with flag_1.
First one for the past 30 days (Date-30)
Second for the past 1 day (Date-1)
tried it with below query but it retrievs same value for both:(

any ideas??

select TO_CHAR (TO_DATE(DATE,'YYYYMMDD'), 'DD-MON-YY'),user_id,

CASE
WHEN (flag_1=1 or flag_2=1)

           and   TO_CHAR (TO_DATE(date,'YYYYMMDD'), 'YYYYMMDD') >= TO_CHAR (TO_DATE(date,'YYYYMMDD')-30, 'YYYYMMDD')
                
      THEN 1 else 0 end last_30,

CASE

      WHEN     (flag_1=1 or flag_2=1)                
          
           and    TO_CHAR (TO_DATE(date,'YYYYMMDD'), 'YYYYMMDD') >= TO_CHAR (TO_DATE(date,'YYYYMMDD')-1, 'YYYYMMDD')
                
      THEN 1 else 0 end last_day
    
      from table_1
2

This looks like an Oracle function, but this is a SQL Server forum

3

hi, thank you for your replay
i have both platforms can you advice how to do in SQL?

advanced thanks

4 
SELECT
	SUM(CASE WHEN [Date] >= CAST( DATEADD(DAY,-1,GETDATE()) AS DATE) THEN 1 ELSE 0 END ) 
		AS OneDayTotal,
	SUM(CASE WHEN [Date] >= CAST( DATEADD(DAY,-30,GETDATE()) AS DATE) THEN 1 ELSE 0 END ) 
		AS ThirtyDayTotal
FROM
	Table_1