Date precedence logic - 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)

Date precedence logic

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-08-09 : 09:19:59

Hello All,

I need to show the output to the users based on the records created by date.
Show the records created in last seven days or the last 3 records whichever is greater will be the results.

How do I achieve this kind of output. I already have a query which does bunch of joins and 2 conditional statements.

Thanks for any help
-P

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-09 : 09:27:13

Post table definitions and the query that you have so far please

--
Gail Shaw
SQL Server MVP

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-08-09 : 09:38:03

Table Observant
(
ObsID INT,
Comments VARCHAR(1000),
CustomerID VARCHAR(30),
ActionRequired VARCHAR(1000),
CreatedBy VARCHAR(100)
CreatedByDate DATETIME
)

My query so far is - As you can see currently all the observations are show. I want only the createdbyDate either 7 days or first 3 whichever is greater.


select A.ObsID, A.CustomerID, C.CustName, A.ActionRequired, A.Comments				A.CreatedByDate        
from Observant A (NOLOCK)    
join Customers C 
ON C.CustomerID = A.CustomerID

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-09 : 09:52:28

SELECT A.ObsID, A.CustomerID, C.CustName, A.ActionRequired, A.Comments, A.CreatedByDate
FROM Observant A (NOLOCK) JOIN Customers C ON C.CustomerID = A.CustomerID
WHERE A.CreatedByDate>=GETDATE()-7
UNION
SELECT TOP 3 A.ObsID, A.CustomerID, C.CustName, A.ActionRequired, A.Comments, A.CreatedByDate
FROM Observant A (NOLOCK) JOIN Customers C ON C.CustomerID = A.CustomerID ORDER BY A.CreatedByDate DESC

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-08-09 : 10:28:48

I want to see only one set depending on
SELECT @Date1 = MAX(A.CreatedByDate) WHERE A.CreatedByDate>=GETDATE()-7
SELECT TOP 3 dates in one CTE and then compare @Date1 with CTE.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-09 : 10:39:15

You'll have to post sample data and expected results that cover both scenarios.

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-08-09 : 16:42:02

I resolved my problem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources