Query is not running - SQL Server Forums

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)

Query is not running

Author

Topic

Deep123
Starting Member

8 Posts

Posted - 2009-06-16 : 08:44:45

wats problem in this query

select case ActionDueDate when ActionDueDate < getdate()
then
count(Id)
end
from DMS_Distribution_Details
where ActionRequired='For Approval' and
(approvestatus!='Approved' or approvestatus is null)
and ActionDueDate < getdate()

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-16 : 08:51:21

Well there's more than 1 thing wrong with the query.

1) Your case statement isn't syntactically correct I think what you meant to write was:


SELECT
    COUNT(CASE WHEN [actionDueDate] < GETDATE() THEN [Id] END)
....
....

However,
2) Your WHERE clause contains the line


    AND [actionDueDate] < GETDATE()

Which makes your CASE statement completely pointless.

Maybe you should provide some sample data and expected output. I'm sure this SQL isn't doing what you want it to.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 08:54:19

A few things.

1. WHEN case
2. WHERE clause

Try this

select	count(Id)
from	DMS_Distribution_Details
where	ActionRequired = 'For Approval'
	and (approvestatus <> 'Approved' or approvestatus is null) 
	and ActionDueDate < getdate()

E 12°55'05.63"
N 56°04'39.26"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-16 : 09:11:15

Lol Peso -- a few words ahead of me! Deep123 here's some sample data (I had to guess so it might not look like yours)

The count brought back either way is 1.


DECLARE @foo TABLE (
	[ID] INT
	, [ActionRequired] NVARCHAR(255)
	, [approvestatus] NVARCHAR(255)
	, [ActionDueDate] DATETIME
	)

INSERT @foo ([Id], [actionRequired], [approveStatus], [ActionDueDate])
      SELECT 1, 'Have Lunch', 'Approved', '2009-06-15T13:30:00'
UNION SELECT 2, 'For Approval', NULL, '2009-06-15T15:25:00'
UNION SELECT 3, 'For Approval', 'Approved', '2009-06-15T17:30:30'
UNION SELECT 4, 'Sleep', 'Approved', '2009-06-16T01:00:00'

SELECT * FROM @foo

-- With CASE statement
SELECT
    COUNT(CASE WHEN [actionDueDate] < GETDATE() THEN [Id] END)
FROM
	@foo
WHERE
	[ActionRequired] = 'For Approval'
	AND (
		[approvestatus] <> 'Approved'
		OR [approvestatus] IS NULL
		) 
	AND [ActionDueDate] < GETDATE()

SELECT
	COUNT([ID])
FROM
	@foo
WHERE
	[ActionRequired] = 'For Approval'
	AND (
		[approvestatus] <> 'Approved'
		OR [approvestatus] IS NULL
		) 
	AND [ActionDueDate] < GETDATE()

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources