Salary Report Generator - SQL Interview Query 5 | SQL Problem Level "MEDIUM"

[@JyotiGupta-v5w]

I have solved it with case statement
with cte as(
select * from salary
cross join
(select income,percentage
from income
union
select deduction ,percentage from deduction )),
cte2 as(
select *,case
when income='Insurance' then round((base_salary * (cast(percentage as decimal)/100)),2)
when income='House' then round((base_salary * (cast(percentage as decimal)/100)),2)
when income='Basic' then round((base_salary * (cast(percentage as decimal)/100)),2)
when income='Health' then round((base_salary * (cast(percentage as decimal)/100)),2)
when income='Allowance' then round((base_salary * (cast(percentage as decimal)/100)),2)
when income='Others' then round((base_salary * (cast(percentage as decimal)/100)),2) end as amount
from cte)
select emp_name,
sum(case when income='Basic' then amount end) as basic,
sum(case when income='Allowance' then amount end) as Allowance,
sum(case when income='Others' then amount end) as Others,
sum(case when income in('Others','Allowance','Basic') then amount end) as gross,
sum(case when income ='Insurance' then amount end) as insurance,
sum(case when income ='Health' then amount end )as Health,
sum(case when income ='House' then amount end )as House,
sum(case when income in('House','Health','Insurance') then amount end )as total_deduction,
sum(case when income in('Others','Allowance','Basic') then amount end) -
sum(case when income in('House','Health','Insurance') then amount end )as net_pay
from cte2
group by 1

[@samsonm4345]

Hi I paid money for the sql class and having issues and you haven’t been responding to any messages about additional materials we are supposed to have received. Definitely not good service.

[@rushikeshwaghmare3446]

Nice question…it got cleared two concepts cross join and pivot…thank u…keep it up…waiting for more such question…❤

[@sivakrishnasriram4782]

Thank You as small word for your work ,dedication and finally helping to others progress. Thank you very Much.

[@radhikamaheshwari4835]

with cte as
(
select id, income as trans_type, percentage
from
income
union all
select id, deduction as trans_type, percentage
from deduction
),
cte2 as
(
select emp_id, emp_name, trans_type, (percentage * base_salary)/100 as amount
from salary
cross join
cte
),
cte3 as
(
select emp_name as employee,
sum(case when trans_type = 'Basic' then amount else null end) as Basic,
sum(case when trans_type = 'Allowance' then amount else null end) as Allowance,
sum(case when trans_type = 'Others' then amount else null end) as Others,
sum(case when trans_type = 'Insurance' then amount else null end) as Insurance,
sum(case when trans_type = 'Health' then amount else null end) as Health,
sum(case when trans_type = 'House' then amount else null end) as House
from cte2
group by emp_name
)
select *,
Basic + Allowance + Others as Gross,
Insurance + Health + House as Total_deduction
from cte3

[@nikhilbodapudi7806]

My point is at 10:50 why do we use the case statement even though we are using the same formula base_salary* percentage/100 by using this directly is also fine right !!!!!

[@techTFQ]

you are right.. It was unnecessary over here.

[@riyasethi5966]

I had the same question. I in fact came to read the comments to know if anyone had this question. Thanks thoufiq for the confirmation here.

[@pawangadhave1548]

Thank you so much for the detailed explanation.😊

[@georgekakweza3767]

Using MySql Workbench for first part of Question:
WITH trns_type AS
(
SELECT income.income AS trns_type, income.percentage FROM income
UNION
SELECT deduction.deduction AS trns_type, deduction.percentage FROM deduction
)
SELECT salary.emp_id, salary.emp_name, trns_type,
ROUND(salary.base_salary * (percentage/100)) AS amount
FROM salary
CROSS JOIN
trns_type;

[@haleylearn]

-- SOLUTION 2: Using MAX(), GROUP BY and CTE
WITH cte AS (
SELECT emp_name,
MAX(CASE WHEN trns_type = 'Basic' THEN amount END) AS Basic,
MAX(CASE WHEN trns_type = 'Allowance' THEN amount END) AS Allowance,
MAX(CASE WHEN trns_type = 'Others' THEN amount END) AS Others,
MAX(CASE WHEN trns_type = 'Insurance' THEN amount END) AS Insurance,
MAX(CASE WHEN trns_type = 'Health' THEN amount END) AS Health,
MAX(CASE WHEN trns_type = 'House' THEN amount END) AS House
FROM emp_transaction
GROUP BY emp_name
)
SELECT emp_name,
Basic, Allowance, Others,
Basic + Allowance + Others AS Gross,
Insurance, Health, House,
Insurance + Health + House AS Total_Deduction
FROM cte

[@haleylearn]

-- SOLUTION 3: DYNAMIC SQL
-- STEP 1: Generate column list dynamically
DECLARE @cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(trns_type), (','))
FROM (SELECT DISTINCT trns_type FROM emp_transaction) x;
PRINT @cols;
-- STEP 2: Build the dynamic SQL query
SET @sql = N' SELECT emp_name, ' + @cols +
N' FROM (
SELECT emp_id, emp_name, trns_type, amount
FROM emp_transaction
) AS SourceTable
PIVOT
(
MAX(amount)
FOR trns_type IN (' + @cols + N')
) AS pivot_table ';
-- Execute the dynamic SQL query
EXEC sp_executesql @sql;

[@akshaysalve195]

with cte as (
Select * from employees
cross join
(Select * from income union select * from deductions) a)
Select Emp_id,Emp_name, Income as Trans_type, round((salary*percentage)/100,2) as amount from cte;
not very clean solution but got the EMP_Transaction table in a single attempt

[@amateurbyparul]

Hey, is the below query correct for the first expected output table: I have not applied case statement Select salary.Emp_id as Emp_id, salary.emp_name as Emp_name,
trns_type, base_salary*percentage/100
From Salary
Cross Join (Select id, income as Trns_type, percentage
from Income
Union
Select id, deduction as trns_type, percentage
From Deduction)

[@kishanagrawal5555]

sir you could directly multiply salary column with percentage from the union and divide by 100 as the cross join would work for all rows and we have same formula for all rows

[@radhakrishnandapkara5505]

that's what I was thinking, I believe there was no need to write case statement but thanks for your efforts @techtfq

[@sathvikdakappagari9122]

exactly
select s.emp_id, s.emp_name, a.income as trans_type,
(s.base_salary*a.percentage)/100 as amount
from salary as s
cross join ((select * from income)
union all
(select * from deduction)) a
this is what i did and it worked fine, lmk if i missed anything

[@nandan7755]

Thank You 🎉🎉❤

[@fathimafarahna2633]

Thank you for your efforts 🙏

[@splendidabhi]

Done day5 of #30daySQLQueryChallenge

[@naveendevihosur8050]

thank you

[@ameygoesgaming8793]

Why do you need a case statement, we can do directly base_salary*percentage/100??

[@techTFQ]

exactly, why did I need the case statement. guess I am getting old..
It was unnecessary over here.

[@MdZeeshan-m9u]

@@techTFQ sir😅

[@sass520]

is this crosstab important from interview perspective?

[@sapnasaini851]

I have solved this using case -
select Employee, Basic, Allowance, Others, (Basic + Allowance + Others) as Total_Income,
Insurance, Health, House, (Basic + Allowance + Others) as total_Deduction,
(Basic + Allowance + Others -Insurance - Health - House) as net_pay
from(
select emp_name as Employee,
sum(case when trns_type = 'Basic' then amount else 0 end ) as Basic,
sum(case when trns_type = 'Allowance' then amount else 0 end ) as Allowance,
sum(case when trns_type = 'Others' then amount else 0 end ) as Others,
sum(case when trns_type = 'Insurance' then amount else 0 end ) as Insurance,
sum(case when trns_type = 'Health' then amount else 0 end ) as Health,
sum(case when trns_type = 'House' then amount else 0 end ) as House
from emp_transaction
group by employee ) as x

[@user-Veeravalli]

-------------2nd approach for Create Emp_Transaction Table in SSMS-------------
select * into Emp_Transaction from(
select a.Emp_id,a.Name,b.income as tran_type,a.Sal/100*b.percentage as amount from Sal a cross join(
select income,percentage from income
union all
select deduction,percentage from deduction) b
) k

[@sourabhkumar6811]

with cte as
(select distinct s.emp_id,
s.emp_name,
d.deduction as trans_type,
s.base_salary*(d.percentage/100.0)as amount
from salary s,deduction d
union all
select distinct s.emp_id,
s.emp_name,
i.income as trans_type,
s.base_salary*(i.percentage/100.0)as amount
from salary s,income i
order by emp_id),
cte1 as
(select * from cte)
select emp_name,
max(case when trans_type='Basic' then amount end) as Basic,
max(case when trans_type='Allowance' then amount end) as Allowance,
max(case when trans_type='Health' then amount end) as Health,
max(case when trans_type='Others' then amount end) as Others,
max(case when trans_type='Insurance' then amount end) as Insurance
from cte1
group by emp_name
order by emp_name;

[@devarapallivamsi7064]

My query for populating employee_transaction:
Note:
uncomment "--WHERE.." and you will get to know how each row is interacting with others.
The outermost CTE 'my_final' is not mandatory. I've included it only for sake of ordering the rows as shown in the video.
WITH my_final AS (
WITH cte AS (
SELECT * FROM salary
-- WHERE emp_id = 1
)
SELECT emp_id,emp_name,income.income AS "TRANS_TYPE",((base_salary*percentage)/100)::int AS amount FROM cte
CROSS JOIN income
UNION
SELECT emp_id,emp_name,deduction.deduction AS "TRANS_TYPE",((base_salary*percentage)/100)::int AS amount
FROM cte
CROSS JOIN deduction)
SELECT *,ROW_NUMBER() OVER(PARTITION BY emp_id ORDER BY NULL) AS rn FROM my_final

[@rasuni24]

used PostgreSQL without cast , it worked fine for me
select s.emp_id,s.emp_name ,d.deduction as trans_type, (s.base_salary *d.percentage /100) as Amount
from salary s cross join deduction d
union
select s.emp_id,s.emp_name ,i.income as trans_type, (s.base_salary *i.percentage /100) as Amount
from salary s cross join income i;

[@termteluguchannel9070]

Hi Thoufiq,
Could you please explain the reason why you have used sum alone why not Max or min for aggregation in pivot query.
i will get some clarification on sum(amount) 😌. Even if we have used the sum of amount, the values in the result set are not added together to bring a new value.

[@ojaswinidixit3831]

Thank you! I finally understood Pivot you just made it so easy!

[@shivinmehta7368]

select emp_name,
max(case when trns_type='Basic' then amount end) as basic,
max(case when trns_type='Allowance' then amount end) as Allowance,
max(case when trns_type='Others' then amount end) as Others,
sum(case when trns_type in ('Basic','Allowance','Others') then amount end) as Gross,
max(case when trns_type='Insurance' then amount end) as Insurance,
max(case when trns_type='Health' then amount end) as Health,
max(case when trns_type='House' then amount end) as House,
sum(case when trns_type in ('Insurance','Health','House') then amount end) as Deductions,
(sum(case when trns_type in ('Basic','Allowance','Others') then amount end)-
sum(case when trns_type in ('Insurance','Health','House') then amount end)) as net
from emp_transaction
group by 1 order by 1

[@rohitsharma-mg7hd]

can u explain what does max do here ? and how u get rid of nulls

[@shivinmehta7368]

