-
Notifications
You must be signed in to change notification settings - Fork 0
/
CTE.sql
37 lines (23 loc) · 955 Bytes
/
CTE.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- CTE
---Make a report of employees Total transaction for the year 2014 and 2015 side by side for comparison
with Transaction2014 as
(
select Emp_id,DateOfTransaction, sum(Amount) over(partition by Emp_Id ) as TotalTransaction2014
from EmpTransaction
where DateOfTransaction <'2015-01-01'),
Transaction2015 as
(select Emp_id, DateOfTransaction,sum(Amount) over(partition by Emp_Id ) as TotalTransaction2015
from EmpTransaction
where DateOfTransaction between '2015-01-01'and '2015-12-31'
)
select * from Transaction2014 left join Transaction2015 on Transaction2014.Emp_id = Transaction2015.Emp_id
---Find out which emp_id are not used for Transaction
with rownum as
(
select top(1125) ROW_NUMBER() over(order by (select null)) as Rowss from EmpTransaction
)
select * from rownum as r
left join EmpTransaction as t
on r.Rowss = t.Emp_id
where t.Emp_id is null
order by r.Rowss