Treklady1


Hello.

The purpose of the code below is to produce graphs in a report (thus the SELECT DATEPART so the month and year appear on the graphs using COUNT in the report.) I have two challenges:

1. How should the code be modified so that it returns the previous two months at the start of a new year (I.e. In Jan. 2008, returning Nov. & Dec. 2007, and in Feb. 2008, returning Dec. 2007 & Jan 2008, etc.)

2. How should the code be modified so that the DatePart and DateName data are treated as dates yet still appear as the Month Name and Year (June 2007) Currently it is treating them as alphanumeric so that on the chart, July is coming before June.

Thank you.

SELECT DISTINCT TBL_01.id, TBL_01.url, TBL_01.source, Tbl_02.type, TBL_01.time, Tbl_03.time, DATENAME(month, TBL_01.time) + ' ' + DATENAME(year,TBL_01.time) as Month_Year

FROM Tbl_03 INNER JOIN

Tbl_04 ON Tbl_03.id = Tbl_04.endsTBL_01 LEFT OUTER JOIN

Tbl_02 ON Tbl_04.endsTBL_01 = Tbl_02.id LEFT OUTER JOIN

Tbl_05 ON Tbl_04.ursid = Tbl_05.SubId INNER JOIN

Tbl_03 AS TBL_01 ON TBL_01.id = Tbl_04. EFP

WHERE (Tbl_04. EFP IS NOT NULL) AND (Tbl_05.grade IS NULL OR

Tbl_05.grade = 'NC')

AND (DATEPART(month, TBL_01.time) < (SELECT DATEPART(month, getutcdate()))

AND DATEPART(month, TBL_01.time) > (SELECT DATEPART(month, getutcdate()))-3

AND DATEPART(year, TBL_01.time) = (SELECT DATEPART(year, getutcdate()))

AND (Tbl_02.type IS NULL OR Tbl_02.type = 'CC'

OR Tbl_02.type = 'MK'))

ORDER BY Tbl_02.Type, Tbl_03.time DESC




Re: Query using DATEPART

rusag2


For Q2, you might be able to fix the ordering with a tiny change to the 'ORDER BY ' clause:

ORDER BY TBL_01.time desc, Tbl_02.Type, Tbl_03.time

You don't always have to have the <ORDER BY> field in the select list.







Re: Query using DATEPART

rusag2

For Q1, I would do something like this:

Code Snippet

declare @dtStartOfThisMonth datetime, @dtStartOfTimePeriod datetime

set @dtStartOfThisMonth = ('01 ' + right(convert(varchar, getdate(),106),9))

set @dtStartOfTimePeriod = Dateadd(m,-2, @dtStartOfThisMonth)

---THE REST OF THE QUERY HERE

Then, in the WHERE clause put something like:

Code Snippet

AND Tbl_01.Time >= @dtStartOfTimePeriod and Tbl_01.Time < @dtStartOfThisMonth







Re: Query using DATEPART

Treklady1

This worked in putting things in the proper order. I just took out the "desc" and everything fell into place. The one field that was in the select list that didn't need to be was merely a leftover from when I was experimenting with an aggregate. I had forgotten to take it out.

Thank you so much.





Re: Query using DATEPART

Treklady1

This worked beautifully! Thank you so much!