@@rohitsharma-mg7hd rows to column conversion

[@digitaltechconnect1318]

Nice thoufiq, buts it's hard to remember the syntax on cross tab and pivot if we don't use frequently 😊

[@kushmanthreddy4762]

select emp_id, emp_name, trns_type,round(base_salary*(cast(percentage as decimal)/100),2) as v
from salary
cross join (select income as trns_type, percentage from income
union
select deduction as trns_type, percentage from deduction) x by using this also iam getting same answers.is case compulsory

[@TheRaju991]

No case is not compulsory.

[@annar6394]

you are brilliant , awsome question :)

[@invincible9971]

Although I have delayed to participate in this challenge.
Solution in MySQL:
select emp_id, emp_name, TRANS_TYPE,
case
when TRANS_TYPE = "Basic" then base_salary
when TRANS_TYPE = "Allowance" then round((base_salary*4)/100 , 2)
when TRANS_TYPE = "Others" then round( (base_salary*6)/100, 2)
when TRANS_TYPE = "Insurance" then round( (base_salary*5)/100 , 2)
when TRANS_TYPE = "Health" then round( (base_salary*6)/100, 2)
when TRANS_TYPE = "House" then round( (base_salary*4)/100 , 2)
end as AMOUNT
from (
(select distinct s.emp_id, s.emp_name, s.base_salary, i.income as TRANS_TYPE, i.percentage
from Q5_salary s join Q5_income i )
union
(select s.emp_id, s.emp_name, s.base_salary, d.deduction as TRANS_TYPE, d.percentage
from Q5_deduction d cross join Q5_salary s )
) abc ;

[@forzahorizon4eliminator206]

you the best brother thanks

[@HeyyRomii]

good question :)

[@juanromantorres5063]

Thank you !!

[@siddhantsharma4245]

I was trying to do the second objective on mysql....I am exhausted...Can you help me?

[@Alexpudow]

with a as (
select emp_id, emp_name, income, base_salary/100*percentage amount
from salary
cross join
(select * from income
union
select * from deduction) t
)
select emp_name
,max(case when income = 'Basic' then amount end) Basic
,max(case when income = 'Allowance' then amount end) Allowance
,max(case when income = 'Others' then amount end) Others
,max(case when income = 'Basic' then amount end)
+max(case when income = 'Allowance' then amount end)
+max(case when income = 'Others' then amount end) Gross
,max(case when income = 'Insurance' then amount end) Insurance
,max(case when income = 'Health' then amount end) Health
,max(case when income = 'House' then amount end) House
,max(case when income = 'Insurance' then amount end)
+max(case when income = 'Health' then amount end)
+max(case when income = 'House' then amount end) total_deduction
from a
group by emp_name

[@정키위]

my oracle answer
--expected output1
SELECT emp_id, emp_name, trns_type,
base_salary*percentage*0.01 AS amount
FROM salary s,
(select income as trns_type, percentage from income
union
select deduction as trns_type, percentage from deduction) x
ORDER BY trns_type, emp_id, amount
;
--expected output2
SELECT emp_name,
sum(decode(trns_type, 'Basic', amount)) AS BASIC,
sum(decode(trns_type, 'Allowance', amount)) AS ALLOWANCE,
sum(decode(trns_type, 'Others', amount)) AS OTHERS,
SUM(CASE WHEN trns_type IN ('Basic', 'Allowance', 'Others')
THEN amount
END) AS GROSS,
sum(decode(trns_type, 'Insurance', amount)) AS INSURANCE,
sum(decode(trns_type, 'Health', amount)) AS HEALTH,
sum(decode(trns_type, 'House', amount)) AS HOUSE,
SUM(CASE WHEN trns_type IN ('Insurance', 'Health', 'House')
THEN amount
END) AS TOTAL_DEDUCTION
FROM emp_transaction
GROUP BY emp_name
ORDER BY emp_name
;