Need help with sequel statement.

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)

Need help with sequel statement.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-07-28 : 14:22:57

Give the table and business rules below, how can I get desired result showing below.
I have a work around but I wonder if there is a better way to construct this query.

Thank you very much in advance for your help.

--SQL 2005
IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
DROP TABLE #Temp
GO
CREATE TABLE #Temp
(
LoanNum VARCHAR(8) NULL,
EnterDt DATETIME
)
GO

INSERT INTO dbo.#Temp (LoanNum, EnterDt)
VALUES (12345, '07/26/2010')
INSERT INTO dbo.#Temp (LoanNum, EnterDt)
VALUES (12346, '07/24/2010')
INSERT INTO dbo.#Temp (LoanNum, EnterDt)
VALUES (12347, '07/23/2010')
INSERT INTO dbo.#Temp (LoanNum, EnterDt)
VALUES (12348, '07/22/2010')
INSERT INTO dbo.#Temp (LoanNum, EnterDt)
VALUES (12349, '07/21/2010')
INSERT INTO dbo.#Temp (LoanNum, EnterDt)
VALUES (12350, '07/20/2010')
INSERT INTO dbo.#Temp (LoanNum, EnterDt)
VALUES (12351, '07/19/2010')
INSERT INTO dbo.#Temp (LoanNum, EnterDt)
VALUES (12352, '07/18/2010')
INSERT INTO dbo.#Temp (LoanNum, EnterDt)
VALUES (12353, '07/27/2010')
INSERT INTO dbo.#Temp (LoanNum, EnterDt)
VALUES (12354, '07/25/2010')
INSERT INTO dbo.#Temp (LoanNum, EnterDt)
VALUES (12356, '07/28/2010')
INSERT INTO dbo.#Temp (LoanNum, EnterDt)
VALUES (12357, '07/29/2010')
go

SELECT *
FROM #Temp
ORDER BY EnterDt ASC;
GO

-- Business Rules: Return all the rows within the last 3 business days include Sat as
business day and Exclude Sun.

-- Case1: Give today is 07/29 Thur. How can I get the desire results below.

LoanNum EnterDt
-------- -----------------------
12345 2010-07-26 00:00:00.000
12353 2010-07-27 00:00:00.000
12356 2010-07-28 00:00:00.000
12357 2010-07-29 00:00:00.000

-- Case2: Give today is 07/26 Mon. How can I get the desire results below.
LoanNum EnterDt
-------- -----------------------
12348 2010-07-22 00:00:00.000
12347 2010-07-23 00:00:00.000
12346 2010-07-24 00:00:00.000
12354 2010-07-25 00:00:00.000
12345 2010-07-26 00:00:00.000

-- It's working but wonder there is a better way to write this.
DECLARE @Day TINYINT
SET @Day = DATEPART(WEEKDAY, CURRENT_TIMESTAMP) -- Return weekday 2, 3, 4: Mon, Tue, Wed.

SELECT GETDATE() AS 'Today', @Day

IF (@Day IN (2, 3, 4)) -- Mon, Tue, Wed
BEGIN
SELECT *
FROM #Temp AS a
WHERE (CONVERT(CHAR(8), EnterDt, 112) >= CONVERT(CHAR(8), Dateadd(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -4), 112) -- substract 4 days.
AND CONVERT(CHAR(8), EnterDt, 112) <= CONVERT(CHAR(8), DATEADD(DAY, 0, {fn CURDATE()}), 112) ) -- return today.
ORDER BY EnterDt ASC
END
ELSE
BEGIN
SELECT *
FROM #Temp AS a
WHERE (CONVERT(CHAR(8), EnterDt, 112) >= CONVERT(CHAR(8), Dateadd(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -3), 112) -- substract 3 days.
AND CONVERT(CHAR(8), EnterDt, 112) <= CONVERT(CHAR(8), DATEADD(DAY, 0, {fn CURDATE()}), 112) ) -- return today.
ORDER BY EnterDt ASC
END;

Sachin.Nand

2937 Posts

Posted - 2010-07-29 : 00:58:57

Did you see my previous post?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=147844

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-07-29 : 11:07:31

First, thank you for helping but when I try your query with different days, it does not return anything.
ex:
declare @dt as datetime
select @dt= (GETDATE() - 2)
SELECT @dt

;with datecte
as
(

select ROW_NUMBER()over(order by (select 1))rid,
dateadd(dd,number,datediff(dd,7,@dt))as date
from master.dbo.spt_values where type='p'
and dateadd(dd,number,datediff(dd,7,@dt))<=@dt
--and datename(weekday,dateadd(dd,number,datediff(dd,7,@dt))) <>'Sunday'
)
,cte
as
(
select rid,date from datecte where date=@dt
)

select date from datecte where rid between(select rid-3 from cte)and (select rid from cte)

--end

quote:
Originally posted by Idera

Did you see my previous post?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=147844

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-07-30 : 00:35:28

quote:
Originally posted by NguyenL71

First, thank you for helping but when I try your query with different days, it does not return anything.
ex:
declare @dt as datetime
select @dt= (GETDATE() - 2)
SELECT @dt

;with datecte
as
(

select ROW_NUMBER()over(order by (select 1))rid,
dateadd(dd,number,datediff(dd,7,@dt))as date
from master.dbo.spt_values where type='p'
and dateadd(dd,number,datediff(dd,7,@dt))<=@dt
--and datename(weekday,dateadd(dd,number,datediff(dd,7,@dt))) <>'Sunday'
)
,cte
as
(
select rid,date from datecte where date=@dt
)

select date from datecte where rid between(select rid-3 from cte)and (select rid from cte)

--end

quote:
Originally posted by Idera

Did you see my previous post?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=147844

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Why are you using Getdate()????
Use constant dates.GetDate() has time part to.
So with my query it will never return anything.

Use constant like '29-July-2010' or instead of Getdate() use
dateadd(dd,datediff(dd,0,getdate()),0)

ex-select @dt= dateadd(dd,datediff(dd,0,getdate()),0)- or + whatever

Come on if you are not a newbie you are expected to know this things.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources