January 4, 2013 at 6:09 am
Hi, I have the following query which I'm converting from Access:
SELECT [SPM Orders by Week Prepare].AccRef, [SPM Orders by Week Prepare].SPM, [Week]+1 AS WeekAdjusted, [SPM Orders by Week Prepare].OrdNo, IIf([SPM]<10,"0" & [SPM],[SPM]) AS SPMNo, IIf(([Week]+2)<10,"0" & ([Week]+2),([Week]+2)) AS WKNo
FROM [SPM Orders by Week Prepare]
GROUP BY [SPM Orders by Week Prepare].AccRef, [SPM Orders by Week Prepare].SPM, [Week]+1, [SPM Orders by Week Prepare].OrdNo, IIf([SPM]<10,"0" & [SPM],[SPM]), IIf(([Week]+2)<10,"0" & ([Week]+2),([Week]+2))
HAVING ((([SPM Orders by Week Prepare].SPM)<>0))
ORDER BY [SPM Orders by Week Prepare].SPM, [Week]+1, [SPM Orders by Week Prepare].OrdNo;
Basically, in the SPMNo and WKNo fields i need to always have a 2 digit week ie week 10 to 53 are ok, but weeks 1 to 9 must show as 01, 02, 03 etc.
How do I force it to add a zero to the beginning? The result would obviously be text not numeric.
This is what I've tried with no success:
SELECT AccRef, SPM, [Week], OrdNo, CASE WHEN SPM < 10 THEN '0' + obw1.SPM ELSE SPM END AS SPMNo, CASE WHEN [Week]+2 < 10 THEN '0' + obw1.[Week]+2 ELSE obw1.[Week]+2 END AS WKNo
January 4, 2013 at 6:13 am
RIGHT('0'+convert(varchar,weeknumber),2)
Something like the above.
The weeknumber will need to be a string, as if you stick a 0 to the begining of an int, it disapears, then if you get 1 it is 01, if you get 10 you get 010 but right 2 returns 10.
January 4, 2013 at 6:20 am
Opted for this in the end:
SELECT AccRef, SPMNo, WKNo, CASE WHEN SPMNo < 10 THEN '0' + obw2.SPMNo ELSE SPMNo END AS SPMNo, CASE WHEN [WKNo] < 10 THEN '0' + obw2.[WKNo] ELSE [WKNo] END AS WKNo
FROM
(
SELECT AccRef, CAST(SPM AS NVARCHAR(2)) AS SPMNo, CAST([Week] AS NVARCHAR(2)) AS WKNo, OrdNo
FROM
(
SELECT [P/O Header].AccRef, Products.SPM, DatePart("ww",[Due Date]) AS [Week], [P/O Header].OrdNo
FROM
[P/O Header]
INNER JOIN [P/O Details]
ON [P/O Header].OrdNo = [P/O Details].OrdNo
INNER JOIN Products
ON [P/O Details].StockCode = Products.StockCode
WHERE SPM <> 0 AND [P/O Header].[Due Date] Is Not Null AND [P/O Header].[Complete ?]=0
) obw1
GROUP BY obw1.AccRef, obw1.SPM, obw1.[Week], obw1.OrdNo
) obw2
ORDER BY obw2.SPMNo ASC, obw2.[WKNo] ASC, obw2.OrdNo ASC;
January 4, 2013 at 6:29 am
This should also do the same
SELECT
[P/O Header].AccRef,
RIGHT('0'+CONVERT(VARCHAR,Products.SPM),2) AS SPMNo,
RIGHT('0'+CONVERT(VARCHAR,DatePart(ww,[Due Date])),2) AS [WKNo],
[P/O Header].OrdNo
FROM
[P/O Header]
INNER JOIN
[P/O Details]
ON
[P/O Header].OrdNo = [P/O Details].OrdNo
INNER JOIN
Products
ON
[P/O Details].StockCode = Products.StockCode
WHERE
SPM <> 0
AND
[P/O Header].[Due Date] IS NOT NULL
AND
[P/O Header].[Complete ?] = 0
GROUP BY
[P/O Header].AccRef,
RIGHT('0'+CONVERT(VARCHAR,Products.SPM),2),
RIGHT('0'+CONVERT(VARCHAR,DatePart(ww,[Due Date])),2),
[P/O Header].OrdNo
ORDER BY
SPMNo,
[WKNo],
OrdNo;
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply