Thanks for the reply.

I tried that it returns noresults. Can some one write a query from the sample tables I have in the original post.

Just to bring it to the front page.

SELECT A.*

FROM

Table1 A

JOIN

(

SELECT A_Acct, MAX(A_Date) A_Date

FROM

Table1

GROUP BY

A_Acct

) MaxDate

ON

A.A_Acct = MaxDate.A_Acct

ANDA.A_Date = MaxDate.A_Date

Jason -

Intersetingly your query costs double with the case. I guess that is due to double correlated queries with the case statement.

As of now it costs little more than mine.

Jason's query is better it gives query cost 27.74 % and mine gives 36.18 %

I thought it is InvoiceID or something. Modify the table @I_master in my previous post as Inv_id instead of part number.

try this

SET NOCOUNT ON

DECLARE @workTable TABLE

(

[ID] INT,

WorkReq VARCHAR(16),

Partno VARCHAR(16),

dateAdded DATETIME

)

INSERT @workTable

SELECT 1, '123', '02-FIT-1', '9/12/2007' UNION

SELECT 2, '123', '02-FIT-1', '9/12/2007' UNION

SELECT 3, '123', '02-BRG-123', '9/12/2007' UNION

SELECT 4, '123', '02-BRG-432', '9/12/2007' UNION

SELECT 5, '123', '02-BRG-432', '9/12/2007' UNION

SELECT...

Why do you join inv_id with partnumber?

My above query is only subtracting one from the I_master qty_on_hand for each part even though I have multiple items in worktable for that...

Thanks Jeff that is perfect.

Unfortunately I had to create a user defined function for these values (Cannot use # tables so no identity function). I cannot add tables to database so the...

Lynn as I said if the results needed are only for the dates available in the table it is as easy as that. But I need data for all the...

Thanks Peter. I was able to make the dates in a temtable almost similar way. My problem is to get the desired results for all the dates using a join....

Lynn it is not as easy as that. If you see my expected results it lists all dates of the month for the data available as of that date.

Sorry that was a mistake. I meant July & August there. Corrected the post.

I was able to do it using a loop. I would like to know whether it is...

SELECT CONVERT(DATETIME,STUFF(STUFF('30122006', 3, 0, '-'), 6, 0, '-'), 105)

Thanks Peter. That will work. I got fixed columns so this solutuon is okay.

Some how I did like this. But your solution is better.

DECLARE @myTable TABLE

(

DataDate DATETIME,

TotalCount INT,

Count10s INT,

Count20s INT,

Count30s...