April 15, 2008 at 6:46 am
Same as chandrasekhar.ms
The first thing that came in my mind was using truth table permutation
OR by NOT(AND) and thus satisfying the request.
April 15, 2008 at 6:48 am
And just to mention that we all have made bad decisions in our past.
Some might not admit it, but thats how we function. We're not robots.
Best Regards,
Chris Büttner
April 15, 2008 at 7:21 am
Rob Goddard (4/15/2008)
Correct. The developer will usually get the better way done, while a DBA will usually just say it can't be done.
Ouch, although I think that is almost true in a lot of circumstances:
Just the other day a developer's 'solution' entailed enabling xp_cmdshell, switching authentication to mixed mode and creating a sql user in the sysadmin role just to programmatically copy log files from a SQL machine. I said that can't be done! :hehe:
LOL
(My solution would be SSIS, if I had to use something SQLy for it.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2008 at 7:23 am
How does it satisfy a requirement if Feb can have an even or odd number of days, depending if it is a leap year? This solution is obviously not correct.
April 15, 2008 at 7:26 am
John Portnov (4/15/2008)
How does it satisfy a requirement if Feb can have an even or odd number of days, depending if it is a leap year? This solution is obviously not correct.
The solution is counting the number of letters in the name of the month, not the days in the month!
The solution itself is correct, just not very sensible. 🙂
Derek
April 15, 2008 at 7:44 am
Although the explanation is incorrect, today's question does achieve the objective of the QOD. It makes you think and generates discussion. Sometimes the QOD and the subsequent discussion have bad queries, but the group is always on top of it. The group helps explain what is wrong with the code and better ways to do things.
As in this case, there are many ways to do it, but is there a good way? That is what a DBA is suppose to determine. Developers want the data fast and don't usually think about or know the best practices. If a developer came to me with this query or an alternative solution like
Select * from stud_mast where datename(m,dob) like '%u%' and month(dob) > 1
union all
Select * from stud_mast where month(dob) > 10
I would have many questions for him/her. This may be something that is a one time deal; it could be that it will be used all the time; etc.... Based on the answers to my questions, I would help the developer find the best solution and the best database from which to run it.
Just looking at the Select * portion of the query sends chills up the DBA's spine.
Q
Please take a number. Now serving emergency 1,203,894
April 15, 2008 at 7:47 am
I agree about the 'trick' of data being unbelievably bad practice.
Whether you use IN or join to a derived table of numbers, they're essentially the same solution, no? (I didn't inspect a plan for each)
I question the use of a function on the left side of an equality in the join/where clause making you miss an index. I did like the suggestion of a table containing requested date ranges.
Select *
from
stud_mast sm
join
daterange dr
on sm.dob >= dr.rangeBegin and sm.dob < dr.rangeEnd
Assuming there exists a foreign key to some header that logically parents the records in the daterange table, we could add a where clause that chooses the range by it's parent id (allowing the user to select named ranges like, "Even Months" or "Summer Months" or "Second Sunday of each Month" or whatever)
April 15, 2008 at 8:15 am
Hmm last time I checked the calendar here in the US, June and July have 4 characters, and 4 %2 =0 not 1.
April 15, 2008 at 9:02 am
This will work too - we will parse the string with the month and put it into the temp table. This way you can receive an input from the front end in the form of any comma-separated list of months. It would be better to do it as SP, but I am too lazy. I used HumanResources.Employee table from AdventureWorks database for example. The parse string was posted by me earlier in the topic
http://www.sqlservercentral.com/Forums/Topic446607-146-1.aspx#bm446632
Create Table ##TempTable (Id nvarchar(5))
declare @Ids nvarchar(4000)
declare @tempstr nvarchar(4000)
select @Ids = '2,6,7,8,11,12'
select @tempstr = @Ids
WHILE len(@tempstr) > 0
BEGIN
--Printing the current temp string and the position of the first comma in it
--Print @tempstr
--Print patindex('%,%',@tempstr)
-- Inserting the string before the first comma
Insert ##TempTable(Id)
values(substring(@tempstr,1,patindex('%,%',@tempstr)-1))
--resetting the temporary string to start after the first comma in the previous temp string
select @tempstr = substring(@tempstr,patindex('%,%',@tempstr)+1,Len(@tempstr))
--Checking if there is no more commas
IF patindex('%,%',@tempstr)<=0
Begin
--Inserting the last ID
Insert ##TempTable(Id)
values(@tempstr)
BREAK
End
ELSE
CONTINUE
END
-- Getting the Employee Data
select *
from HumanResources.Employee e
join ##TempTable t
on month(e.BirthDate)= t.Id
drop table ##TempTable
Regards,Yelena Varsha
April 15, 2008 at 9:13 am
That's one of the attributes that separate a DBA from a developer!
I was a developer (and de-facto DBA) for many years before becoming a full-time DBA. I still work with some very talented SQL developers, some of whom I have tried hard to persuade to change careers to a DBA, most of them like what they do, are very good at it and don't want to make the switch. Being a DBA does not make you 'better' than a developer, we are all different parts of the same organization with our own skills and responsibilities.
April 15, 2008 at 9:35 am
Simon Facer (4/15/2008)
Being a DBA does not make you 'better' than a developer, we are all different parts of the same organization with our own skills and responsibilities.
The inference that a DBA is better than a developer is your own! I was merely stating that they are different skill sets
And I actually meant developers whose main skill isn't SQL - as in my world SQL developers are known as development DBAs.
April 15, 2008 at 10:42 am
I started to agree with you, Mike Dougherty, about a separate table of date ranges, applauding your generalization of the query to allow configurable ranges and not just calendar months, but then realized that the table's maintenance would be cumbersome. To satisfy the requirement of finding students with birthdays in any of the specified ranges, you'd have to have six entries for each possible birth year, and then have to make sure either that entries that don't make sense now are included anyway or added later so the query still works twenty years from now.
I see how your solution would use the stored dob without any parsing function and would allow for an index, but wonder if the maintenance requirement would be worth while for the added efficiency of the query. How much computing time do month() or day() take? Is an index effective if, assuming random distribution of birthdays, it only just splits the data in halves anyway?
April 15, 2008 at 3:59 pm
john.arnott (4/15/2008)
[snip] the table's maintenance would be cumbersome. [snip] How much computing time do month() or day() take? Is an index effective if, assuming random distribution of birthdays, it only just splits the data in halves anyway?
You're right about the maintenance.
If you were really going to be managing the data this way, I would suggest having the date stored in multiple pieces: dob_year, dob_month, dob_day.
I'm not sure the performance penalty of using a function on the left side of your comparison; I have only read that it is bad - for reasons that make enough sense that I simply avoid it when possible.
April 15, 2008 at 4:15 pm
The UNION answer was the first one that popped into my mind as well. I'd never really thought of using the number of characters in the English name of a month and hope that I'd never have to fabricate something like that. 🙂 I was also trying to think of a solution that would work if we wanted to add September to the mix.
Shortly after I started to read these solutions, I realized that the list could also be parsed out into a temp table and we could then join against the temp table to achieve the end goal.
While I agree that the given solution was not really a great one, it was a creative way to achieve the goal that most of us didn't think of right away (probably because it doesn't scale or even port outside of English).
-Pete
April 16, 2008 at 7:10 am
I've been using case a lot in selectively aggregating to optimize (and simplify) complex reports so the first thing that came to mind for me was something similar to this :
select
getdate()
where
case month(getdate())
when 1 then 0
when 2 then 1
when 3 then 0
when 4 then 0
when 5 then 0
when 6 then 1
when 7 then 1
when 8 then 1
when 9 then 0
when 10 then 0
when 11 then 1
when 12 then 1
else 0
end = 1
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply