quote:

---

Originally posted by Windza

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...

As always - your help is appreciated.
Cheers...

---

quote:

---

Originally posted by Windza

The statement with the date parameters is simply as follows...

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, [Date1Start])+' '+DATENAME(yyyy, [Date1Start]) AND
DATENAME(wk, [Date1End])+' '+DATENAME(yyyy, [Date1End])
GROUP BY a.WeekOfYear

---

quote:

---

Originally posted by visakh16
the between wont work as expected you're giving two string value as start and end range.What will be contents of WeekofYear column?

---

quote:

---

Originally posted by Windza

The WeekOfYear is week and year in the following format

12 2008
13 2008
14 2008

etc.

---

CAST(RIGHT(a.WeekOfYear,4)+LEFT(a.WeekOfYear,2) AS int)>=CAST(DATENAME(yyyy, [Date1Start])+DATENAME(wk, [Date1Start])AS int)
AND CAST(RIGHT(a.WeekOfYear,4)+LEFT(a.WeekOfYear,2) AS int)>=CAST(DATENAME(yyyy, [Date1End])+DATENAME(wk, [Date1End])AS int)

a.WeekOfYear BETWEEN
DATENAME(wk, [Date1Start])+' '+DATENAME(yyyy, [Date1Start]) AND
DATENAME(wk, [Date1End])+' '+DATENAME(yyyy, [Date1End])

quote:

---

then use

CAST(RIGHT(a.WeekOfYear,4)+LEFT(a.WeekOfYear,2) AS int)>=CAST(DATENAME(yyyy, [Date1Start])+DATENAME(wk, [Date1Start])AS int)
AND CAST(RIGHT(a.WeekOfYear,4)+LEFT(a.WeekOfYear,2) AS int)>=CAST(DATENAME(yyyy, [Date1End])+DATENAME(wk, [Date1End])AS int)

---

quote:

---

Originally posted by Windza

quote:

---

then use

CAST(RIGHT(a.WeekOfYear,4)+LEFT(a.WeekOfYear,2) AS int)>=CAST(DATENAME(yyyy, [Date1Start])+DATENAME(wk, [Date1Start])AS int)
AND CAST(RIGHT(a.WeekOfYear,4)+LEFT(a.WeekOfYear,2) AS int)>=CAST(DATENAME(yyyy, [Date1End])+DATENAME(wk, [Date1End])AS int)

---

Visakh16 - I'm sure you've given me the correct functionality with the statement but is the latter 'greater than or equal' condition (>=)correct or should it be 'less than or equal' (<=)?

What I need is Date1Start <= WeekOfYear <= Date1End (I may not have been clear in this). I thought it would be just a simple matter of reversing this particular sign but then I don't get any results...

---

quote:

---

Originally posted by Windza

No worries - but the trouble is now that I have reversed the sign I don't have any results at all...

---

quote:

---

do you always have WeekNumber in format wk yyyy? all this complications happen only because you have character field for storing week and year. You might have used date field or even integer to store this to make manipulations easier.

---

quote:

---

Originally posted by Windza

I discovered the problem, thanks Visakh16... was just as you suggested (I had CONVERT on the date parameters in the initial SELECT clause).
Cheers...

---