September 23, 2008 at 7:51 pm
Hi all...
Regarding the following statement,
SELECT
a.WeekOfYear, SUM(b.WeekTotals) AS HistBacklog
FROM
(
SELECT
DATENAME(wk, CONVERT(datetime, dbo.WorkOrder.RaisedDateTime, 103))+' '+DATENAME(yyyy, CONVERT(datetime, dbo.WorkOrder.RaisedDateTime, 103)) AS WeekOfYear,
COUNT(RaisedDateTime)-COUNT(FinishedDateTime) AS 'WeekTotals'
FROM dbo.WorkOrder
GROUP BY DATENAME(wk, CONVERT(datetime, dbo.WorkOrder.RaisedDateTime, 103))+' '+DATENAME(yyyy, CONVERT(datetime, dbo.WorkOrder.RaisedDateTime, 103))
)AS a
INNER JOIN
(
SELECT
DATENAME(wk, CONVERT(datetime, dbo.WorkOrder.RaisedDateTime, 103))+' '+DATENAME(yyyy, CONVERT(datetime, dbo.WorkOrder.RaisedDateTime, 103)) AS WeekOfYear,
COUNT(RaisedDateTime)-COUNT(FinishedDateTime) AS 'WeekTotals'
FROM dbo.WorkOrder
GROUP BY DATENAME(wk, CONVERT(datetime, dbo.WorkOrder.RaisedDateTime, 103))+' '+DATENAME(yyyy, CONVERT(datetime, dbo.WorkOrder.RaisedDateTime, 103))
)AS b
ON a.WeekOfYear >= b.WeekOfYear
WHERE
a.WeekOfYear BETWEEN
DATENAME(wk, GETDATE()-100)+' '+DATENAME(yyyy, GETDATE()-100) AND
DATENAME(wk, GETDATE())+' '+DATENAME(yyyy, GETDATE())
GROUP BY a.WeekOfYear
The results are fine when I use the GETDATE() command in the WHERE clause - but when I attempt to utilise user specified dates ([Date1Start] and [Date1End]) there are no results returned...
The user fields are regular datetime format and have worked fine with other statements... I'm hoping there is an easy explanation (possibly my structure) for this...
Any suggestions are most appreciated.
Cheers...
September 24, 2008 at 3:18 pm
Not to worry - I have resolved this issue in another forum... see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111229
The problem was occurring in trying to use a string in a BETWEEN condition...
Cheers...
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply