Msg 8117, Level 16, State 1, Line 61
Operand data type time is invalid for sum operator.
What I have tried:
create table USERINFO(
username varchar(20),
country varchar (15),
timeinmin time,
dateofcall date)
insert into USERINFO (username, country, timeinmin, dateofcall)
values
('Bharat', 'A', '0:12', '11/11/2021'),
('Bharat', 'A', '0:10', '12/12/2021'),
('Bharat', 'B', '0:11', '12/13/2021'),
('Bharat', 'B', '0:01', '12/14/2021'),
('Bharat', 'B', '0:12', '12/15/2021'),
('Bharat', 'C', '0:03', '12/16/2021'),
('Bharat', 'C', '0:45', '12/17/2021')
;
---------------------select USERINFO table
select * from USERINFO
----------------------create tariff table
Create table Tariff(
country varchar(15),
tariff_inUSD_permin money)
insert into Tariff
select 'A',1 UNION ALL
select 'B',2 UNION ALL
select 'C',3
select * from Tariff
-----------------create Tax table
create table Tax(
country varchar(15),
tax decimal(4,3))
insert into Tax
select 'A',0.015 UNION ALL
select 'B',0.025 UNION ALL
select 'C',0.035
select * from Tax;
;WITH CTE_sumduration
AS
(select U.username,U.country,SUM(U.timeinmin) AS duration
from USERINFO U
GROUP BY U.username,U.country
)
select CTE.username,CTE.country,CTE.duration,T.tariff_inUSD_permin * CTE.duration AS charge
,T.tariff_inUSD_permin * CTE.duration * Tx.tax Taxcharge,T.tariff_inUSD_permin * CTE.duration * Tx.tax +
T.tariff_inUSD_permin * CTE.duration Total
from CTE_sumduration CTE
JOIN Tariff T
ON
CTE.country=T.country
JOIN TAX Tx
ON CTE.country=Tx.country
GROUP BY CTE.username,CTE.country,CTE.duration,Tx.tax,T.tariff_inUSD_permin * CTE.duration,
T.tariff_inUSD_permin * CTE.duration * Tx.tax,T.tariff_inUSD_permin * CTE.duration * Tx.tax +
T.tariff_inUSD_permin * CTE.duration