SQLTeam.com | Weblogs | Forums

Error in groupby in sqlserver2008

sql2008

#1

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


#2
insert into busdetails values('109','rajesh','2017-06-20 00:00:00 000','109-2017-06-20','M','0','500','600','7000')

that needs to be at the least:

insert into busdetails values('109','rajesh','2017-06-20 00:00:00.000','109-2017-06-20','M','0','500','600','7000')
.................................................................^

and you may also find that (depending on the Locale that your Server is set up for AND various attirbutes of the current connection, such as LANGUAGE and so on) that you need the date part of a DATETIME string to be "20170620 00:00:00.000" without any hyphens.

The only Datetime strings which are converted UNambiguously are "yyyymmdd", "yyyymmdd 00:00:00.000" or the ISO "yyyy-mm-ddT00:00:00.000" - so best to leave all the punctuation out of date strings, and only ever use the "yyyymmdd" style


#3

You should REALLY consider using the proper data types. Storing everything as NVARCHAR(50) is just a phenomenally bad idea and will all but guarantee horrible performance and bloat the size of the data files.


#4

have you not read my Woes of trying to import a CSV file where even SQL thought VARCHAR(50) was the correct size for every column?!! :rage:

Come to think of it, doesn't ACCESS and maybe? SSMS Table Design too default to that? Crazy idea, lots will get created with that size "regardless" :frowning:

Might well be how this user's table was originally created, and quite possibly not by the O/P at all ... of course your observation are still just as relevant, sadly it seems to me to be par-for-the-course :frowning:


#5

If you have to temporarily land data into an all VARCHAR / NVARCHAR "staging table" as part of an ETL process, I get it, that's fine. Just get it out of there and into properly designed tables before you start trying to actually use the data.

No clue... It's been years since I touched MS Access and, IMO, using any of the SQL Server design wizards should be grounds for automatic termination of employment.
Note: an exception to this would be the Import Data Wizard for doing one-off imports and things of that nature.

Yea, I think we've all been faced with this one and it's always painful. It also illustrates the importance of putting some time into thinking about the data that will be loaded to your table and assigning the appropriate data types, up front, when designing new tables.


#6

Let's face it: it would not be hard for those Import Wizards to size the columns to the MAX width of the actual data ... instead of "Let's try 50 and see if anything truncates" :frowning: