T-SQL Query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Raghavan CV » T-SQL Query

44 posts, Page 1 of 5

1 2 3 4 5 »»»

T-SQL Query

Rate Topic

Display Mode

Topic Options

Author

Message

Raghav-673586

Posted Monday, April 14, 2008 8:05 PM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, May 04, 2009 4:44 AM
Points: 75, Visits: 8

Comments posted to this topic are about the item T-SQL Query

Post #484735

VALEK

Posted Monday, April 14, 2008 11:34 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:45 PM
Points: 374, Visits: 422

The explanation is incorrect.
First of all the LENGTH of the string representation of the months is EVEN, not ODD.
Second of all, the proposed method is NOT THE ONLY ONE another way.
So the answer should not start from BECAUSE, it should start from BECAUSE FOR EXAMPLE....

Another (not the last) way to extract is to JOIN the table stud_mast ON month(dob) with another table of subselect, or UNION, which contains the given numbers : 2,6,7,8,11,12.

M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM

Post #484764

Ian Lacey

Posted Tuesday, April 15, 2008 1:20 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 13, 2012 11:20 PM
Points: 131, Visits: 16

Clsoe but no cigar!
Yes, these are all the months with an even count of characters - all the other months have an odd number of characters.

Post #484798

kevriley

Posted Tuesday, April 15, 2008 1:54 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:01 PM
Points: 2,677, Visits: 2,273

There is always 101 ways to do anything in t-sql - the trick (or more specifically, the skill) is knowing which is the right way:D

That's one of the attributes that separate a DBA from a developer!

Post #484807

AC Lupu

Posted Tuesday, April 15, 2008 2:46 AM

Old Hand

Group: General Forum Members
Last Login: Monday, February 18, 2013 10:31 AM
Points: 372, Visits: 94

select * where month = 2
union all
select * where ...
...
???

Post #484820

Christian Buettner-167247

Posted Tuesday, April 15, 2008 3:06 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:12 AM
Points: 2,526, Visits: 3,620

Hello,

The proposed solution doesn't work with other languages:

SET LANGUAGE GERMAN
SELECT Datename(m, TheDate)TheMonthName, TheDate, Month(TheDate) TheMonth FROM (
	SELECT   	'19000101' AS TheDate
	UNION ALL SELECT '19000201'
	UNION ALL SELECT '19000301'
	UNION ALL SELECT '19000401'
	UNION ALL SELECT '19000501'
	UNION ALL SELECT '19000601'
	UNION ALL SELECT '19000701'
	UNION ALL SELECT '19000801'
	UNION ALL SELECT '19000901'
	UNION ALL SELECT '19001001'
	UNION ALL SELECT '19001101'
	UNION ALL SELECT '19001201'
) TheDates
WHERE LEN(Datename(m, TheDate))%2 = 0

Changed language setting to Deutsch.
TheMonthName                   TheDate  TheMonth    
------------------------------ -------- ----------- 
Januar                         19000101 1
März                           19000301 3
Juni                           19000601 6
Juli                           19000701 7
August                         19000801 8
November                       19001101 11
Dezember                       19001201 12

Another solution could be the following (the month list must be adjusted for your specific language):

SET LANGUAGE English
SELECT Datename(m, TheDate)TheMonthName, TheDate, Month(TheDate) TheMonth FROM
(SELECT   	'19000101' AS TheDate
	UNION ALL SELECT '19000201'
	UNION ALL SELECT '19000301'
	UNION ALL SELECT '19000401'
	UNION ALL SELECT '19000501'
	UNION ALL SELECT '19000601'
	UNION ALL SELECT '19000701'
	UNION ALL SELECT '19000801'
	UNION ALL SELECT '19000901'
	UNION ALL SELECT '19001001'
	UNION ALL SELECT '19001101'
	UNION ALL SELECT '19001201') TheDates
WHERE 'FebruaryJuneJulyAugustNovemberDecember' LIKE '%' + DATENAME(m, TheDate) + '%'

Results:

Changed language setting to us_english.
TheMonthName                   TheDate  TheMonth    
------------------------------ -------- ----------- 
February                       19000201 2
June                           19000601 6
July                           19000701 7
August                         19000801 8
November                       19001101 11
December                       19001201 12

Best Regards,
Chris Büttner

Post #484826

Robert-378556

Posted Tuesday, April 15, 2008 3:12 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 8:40 AM
Points: 1,078, Visits: 848

kevriley (4/15/2008)

There is always 101 ways to do anything in t-sql - the trick (or more specifically, the skill) is knowing which is the right way:D

That's one of the attributes that separate a DBA from a developer!

No, that's one of the attributes that separate a good DBA (or developer) from poor DBA.
A good database developer must know 101 ways to do a trick in sql and which best fits into particular problem regarding performance, side effects, integrity,...
and implementation capabilities of client software. Developer needs not to know about administration tasks.

Post #484828

chandrasekhar.ms

Posted Tuesday, April 15, 2008 3:35 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 29, 2011 6:56 AM
Points: 8, Visits: 53

You should not use or as per the question but you can use 'and'. So we can build a negative logic like this:

select * from std_tab
where datename(m, dob) <> 1 and datename(m, dob) <> 3 and so on...

Regards,
-Aazad.

Post #484839

Derek Dongray

Posted Tuesday, April 15, 2008 3:44 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342, Visits: 1,946

I would never rely on a trick based on a particular feature of the data which wasn't relevant to the selection. In my opinion, this is extremely bad practice.

As written, the selection data does satisfy the requirement that all the months (when displayed in English) have an even number of characters. But this is a mere coincidence. If the requirements change so that the user wants 'March' instead 'February', then someone's got to go and find all those obscure selection clauses and change them to the way they should have been in the first place, since the 'cute trick' doesn't work any more.

I, along with may others, answer YES because of the technical reason that you can always avoid an IN (or OR) by using a JOIN. For example:

select *
from stud_mast s
join ( select 2 as mth union all 
       select 6 union all 
       select 7 union all 
       select 8 union all
       select 11 union all
       select 12) req
on month(s.dob) = req.mth

Of course, an even better way would be to put the required months in a table so that a future update just requires adding, deleting or changing records and you don't have to alter the code at all!

select *
from stud_mast s
join req_month_table r
on month(s.dob) = r.mth

Derek

Post #484843

Robert-378556

Posted Tuesday, April 15, 2008 5:08 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 8:40 AM
Points: 1,078, Visits: 848

Right, but Steve once mentioned that QoD is not about best practices. Some are about bad practice, probably to spark discussions like this, which is OK for me.

If the solution was "correct" in all respects or even mentioned that what was proposed is a bad practice, there would be no discussion.

Post #484873

« Prev Topic | Next Topic »

44 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions