May 20, 2010 at 7:30 am
What I'm trying to do is create a derived table of all reportable packages according to the current financial year. A reportable package would be considered as any of these 3 conditions
startDate < FYendDate AND endDate > FYstartDate
OR startDate < FYendDate AND endDate IS NULL
OR startDate > FYendDate
This is the code as it stands:
SELECT
dbo.T_Package.PackageID, dbo.T_Package.ClientID, dbo.T_Package.StartDate, dbo.T_Package.EndDate, datepart(month,getdate()) AS currMonth
FROM dbo.T_Package
WHERE
CASE
when currMonth >3 then
(((dbo.T_Package.StartDate)<=convert(datetime, convert(varchar,datepart(year,getdate())+1) + '0331',103)) AND ((dbo.T_Package.EndDate)>=convert(datetime, convert(varchar,datepart(year,getdate())) + '0401',103))
OR (((dbo.T_Package.StartDate)<=convert(datetime, convert(varchar,datepart(year,getdate())+1) + '0331',103)) AND ((dbo.T_Package.EndDate) IS NULL))
OR (((dbo.T_Package.StartDate)>convert(datetime, convert(varchar,datepart(year,getdate())+1) + '0331',103))))
ELSE
(((dbo.T_Package.StartDate)<=convert(datetime, convert(varchar,datepart(year,getdate())) + '0331',103)) AND ((dbo.T_Package.EndDate)>=convert(datetime, convert(varchar,datepart(year,getdate())-1) + '0401',103))
OR (((dbo.T_Package.StartDate)<=convert(datetime, convert(varchar,datepart(year,getdate())) + '0331',103)) AND ((dbo.T_Package.EndDate) IS NULL))
OR (((dbo.T_Package.StartDate)>convert(datetime, convert(varchar,datepart(year,getdate())) + '0331',103))))
end
The error I'm getting may just be related to syntax as it reads:
Incorrect syntax near '<' State 42000
Maybe there's an easier way to do this, I'd appreciate any help.
David
May 20, 2010 at 8:36 am
Using @variables makes the code much easier to read - for the purposes of figuring out the problem, if nothing else. The same applies to using table aliases - 'p' in the code below.
DECLARE @Date1 DATETIME, @Date2 DATETIME
SET @Date1 = convert(datetime, convert(varchar,datepart(year,getdate())+1) + '0331',103))
SET @Date2 = convert(datetime, convert(varchar,datepart(year,getdate())) + '0401',103))
SELECT
p.PackageID,
p.ClientID,
p.StartDate,
p.EndDate,
datepart(month,getdate()) AS currMonth
FROM dbo.T_Package p
WHERE
CASE
when currMonth > 3
then
(((p.StartDate) <= @Date1 AND ((p.EndDate) >= @Date2
OR (((p.StartDate) <= @Date1 AND ((p.EndDate) IS NULL))
OR (((p.StartDate) > @Date1))
ELSE
(((p.StartDate) <= @Date1 AND ((p.EndDate) >= @Date2
OR (((p.StartDate) <= @Date1 AND ((p.EndDate) IS NULL))
OR (((p.StartDate) > @Date1))
end
There are two obvious problems here. Firstly,
WHERE CASE when currMonth > 3
CurrMonth is an output - hasn't been evaluated for the WHERE clause. You would have to use
WHERE datepart(month,getdate()) > 3.
The second problem is that CASE offers alternative values - in your statement, you are expecting it to offer alternative statements.
SELECT @MyValue = CASE WHEN 1 = 1 THEN 'this' ELSE 'that' END
Finally, the two alternatives in your WHERE clause are the same 😉
When you say you are trying to build a derived table, are you suggesting that this statement is part of a larger query?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 20, 2010 at 9:05 am
Chris, thanks for the prompt reply. The derived table is actually to be a view table in a Business Objects Universe, the underlying database being SQLserver 2005. I'm fairly new to this and would the declaring of variables in the script work ok?
May 20, 2010 at 9:11 am
Hi David
I've no idea whether or not BO would permit the use of variables, however, they will certainly assist you in creating and debugging a SQL statement - after which, they could be swapped out in the SQL statement.
Looking at your code again, I reckon something like this...
DECLARE @CurrentFYend DATETIME, @CurrentFYStart DATETIME
SET @CurrentFYStart = convert(datetime, convert(varchar,datepart(year,getdate())) + '0401',103) --
SET @CurrentFYend = DATEADD(dd, -1, DATEADD(yy,1,@CurrentFYStart))
SELECT @CurrentFYStart, @CurrentFYend
SELECT
p.PackageID,
p.ClientID,
p.StartDate,
p.EndDate,
datepart(month, getdate()) AS currMonth
FROM dbo.T_Package p
WHERE (p.StartDate <= @CurrentFYend AND (p.EndDate >= @CurrentFYStart OR p.EndDate IS NULL))
OR p.StartDate > @CurrentFYend
...should work?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 20, 2010 at 9:41 am
Chris, I tried the code you suggested might work, but doesn't seem to like the word DECLARE
I used your suggestion re WHERE datepart(month,getdate()) >3 and the following code below works perfectly and returns exactly the right amount of records, but in a sense it is hard coded for all months after MARCH, how do I combine it to account for when datepart(month,getdate()) <=3
SELECT
p.packageID, p.ClientID, p.StartDate, p.EndDate, datepart(month,getdate()) AS currMonth
FROM p
WHERE datepart(month,getdate()) >3 AND
(((p.StartDate)<=convert(datetime, convert(varchar,datepart(year,getdate())+1) + '0331',103)) AND ((p.EndDate)>=convert(datetime, convert(varchar,datepart(year,getdate())) + '0401',103))
OR (((p.StartDate)<=convert(datetime, convert(varchar,datepart(year,getdate())+1) + '0331',103)) AND ((p.EndDate) IS NULL))
OR (((p.StartDate)>convert(datetime, convert(varchar,datepart(year,getdate())+1) + '0331',103))))
May 20, 2010 at 9:49 am
david.woulds (5/20/2010)
Chris, I tried the code you suggested might work, but doesn't seem to like the word DECLAREI used your suggestion re WHERE datepart(month,getdate()) >3 and the following code below works perfectly and returns exactly the right amount of records, but in a sense it is hard coded for all months after MARCH, how do I combine it to account for when datepart(month,getdate()) <=3
Assuming you want two conditions, one operating before and the other after a cut-off - what are the two conditions?
The brackets in your WHERE clause could do with a little attention 😎
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 20, 2010 at 10:05 am
Chris, well it's basically determined by the current month which comes from:
datepart(month,getdate()) as I use this the get the FY start and end dates. I just need to incorporate the code in my previous reply in 2 conditions
datepart(month,getdate()) >3
datepart(month,getdate()) <=3
I dont know if a IF datepart(month,getdate()) >3 THEN .... ELSE ..... would work
CASE statements do work as I use them in other objects
but I don't know how to combine this with the WHERE criteria
David
May 20, 2010 at 10:09 am
david.woulds (5/20/2010)
Chris, well it's basically determined by the current month which comes from:datepart(month,getdate()) as I use this the get the FY start and end dates. I just need to incorporate the code in my previous reply in 2 conditions
datepart(month,getdate()) >3
datepart(month,getdate()) <=3
I dont know if a IF datepart(month,getdate()) >3 THEN .... ELSE ..... would work
CASE statements do work as I use them in other objects
but I don't know how to combine this with the WHERE criteria
David
Hey David, if you can explain the logic, then someone here can make it work.
If condition 1 then ?
If condition 2 then ?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 20, 2010 at 10:17 am
Hey Chris I really appreciate your time here.
Basically
IF datepart(month,getdate()) >3
SELECT
p.packageID, p.ClientID, p.StartDate, p.EndDate, datepart(month,getdate()) AS currMonth
FROM p
WHERE
(((p.StartDate)<=convert(datetime, convert(varchar,datepart(year,getdate())+1) + '0331',103)) AND ((p.EndDate)>=convert(datetime, convert(varchar,datepart(year,getdate())) + '0401',103))
OR (((p.StartDate)<=convert(datetime, convert(varchar,datepart(year,getdate())+1) + '0331',103)) AND ((p.EndDate) IS NULL))
OR (((p.StartDate)>convert(datetime, convert(varchar,datepart(year,getdate())+1) + '0331',103))))
ELSE
SELECT
p.packageID, p.ClientID, p.StartDate, p.EndDate, datepart(month,getdate()) AS currMonth
FROM p
WHERE
(((p.StartDate)<=convert(datetime, convert(varchar,datepart(year,getdate())) + '0331',103)) AND ((p.EndDate)>=convert(datetime, convert(varchar,datepart(year,getdate())-1) + '0401',103))
OR (((p.StartDate)<=convert(datetime, convert(varchar,datepart(year,getdate())) + '0331',103)) AND ((p.EndDate) IS NULL))
OR (((p.StartDate)>convert(datetime, convert(varchar,datepart(year,getdate())) + '0331',103))))
David, hope this makes sense
May 21, 2010 at 3:13 am
Hi David
Could you please review the attached pseudocode and confirm that it's logically what you're looking for?
DECLARE @NextMarch DATETIME, @ThisMarch DATETIME, @ThisApril DATETIME, @LastApril DATETIME
SET @NextMarch = convert(datetime, convert(varchar,datepart(year,getdate())+1) + '0331',103)
SET @ThisMarch = convert(datetime, convert(varchar,datepart(year,getdate())) + '0331',103)
SET @ThisApril = convert(datetime, convert(varchar,datepart(year,getdate())) + '0401',103)
SET @LastApril = convert(datetime, convert(varchar,datepart(year,getdate())-1) + '0401',103)
SELECT @NextMarch, @ThisMarch
IF datepart(month,getdate()) >3
SELECT
p.packageID, p.ClientID, p.StartDate, p.EndDate, datepart(month,getdate()) AS currMonth
FROM p
WHERE
(p.StartDate <= @NextMarch AND p.EndDate >= @ThisApril)
OR (p.StartDate <= @NextMarch AND p.EndDate IS NULL)
OR (p.StartDate > @NextMarch)
ELSE
SELECT
p.packageID, p.ClientID, p.StartDate, p.EndDate, datepart(month,getdate()) AS currMonth
FROM p
WHERE
(p.StartDate <= @ThisMarch AND p.EndDate>=@LastApril)
OR (p.StartDate <= @ThisMarch AND p.EndDate IS NULL)
OR (p.StartDate > @ThisMarch)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 21, 2010 at 6:37 am
Chris, yes the logic is correct. I'm not sure whether IF...ELSE works , I know CASE statements work
May 21, 2010 at 7:19 am
Oh don't worry about that, getting the conditional to work properly is the easy bit. Getting the logic engraved in stone first is essential - and the hard bit. Now, I reckon your logic now boils down to this...can you confirm please?
SELECT
p.packageID,
p.ClientID,
p.StartDate,
p.EndDate,
DATEPART(MONTH, GETDATE()) AS currMonth
FROM p
WHERE
(p.StartDate <= @NextMarch AND (p.EndDate >= @ThisApril OR p.EndDate IS NULL))
OR (p.StartDate > @NextMarch)
ELSE
SELECT
p.packageID, p.ClientID, p.StartDate, p.EndDate, datepart(month,getdate()) AS currMonth
FROM p
WHERE
(p.StartDate <= @ThisMarch AND (p.EndDate>=@LastApril OR p.EndDate IS NULL))
OR (p.StartDate > @ThisMarch)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 21, 2010 at 7:24 am
And if it is correct, then this should do the trick:
SELECT
p.packageID,
p.ClientID,
p.StartDate,
p.EndDate,
DATEPART(MONTH, GETDATE()) AS currMonth
FROM p
WHERE
(p.StartDate <= CONVERT(datetime, CONVERT(varchar,DATEPART(year,GETDATE()) +
CASE WHEN DATEPART(MONTH, GETDATE()) > 3 THEN 1 ELSE 0 END
) + '0331',103)
AND (p.EndDate >= CONVERT(datetime, CONVERT(varchar,DATEPART(year,GETDATE()) -
CASE WHEN DATEPART(MONTH, GETDATE()) > 3 THEN 0 ELSE 1 END
) + '0401',103)
OR p.EndDate IS NULL))
OR (p.StartDate > CONVERT(datetime, CONVERT(varchar,DATEPART(year,GETDATE()) +
CASE WHEN DATEPART(MONTH, GETDATE()) > 3 THEN 1 ELSE 0 END
) + '0331',103))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply