Why does my SQL query not have any columns?

I’m not an expert by any means but I write SQL queries fairly regularly.

I am pulling data via Crystal Reports 2013 from SQL Server 2016 (not positive about the version). Just as an aside, CR permits parameters so that’s what {?DateStart} is. {?DateStart} and {?DateEnd} are regular dates, {?ServiceProvider} is a number array. The EXEC sw_vwDateRange generates a table that has a row for every date in between {?DateStart} and {?DateEnd}.

I had this query that worked: (Edited 2021-01-19 14:28 EST to simplify/improve query)

DECLARE @Result Table (CurrentDateTime datetime)

INSERT @Result EXEC sp_vwDateRange {?DateStart}, {?DateEnd}
SELECT * FROM @Result
    
CROSS APPLY
(
SELECT Count(DISTINCT ClientID) AS "Clients",
    Count(DISTINCT RoomID) AS "RoomCount"
FROM vw_Stays 
WHERE vw_Stays.BedDateStart <= DATEADD(day,1,CurrentDateTime)
    AND (vw_Stays.BedDateEnd IS NULL OR vw_Stays.BedDateEnd > DATEADD(day,1,CurrentDateTime)) 
    AND vw_Stays.OrganizationID IN {?ServiceProvider}
) t2

I was getting some inefficiencies because I was pulling data from a non-indexed view, vw_Stays, so I replaced it with references to the original tables.

DECLARE @Result Table (CurrentDateTime datetime)

INSERT @Result EXEC sp_vwDateRange {?DateStart}, {?DateEnd}
SELECT * FROM @Result
    
CROSS APPLY
(
SELECT Count(DISTINCT HIFIS_Client_Services.ClientID) AS "Clients",
    Count(DISTINCT HIFIS_StaysRoomsBedsHistory.RoomID) AS "RoomCount"
FROM HIFIS_Stays
    INNER JOIN HIFIS_Services ON HIFIS_Stays.ServiceID = HIFIS_Services.ServiceID
    INNER JOIN HIFIS_Client_Services ON HIFIS_Services.ServiceID = HIFIS_Client_Services.ServiceID
    INNER JOIN HIFIS_StaysRoomsBedsHistory ON HIFIS_Stays.StayID = HIFIS_StaysRoomsBedsHistory.StayID
    
WHERE HIFIS_StaysRoomsBedsHistory.BedDateStart <= DATEADD(day,1,CurrentDateTime) 
    AND (HIFIS_StaysRoomsBedsHistory.BedDateEnd IS NULL OR HIFIS_StaysRoomsBedsHistory.BedDateEnd > DATEADD(day,1,CurrentDateTime)) 
    AND HIFIS_Services.OrganizationID IN {?ServiceProvider}
) t2

But it stopped working entirely. It doesn’t give me any errors, it just completes the query with no columns and no data.

Sorry if this has already been answered but I really don’t know what search terminology to use!

Answer

Wow, so, this is embarrassing, but turns out I spelled the name of one of the tables wrong. (wrong number of s’s in HIFIS_StaysRoomsBedsHistory.) I’ll just go crawl away and hide now. But I learned a lot in troubleshooting this! Thanks for the info about CROSS APPLY, I will put that to good use.

Advertisement

Answer