Solved it. For anyone who comes across this issue, my solution was CONVERT(VARCHAR(10), DateTimeSecs, 108) BETWEEN '07:00:00' AND '08:00:00'.

Cheers

ab

Cheers for that. I realise now my syntax was at fault as I was trying variations of IsNull and getting the wrong results.

ab

Many thanks guys,

This has worked a treat!

Cheers

ab

Actually I have just spotted a bug. It doesn't fix my problem but it does raise a new one which might mean I am closer to being on the...

Thanks for the pointers to the crosstab articles. I have had a look and had a rough go at it, but I found the result was a bit strange....

Cheers for the quick reply, I'll give that a go a bit later and let you know how it went.

ab

USE [Gym]

GO

/****** Object: Table [dbo].[tblSessions] Script Date: 09/14/2009 08:56:53 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblSessions](

[nSessionPK] [int] IDENTITY(1,1) NOT NULL,

[nGymSessionID] [int] NULL,

[dtDate] [smalldatetime] NOT...

Thanks. I've got just over 2000 rows to work through, so I'll try doing this generating this in Excel and pasting it back to sql and see how it...

Based on the sample data, for job number 15157 I would return 'Finsbury' as the supplier - the lowest SYSID of 93290.

The 'First' record should always we the lowest SYSID...

Select 'PurchaseOrder' As OH_DOC_TYPE,

SYSID As OH_DOC_NUM,

SYSID As OH_PO_NUM,

SUPPLIERNAME As OH_SUPP,

JOBNUMBER As OH_SRC_QJNUM,

TOTALCOST As OH_ORDER_VALUE,

Null As OH_ORDER_LINES

From JobOrd

Where SYSID >= 93175 and QUOTEREQUESTORDER = 'O'

Order by JobNumber

Sample Data would...

Sorry, solved my own problem.  I should have used 'If @varFranchise IS NULL' Instead of 'If @varFranchise = NULL'.

Cheers

Found it.  For anyone who stumbles over this post the aswer it to use 'COLLATE Latin1_General_CS_AS' in the order by query.  For Example:

  ORDER BY  CASE WHEN @varSort = 1 THEN...

The following answer was supplied to me on another forum (thanks Bill W!), and might prove useful to another user: