February 29, 2012 at 5:06 am
What does @Yearmonth -90 mean ?
Also date >=190000
February 29, 2012 at 5:12 am
It depends on the context.
February 29, 2012 at 5:14 am
WHERE (RTRIM(YEAR(A.DOCDATE)) + RIGHT('0' + CONVERT(VARCHAR, MONTH(A.DOCDATE)), 2) <= @YEARMONTH) AND
(RTRIM(YEAR(A.DOCDATE)) + RIGHT('0' + CONVERT(VARCHAR, MONTH(A.DOCDATE)), 2) >= (CASE WHEN SUBSTRING(@YEARMONTH, 5,
2) IN ('01', '02', '03', '04', '05') THEN @YEARMONTH - 93 ELSE @YEARMONTH - 5 END)) AND (C.ITMCLSDC IN (@BRAND)) AND
(B.ITEMNMBR NOT LIKE 'Mkt%') AND (RTRIM(D.SLTERDSC) IN (@SALSTERR)) AND (RTRIM(F.CLASDSCR) IN (@CUSTCLAS))
February 29, 2012 at 5:19 am
olivia.forde (2/29/2012)
What does @Yearmonth -90 mean ?
If @Yearmonth is of the DATETIME datatype, that will subtract 90 days
Also date >=190000
Hard to tell without knowing the datatype but it looks like someone is trying to compare against the 24 hour time version of 7PM..
WHERE (RTRIM(YEAR(A.DOCDATE)) + RIGHT('0' + CONVERT(VARCHAR, MONTH(A.DOCDATE)), 2) <= @YEARMONTH) AND
(RTRIM(YEAR(A.DOCDATE)) + RIGHT('0' + CONVERT(VARCHAR, MONTH(A.DOCDATE)), 2) >= (CASE WHEN SUBSTRING(@YEARMONTH, 5,
2) IN ('01', '02', '03', '04', '05') THEN @YEARMONTH - 93 ELSE @YEARMONTH - 5 END)) AND (C.ITMCLSDC IN (@BRAND)) AND
(B.ITEMNMBR NOT LIKE 'Mkt%') AND (RTRIM(D.SLTERDSC) IN (@SALSTERR)) AND (RTRIM(F.CLASDSCR) IN (@CUSTCLAS))
That is a huge mistake because it will never be able to do an INDEX SEEK due to DocDate and other columns being in a formula. Maybe it's just my eyes, but I don't see your 190000 example in ther.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 29, 2012 at 5:25 am
Hi
It is in this piece (this one is -94 and >=190000
WHERE (RTRIM(YEAR(A.DOCDATE)) + RIGHT('0' + CONVERT(VARCHAR, MONTH(A.DOCDATE)), 2) <= (CASE WHEN SUBSTRING(@YEARMONTH, 5, 2) IN ('01',
'02', '03', '04', '05', '06') THEN @YEARMONTH - 94 ELSE @YEARMONTH - 6 END)) AND (RTRIM(YEAR(A.DOCDATE)) + RIGHT('0' + CONVERT(VARCHAR,
MONTH(A.DOCDATE)), 2) >= 190000) AND (C.ITMCLSDC IN (@BRAND)) AND (B.ITEMNMBR NOT
February 29, 2012 at 5:29 am
You've not shown where @Yearmonth is declared or assigned. Nor have you shown DDL for the tables involved.
WHERE (RTRIM(YEAR(A.DOCDATE)) + RIGHT('0' + CONVERT(VARCHAR, MONTH(A.DOCDATE)), 2) <= @YEARMONTH) AND
(RTRIM(YEAR(A.DOCDATE)) + RIGHT('0' + CONVERT(VARCHAR, MONTH(A.DOCDATE)), 2) >= (CASE WHEN SUBSTRING(@YEARMONTH, 5, 2) IN ('01', '02', '03', '04', '05')
THEN @YEARMONTH - 93
ELSE @YEARMONTH - 5 END)) AND
(C.ITMCLSDC IN (@BRAND)) AND
(B.ITEMNMBR NOT LIKE 'Mkt%') AND
(RTRIM(D.SLTERDSC) IN (@SALSTERR)) AND
(RTRIM(F.CLASDSCR) IN (@CUSTCLAS))
Assumptions made by me - DOCDATE is of type DATETIME and @YEARMONTH is of type INT and stores psuedo dates in the format YYYYMM.
It looks like your where clause is: -
1. Checking that the YEAR and MONTH of DOCDATE appended together as an INT (with a 0 added to the month if it is a month that has a single digit) is greater than or equal to that of @YEARMONTH.
2. Checking that the YEAR and MONTH of DOCDATE appended together as an INT (with a 0 added to the month if it is a month that has a single digit) is greater than or equal to @YEARMONTH where if @YEARMONTH 5th to 7th character is either 01,02,03,04,05 then we minus 93 from the @YEARMONTH (e.g. say @YEARMONTH is "20000101", then we'd minus 93 because the 5th character to the 7th is "01" so we'd check that DOCDATE >= 20000008). When @YEARMONTH 5th to 7th character is not any of 01,02,03,04,05 then we minus 5 instead.
3. ITMCLSDC is in @brand-2
4. ITEMNMBR doesn't start with Mkt
5. A right trim (removing trailing spaces) of SLTERDSC in @SALSTERR
6. A right trim (removing trailing spaces) of CLASDSCR in @CUSTCLAS
February 29, 2012 at 5:33 am
All assumptions are correct - is the -93 subtracting 93 days ?
February 29, 2012 at 5:36 am
No, because the @YEARMONTH is an INT. So in my example, 20000101, represents the 1st Jan 2000, but when we minus 93 from the @YEARMONTH we get 20000008 which would be a nonsense date (8th of the 0th 2000 ??).
Also, make sure you read what Jeff wrote. He's correct, you won't be making best use of any indexes that may exist with the WHERE clause written as it is.
February 29, 2012 at 5:52 am
OK - thats it
@Yearmonth is 201202 (for Feb 2012) and -93 gives 201109 (for Sept 2011)
Perfect thanks for your help - I am no longer going around in circles
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply