Find people on board based on check-in and check-out date using sql query

Transact-SQL
1

Hi, I have a data with check-in and check-out date of different personnel who stayed in a guest house.

Table is something similar to this :

This was my try but it didn't work :
DECLARE @DateFrom datetime, @DateTo datetime;
SET @DateFrom='20170201';
SET @DateTo='20170228';

WITH T(date)
AS
(
SELECT @DateFrom
UNION ALL
SELECT DateAdd(day,1,T.date) FROM T WHERE T.date < @DateTo
)
SELECT CONVERT(VARCHAR(10),t.date,10), a.nationality, count (*) FROM table a
left join T on convert(varchar(10),t.date,10) = convert(varchar(10),a.[check in date],10)
where convert(varchar(10),a.[check in date],10) <= convert(varchar(10),t.date,10)
and convert(varchar(10),a.[check out date],10) > convert(varchar(10),t.date,10)
group by t.date, a.nationality
order by t.date

2

Use PIVOT maybe?

convert(varchar(10),t.date,10) = convert(varchar(10),a.[check in date],10)

I recommend you don't compare dates in this way, its very inefficient and not SARGable - indexes won't be used - so the query will not scale well.

3

Thanks Kristen.

I know I have to use pivot table. But first I'm trying to figure out the main query to get the Persons on Board.

4

OK, I saw your "Expected Output" and assumed that your query was trying to get "Personals on Board" too. Actually all you are trying to do is get a list of dates (including missing dates presumably) and then find rows that match those dates.

To get the relevant data rows the best performing query is likely to be:
``sql
WHERE (a.[check in date] >= @ReportStartDate
AND a.[check in date] < @ReportEndDatePlusOneDay)
OR
(a.check out date] >= @ReportStartDate
AND a.[check out date] < @ReportEndDatePlusOneDay)
OR
(a.check in date] < @ReportStartDate
AND a.[check out date] >= @ReportEndDatePlusOneDay)

It might turn out to be faster to UNION ALL three queries than have the AND / OR combination in a single query.

@ReportEndDatePlusOneDay needs to be midnight (i.e. time portion = 00:00:00) on the day after the last day to be included in the report, e.g.
```sql
SELECT @ReportEndDatePlusOneDay = DATEADD(Day, DATEDIFF(Day, 0, @ReportEndDate), 0)

As a separate issue you maybe? want all possible dates (including ones for which there is no actual data), to generate one-row-per-date, in which case I would use a NUMBERS / TALLY table (or an equivalent User Defined function that generates them on-the-fly). Google should find both solutions.

If you need to "round" a date in the SELECT then rather then converting to string (which is both slow and will pass a string-value to APP meaning it cannot be processed as a date for sorting / formatting . etc.) then this will remove the TIME component. You could also do that by CASTing to DATE

SELECT DATEADD(Day, DATEDIFF(Day, 0, [YourDateColumn]), 0)

as this only uses integer maths it is very efficient.

Important to avoid any form of data manipulation (functions etc.) in the WHERE clause because it will not be SARGable and will prevent SQL using indexes etc. to choose the best performing way of locating the data.

1 Like