Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 case

Author  Topic 

Dani_M
Starting Member

7 Posts

Posted - 2010-12-30 : 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

22859 Posts

Posted - 2010-12-30 : 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
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-12-30 : 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)
Go to Top of Page

Dani_M
Starting Member

7 Posts

Posted - 2010-12-30 : 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
Go to Top of Page

Dani_M
Starting Member

7 Posts

Posted - 2010-12-30 : 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.

Go to Top of Page

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-12-30 : 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?
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-12-30 : 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.
Go to Top of Page

Dani_M
Starting Member

7 Posts

Posted - 2010-12-30 : 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.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-12-30 : 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 ...
Go to Top of Page

Dani_M
Starting Member

7 Posts

Posted - 2010-12-30 : 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 .....
Go to Top of Page
   

- Advertisement -