TOP Operator using a local variable
-
Hi all,
Is there any way that I can use the local variable with a TOP operator like
select top @CT (orders) from orders
Thanks for any help.
-
SET ROWCOUNT @Variable
SELECT * FROM authors
ORDER BY au_lname, au_id
SET ROWCOUNT 0
-
Hi Remi, Iam trying to create this function but Iam not sure that I can use the set rowcount here.
CREATE FUNCTION DBO.BUSINESS_DATEADD (@STARTDATE DATETIME, @DAYS BIGINT )
RETURNS DATETIME
AS
BEGIN
DECLARE @ENDDATE DATETIME
SET @ENDDATE = (SELECT TOP 1 BDATE FROM
(SELECT TOP @DAYS(BDATE) FROM NAT_BUSINESSCALENDAR
WHERE BDATE > CONVERT(VARCHAR(10),@STARTDATE,101) AND BUSINESS_DAY = 1
ORDER BY BDATE)TT
ORDER BY BDATE DESC)
RETURN @ENDDATE
END
GO
-
Why can't you use a SP?
Why not select * order by??
-
I'm a little confused - why are you selecting "top 1" from a "top n" - guess that's what remi meant by directly using a "select top n....order by..." ?!?!
btw - surya - how did you know that RGR'us is remi - was his disguise not good enough ?!
**ASCII stupid question, get a stupid ANSI !!!** -
Hi Sushila,
I was away from SSC.com for some time. When the time I'm back, I didn't find any forums with Remi's answers but RGR'USs, checked his profile, yes he is none other than Remi.
Ok, Sushila, this is the table I have. I need to pass a StartDate parameter and count of businessdays.I am trying to write a function to get the enddate. Suppose if I supply '1980-01-01 00:00:00.000 ' as start date and 5 as the count of businessdays, then I should get '1980-01-08 00:00:00.000 '. Can you help me to find the logic!
BDate BUSINESS_DAY
--------------------------------------- ------------ ----------- ---
1980-01-01 00:00:00.000 1
1980-01-02 00:00:00.000 1
1980-01-03 00:00:00.000 1
1980-01-04 00:00:00.000 1
1980-01-05 00:00:00.000 0
1980-01-06 00:00:00.000 0
1980-01-07 00:00:00.000 1
1980-01-08 00:00:00.000 1
1980-01-09 00:00:00.000 1
-
DECLARE @Table table (Date smalldatetime not null primary key clustered, BD bit not null)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-01', 1)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-02', 1)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-03', 1)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-04', 1)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-05', 0)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-06', 0)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-07', 1)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-08', 1)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-09', 1)
DECLARE @BusinessDays AS INT
SET @BusinessDays = 5
Select T1.Date, COUNT(*) AS Row from @Table T1 INNER JOIN @Table T2 ON T2.Date <=T1.Date AND T1.BD = CAST(1 AS BIT) AND T2.BD = CAST(1 AS BIT) GROUP BY T1.Date HAVING COUNT(*) <= @BusinessDays ORDER BY T1.Date
-
Thanks Remi, u rock as usual.
-
How did you figure out my name really??? I don't have anything in my profile
. -
no body else has a post count >6000 in SSC.com remi! and also by observing your pace and by seeing ur previous posts.
-
Yes there are. Steve has 6600+ posts and antares is in the high 6K too.
Well I guess I can't really hide in here anymore
. -
why do you want to hide anyway ?! btw - remember how good of a detective I am - I've already "unearthed" 2 "hiders"....
**ASCII stupid question, get a stupid ANSI !!!** -
Just like a little discretion on the web... not wanna completly disappear
. -
after the detective work and name calling
, back to work.How's about:
Declare @d datetime
Declare @businessDays int
Declare @cnt int
set @d = '2004/09/18' -- start date
set @BusinessDays = 7 -- num days
set @cnt = 0
while @cnt < @BusinessDays
Begin
if Not DatePart(dw, @d) in (6, 7) -- careful with Set DateFirst
set @cnt = @cnt + 1
if @cnt < @BusinessDays
set @d = DateAdd(d, 1, @d)
End
print @d
No tables, no SQL - looks like unemployment looming large
Best Regards,Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke) -
Your employement or mine???
Run this 1000 times with set @BusinessDays = 10.
My version will be done along with my morning coffee before yours is over (ok, slight exageration).
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply