August 31, 2017 at 7:56 am
I have a difficult sql calculation to make, and I dont't find the answer. Can someone help me?
I have a table with bookings of equipment. Some booking have an equipment item assigned, others not yet. I want to calculate for any given period of time the number of available items. With the code here supplied I achieve a good result but in some cases, it is not correct.
In the ngiven sample I want to check the available item count for the period of 20170803 till 20170806. My code gives a result of 5 available items but that is not correct. Because the bookings in row 6 and 7 don't overlap we could assign item 3 in both bookings, and assign item 4 in row 8. That way we only use 4 items, so the query result should give 6, not 5.
Help really appreciated... thank you!
declare @t table (xRow int, xItem int, xStart smalldatetime, xEnd smalldatetime)
--sample data
insert into @t (xRow, xItem, xStart, xEnd)
values (1, 1, '20170801', '20170804')
, (2, 1, '20170804', '20170807')
, (3, 1, '20170807', '20170810')
, (4, 2, '20170804', '20170807')
, (5, 2, '20170807', '20170810')
, (6, 0, '20170801', '20170804')
, (7, 0, '20170804', '20170807')
, (8, 0, '20170804', '20170807')
--total count of items in stock
declare @total_items int=10
--set the period we want to check
declare @check_start smalldatetime='20170803'
declare @check_end smalldatetime='20170806'
-- 2017 08 1----2----3----4----5----6----7----8----9----10
-- row 1 [----item 1----]
-- row 2 [----item 1----]
-- row 3 [----item 1----]
-- row 4 [----item 2----]
-- row 5 [----item 2----]
-- row 6 [---no item----]
-- row 7 [---no item----]
-- row 8 [---no item----]
-- check [-----check----]
--get the number of booked items, where item>0
declare @booked int=(select count(distinct xItem) from @t
where xStart<@check_end and xEnd>@check_start
and xItem>0)
+
--add bookings to be asigned, where item=0
(select count(*) from @t
where xStart<@check_end and xEnd>@check_start
and xItem=0)
--show result: total item count (10) minus booked items
declare @r varchar(max)='Available items from ' + format(@check_start, 'd/M/yy')
+ ' till ' + format(@check_end-1, 'd/M/yy')
+ ' = ' + cast(@total_items-@booked as varchar(50))
select @r
August 31, 2017 at 8:11 am
Interesting question, but I have to ask what the meaning of "number of items available" is, given any period of time longer than 1 day. During your period of 8/3 to 8/6, it starts out as appearing to actually be 9 on 8/3, and then 8 for the remainder of the period. Thus, given that you were expecting 6, I'm fairly certain that I don't understand what you actually mean. Perhaps it's the number of available booking slots remaining? In order to come to that conclusion, however, I'd have to assume that a booking slot is of a fixed duration, and while your data appears to support that, it's still an assumption. Please clarify, and please explain in as much detail as possible exactly what you want. At the moment, the only information I have is that your table represents bookings, but for some strange reason appears to also include records that don't represent bookings of an item, so I'm a little confused as to what those records are supposed to represent.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 31, 2017 at 8:47 am
The goal is to determine is a user can make a reservation for equipment for a certain period, based on the availability of items.
Some bookings (rows in the sample) have the item already assigned, others not (item=0).
In my sample the user queries for the possibility to make a booking from AUG 3 till AUG 6. The query must calculate if there are enough items to accept the booking. The item must of course be avaiable for the entitre period of the booking. In the sample, I could assign item 3 both on rows 7 and 8, because they do not overlap. Then on row 8 i could assign item 4. So the query should return that on the given period there are 6 items (10-4) still available, and the booking can be accepted.
Is this clear?
August 31, 2017 at 8:57 am
I could also assign item 2 on row 6, item 3 on row 7, and item 4 on row 8. But that gives the same result, 4 booked items, so 6 still available...
August 31, 2017 at 9:07 am
marc.corbeel - Thursday, August 31, 2017 8:47 AMThe goal is to determine is a user can make a reservation for equipment for a certain period, based on the availability of items.
Some bookings (rows in the sample) have the item already assigned, others not (item=0).In my sample the user queries for the possibility to make a booking from AUG 3 till AUG 6. The query must calculate if there are enough items to accept the booking. The item must of course be avaiable for the entitre period of the booking. In the sample, I could assign item 3 both on rows 7 and 8, because they do not overlap. Then on row 8 i could assign item 4. So the query should return that on the given period there are 6 items (10-4) still available, and the booking can be accepted.
Is this clear?
Okay, so what is the significance of the individual rows? As there are only 8 rows, and 10 items, I'm at a loss to understand why your data is structured this way. I'm asking because if there are 10 pieces of equipment, but only 8 locations in which they can be used, then that's a somewhat different scenario than having no tie to a particular location.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 31, 2017 at 9:14 am
okay, in my sample there are only 8 rows (or bookings) but of course in real life this will be much more. If you want to make it more clear you could set the @total_items to 4. That is also exactly how many items we need to complete the 8 rows... 1 & 2 as indicated, item 3 on rows 6 and 7, and item 4 on row 8. The query should return that there are no more items available, because all 4 available items will be used.
Sorry if still not clear...
August 31, 2017 at 10:02 am
See if this is going in right direction...IF OBJECT_ID('tempdb..#EquipmentBooking', 'U') IS NOT NULL
DROP TABLE #EquipmentBooking;
CREATE TABLE #EquipmentBooking (
xRow INT NOT NULL PRIMARY KEY CLUSTERED,
xItem INT NOT NULL,
xStart DATE NOT NULL,
xEnd DATE NOT NULL
);
INSERT #EquipmentBooking(xRow, xItem, xStart, xEnd) values
(1, 1, '20170801', '20170804'),
(2, 1, '20170804', '20170807'),
(3, 1, '20170807', '20170810'),
(4, 2, '20170804', '20170807'),
(5, 2, '20170807', '20170810'),
(6, 0, '20170801', '20170804'),
(7, 0, '20170804', '20170807'),
(8, 0, '20170804', '20170807');
IF OBJECT_ID('tempdb..#Item', 'U') IS NOT NULL
DROP TABLE #Item;
CREATE TABLE #Item (
xItem INT NOT NULL PRIMARY KEY CLUSTERED,
ItemDescription VARCHAR(20) NOT NULL
);
INSERT #Item(xItem, ItemDescription)
SELECT DISTINCT
eb.xItem, CONCAT('Rental Item ' , eb.xItem)
FROM #EquipmentBooking eb
WHERE eb.xItem > 0
--SELECT * FROM #EquipmentBooking eb;
--SELECT * FROM #Item i;
--=====================================================
SELECT
eb0.xRow, eb0.xItem, eb0.xStart, eb0.xEnd,
i.xItem, i.ItemDescription
FROM
#Item i
CROSS JOIN #EquipmentBooking eb0
WHERE
eb0.xItem = 0
AND NOT EXISTS (
SELECT
*
FROM
#EquipmentBooking eb
WHERE
i.xItem = eb.xItem
AND (
(eb0.xStart >= eb.xStart AND eb0.xStart < eb.xEnd)
OR
(eb0.xEnd > eb.xStart AND eb0.xEnd <= eb.xEnd)
)
);
Results...
| xRow | xItem | xStart | xEnd | xItem | ItemDescription |
|---|---|---|---|---|---|
| 6 | 0 | 2017-08-01 | 2017-08-04 | 2 | Rental Item 2 |
September 1, 2017 at 6:37 am
Can you clarify what the result of the query means?
I just need to know if there are enough items to assign to the bookings. I dont't see immediately how this result makes me do that?
September 1, 2017 at 7:53 am
The more I examine my issue, the more complex it gets and i am starting to think there is no solution for this.
Here I have 8 rows, each row is a booking. In rows 1 to 5 there are items assigned. In rows 6 to 8 i still need to assign items.
Same items of course cannot overlap, as they are unique.
When assigning items as a smart planner, i assign item 2 to row 6 (it is the first item that is available in that period), and item 3 to rows 7 and row 8.
This means I need 3 items to complete my 8 bookings.
However when I am a bad planner and I assign item 3 to row 6 in stead of item 2, item 4 on row 7 and item 5 to row 8, i need a total of 5 items.
This means that in my opinion there is no way in determine upfront how many items i will need, as it depends on how they will be assigned...
Any thought on this?
2018 08 1----2----3----4----5----6----7----8----9----10---
row 1: [--- item 1 ---]
row 2: [--- item 1 ---]
row 3: [--- item 1 ---]
row 4: [--- item 2 ---]
row 5: [--- item 2 ---]
row 6: [--- item x ---]
row 7: [--- item x ---]
row 8: [--- item x ---]
September 7, 2017 at 2:41 am
This logic will automatically assign the next item so you dont have to worry about being a bad planner
declare @bookings table(bookingID int, start_date date, end_date date, itemID int)
declare @items table( id int, name varchar(50))
Insert into @items values (1,'Item1'),
(2,'Item2'),
(3,'Item3'),
(4,'Item4'),
(5,'Item5'),
(6,'Item6'),
(7,'Item7'),
(8,'Item8'),
(9,'Item9'),
(10,'Item10')
Insert into @bookings values (1,'20170801', '20170804',1),
(2,'20170804', '20170807',1),
(3, '20170807', '20170810', 1),
(4, '20170804', '20170807',2),
(5, '20170807', '20170810',2),
(6, '20170801', '20170804',2),
(7,'20170804', '20170807',null),
(8, '20170804', '20170807',null)
-- Show the table
select *
from @bookings b
LEFT OUTER JOIN @items i on b.itemID = i.ID
--Lets assign an item automatically for the next booking with no item assigned yet
DECLARE @BookingID int
DECLARE @NextItemID int
-- Gett the next booking with no item
select @BookingID = (select top 1 bookingID FROM @bookings WHERE itemID is null order by bookingID)
--select * from @bookings where bookingID = @BookingID
-- Find the next available item that is not being used already within the booking period
SELECT @NextItemID = MIN(i.id)
FROM @items i
WHERE i.id > ISNULL((
select MAX(i.ID)
from @bookings AllBookingsWithItems
INNER JOIN @items i on AllBookingsWithItems.itemID = i.ID
INNER JOIN @bookings MyBooking on MyBooking.bookingID = @BookingID
AND AllBookingsWithItems.start_date < MyBooking.end_date
AND AllBookingsWithItems.end_date > MyBooking.start_date
),0)
-- Update the booking with the available item
update @bookings set itemID = @NextItemID where bookingID = @BookingID
-- Show the updated table
select *
from @bookings b
LEFT OUTER JOIN @items i on b.itemID = i.ID
September 9, 2017 at 12:58 am
Thank you very much.
Will test it and keep you posted...
September 10, 2017 at 8:11 pm
burfos - Thursday, September 7, 2017 2:41 AMAND AllBookingsWithItems.start_date < MyBooking.end_date
AND AllBookingsWithItems.end_date > MyBooking.start_date
[/code]
+1 to that!.
@marc.corbeel , please see the following article on how that simple bit of logic solves to very much.
http://www.sqlservercentral.com/articles/T-SQL/105968/
--Jeff Moden
Change is inevitable... Change for the better is not.
September 11, 2017 at 2:36 am
declare @t table (xRow int, xItem int, xStart smalldatetime, xEnd smalldatetime)
September 12, 2017 at 11:09 am
For me this looks like a problem of getting the days(s) with most items booked. Getting all days in a period is easy with a CTE, from there you can simply count the items booked on the day, and get the maximum number of items booked on a d day:
\
--sample data
DECLARE@Bookings TABLE (xRow int, xItem int, xStart smalldatetime, xEnd smalldatetime)
INSERT INTO@Bookings (xRow, xItem, xStart, xEnd)
VALUES
(1, 1, '20170801', '20170804')
, (2, 1, '20170804', '20170807')
, (3, 1, '20170807', '20170810')
, (4, 2, '20170804', '20170807')
, (5, 2, '20170807', '20170810')
, (6, 0, '20170801', '20170804')
, (7, 0, '20170804', '20170807')
, (8, 0, '20170804', '20170807')
SELECT * FROM@Bookings
--set theperiod we want to check
DECLARE
@check_start smalldatetime='20170803'
,@check_end smalldatetime='20170807';
-- Statement to get the most bookings on any one day:
WITHPeriodDays AS(
SELECT@check_start AS PeriodDay
UNION ALL
SELECTPeriodDay + 1
FROMPeriodDays
WHEREPeriodDay < @check_end
)
SELECT COALESCE(MAX(TotalBooked), 0)
FROM
(
SELECTPeriodDay, COUNT(xRow) AS TotalBooked
FROMPeriodDays
INNER JOIN @Bookings
ON xStart <= PeriodDay
AND xEnd >= PeriodDay
GROUP BYPeriodDay
) AS Booked
[/code]
There is no attempt to find out if any one item is not reserved on all days in the period, this is simple to add.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply