| Author |
 Topic  |
|
|
Dani_M
Starting Member
7 Posts |
 Posted - 12/30/2010 : 06:28:27
|
I work with SQL Server 2000
I am very beginner in SP
I need to write a SP with 2 parameters: @INITDATA, @Q, something like:
CREATE PROCEDURE [dbo].[Q_TEST]
@INITDATA datetime
AS
DECLARE @c_result INT
DECLARE @Q INT
SELECT
CASE @Q
WHEN 1 THEN @INITDATA< Jobs.ReportingDate AND Jobs.ReportingDate)<=DATEADD(WW,13,@INITDATA)
WHEN 2 THEN DATEADD(WW,13,@INITDATA)< Jobs.ReportingDate AND Jobs.ReportingDate<=DATEADD(WW,26,@INITDATA)
WHEN 3 THEN DATEADD(WW,26,@INITDATA)< Jobs.ReportingDate AND Jobs.ReportingDate<=DATEADD(WW,39,@INITDATA)
WHEN 4 THEN DATEADD(WW,39,@INITDATA)< Jobs.ReportingDate AND Jobs.ReportingDate<=DATEADD(WW,52,@INITDATA)
END
SELECT
Customers.AccountNumber CAccNo,
Company.JobNumber,
Jobs.ReportingDate,
…
FROM
Jobs …..
SELECT @c_result=@@ERROR
RETURN @c_result
GO
Please help
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 12/30/2010 : 06:31:41
|
This perhaps?
CREATE PROCEDURE [dbo].[Q_TEST]
@INITDATA datetime,
@Q INT
AS
DECLARE @c_result INT
DECLARE @Q INT
SELECT
Customers.AccountNumber CAccNo,
Company.JobNumber,
Jobs.ReportingDate,
…
FROM
Jobs …..
WHERE
(@Q = 1 AND @INITDATA< Jobs.ReportingDate AND Jobs.ReportingDate)<=DATEADD(WW,13,@INITDATA))
OR (@Q = 2 AND DATEADD(WW,13,@INITDATA)< Jobs.ReportingDate AND Jobs.ReportingDate<=DATEADD(WW,26,@INITDATA))
OR (@Q = 3 AND DATEADD(WW,26,@INITDATA)< Jobs.ReportingDate AND Jobs.ReportingDate<=DATEADD(WW,39,@INITDATA))
OR (@Q = 4 AND DATEADD(WW,39,@INITDATA)< Jobs.ReportingDate AND Jobs.ReportingDate<=DATEADD(WW,52,@INITDATA))
SELECT @c_result=@@ERROR
RETURN @c_result
GO
EDIT: Added the OR I overlooked first time around  |
Edited by - Kristen on 12/30/2010 09:19:08 |
 |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 12/30/2010 : 06:37:21
|
P.S. I recommend you use "week" instead of "WW" for the DATEADD parameter - makes the code easier to read.
For example is DATEADD(M, 1, @INITDATA) adding one minute? month? millisecond? microsecond?
I think it is much easier to read as:
DATEADD(minute, 1, @INITDATA)
DATEADD(month, 1, @INITDATA)
DATEADD(millisecond, 1, @INITDATA)
DATEADD(microsecond, 1, @INITDATA) |
|
|
|
Dani_M
Starting Member
7 Posts |
Posted - 12/30/2010 : 07:49:05
|
| I get an error or I said it wrong, sorry. I have to built a report and I have to input parameters @Q: 1 or 2 or 3 or 4 and the @INITDATA |
|
|
|
Dani_M
Starting Member
7 Posts |
Posted - 12/30/2010 : 08:00:05
|
I put:
WHERE
(@Q = 1 AND @INITDATA< Jobs.ReportingDate AND Jobs.ReportingDate<=DATEADD(WW,13,@INITDATA)) or
(@Q = 2 AND DATEADD(WW,13,@INITDATA)< Jobs.ReportingDate AND Jobs.ReportingDate<=DATEADD(WW,26,@INITDATA)) or
(@Q = 3 AND DATEADD(WW,26,@INITDATA)< Jobs.ReportingDate AND Jobs.ReportingDate<=DATEADD(WW,39,@INITDATA)) or
(@Q = 4 AND DATEADD(WW,39,@INITDATA)< Jobs.ReportingDate AND Jobs.ReportingDate<=DATEADD(WW,52,@INITDATA))
and it's working!!
Thank you very much.
|
|
|
|
Jim Beam
Posting Yak Master
137 Posts |
Posted - 12/30/2010 : 08:42:30
|
quote:
Originally posted by Dani_M
I work with SQL Server 2000
I am very beginner in SP
I need to write a SP with 2 parameters: @INITDATA, @Q, something like:
CREATE PROCEDURE [dbo].[Q_TEST]
@INITDATA datetime
AS
DECLARE @c_result INT
DECLARE @Q INT
SELECT
CASE @Q
WHEN 1 THEN @INITDATA< Jobs.ReportingDate AND Jobs.ReportingDate)<=DATEADD(WW,13,@INITDATA)WHEN 2 THEN DATEADD(WW,13,@INITDATA)< Jobs.ReportingDate AND Jobs.ReportingDate<=DATEADD(WW,26,@INITDATA)
WHEN 3 THEN DATEADD(WW,26,@INITDATA)< Jobs.ReportingDate AND Jobs.ReportingDate<=DATEADD(WW,39,@INITDATA)
WHEN 4 THEN DATEADD(WW,39,@INITDATA)< Jobs.ReportingDate AND Jobs.ReportingDate<=DATEADD(WW,52,@INITDATA)
END
Please help
Can someone take me through the first Case condition in red? I'm completely lost with it, does it set @INITDATA at less than the ReportingDate, and the ReportingDate to less than or equal to whatever date is concocted by @INITDATA ?
And DateAdd, is this a statement that is used to joing dateparts together? |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 12/30/2010 : 09:21:25
|
Jim that code won't work, as it stands. I think it was just the O/P's first-attempt at getting the answer
"DateAdd, is this a statement that is used to joing dateparts together?"
DATEADD will add (or subtract) an OFFSET to a DATE. The "Offset" can be Years, Months, Days, Hours, etc.
DATEADD(OffsetType, AmountOfOffset, DateToAddTo)
Use negative "AmountOfOffset" to Subtract, rather than Add. |
|
|
|
Dani_M
Starting Member
7 Posts |
Posted - 12/30/2010 : 09:24:34
|
I'm not sure that I understood right, but this is my mistake:
WHEN 1 THEN @INITDATA< Jobs.ReportingDate AND Jobs.ReportingDate<=DATEADD(WW,13,@INITDATA)
instead of
WHEN 1 THEN @INITDATA< Jobs.ReportingDate AND Jobs.ReportingDate)<=DATEADD(WW,13,@INITDATA)
The parantesis ) had to go.
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 12/30/2010 : 09:35:59
|
That code won't work (well, it might - but if it does it will only give you a True / False result as to whether the Equality test succeeded or failed), but I suspect it is not the solution you were looking for.
You could say
CASE WHEN @Q = 1 AND @INITDATA< Jobs.ReportingDate AND Jobs.ReportingDate)<=DATEADD(WW,13,@INITDATA) THEN ... some value ...
WHEN @Q = 2 AND ... THEN ... some other value ...
|
|
|
|
Dani_M
Starting Member
7 Posts |
Posted - 12/30/2010 : 09:47:17
|
We have a client with a particular calendar; for example year 2005 begins with 2 July; that's why I needed @INITDATA (I should say @INITDATE ). Each year begins with another date (2010 begins with 3 July). Then we need the quarters, @Q, first it's 13 weeks, from @INITDATA to @INITDATA+13 weeks, second .....
|
|
|
| |
Topic |
|
|
|
case
