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 | 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.
Views
Replies
Total Likes
Hi @Ramaswami
If 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 ago
Regards,
Milan
Views
Replies
Total Likes
Views
Replies
Total Likes
Hi @Ramaswami,
set up like this:
Regards,
Milan
Views
Replies
Total Likes
Hi @milan
tried like this and it says id should be in group by :
09/03/2020 6:00:51 AM query PGS-220000 PostgreSQL error: ERROR: column "n0.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...ATED_DATE, 'America/Chicago')), N0.RESPONDENT_ID, N0.ID FROM...
if i keep id in group by it is giving me all the counts like : So for each respondent it is giving me min date of the unique id which is not what we need :
here the resp is same but because of group by unique id it is giving me all the records.
count = 1
resp = 123
min date = 2020-08-30
id = 1290
count =1
resp = 123
min date = 2020-08-23
id = 122345
Views
Replies
Total Likes
Views
Replies
Total Likes
Hi @Ramaswami,
Was the proposed solution helpful for you? Please let us know.
Thanks!
Views
Replies
Total Likes
Views
Replies
Total Likes
Views
Likes
Replies
Views
Likes
Replies
Views
Likes
Replies