issue with tsql and submitdate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

issue with tsql and submitdate

Author

Topic

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2012-04-12 : 06:21:53

Hi,

I have a table which shows a file as 'booked' or 'available' and the identifing factor is the submitdate. In practise how the system works - if a file is returned, a new row is inserted as 'available' and a user can tell if a file is available by its most recent status. This works fine.

But now i need to get a count of the files showing currently as booked. If i do a basic select with a where clause for booked...its incorrect as the file may be available based on a more recent submitdate. I tried to do an order by submitdate or a group by submitdate...but its not working for me.

I cant change the way the data is input...is there some way im missing to get the select correct.

The table has 3 fields called - status, fileid and submitdate.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-12 : 07:21:56

See if this will work for you:

;WITH cte AS
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY fileId ORDER BY submitdate DESC) AS RN
	FROM
		YourTable
)
SELECT
	COUNT(*) AS Booked
FROM
	cte
WHERE
	RN = 1 AND status = 'Booked';

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2012-04-12 : 07:58:14

Hi,

Sorry about this...its a sql server 2000 DB...most of our dbs are 2005 but this legacy one is 2000...i missed this.

I pasted the above into query analyser and got the error that it doesnt recognise row_number()...is this something only available in 2005.

Should i repost this in the 2000 forum?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-12 : 19:37:10

ROW_NUMBER function is available only in SQL 2005 and higher. For SQL 2000, you could do the following:

SELECT
	COUNT(*)
FROM
	YourTable a
	INNER JOIN
(
	SELECT
		fileId,
		MAX(submitdate) AS MaxDate
	FROM
		YourTable
	GROUP BY
		fileId
) b ON a.fileId = b.fileId AND a.submitDate = b.MaxDate;

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2012-04-13 : 05:25:56

Hi sunitabeck,

I tried your sql but the problem is that i want to see only those files marked as 'booked' in the column 'status'. In my test case i have 2 files with multiple entries for been 'booked' or 'available'. Their most recent entries are that 1 file is booked and the other file is available. So the total count for booked files should be 1...but with your sql its 2. I tried to amend the sql to include the column 'status' in a where clause but still cant get it.

Any ideas?? Thanks for all help so far.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-13 : 14:59:31

Where did you add the WHERE clause? Take a look at the example that I constructed to see if that gives you the correct results. If not, what is the output that you would expect?

CREATE TABLE #tmp(fileId INT, submitDate DATETIME, STATUS VARCHAR(32));
INSERT INTO #tmp VALUES (1,'20120101','booked');
INSERT INTO #tmp VALUES (1,'20120102','available');
INSERT INTO #tmp VALUES (2,'20120101','available');
INSERT INTO #tmp VALUES (2,'20120102','booked');

SELECT
	COUNT(*)
FROM
	#tmp a
	INNER JOIN
(
	SELECT
		fileId,
		MAX(submitdate) AS MaxDate
	FROM
		#tmp
	GROUP BY
		fileId
) b ON a.fileId = b.fileId AND a.submitDate = b.MaxDate
WHERE
	a.status = 'booked';
	
	
DROP TABLE #tmp;

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2012-04-16 : 05:06:01

Thanks for that...that got it working. Would have never resolved it without ur help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-16 : 07:14:31

you are very welcome.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources