Please post some data that is causing the problem. Use the format that I created in the previous responses.

Try this:

declare @records table (date datetime, id int, amount int)

insert @records values('30 Jan 2008', 1, 2)

insert @records values('31 Jan 2008', 1, 3)

insert @records values('30 Jan 2008', 2, 2)

insert @records values('31...

Try this:

declare @table table ([Var] varchar(50), [Time] datetime, [MilliSec] int, [Value] varchar(50))

insert @table

values ('Stage_num[11]', '6/16/2008 9:09:20 AM', 375, '11')

insert @table

values ('Stage_Status[11]', '6/16/2008 9:09:20 AM', 375, '5') ...

I bet its seeing 9/5/2007 as May 5th 2007. Try 9/20/2007 and see what happens. Also try convert(datetime, '5 September 2007')

Instead of IIF, try:

case when '2008-01-14' = '2008-01-14' then '2008-01-13' else '2008-01-14' end

kirk (1/17/2008)

---

GSquared

I understand your explanation. Makes perfect sense. The problem I have had when working with DISTINCT is that all columns in the select list are engaged in the...

I would say they are some sort of temporary table created by the access frontend.

As far as some users seeing rows and other not, there could be open transactions.  This...

Then why did you post to a SQL 2005 forum?

The problem that I see has to do with the rows have both values for sales and purchases and I didn't consider this based on the initial data.  I'll see...

All temporary tables are created in tempdb. what database are you looking in?  How are you looking?  With QA? With EM?  What does this query return:

select * from sysobjects where...

Since this is SQL2005, we should be using the features of 2005.  There is no need for temp tables or loops to do this anymore.

This is one of the new feature of SQL2005 and no kludge or workaround is required.

declare @Employees table (firstname varchar(20), lastname varchar(20))

insert @employees values('Jane', 'Doe')

insert @employees values('John', 'Doe')

insert @employees values('Ed',...

What are you talking about?

How are temp files added to the database file?  How are you looking in the database file to determine if there are .tmp files?

There is no...

OUTER JOINs always perform better than NOT INs. 

I don't believe you can do what you are trying to do.

Try casting fax_date as a smalldatetime to remove the seconds:

SELECT DateAdd(mi, 0 - DatePart(mi, fax_Date) + (DatePart(mi, fax_Date) / 30) * 30 , cast(fax_Date as smalldatetime)) AS AdjustedDate

, Count(*) As...