I have a table on which i want to do some advance calculation and get the table in a new shape

Question

ID  input_date  created
22564   3/4/2019    5/29/2019
22564   3/4/2019    3/6/2019
22564   3/4/2019    3/12/2019
22564   3/4/2019    4/18/2019
22564   3/4/2019    4/12/2019
22564   3/4/2019    4/10/2019
22564   3/4/2019    3/25/2019
22564   3/4/2019    3/29/2019
22564   3/4/2019    4/4/2019
22564   3/4/2019    3/19/2019
22564   3/4/2019    5/29/2019
22564   3/4/2019    5/20/2019
22543   3/4/2019    3/20/2019
22543   3/4/2019    3/28/2019
22543   3/4/2019    4/12/2019
22543   3/4/2019    4/19/2019
22543   3/4/2019    3/13/2019
22543   3/4/2019    3/6/2019
22543   3/4/2019    5/20/2019
22543   3/4/2019    6/17/2019
22543   3/4/2019    4/4/2019
12558   3/4/2019    1/4/2019

I want to get this first

acctrefno   first   second  third   fourth
22564   2   8   15  21
22543   2   9   16  24
22595   2   9   16  24
24324   1   7   12  19
26506   1   12  20  26

Finally, I would like to get here i.e.

if the first is within the 3 days from date input I want to pit 1
if second is withing 3 days from first then I want to put 1
else 0 and so on till the fourth date.

Etc...

acctrefno   first   second  third   fourth
22564   1   0   0   0
22543   1   0   0   0
22595   1   0   0   0
24324   1   0   0   0
26506   1   0   0   0

I have completed this in excel and Tableau but its very time consuming and I have a large data set so I need to do it in SQL

Do not have a code as I did not try thin in SQL I have no clue how to go about it. The only thing I can do is use datediff function and come up with days

NA

I don't understand what you are trying to do. What does "first" or "second" mean in this context? — Sean Lange, Aug 15 '19 at 18:33
Why the tag R? In R this is basically a dupe of [reshape data from long to wide](https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format). Also, how does the table of differences correspond to the input data? — Rui Barradas, Aug 15 '19 at 18:58
first = difference of the number of days input date - created, Second = min (created) - next created, and so on So to calculate first the reference date is in put date and for second onwards it's prior created. Thank you — Shrey, Aug 15 '19 at 18:58
Is it `abs(created - input_date)` then made binary by `abs(created - input_date) <= 3`? — Rui Barradas, Aug 15 '19 at 19:00
I used datediff(day,lag( LC.created,1) over (partition by LC.acctrefno order by LC.created asc), LC.created) AS X to get the datediff in a new colum. — Shrey, Aug 15 '19 at 19:01

score 1 · Answer 1 · answered Aug 15 '19 at 19:02

1

Compute datediff, pivot by row_number() and chain difference threshold flags so that if previous flag is 0 all the rest are 0 too. If you don't want to chain flags just select ID, f1 first, f2 second, f3 third, f4 fourth.

select ID, f1 first, f1*f2 second, f1*f2*f3 third, f1*f2*f3*f4 fourth
from(
    select ID, [1],[2],[3],[4]
    from(
        select ID, input_date, datediff(dd, input_date, created) d
        , row_number() over(partition by ID order by created) rn
        from ta
    ) t
    pivot (max(d) for rn in ([1],[2],[3],[4])) p
) t
cross apply(
 select f1 = case when [1] <= 3 then 1 else 0 end
   ,f2 = case when [2]-[1] <= 3 then 1 else 0 end
   ,f3 = case when [3]-[2] <= 3 then 1 else 0 end
   ,f4 = case when [4]-[3] <= 3 then 1 else 0 end
) flags;

Fiddle

answered Aug 15 '19 at 19:02

Serg

22,285
5
21
48

Thank you I will try it out – Shrey Aug 15 '19 at 19:22
See my [comment](https://stackoverflow.com/questions/57514430/i-have-a-table-on-which-i-want-to-do-some-advance-calculation-and-get-the-table/57514958#comment101499639_57514958) to my answer. – Rui Barradas Aug 15 '19 at 20:30
@Shrey Shouldn't you accept [this answer](https://stackoverflow.com/questions/57514430/i-have-a-table-on-which-i-want-to-do-some-advance-calculation-and-get-the-table/57514833#57514833) and not [mine](https://stackoverflow.com/questions/57514430/i-have-a-table-on-which-i-want-to-do-some-advance-calculation-and-get-the-table/57514958#57514958)? It exactly fits the expected output in the question. – Rui Barradas Aug 15 '19 at 20:32
I really liked this solution just one question there are som more column which I want to include with this query. Where should I fit the join statement – Shrey Aug 19 '19 at 18:15
@Shrey, it depends on this extra column relationship with the original table. Can you provide more details? – Serg Aug 19 '19 at 19:08
How can i put the sample query I have? I am not able to put sample query here – Shrey Aug 19 '19 at 19:17
You may edit your question to add additional info. – Serg Aug 19 '19 at 19:18

Rui Barradas · Accepted Answer · 2019-08-15T19:20:22.203

In R the problem can be solved with package tidyverse. It's a simple matter of spreading by the appropriate columns.

library(tidyverse)

df1 %>%
  mutate(input_date = as.Date(input_date, "%m/%d/%Y"),
         created = as.Date(created, "%m/%d/%Y"),
         d = as.integer(abs(created - input_date) <= 3)) %>%
  select(-created, - input_date) %>%
  group_by(ID) %>%
  mutate(rowid = row_number()) %>%
  spread(rowid, d, fill = 0) %>%
  as.data.frame()
#     ID 1 2 3 4 5 6 7 8 9 10 11 12
#1 12558 0 0 0 0 0 0 0 0 0  0  0  0
#2 22543 0 0 0 0 0 1 0 0 0  0  0  0
#3 22564 0 1 0 0 0 0 0 0 0  0  0  0

Test data.

df1 <- read.table(text = "
ID  input_date  created
22564   3/4/2019    5/29/2019
22564   3/4/2019    3/6/2019
22564   3/4/2019    3/12/2019
22564   3/4/2019    4/18/2019
22564   3/4/2019    4/12/2019
22564   3/4/2019    4/10/2019
22564   3/4/2019    3/25/2019
22564   3/4/2019    3/29/2019
22564   3/4/2019    4/4/2019
22564   3/4/2019    3/19/2019
22564   3/4/2019    5/29/2019
22564   3/4/2019    5/20/2019
22543   3/4/2019    3/20/2019
22543   3/4/2019    3/28/2019
22543   3/4/2019    4/12/2019
22543   3/4/2019    4/19/2019
22543   3/4/2019    3/13/2019
22543   3/4/2019    3/6/2019
22543   3/4/2019    5/20/2019
22543   3/4/2019    6/17/2019
22543   3/4/2019    4/4/2019
12558   3/4/2019    1/4/2019                  
", header = TRUE)

@Shrey Shouldn't you accept [the other answer](https://stackoverflow.com/questions/57514430/i-have-a-table-on-which-i-want-to-do-some-advance-calculation-and-get-the-table/57514833#57514833)? It exactly fits the expected output in the question. — Rui Barradas, Aug 15 '19 at 20:29

I have a table on which i want to do some advance calculation and get the table in a new shape

2 Answers2