Instead of using a parameter twice is it possible using the parameter once?
-
I have this SQL, giving me the first monday, one, or sometimes two weeks before the start of the month.
DECLARE @YYYYMM01 CHAR(8) SET @YYYYMM01 = '20060501'
SELECT Day1 FROM
(SELECT DATEADD(dd, -DATEPART(dw, @YYYYMM01)-5, @YYYYMM01) AS Day1)Tmp
It gives perfectly what I want (20060424).
But is it possible to get the same result with the parameter used once?
Or even to get the same result with the parameter used once in a WHERE clause at the end?
Something like:
SELECT Day1 FROM ......... WHERE TheDate = @YYYYMM01
-
Maybe, but the use of the "double parameter" isn't really going to slow anything down here... I am confused by your posting, though... you say "giving me the first Monday"... of what? Do you mean the next available Monday or the first Monday of the Month? AND, it appears from you example that it gave you the PREVIOUS Monday, not some Monday in the future. So, please clarify... compared to a given date, which Monday are you actually looking for?
Also, why the double SELECT? Why not just do it this way?
SELECT DATEADD(dd, -DATEPART(dw, @YYYYMM01)-5, @YYYYMM01) AS Day1
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
Some background information for you:
I need to show the days of 6 weeks on the internet.
Ie: When someone chooses the month september 2005 I want to show him/her a table like:
22 23 24 25 26 27 28
29 30 31 01 02 03 04
05 06 07 08 09 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 01 02
The startdate that you see here is 22 aug when someone chooses september 2005.
The SQL is for determining the date 22 aug (the first monday 2 weeks before the 2005-09-01 (sept 1).
There is someone else who will use my sql, and he says that he wants just one place for filling in the date parameter (ie '20050901'). Thus not for reasons of speed.
The SELECT around the real SELECT is because it simulates the way it is used.
The SELECT part with
(SELECT DATEADD(dd, -DATEPART(dw, @YYYYMM01)-5, @YYYYMM01) AS Day1)
will be used in conjunction with other SQL.
Could you help me with wrapping some sql around the part with 2 parameters, so that I can let this sql function with just one place for filling in the parameter?
-
Would a user defined function work for you and/or the programmer?
SELECT FIRST_FRIDAY(@YYYYMM01)
-
A UDF would be a solution, I think.
But, it should be possible without UDF.
I know that I once had a similar problem and had a solution based on a INNER JOIN or a SubQuery.
But I can't remember how I did it, and I was hoping someone could give me a clue.
-
Oh! Here the parameter is mentioned only once. This seems rather trivial and rather unnecessary though!
SELECT DATEADD(dd, -DATEPART(dw, X.ONEDATE)-5, X.ONEDATE) AS Day1
FROM (SELECT @YYYYMM01 AS ONEDATE) X
What are the reasons for needing to mention the parameter only once? I really can't think of any reason.
-
Peter, thanks.
You asked: What are the reasons for needing to mention the parameter only once
The answer is not a real answer:
"someone else (...) says that he wants just one place for filling in the date parameter"
And as the other one is important, I try to find a way of doing it (and you help me with it)
-
If the other guy's app just writes out SQL text to SQL Server (using ADO command, ADO.NET command, OSQL, or even crystal reports, etc) you could simply have (as per your code)
DECLARE @YYYYMM01 CHAR(8) SET @YYYYMM01 = '20060501'
SELECT Day1 FROM
(SELECT DATEADD(dd, -DATEPART(dw, @YYYYMM01)-5, @YYYYMM01) AS Day1)Tmp
And he changes the '20060501' bit and leaves the rest the same... That shouldn't be too hard?
-
Ian,
The DECLARE is just for giving the reader of this tread a simple way for testing the code. In the real world the code is part of a big SQL statement with a few other parameters. I just showed the "problem-part" of the code. The filling in of the parameter comes from a part of javascript code. Thanks anyway.
-
Try this:
SELECT Day1 FROM
(SELECT DATEADD(dd, -DATEPART(dw, t1.X)-5, t1.X) as Day1
From (Select '20050102' as X) as t1) as Y
BAM!
(Sorry, but I just got done watching Emeril)
jg
-
Jeff, thank you, that is what I needed.
But what is Emeril and why BAM?
Is it a TV program?
Btw I'am not realy expecting a answer (living in the Netherlands I probably would never see Emeril)
-
Henk,
Peter K also posted essentially the same thing, which I failed to notice before.
Emeril is the host of a cooking show in the US. He says "BAM!" may times during a show, to emphasize just how great he thinks he is.
I, on the other hand, was just goofing around.
Now that you have your answer, I believe that it would be good to mention that the solution presented is not the best way to do things.
You said: "There is someone else who will use my sql, and he says that he wants just one place for filling in the date parameter (ie '20050901'). Thus not for reasons of speed."
A better way would be to create a stored procedure that takes the date as a parameter and returns a result set. This way, the "someone else" just needs to call the sproc from their application. There are numerous reasons why this is the best approach. There is pretty much a consensus on this approach as being a "best practice" in the field, but I'll make mention of a few benefits:
This will normally be faster, as the entire sproc's execution plan will usually be compiled ahead of time.
This allows you to define "formal" access methods to the database from applications, resulting in a potentially better security model, AND it aids maintainability going forward.
The other benefit, which is highly subjective, is that it keeps the web developers from writing bad SQL. That may not be helpful, if they are good at SQL, but that is not always the case.
HTH jg
-
Jef, I know what you said about using a SP. But at this stage the Javascript is constantly changing and so does the SQL. So a SP would constantly have to be updated. That's the reason that for now we don't use SP's. Maybe later, and then for security reasons.
And thanks for your contribution
-
Umm... if this is something that is in development, then changes to the code involved is expected to a certain point.
If you're considering using procedures 'later', you really should start using them ASAP. Using procedures is also a means of design, and that choice also affects other code (like the javascript)
You're most likely not going to be able to later 'switch' from inline SQL code to calling procedures - this choice is nothing that you can just 'plug into' an existing architecture in an organized way.
I'd suggest that if you're thinking about using procedures, start using them now, not later.
/Kenneth
-
Quote:
I'd suggest that if you're thinking about using procedures, start using them now, not later.
OK, I'll keep this in mind
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply