Query using DATEPART

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

FROMTbl_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

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.

|||

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

|||

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.

|||

This worked beautifully! Thank you so much!

Query syntax help

I am trying to write a query that returns all suppliers within a given range
that either do not have any insurance (appear only in Suppliers table) or
Suppliers where the insurance has expired from a given date
Eg 4 Suppliers
Supplier1 - no insurance
Supplier2 - insurance expired
Supplier3 - insurance current
Supplier4 - not in range
The Supplier range is 'where AccRef like '^SC%'
The Expiry Date is less than or equal to '20071130'
The result set would include Supplier1 because it is not in the
InsuranceDetails table at all and Supplier2 because the insurance has
expired.
How can I do this in one query?
I have included some SQL for your info
Thanks
A
CREATE TABLE [dbo].[Suppliers](
[AccRef] [nvarchar](8) NOT NULL,
[AccName] [nvarchar](30) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[InsuranceDetails](
[AccRef] [nvarchar](8) NOT NULL,
[DateExpire] [datetime] NOT NULL
) ON [PRIMARY]
INSERT INTO dbo.Suppliers ([AccRef], [AccName])
SELECT '^SC100' As Expr1, 'Supplier1' as Expr2
INSERT INTO dbo.Suppliers ([AccRef], [AccName])
SELECT '^SC200' AS Expr1, 'Supplier2' as Expr2
INSERT INTO dbo.Suppliers ([AccRef], [AccName])
SELECT '^SC300' AS Expr1, 'Supplier3' as Expr2
INSERT INTO dbo.Suppliers ([AccRef], [AccName])
SELECT '10000' AS Expr1, 'Supplier4' as Expr2
INSERT INTO dbo.InsuranceDetails ([AccRef], [DateExpire])SELECT '^SC300' AS
Expr1, '20080331' as Expr2
INSERT INTO dbo.InsuranceDetails ([AccRef], [DateExpire])SELECT '^SC200' AS
Expr1, '20071101' as Expr2
There are a variety of ways you can do this. Here is one:
SELECT s1.accref, s1.AccName
FROM suppliers s1
WHERE s1.accref LIKE '^SC%'
AND COALESCE(
( SELECT i1.dateexpire
FROM InsuranceDetails i1
WHERE i1.accref = s1.accref ), '19000101' )
<= '20071130' ;
Anith