how to check if value exists in previous date and not in current date

1
votes

Below is my table and has id's, dates columns

ID      Date
1       2017-06-11
2       2017-06-11
3       2017-06-04
1       2017-06-04
4       2017-06-04
5       2017-06-04
6       2017-05-28
5       2017-06-04

2017-06-11 is this week beginning date and 2017-06-04 is last week beginning date. I want to create a flag where an Id exists in previous week and not in this week as 1.

for example, ID 4 is present in previous week and not in this week.similarly ID 6 is present in 2017-05-28 and not in 2017-06-04. When I query/analyse my data in this week then my data should look like

ID      Date                 flag
1       2017-06-11           0
2       2017-06-11           0
3       2017-06-04           0
1       2017-06-04           0 
4       2017-06-04           1 
5       2017-06-04           0

Similarly when I analyse my data for previous week,

ID      Date              flag
3       2017-06-04        0   
1       2017-06-04        0   
4       2017-06-04        0    
5       2017-06-04        0
6       2017-05-28        1
5       2017-06-04        0

I tried with Left join to check Id's present in current date and not in previous date , however i get inaccurate data because this has to be dynamic. When I query each week, I have to compare this week and previous week only and when I query last week, I have to get last week and 2017-05-28 data only.

Then I give filter in tableau to select the date and display the result.

Can anyone help me how to solve this problem?

2
sqltableau-api
which database are you using? (MSSQL, Oracle, Mysql, ...) – etsa

2 Answers

1
votes

Calculate the starting day of the week you want and return the value for two weeks

DECLARE @date DATETIME = GETDATE()

DECLARE @firstDayWeek DATE  =(SELECT convert(DATE,(DATEADD(dd,-DATEPART(WEEKDAY, @date)+1,@date))))

 SELECT *
 ,CASE WHEN f.lastDate < @firstDayWeek THEN 1 ELSE 0 END flag 
FROM tablee t1 
OUTER APPLY( SELECT MAX(t2.date) lastDate 
             from tablee t2  
             WHERE t2.ID=t1.ID 
              AND t2.date < DATEADD(dd,7,@firstDayWeek )
            ) f
WHERE BETWEEN DATEADD(dd,-8,@firstDayWeek ) AND DATEADD(dd,6,@firstDayWeek )
0
votes

As you want to have the query result depending on the week you want to view, I'd suggest to write a query that takes the start of the week of interest and the start of the corresponding previous week as parameter. If I understand you right, then you want a query like ... "Take all records from the current and the previous week; for those records of the previous week, where no corresponding entry for the current week exists, write flag=1; else write flag=0". Then the following query, which directly "translates" this into SQL should do the job: 

select
  t1.*,
  (case when t1.date = '2017-06-04'
         and not exists
            (select * from table t2 where t2.id = t1.id and t2.date = '2017-06-11')
       then 1
       else 0
       end) as flag
from table t1
where t1.date = '2017-06-04' or t1.date = '2017-06-11'