July 14, 2011 at 5:51 am
Hello
I have a query that it's built in runtime, i need to get the greatest date from a previous top query.
If i use the MAX function it returns the MAX date of the table if i use the TOP 1 it returns the correct data.
Example, i have one table Calendar with dates from 2000 to 2029, the i use this query
SELECT MAX(TB.Data)
FROM (SELECT TOP 7 Data FROM dbo.Calendar WHERE Hol=0 AND WeekDay=1 AND Data > GETDATE()) AS TB
It returns 2029-12-31 00:00:00, wrong date
If i use this one:
SELECT TOP 1 TB.Data
FROM (SELECT TOP 7 Data FROM dbo.Calendar WHERE Hol=0 AND WeekDay=1 AND Data > GETDATE()) AS TB
It returns 2011-07-15 00:00:00, correct date
Why the max function doesn't work?
Thanks
July 14, 2011 at 5:56 am
Actually july 15th is not the correct date either. You HAVE TO use order by with top to garantee the correct results.
SELECT MAX(TB.dt) AS dt
FROM (SELECT TOP 7 dt FROM dbo.Calendar C WHERE C.isHoliday = 0 AND C.isWeekday = 1 AND dt > GETDATE() ORDER BY dt) AS TB
GO
SELECT TOP 1 TB.dt
FROM (SELECT TOP 7 dt FROM dbo.Calendar C WHERE C.isHoliday = 0 AND C.isWeekday = 1 AND dt > GETDATE() ORDER BY dt) AS TB
ORDER BY dt DESC
GO
July 14, 2011 at 6:03 am
As a side note it'd be interesting to see if it's worth posting a connect item about this.
I personally think it isn't... because if you look at the plan you see that the plan is smart enough to deduce that the final query will get the max value from the top 7. So it doesn't even bother with getting 7 rows, it immediately goes to 1 row. Since there's no order by to act as an end date filter it just goes to the last value of the index which satisfies the max condition and makes for the fastest plan to return the data requested in the query. But since the query is incorrect then the results don't match what you expect.
July 14, 2011 at 7:07 am
Thanks for the quick replies.
I was testing the query, and i didn't bother to put the order by so i didn't realize that if i put the order by clause, the Max function works... I just looked to the year and saw the wrong value...
Always learning.
Thanks
July 14, 2011 at 9:32 am
Now i have another problem, i totally forgot that i can't call sp's inside functions...
I need to add X days to a date, this days must be working days, i have the calendar table ready, with weekends and holidays, what i was doing (it's in portuguese sorry):
CREATE FUNCTION AddWorkingDays
(
@dataSMALLDATETIME,
@DiasINT,
@TodayCounts BIT = 0
)
RETURNS SMALLDATETIME
AS
BEGIN
-- Declare the return variable here
DECLARE
@SqlStringNVARCHAR(MAX),
@SqlParametrosNVARCHAR(MAX),
@DataInicialSMALLDATETIME,
@DataDevolverSMALLDATETIME
IF @TodayCounts= 1
BEGIN
SET @DataInicial = DATEADD(DAY,-1,@Data)
END
ELSE
BEGIN
SET @DataInicial = @data
END
SET @SqlString = N'
SELECT @DataDevolverOUT = MAX(Data) AS Data
FROM (
SELECT TOP ' + CAST(@Dias AS NVARCHAR) + ' Data
FROM dbo.Calendario
WHERE Feriado=0 AND DiaSemana=1 AND Data > @DataInicialIN
ORDER BY Data
) AS TB'
SET @SqlParametros = N'
@DataInicialINSMALLDATETIME,
@DataDevolverOUTSMALLDATETIME OUTPUT'
EXECUTE sp_executesql
@SqlString, -- Define a query
@SqlParametros, -- Define os parametros
@DataInicialIN = @DataInicial, -- Parametro Entrada
@DataDevolverOUT = @DataDevolver OUTPUT -- Parametro Saida
-- Return the result of the function
RETURN @DataDevolver
END
GO
I need this as a function because it will be used inside other querys.
Can anyone give me some hints how to create this function, i'm out of ideas?
Thanks
July 14, 2011 at 9:40 am
DECLARE @Today DATETIME
SET @Today = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
DECLARE @AddBusDays INT
SET @AddBusDays = 9
SELECT MAX(dt) AS Nth_next_business_day FROM (
SELECT TOP (@AddBusDays) * FROM dbo.Calendar C WHERE C.dt > @Today AND C.IsBusDay = 1 ORDER BY C.dt) dtBD
--2011-07-27
July 14, 2011 at 9:41 am
P.S. Make sure you can't somehow only call this function only once. It's going to get expansive really fast even with cross apply.
July 14, 2011 at 9:45 am
You can use a variable in TOP in 2k8, not sure about 2k5:
DECLARE @RowsToReturn INT
SET @RowsToReturn = 50
SELECT TOP (@RowsToReturn) * FROM ...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 14, 2011 at 9:49 am
ChrisM@Work (7/14/2011)
You can use a variable in TOP in 2k8, not sure about 2k5:DECLARE @RowsToReturn INT
SET @RowsToReturn = 50
SELECT TOP (@RowsToReturn) * FROM ...
I'm on 2K5 and I tested on my own servers.
July 14, 2011 at 9:53 am
Ninja's_RGR'us (7/14/2011)
ChrisM@Work (7/14/2011)
You can use a variable in TOP in 2k8, not sure about 2k5:DECLARE @RowsToReturn INT
SET @RowsToReturn = 50
SELECT TOP (@RowsToReturn) * FROM ...
I'm on 2K5 and I tested on my own servers.
What's the verdict Ninja?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 14, 2011 at 9:57 am
ChrisM@Work (7/14/2011)
Ninja's_RGR'us (7/14/2011)
ChrisM@Work (7/14/2011)
You can use a variable in TOP in 2k8, not sure about 2k5:DECLARE @RowsToReturn INT
SET @RowsToReturn = 50
SELECT TOP (@RowsToReturn) * FROM ...
I'm on 2K5 and I tested on my own servers.
What's the verdict Ninja?
You might want to look up by 5-6 posts ;-).
July 14, 2011 at 10:00 am
Ninja's_RGR'us (7/14/2011)
ChrisM@Work (7/14/2011)
Ninja's_RGR'us (7/14/2011)
ChrisM@Work (7/14/2011)
You can use a variable in TOP in 2k8, not sure about 2k5:DECLARE @RowsToReturn INT
SET @RowsToReturn = 50
SELECT TOP (@RowsToReturn) * FROM ...
I'm on 2K5 and I tested on my own servers.
What's the verdict Ninja?
You might want to look up by 5-6 posts ;-).
D'oh! Timing. Sorry mate.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 14, 2011 at 10:06 am
Happens all the time. I should stop answering so fast. :w00t:
July 14, 2011 at 10:18 am
Thanks
I thought that i can't declare the top value as a variable, in the 2k it wasn't possible so once again i didn't try it... :blush:
July 14, 2011 at 10:31 am
rootfixxxer (7/14/2011)
ThanksI thought that i can't declare the top value as a variable, in the 2k it wasn't possible so once again i didn't try it... :blush:
Been there, done that and in reverse too (from 2000 with 1000s of man hours down to sql 7 for the first time. sql 2000 was a heck of a great upgrade :w00t:)
It's a nice read to pick up the marketing and tech overviews of the new features. No need to waste 40 hours on that. Just knowing that a,b, and c is now possible is good enough :w00t:.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply