Need help in sorting data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help in sorting data

Author

Topic

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-10-02 : 23:19:48

I have a table in which there is no ID

Structure of my table is:

[Vehicle_ID] (varchar (4)),
[Code] (varchar (4)),
[TimeRx] (varchar (9)),
[DateRx] (varchar (9)),
[FCN] (varchar (4)),
[Status] (varchar (4)),
[TS] (varchar (4))

in which VehicleID is a custom ID number saved by user.

The sample data is like this:

Vehicle_ID Code FCN Status TS TimeRx DateRx
0236 0001 NST 001 @12:16 12:16:55 08-SEP-11
0001 0236 ACK NST 12:16:55 08-SEP-11
0236 0001 NST 002 @12:16 12:17:10 08-SEP-11
0001 0236 ACK NST 12:17:10 08-SEP-11

I want to sort data based on DateTX and TimeRX in Desc Order.

I have used this command:
SELECT TOP 50 [Vehicle_ID], [Code], [TimeRx], [DateRx], [FCN], [Status], [TS] FROM [Status] order by DateRx desc, TimeRx desc

The problem is it displays data till TimeRX is 23:59, after that it does not show data for next day becoz TimeRX goes like 00:30...and so on... so according to the sql command 23 is higher than 00 so it stops displaying data after 24 hours and keep data line with 23:00 time on top.

Please help me with a solution...
I already have data in the table - Is it possible to change the TimeRX and TS field to datetime ? if yes, will datetime field will accept data like "12:17:10" or "@12:16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-02 : 23:22:41

quote:
][DateRx] (varchar (4)),

the DateRx is a varchar(4) ? how do you store the date in there ?

KH
[spoiler]Time is always against us[/spoiler]

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-10-02 : 23:30:30

These is a device that is connected to the PC so I have developed an windows application to save that incoming data in SQL Server table.

Data can't be changed in anyway because it is coming from a device.

I am sorry it was my mistake in above post, it is varchar(9) - i just updated my post above.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-02 : 23:38:27

quote:
Is it possible to change the TimeRX and TS field to datetime ? if yes, will datetime field will accept data like "12:17:10" or "@12:16"

Yes. you can. Since you have the date & time in 2 separate field, you can create a column DateTimeRX and combine the date & time together. If the format for the DateRx is in YYYYMMDD you can simply


update table set DateTimeRX = DateRx + ' ' + TimeRX

If it is not, just convert it to YYYYMMDD format.

Note : you will need to change your application to use the new column instead of the existing 2 column.

KH
[spoiler]Time is always against us[/spoiler]

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-10-02 : 23:47:04

thanks for the reply.

Like I mentioned above, I cant make any changes in the data. That's how the data is coming from device in 7 fields so that's why I have created 7 individual columns in the table to get that data for 7 fields.

So what would you recommend ? changing TimeRX or TS datatype to datetime ? will it store the value that I mentioned above ?

or adding a new column called "ID" which will be incremented with each new data row and sorting data with the ID ?

The only strange thing I see it that, in the table data, why today's data is saved in the middle of the table and data with previous date is at the bottom ?
See here: [url]http://www.strivingprogrammers.com/myfiles/statusdatatable.JPG[/url]
So I doubt if I go with adding a new field ID, this strange thing might create problem ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-02 : 23:56:34

quote:
or adding a new column called "ID" which will be incremented with each new data row and sorting data with the ID ?

Provided that the data is always comes in sequentially and you don't have out of sync data. For whatever reason, the data is does not comes in sequentially or you need to patch some data etc, your ID will be mess up and your query will not work.

alternatively you can create a COMPUTE COLUMN


ALTER TABLE <table> ADD DateTimeRX AS convert(datetime, DateRX + ' ' + TimeRX)

assuming that you date column is always in DD-MMM-YY format

KH
[spoiler]Time is always against us[/spoiler]

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-10-03 : 00:09:22

quote:
Originally posted by khtan

quote:
or adding a new column called "ID" which will be incremented with each new data row and sorting data with the ID ?

Provided that the data is always comes in sequentially and you don't have out of sync data. For whatever reason, the data is does not comes in sequentially or you need to patch some data etc, your ID will be mess up and your query will not work.

alternatively you can create a COMPUTE COLUMN

ALTER TABLE <table> ADD DateTimeRX AS convert(datetime, DateRX + ' ' + TimeRX)

assuming that you date column is always in DD-MMM-YY format

KH
[spoiler]Time is always against us[/spoiler]

Yes, you are correct DateRX is always DD-MMM-YY

"ALTER TABLE <table> ADD DateTimeRX AS convert(datetime, DateRX + ' ' + TimeRX)"

The above SQL command will create a column named as "DateTimeRX" which will be the addition of DateRX + TimeRX, am I correct ?

I understand for all the current data in the table, new column "DateTimeRX" will be created with data in it (based on other 2 columns) - So for new data coming from the device in future, this new field will always take the value from those 2 columns ?

I mean for new future data that will be coming, how data will go to this new field "DateTimeRX" because right now I will be running a manual query to put data in it but it is not feasible to run query manually everytime to save data in this field.

Hope you understand what I am asking ? Please correct me if I am wrong...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-03 : 00:42:00

quote:
The above SQL command will create a column named as "DateTimeRX" which will be the addition of DateRX + TimeRX, am I correct ?

I understand for all the current data in the table, new column "DateTimeRX" will be created with data in it (based on other 2 columns) - So for new data coming from the device in future, this new field will always take the value from those 2 columns ?

I mean for new future data that will be coming, how data will go to this new field "DateTimeRX" because right now I will be running a manual query to put data in it but it is not feasible to run query manually everytime to save data in this field.

yes. correct. The DateTimeRX is a computed column. It is not a physical column. You do not need to update that column with value. The value of DateTimeRX is computed from DateRX & TimeRX

KH
[spoiler]Time is always against us[/spoiler]

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-10-03 : 00:53:28

1) So I guess everytime when data will come from device and will get saved into database, this computer field will take value from DateRX and TimeRX, field, am I correct ?

2) so to sort the data I will be using this command ?

SELECT TOP 50 [Vehicle_ID], [Code], [TimeRx], [DateRx], [FCN], [Status], [TS] FROM [Status] order by DateTimeRX desc ?

3) In future if I want to delete this computed column, how to do that ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-03 : 00:56:08

1) Yes

2) yes

3) alter table <table> drop column <column name>

KH
[spoiler]Time is always against us[/spoiler]

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-10-03 : 00:58:10

Thank you very much for your help.

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-10-03 : 01:05:29

Tried the query. The column was created but when I select ALL rows in SSMS to see data
I get an error message:

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

FYI:
I have also notices that some values don't come up with select * from tablename.

Just to let you know, there are few rows in the data in which there is blank/nothing in DateTX and TimeTX.

Is this the reason of the above error message ? or the error is coming because we are trying to convert varchar to datetime ?

Any Solution ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-03 : 01:21:39

blank as in empty string or NULL ?

Actually either one of the column contains NULL or empty string '' will not cause that error. It must be some other row with bad date or time.

quote:
Is this the reason of the above error message ? or the error is coming because we are trying to convert varchar to datetime ?

Yes. Correct.

KH
[spoiler]Time is always against us[/spoiler]

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-10-03 : 01:30:20

yes, blank as in empty string

and nothing can't be done for bad time and date because it is being send by the device. However the date and time is being send by the device, it gets saved in the database.

Few time device send "blank as in empty string " for DateRX and TimeRX.

After adding the column "DateTimeRX", When I select ALL ROWS, why I get only those rows for which there is value in "DateTimeRX" ? Before adding this column, I used to get all rows.

Any solution for this ?

Along with that, any solution for the error msg ? or we can ignore it ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-03 : 02:46:46

you can't ignore the error. You can use CASE WHEN . . . to check for valid date before converting it and return NULL if it is not valid

KH
[spoiler]Time is always against us[/spoiler]

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-10-03 : 07:43:06

Could you please show me how ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-03 : 08:29:28

[code]
alter table tt add DateTimeRX as case when isdate(DateRX + ' ' + TimeRX) = 1
then convert(datetime, DateRX + ' ' + TimeRX)
else NULL
end
[/code]

KH
[spoiler]Time is always against us[/spoiler]

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-10-03 : 09:54:37

Thank you - that worked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources