SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 case
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Dani_M
Starting Member

7 Posts

Posted - 12/30/2010 :  06:28:27  Show Profile  Reply with Quote
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
22431 Posts

Posted - 12/30/2010 :  06:31:41  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/30/2010 :  06:37:21  Show Profile  Reply with Quote
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 - 12/30/2010 :  07:49:05  Show Profile  Reply with Quote
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 - 12/30/2010 :  08:00:05  Show Profile  Reply with Quote
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 - 12/30/2010 :  08:42:30  Show Profile  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 12/30/2010 :  09:21:25  Show Profile  Reply with Quote
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 - 12/30/2010 :  09:24:34  Show Profile  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 12/30/2010 :  09:35:59  Show Profile  Reply with Quote
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 - 12/30/2010 :  09:47:17  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000