How to solve TOP clause contains an invalid value

Question

I have a TOP clause in one of my SELECT statement. When I have data, I do not have error in my TOP clause. When I do not have data returned from my query, I received TOP clause contains an invalid value error.

How do I resolve this error?  The error I believe is in SELECT TOP(DATEDIFF(DAY,StartDt,EndDt)+1)

My sample query is below:

Thank you.

DECLARE @BeginDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @Status VARCHAR(MAX)
 
 
SET @BeginDate = '1-4-2017'
SET @EndDate = '11-4-2017'
SET @Status = 'Pending' 

;WITH CTE_AmountRequested_VL AS
(SELECT 
       vl.LoanID
       ,vl.AmountRequested 
              FROM VehicleLoans vl 
)
 
 
,CTE_FinalQuery AS
(
SELECT 
 t.ID, 
 t.LoanNumber,
 t.LoanStatus

FROM CTE_PrepareQuery t
CROSS APPLY (
SELECT TOP(DATEDIFF(DAY,StartDt,EndDt)+1) 
  [BusinessDate] = CAST(DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,CAST(StartDt AS DATE)) AS DATETIME)
FROM (VALUES (0),(0),(0),(0),(0),(0),(0)) d (n)
) x
CROSS APPLY (VALUES 
 (1, CAST('08:30' AS DATETIME), CAST('21:00' AS DATETIME)),
(2, '08:30', '21:00'),
(3, '08:30', '21:00'),
(4, '08:30', '21:00'),
(5, '08:30', '21:00'),
(6, '08:30', '21:00'),
(7, '11:00', '19:00') -- Sunday
) y ([DayOfWeek], BusinessDayStartHour, BusinessDayFinishHour)
CROSS APPLY (
SELECT 
  ActualDayStart = BusinessDate + CASE WHEN BusinessDate = CAST(StartDt AS DATE) THEN CAST(CAST(StartDt AS TIME(0)) AS DATETIME) ELSE BusinessDayStartHour END,
  ActualDayFinish = BusinessDate + CASE WHEN BusinessDate = CAST(EndDt AS DATE) THEN CAST(CAST(EndDt AS TIME(0)) AS DATETIME) ELSE BusinessDayFinishHour END
) z
 
WHERE y.[DayOfWeek] = 1+DATEDIFF(DAY,0,BusinessDate)%7 -- settings-agnostic day of week matching
 
GROUP BY 
ID, 
LoanNumber,

 
)
 
 
SELECT
ID
,LoanNumber
,LoanStatus
,LoanType
,StartDt
,EndDt
,AmountRequested
,TotalDisbursedAmount
,loanofficer = ISNULL(loanofficer,'UNCLAIMED')
,MinutesWorked
,FinalHoursWorked = (MinutesWorked/60)
,FinalHoursWorked = REPLACE(STR((MinutesWorked/60),2),' ','0')
,FinalMinutesWorked = MinutesWorked%60 

       FROM CTE_FinalQuery
 
 
ORDER BY 
ID, 
LoanNumber

Answer 1

Is this the full query?

I cant see the definition of CTE_PrepareQuery   in it

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 2

Hi Garin,

Since you are using DATEDIFF() function in TOP clause, the values in StartDt and EndDt should always be date type data. And as you said there will be null values. For sure, the TOP clause will return an error.

I can reproduce your issue, please refer to following sample.

create table #test
(
col int,
StartDt date,
EndDt date
)

insert into #test values
(1,null,null)

select *
from #test

cross apply ( select TOP(DATEDIFF(DAY,StartDt,EndDt)+1) col from #test) t

drop table #test

I think you should consider of the situation about Null values in StartDt and EndDt. Maybe when StartDt and EndDt are Null, you can give them a specific value. Like:

select *
from #test
cross apply ( select TOP(DATEDIFF(DAY,Cast(case when StartDt is null then '1900-01-01' else StartDt end as date),Cast(case when EndDt is null then '1900-01-01' else EndDt end as date))+1) col from #test) t

Thanks,
Xi Jin.

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

Maybe this?

TOP(isnull(DATEDIFF(DAY,StartDt,EndDt), 0) +1) 
It depends on what you want to happen when any of StartDt and EndDt is null.

---

Erland Sommarskog, SQL Server MVP, [e-mail]