Date query

Question

Hi Folks,

I want to grab the data from a table whose date is only 2 days ago and ignore if he has more than 1 row in the table.

For example : the below is the table

unique id	respondent id	class	occupation	created date
11	1	abc	abc	11/8/2020
12	1	efg	o	12/8/2020
13	2	huu	p	11/8/2020
14	2	abc	abc	11/8/2020
15	3	efg	o	11/8/2020
16	3	df	df	9/8/2020

if i run the above table it should return the below :

13

2

huu

p

11/8/2020

14

2

abc

11/8/2020

I want to grab the people of only 11th Aug and if a guy have created date as 11th aug and some other date i should ignore him. Ideally a guy should have activities only for 11th Aug ( it may be more than 1 row and created by is 11 Aug that's fine) he should not have any more activity on other dates.

i have created a query like this :

related sql query :

SELECT N0.ID FROM tablename N0 WHERE (DateOnly(N0.CREATED_DATE, 'America/Chicago') = AddDays(DateOnly(GetDate(), 'America/Chicago') , -2)) AND NOT ((DateOnly(N0.CREATED_DATE, 'America/Chicago') > AddDays(DateOnly(GetDate(), 'America/Chicago') , -2)) AND (DateOnly(N0.CREATED_DATE, 'America/Chicago') < AddDays(DateOnly(GetDate(), 'America/Chicago') , -2))) LIMIT 10000

it is returning me all the rows "NOT" is not working.

Milan_Vucetic · Answer

Hi @ramaswamiIf I have correct understanding you need unique record which occured before two days: basicaly you want to fetch records which happended exactly two day ago and there is no duplicates on it in the whole table no matter on the date.You may use  one Query node and make the following aggregation: SELECT respondent_id, Count(unique id) as CT, Max(trunc(created_date)) as MX from your_table GROUP BY respondent_id  and then use another Query or Split node to query previous one with condition CT = 1 and MX = two days agoRegards,Milan

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded