Group by clause error in sqlserver 2008
Hi following is my tablecreate 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