Error in group by clause in sqlserver

Posted by Klbaiju under Sql Server on 6/23/2017 | Points: 10 | Views : 210 | Status : [Member] | Replies : 0
Hi following is my table


create table busdetails(bus_id nvarchar(50),uname nvarchar(20),tour_date datetime,tour_id nvarchar(50),Tour_type varchar(2),balance nvarchar(50),dsal nvarchar(50),csal nvarchar(50),fare nvarchar(50))
insert into busdetails values('109','rajesh','2017-06-20 00:00:00 000','109-2017-06-20','M','0','500','600','7000')
insert into busdetails values('109','rajesh','2017-06-21 00:00:00 000','109-2017-06-20','M','0','0','0','0')
insert into busdetails values('109','rajesh','2017-06-22 00:00:00 000','109-2017-06-20','M','0','0','0','0')
insert into busdetails values('109','rajesh','2017-06-22 00:00:00 000','109-2017-06-22','S','0','200','300','5000')
insert into busdetails values('110','rajesh','2017-06-22 00:00:00 000','110-2017-06-22','S','0','200','600','5000')

select query will be like this

bus_id uname tour_date tour_id Tour_type balance dsal csal fare
109 rajesh 2017-06-20 00:00:00 000 109-2017-06-20 M 0 500 600 7000
109 rajesh 2017-06-21 00:00:00 000 109-2017-06-20 M 0 0 0 0
109 rajesh 2017-06-22 00:00:00 000 109-2017-06-20 M 0 0 0 0
109 rajesh 2017-06-22 00:00:00 000 109-2017-06-22 S 0 200 300 5000
110 rajesh 2017-06-22 00:00:00 000 110-2017-06-22 S 0 200 600 5000

My requirement is to add dsal and csal and subtract that from fare.

following is my query for that

select bus_id,tour_date,case when (balance=0 and dsal <>0) then (CAST(fare as int))-(CAST(dsal as int)+CAST(csal as int)) else 0 end as profit
from busdetails group by tour_date,bus_id,case when (balance=0 and dsal <>0) then (CAST(fare as int))-(CAST(dsal as int)+CAST(csal as int)) else 0 end

if I execute above query following error is showing

Msg 242, Level 16, State 3, Line 7
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
Msg 242, Level 16, State 3, Line 8
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
Msg 242, Level 16, State 3, Line 9
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
Msg 242, Level 16, State 3, Line 10
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
Msg 242, Level 16, State 3, Line 11
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

(0 row(s) affected)
if i change datatype of tour_date to nvarchar(50) it will work fine.

in my database tour_date is datetime type.

following is my required output

bus_id tour_date profit
109 2017-06-20 00:00:00 000 5900
109 2017-06-21 00:00:00 000 0
109 2017-06-22 00:00:00 000 0
109 2017-06-22 00:00:00 000 4500
110 2017-06-22 00:00:00 000 4200

how to do this

regards

Baiju




Responses

(No response found.)

Login to post response