Exactly. If the Purchase amount is less than a whole number then I want to see a 0.

This is the code:

DECLARE @consultantID VARCHAR(20)

DECLARE @StartDt DateTime

DECLARE @EndDt DateTime

--SET @ConsultantID = @COnsultantID

--SET @StartDt = @StartDt

--SET @EndDt = @EndDt

--

SET @ConsultantID = '0000344'

SET @StartDt = '11/01/2007'

SET @EndDt = '10/31/2008'

--

select v.ConsultantID

,(Select...

This is getting fustrating. I the query ran but it returns nothing but nulls. However, I did create and insert statement for my missing Order Types and that seems to...

That worked thanks. This is the DDL for the table.

CREATE TABLE [dbo].[OrderListing](

[TSOrderType] [int] NULL,

[TSUserType] [int] NULL,

[InternalUserName] [nvarchar](150) COLLATE Latin1_General_CI_AS NULL,

[OrderTypeXID] [int] NULL,

[OrderCategory] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[OrderType] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[OrderStatusXID]...

CREATE TABLE [dbo].[OrderListing](

[TSOrderType] [int] NULL,

[TSUserType] [int] NULL,

[InternalUserName] [nvarchar](150) NULL,

[OrderTypeXID] [int] NULL,

[OrderCategory] [nvarchar](20) NULL,

[OrderType] [nvarchar](20) NULL,

[OrderStatusXID] [int] NULL,

[OrderGroupType] [int] NULL,

[OrderCreateDate] [varchar](10) NULL,

[OrderGroupNumber] [varchar](21) NULL,

[OrderNumber] [int] NULL,

[PartOneTotal] [decimal](18, 2) NULL,

[PartTwoTotal] [decimal](18, 2)...

I am trying to create some test data based on the article you gave me a link to:

SELECT 'SELECT '

+ QUOTENAME(TSORDERTYPE,'''')+','

...

See attached xls it's easier than explaining:

Need to insert rows for the Order Types that have no dollar amounts.

Works like a champ. Thanks for eveybody's insight. Shouldn't have another question about datetime function for a while now. 😀

I think I found the problem with.

The actual table is storeing the endDate as 2008-03-31 22:59:59.000

but I have declared my @EndDt variable as DateTime which is stored as 2008-03-31 00:00:00.000

How...

Well it came down that I need to have up to the minute:

02/29/2008 11:59:59 PM

So when I run this:

SET @MonthStartDt = DATEADD(month, DATEDIFF(month, 0, @StartDate), 0)

Set @MonthEndDt = DATEADD(month, DATEDIFF(month,...

I got it. What a pain. This is what it looks like:

SELECT Distinct o.orderNumber

,c.consultantid

...

I tried it with the MAX statement and received the same results.

I got it pared down to this:

--

DECLARE @COnsultantID CHAR(20)

DECLARE @StartDate Datetime

DECLARE @EndDate Datetime

SET @ConsultantID = '0000344'

SET @StartDate ='12/01/2007'

SET @EndDate =GetDate()

SELECT Distinct o.orderNumber

,c.consultantid

,(Select c.firstname + ' ' + c.Lastname) ...

The reason for the inline query is because the consultant table holds multiple records for a single consultant and the inline query gives me the most recent record for the...

Sorry, I meant to add the From clause:

FROM consultant c

JOIN ( SELECT c2.ConsultantId

...