I have got source data in following way:
Col_1 | Col_2
100 | 2000
100 | 3000
100 | 4000
300 | 5000
300 | 6000
300 | 7000
I need to generate an extra column as Col_3 (based upon Col_2) where it contains the value from previos row, starting from 0 for every group based on Col_1. The outcome expected as:
Col_1 | Col_2 | Col_3
100 | 2000 | 0
100 | 3000 | 2000
100 | 4000 | 3000
300 | 5000 | 0
300 | 6000 | 5000
300 | 7000 | 6000
coalesce(min(col_2) over (partition by col_1 order by col_2 rows between 1 preceding and 1 preceding), 0)
Can some one help please .I arrive at a wrong result in CALL_SEQUENCE at the step highlighted in yellow
19/11/2013 12:37| 10222014| -1| 1| 1
22/11/2013 19:00| 10222014| 3| 2| 2
22/11/2013 20:01| 10222014| 0| 3| 3
25/11/2013 18:51| 10222014| 3| 4| 4
05/12/2013 17:47| 10222014| 10| 1| 1
05/12/2013 18:37| 10222014| 0| 6| 2
07/12/2013 17:44| 10222014| 2| 7| 3
16/07/2014 20:18| 10222014| 221| 1| 1
16/07/2014 20:25| 10222014| 0| 9| 2
ORDER BY CLE,EVT_DT
WHEN DELTA=-1 OR DELTA >3 THEN 1 ELSE ADD 1 TO PRECEDING CALL_SEQUENCE
,CASE WHEN DELTA=-1 OR DELTA>3 THEN 1 ELSE
ROW_NUMBER() OVER ( PARTITION BY CLE ORDER BY CLE,EVT_DT ASC ) END AS CALL_SEQUENCE
,CASE WHEN CLE=MAX(CLE) OVER (ORDER BY CLE,EVT_DT ASC ROWS BETWEEN
1 PRECEDING AND 1 PRECEDING)
THEN CAST(EVT_DT AS DATE )-MAX(CAST(EVT_DT AS DATE )) OVER ( ORDER BY CLE,EVT_DT ASC ROWS
BETWEEN 1 PRECEDING AND 1 PRECEDING) ELSE -1 END AS DELTA
FROM TABLE_NM ADV
WHERE CLE IN ('10222013','10222014','10222015') ORDER BY CLE,EVT_DT
Hi All ,
I need help for the below scenario.
This will return the UID if both values exist:
where SID in ('S1','S2') -- only searched values
group by uid
having min(SID) <> max(SID)-- both must exist
If you need to search for more than two values change to:
having count(distinct SID) = 2 -- number of searched values
If there are no duplicate values you can remove the DISTINCT.
But what if there's an additional value like 'S3', the previous will still return the UID.
If you need exactly 'S1' and 'S2' but no other value:
group by uid
count(distinct case when SID in ('S1','S2') then SID end) = 2 -- both values
count(case when SID in ('S1','S2') then NULL else SID end) = 0 -- but no other value
Again, if there are no duplicate values you can remove the DISTINCT
Thanks Dieter for you Instant Reply,
But in my case the values or the count is not fixed . It may happen that we can get two values or Twelve values .
In such case where we dont know how many Source ID (SID) will come , how can we handle it ?
WITH YOU Query Dieter ! when we use the IN operator it will also give those user names(UID) which are having only permission like (S1).
SID is permission like Grant , Read , Execute , Write so on S1......S300
UId are the usernames U1....Un
so one UID can have many Permissions or vice versa.
Now suppose our Client needs to know how many users are having the access of Read(s7) and Grant(s12)
So it should display the userid , having both the Permissions.But this permission values is not fixed , means any number of Permissions can come as Input ranging (S1 to S300) so we cannot write a fixed values query to acheive it .
APart from this , the value will be like (S1,S8,S9) then we need to seperate each of the Permission and find the required User id .
I hope m clear enough this time :P .