Re: SQL query Date Sort Problem - Database Discussion Boards

AnirbanM 23-Dec-12 18:52

3-Dec-12 18:52

Thanks, but sort is based on Date column only. As you rightly said, I always keep date as date column only. Month, Year, NoofDays etc, these columns are meant to be varchar, these are not included for sorting.

Re: SQL query Date Sort Problem

Mycroft Holmes3-Dec-12 16:24

Mycroft Holmes

3-Dec-12 16:24

There are a bunch of uglies in this table design
1. Storing numerics as character
2. Mixing varchar and nchar for no obvious reason
3. Using nchar instead of char (I would use varchar)
4. Using nchar instead of a bit (holiday)

Personally I think you should have only 3 fields in the table SNo,ToDate and Holiday. All the others should be calculated in a view!

Never underestimate the power of human stupidity
RAH

Re: SQL query Date Sort Problem

PIEBALDconsult3-Dec-12 17:28

PIEBALDconsult

3-Dec-12 17:28

10!

Re: SQL query Date Sort Problem

AnirbanM 23-Dec-12 19:07

AnirbanM 2

3-Dec-12 19:07

Thanks for your valuable feedback. I will change all nchar to varchar and holiday to bit. Moreover, I can safely remove Month and Date. But as you are suggesting, rest of the fields are calculative, then I need to make query then construct data table, make calculation and then put it into grid, otherwise, i'm simply getting all values from sql and putting straightaway into grid. So I made a trade off here, though it may not be the best way to do so.

Re: SQL query Date Sort Problem

AnirbanM 23-Dec-12 19:10

AnirbanM 2

3-Dec-12 19:10

What I can't understand and one of the puzzle is why SQL Server is not consistent and giving different results based on same query? Why out of 100 times, 97-98 times giving correct results and 1-2 times giving wrong.

Re: SQL query Date Sort Problem

Mycroft Holmes3-Dec-12 19:48

Mycroft Holmes

3-Dec-12 19:48

Please note I did not offer ANY help on that subject - it does not make any sense to me so I concentrated on the other areas hoping they may have an impact on your results.

To me a query cannot return inconsistent results!

Never underestimate the power of human stupidity
RAH

Re: SQL query Date Sort Problem

AnirbanM 24-Dec-12 2:12

AnirbanM 2

4-Dec-12 2:12

Yes, that's my core question. even if my table design is not good, even if SQL may internally converting date into float or even if i need to put 'ASC' after Order By, still question remains same. Why on earth SQL query is not consistent? Why not it's producing same results 100 out of 100 times? Is that means any internal bug in the SQL Server? Thanks.

Re: SQL query Date Sort Problem

AnirbanM 24-Dec-12 2:30

AnirbanM 2

4-Dec-12 2:30

Actually, based on the query results, within a loop creating new records and putting into a new SQL table 'Timesheet'. Now, when I see this new table 'Timesheet', it's rows are not created based on that sorting. For example, output of the query I got as 1/1/2012 to 1/31/2012 and now in the new table, inserting records like :
1/1/2012 - 51 - 12
1/2/2012 - 10 - 13
1/3/2012 - 15 - 20
..... till
1/31/2012 - 40 - 30

Now, sometimes (as I said earlier), it's creating rows in 'Timesheet' table that looks like:

1/12/2012 - 10 -20
1/13/2012 - 15-12
.. then
1/31/2012 - 40 - 30
..then
1/1/2012 - 51 - 12
1/2/2012 - 10 - 13
1/3/2012 - 15 - 20

It's not that all errors are starting from 1/12/2012, it can be 10th or 15th. So no possible pattern in these output which are giving error.

May be it will give more light into this matter. thanks.

Re: SQL query Date Sort Problem

Eddy Vluggen4-Dec-12 3:12

Eddy Vluggen

4-Dec-12 3:12

..and the table "timesheet" holds the day as a VARCHAR? That's not a bug, that's by design. Strings are sorted depending on the settings, should have been DATE type.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

Re: SQL query Date Sort Problem

AnirbanM 24-Dec-12 3:27

AnirbanM 2

4-Dec-12 3:27

No sir, the sorting is made on 'ToDate' column which is of Date type, not varchar type, as I already given my column types before.

Re: SQL query Date Sort Problem

Eddy Vluggen4-Dec-12 4:44

Eddy Vluggen

4-Dec-12 4:44

AnirbanM 2 wrote:
No sir, the sorting is made on 'ToDate' column which is of Date type, not varchar type, as I already given my column types before.

Still, it sorts like it would be. Can you create a TSQL-script that reproduces the problem? Seems I can't, my dates keep returning ordered in a way that could be expected;

SQL

DECLARE @TEST TABLE (SD DATE);
INSERT INTO @TEST (SD) VALUES ('20010101');
INSERT INTO @TEST (SD) VALUES ('20011010');
INSERT INTO @TEST (SD) VALUES ('20010310');
INSERT INTO @TEST (SD) VALUES ('20010210');
INSERT INTO @TEST (SD) VALUES ('20010301');
INSERT INTO @TEST (SD) VALUES ('20010201');
SELECT * FROM @TEST ORDER BY SD;

Results:
2001-01-01
2001-02-01
2001-02-10
2001-03-01
2001-03-10
2001-10-10

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

Re: SQL query Date Sort Problem

Michael Potter4-Dec-12 8:11

Michael Potter

4-Dec-12 8:11

I would think the error is happening after the query is returned. Test the query in SQL alone and see if you get the error you describe.

Re: SQL query Date Sort Problem

jschell4-Dec-12 8:23

jschell

4-Dec-12 8:23

AnirbanM 2 wrote:
Please suggest where is the problem, thanks to all in advance.

Some posssible reasons.
- The problem originates from some other place in the code. So nothing to do with the code that you posted. And one very obvious possibility is that there is code somewhere that is sorting based on alphabetic ("10", "11" versus "1", "2) instead of using the sort that you gave.
- You are not looking at the actual data.
- You are not running the code that you think you are.

oltp vs olap

a.fatemeh2-Dec-12 10:51

a.fatemeh

2-Dec-12 10:51

hi every one

i'm not sure if it's true to brought my question here but I want to make clear how much Datawarehouse is useful for a company.what i want is a query example in sql which is long and hard for operational database and is a piece of cake for analytical database.
I've searched a lot but I can't fine one may be I searched wrongly but I would be so thanks full if any one could help me with this as soon as possible.

Re: oltp vs olap

Mycroft Holmes2-Dec-12 11:49

Mycroft Holmes

2-Dec-12 11:49

Instead of trying to find a "query" difference try researching the uses of the different types of database, a datawharehouse is a snapshot, usually delayed from real time, of the transaction (OLPT) database that is optimised for reporting (querying).

It is not an either/or question, once you reporting queries begin impacting on your transaction processing you need an OLAP solution as well as the OLTP.

This is a very simplistic view, there are entire libraries written on this subject!

Never underestimate the power of human stupidity
RAH

Re: oltp vs olap

a.fatemeh2-Dec-12 11:57

a.fatemeh

2-Dec-12 11:57

thanks so much,i almost know what you are talking about. but I really want a query .
this is a an extract from a book :"Although many decision-support queries can be written in SQL, others either
cannot be expressed in SQL or cannot be expressed easily in SQL."

I want an example of this kind of query which is written in both.

Re: oltp vs olap

Mycroft Holmes2-Dec-12 13:28

Mycroft Holmes

2-Dec-12 13:28

a.fatemeh wrote:
I want an example of this kind of query which is written in both.

Good luck with that, even if you could get the examples it would take a lot explaining to get the context of the requirement. You might want to look into the Microsoft example databases, they have various OLTP and OLAP dbs (AdventureWorks etc), there may be such comparative examples based on them but I doubt it.

Never underestimate the power of human stupidity
RAH

Re: oltp vs olap

a.fatemeh2-Dec-12 13:41

a.fatemeh

2-Dec-12 13:41

thanks by the way.
it seems I'd hardly find one.

SQL instance name when using network group

Aamir Saleem2-Dec-12 10:32

Aamir Saleem

2-Dec-12 10:32

i have made a network group and i have installed sql in server pc so i m having problem of instance name while logging in sql management stdio in other network pc's ... plx give any solution to this prblm

Re: SQL instance name when using network group

Mycroft Holmes2-Dec-12 11:43

Mycroft Holmes

2-Dec-12 11:43

As we are not telepathic we cannot help you without knowing what the error is. Try giving us some detail on the error message you are getting!

The instance name is the name you gave the server when installing OR then name/IP of the server you installed it on.

Never underestimate the power of human stupidity
RAH

ACL vs SQL for fuzzy matching

Ben Dessau29-Nov-12 4:15

Ben Dessau

29-Nov-12 4:15

We are discussing a new project that will do lots of word matching, including some fuzzy logic in the matching.

We are deciding between using SQL and ACL for this job, and I've been told that SQL has more fuzzy logic facilities than ACL.

Anyone know about this or what the additional fuzzy facilities are?

Thanks in advance - Ben

Re: ACL vs SQL for fuzzy matching

Pete O'Hanlon29-Nov-12 5:07

Pete O'Hanlon

29-Nov-12 5:07

That's a pretty broad assumption. What variant of SQL database are you talking about here, because different engines have different features?

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

Re: ACL vs SQL for fuzzy matching

Ben Dessau29-Nov-12 6:43

Ben Dessau

29-Nov-12 6:43

Currently we're using Microsoft SQL Server 2008 but could upgrade to the latest for this project.

Re: ACL vs SQL for fuzzy matching

Pete O'Hanlon29-Nov-12 6:50

Pete O'Hanlon

29-Nov-12 6:50

At the most basic, you're talking about Soundex matching, which isn't great. You might want to consider Full Text Indexing, which will return misspelled items if they are close enough. Of course, you can always implement your own fuzzy logic inside SQL Server, using CLR functions.

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

SQL Query

berba29-Nov-12 2:22

berba

29-Nov-12 2:22

I have the following query:

select CASE when EA.Address1 <> '' and EA.Address2 <> '' and EA.Address3 <> '' then Replace(EA.Address2, ',' , ' ') + '' + Replace(EA.Address3, ',' , ' ')

else EA.Address1 end as employeeaddress

from EmployeeAdress EA

However employeeaddress should hold a maximum of 35 characters irrespective the number of characters in columns Address1, Address2, Address3.

Any advice how to do this?

Thanks
Berba Smile | :)

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.