Max and inline query help

[sjjustina]

I have a table that gets written to every time there is an inventory transaction. I need to write a report that returns the last record for a given fiscal period, either the current period or a period in the past. I want only one record per location, type, product and costid of the fiscal period specified.

A product can have a transaction in the period you're looking for or before then if there's been no recent activity.

I've concatenated the date and time together. I'm using this concatenated field to join the table back to itself. I'm getting an error that maxdate1 is not a valid column name.

SELECT TOP 100 PERCENT a.loc, a.protype, a.product, a.costid, a.fiscalperiod, a.fiscalyear, a.datecreated + ' ' + a.timecreated AS maxdate1
FROM dbo.cst_mac a INNER JOIN
(SELECT MAX(datecreated + ' ' + timecreated) AS maxdate2
FROM dbo.cst_mac
GROUP BY loc, protype, product, costid) b ON a.maxdate1 = b.maxdate2
WHERE (a.fiscalperiod <= 9) AND (a.fiscalyear <= 2007) AND (a.product = 'cd3x3c')
ORDER BY a.loc, a.protype, a.product

The inline query by itself produces this result:
maxdate1 loc protype product costid
8/9/2007 6:09:07 AM BEWO FL CD3X3C 7
8/17/2007 10:40:51 AM L FL CD3X3C 7
8/9/2007 4:55:01 PM MGMA FL CD3X3C 7
8/10/2007 3:45:18 PM NA FL CD3X3C 7
8/14/2007 12:00:05 PM NO#2 FL CD3X3C 7
8/9/2007 4:08:08 PM NOVA FL CD3X3C 7
8/10/2007 4:05:50 PM SCAR FL CD3X3C 7
8/14/2007 4:12:48 PM SOSH FL CD3X3C 7
8/10/2007 3:39:06 PM TES1 FL CD3X3C 7
8/10/2007 4:05:49 PM TES2 FL CD3X3C 7
8/8/2007 11:35:50 AM TNC1 FL CD3X3C 7
6/15/2007 1:47:09 PM TNC2 FL CD3X3C 7
7/27/2007 7:29:14 PM TNL1 FL CD3X3C 7

 

[mrdenny]

The join should look like: a.datecreated + ' ' + a.timecreated = b.maxdate2

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com