Select query adding one more condition

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 2000 Forums

SQL Server Development (2000)

Select query adding one more condition

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2006-07-31 : 17:25:14

For the field under the condition A.DueDate = (Select Min(DueDate) From TAB_ccsNetActions Where DueDate > getdate() )
which is getting one nearest duedate record comparing with getdate(), along with these records i also want to bring all the records which has a past duedate. can i use a union.

I have the following Select case when @eStatus is 'OPEN':
****************************************

SELECT @SQLJoin =

CASE @eStatus

WHEN 'Open' THEN ' AND (A.ModuleName = ''SU'' and isdate(S.PDToContractorDate) = 0 and isdate(S.PDReceivedDate) = 1) AND isdate(A.CompleteDate) = 0 And
A.DueDate = (Select Min(DueDate) From TAB_ccsNetActions Where DueDate > getdate() )
GROUP BY S.SUNumber, S.SUTitle, S.PDReceivedDate, S.PDToReviewDate, S.PDNeedDate,
S.PDToContractorDate, S.PDSupervisor, S.SUID, A.ActionDescription'

****************************************

Thank you very much for the help.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-07-31 : 18:24:15

Can you please post your entire procedure so I can get a better understanding of your issue.

reddymade
Posting Yak Master

165 Posts

Posted - 2006-08-01 : 14:54:35

Hello Vinnie,
Here is the entire stored proc.

*********************
CREATE PROCEDURE dbo.USP_ReportStatus
( @UserName nvarchar(50),
@eStatus nvarchar(50))

AS

DECLARE @ErrorCode int
DECLARE @SQL varchar(2000)
DECLARE @SQLType varchar(2000)
DECLARE @SQLJOIN varchar(2000)
DECLARE @SQLWHERE varchar(2000)
DECLARE @SQLWHERE2 varchar(2000)
DECLARE @Language nvarchar(50)

SET @SQLJOIN = ''

SET NOCOUNT ON
SELECT @ErrorCode = @@Error
IF @ErrorCode <> 0
BEGIN
SET NOCOUNT OFF
RETURN @ErrorCode
END

If @UserName IS NOT NULL and @UserName <> ''
BEGIN
SET @SQLWHERE = 'WHERE U.UserName=' + char(39) + @UserName + Char(39)
END

SELECT @SQLJoin =
CASE @eStatus
WHEN 'All' THEN ' AND (A.ModuleName = ''SU'')
GROUP BY S.SUNumber, S.SUTitle, S.PDReceivedDate, S.PDToReviewDate, S.PDNeedDate, S.PDToContractorDate, S.PDSupervisor, S.SUID, A.ActionDescription'

WHEN 'Open' THEN ' AND (A.ModuleName = ''SU'' and isdate(S.PDToContractorDate) = 0 and isdate(S.PDReceivedDate) = 1) AND isdate(A.CompleteDate) = 0 And
A.DueDate = (Select Min(DueDate) From TAB_ccsNetActions Where DueDate > getdate() )
GROUP BY S.SUNumber, S.SUTitle, S.PDReceivedDate, S.PDToReviewDate, S.PDNeedDate,
S.PDToContractorDate, S.PDSupervisor, S.SUID, A.ActionDescription'

WHEN 'Closed' THEN ' AND (A.ModuleName = ''SU'' and isdate(S.PDToContractorDate) = 1 and isdate(S.PDReceivedDate) = 1)
GROUP BY S.SUNumber, S.SUTitle, S.PDReceivedDate, S.PDToReviewDate, S.PDNeedDate,
S.PDToContractorDate, S.PDSupervisor, S.SUID, A.ActionDescription'

WHEN 'Late' THEN ' AND (A.ModuleName = ''SU'' and isdate(S.PDReceivedDate) = 1 and isdate(S.PDCompleteddate) = 0
and isdate(S.pdneeddate) = 1 and S.pdneeddate < getdate()) AND isdate(A.CompleteDate) = 0 And
A.DueDate = (Select Min(DueDate) From TAB_ccsNetActions Where DueDate < getdate() )
GROUP BY S.SUNumber, S.SUTitle, S.PDReceivedDate, S.PDToReviewDate, S.PDNeedDate,
S.PDToContractorDate, S.PDSupervisor, S.SUID, A.ActionDescription'

ELSE ' AND (A.ModuleName = ''SU'')
GROUP BY S.SUNumber, S.SUTitle, S.PDReceivedDate, S.PDToReviewDate, S.PDNeedDate,
S.PDToContractorDate, S.PDSupervisor, S.SUID, A.ActionDescription'
END

SET @SQL = 'SELECT S.SUNumber, S.SUTitle, CONVERT(varchar(10), S.PDReceivedDate,
101) AS PDReceivedDate,
CONVERT(varchar(10), S.PDToReviewDate, 101) AS PDToReviewDate, CONVERT(varchar(10),
S.PDToContractorDate, 101) AS PDToContractorDate, S.PDSupervisor,
S.SUID, rtrim(isnull(A.ActionDescription,'''')) as ActionDescription,
CONVERT(varchar(10), S.PDNeedDate, 101) AS [When]
FROM TAB_ccsNetSU AS S JOIN TAB_ccsNetUserAccess AS U ON (S.ProgID = U.ProgID AND S.ProjID = U.ProjID AND S.ContractID = U.ContractID)
INNER JOIN TAB_ccsNetPrograms PG ON S.ProgID = PG.ProgID
INNER JOIN TAB_ccsNetProjects PJ ON S.ProjID = PJ.ProjID
INNER JOIN TAB_ccsNetContracts CT ON S.ContractID = CT.ContractID
INNER JOIN TAB_ccsNetActions A ON S.SUID = A.ModuleRecordID '+ @SQLWHERE + @SQLJoin

--PRINT (@SQL)

EXEC (@SQL)

SELECT @ErrorCode = @@Error
IF @ErrorCode <> 0
BEGIN
SET NOCOUNT OFF
RETURN @ErrorCode
END
GO
**********************************

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources