Author |
Topic |
Dani_M
Starting Member
7 Posts |
Posted - 2010-12-30 : 06:28:27
|
I work with SQL Server 2000I am very beginner in SPI need to write a SP with 2 parameters: @INITDATA, @Q, something like:CREATE PROCEDURE [dbo].[Q_TEST] @INITDATA datetimeASDECLARE @c_result INTDECLARE @Q INTSELECTCASE @QWHEN 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)ENDSELECTCustomers.AccountNumber CAccNo,Company.JobNumber,Jobs.ReportingDate,…FROM Jobs …..SELECT @c_result=@@ERRORRETURN @c_resultGOPlease help |
|
Kristen
Test
22859 Posts |
Posted - 2010-12-30 : 06:31:41
|
This perhaps?CREATE PROCEDURE [dbo].[Q_TEST] @INITDATA datetime, @Q INTASDECLARE @c_result INTDECLARE @Q INTSELECTCustomers.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=@@ERRORRETURN @c_resultGO EDIT: Added the OR I overlooked first time around |
|
|
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) |
|
|
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 |
|
|
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. |
|
|
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 2000I am very beginner in SPI need to write a SP with 2 parameters: @INITDATA, @Q, something like:CREATE PROCEDURE [dbo].[Q_TEST] @INITDATA datetimeASDECLARE @c_result INTDECLARE @Q INTSELECTCASE @QWHEN 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)ENDPlease 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
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. |
|
|
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 ofWHEN 1 THEN @INITDATA< Jobs.ReportingDate AND Jobs.ReportingDate)<=DATEADD(WW,13,@INITDATA)The parantesis ) had to go. |
|
|
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 sayCASE 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 - 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 ..... |
|
|
|
|
|