Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

T-SQL Query Expand / Collapse
Author
Message
Posted Monday, April 14, 2008 8:05 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 4, 2009 4:44 AM
Points: 75, Visits: 8
Comments posted to this topic are about the item T-SQL Query
Post #484735
Posted Monday, April 14, 2008 11:34 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 10:06 PM
Points: 374, Visits: 428
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
Posted Tuesday, April 15, 2008 1:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Tuesday, April 15, 2008 1:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:34 AM
Points: 2,684, Visits: 2,432
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
Posted Tuesday, April 15, 2008 2:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 10, 2014 5:02 PM
Points: 380, Visits: 99
select * where month = 2
union all
select * where ...
...
???
Post #484820
Posted Tuesday, April 15, 2008 3:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:45 AM
Points: 2,826, Visits: 3,866
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
Posted Tuesday, April 15, 2008 3:12 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 10, 2014 6:56 AM
Points: 1,176, Visits: 878
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
Posted Tuesday, April 15, 2008 3:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, April 15, 2008 3:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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
Posted Tuesday, April 15, 2008 5:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 10, 2014 6:56 AM
Points: 1,176, Visits: 878
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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse