Complex SQL Query for Inventory. Need Help

chichocojo

Hi, I need some guidance on the following scenario: I need to create a historic inventory of number of rooms sold per hotel. A transactional table stores data in the following columns IdHotel, CheckInDate, CheckOutDate, NoRooms I need to generate a table with 365 records/days per year per hotel as follows: hotel1, 01/Jan/2014, 1 room hotel1,02/Jan/2014,2 rooms hotel1,03/Jan/2014,2 rooms hotel1,04/Jan/2014,0 rooms . . . . . . hotel1, 01/Nov/2014, 1 room hotel1, 02/Nov/2014, 1 room hotel1, 03/Nov/2014, 1 room . . . . . I am a bit lost as to how to approach this problem for a solution. I do not need the actual code. Just a hint on how to solve this problem.

Mycroft Holmes

Select hotel,date,sum(no rooms )from table where datepart(yy,datefied)=2013 group by( hotel,date) That should give you some ideas, get a book on tsql and work through it,you will find it invaluable .

Never underestimate the power of human stupidity RAH

khun_panya

I have some quite dirty solution 1. Create a dummy table of an int column stores 1...365 2. Select DATEADD 1/1/2014 with an int from table above. You'll get all "dates" in 2014. 3. Select COUNT on your transaction GROUP BY date from (2). If you want to count how many rooms had been sold each day, you might need to count by matching CheckInDate. If you want to count how many rooms were occupied each day, you might need to count by seeing if the "date" within CheckInDate & CheckOutDate.

King Fisher

so,the hotel name is same ?

Richard Deeming

chichocojo wrote:

I need to generate a table with 365 records/days per year per hotel as follows:

You do realise than some years have 366 days, right? :) You can generate a list of all days in a given year by using a tally-table (or a common table expression which generates a tally table), along with the DateAdd function:

DECLARE @Yearint = 2014;

-- Get 1st January in the specified year:
DECLARE @StartDate date = Convert(date, Convert(char(4), @Year + '0101');

WITH cte1 (N) As
(
SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
-- 8 rows
),
cte2 (N) As
(
SELECT TOP 366
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
FROM
cte1 As A
CROSS JOIN cte1 As B
CROSS JOIN cte1 As C
-- 8 * 8 * 8 = 512 rows
),
cteDates (Value) As
(
SELECT
DateAdd(day, N, @StartDate)
FROM
cte2 -- 366 rows
WHERE
-- If this isn't a leap-year, we only need 355 rows:
Year(DateAdd(day, N, @StartDate)) = @Year
)
SELECT
Value
FROM
cteDates
ORDER BY
Value
;

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer