Avatar billede Mik2000 Professor
27. juli 2017 - 00:28 Der er 13 kommentarer

SQL - svær forspørgsel

Hej

Håber der er en der er stærk i SQL som har mod på en svær en :)
---------------------

Jeg har følgende tabeller i et mini booking system
Alle dates er timestamp

---------------------

Ressourcer
En form for kategori til units (units er det man kan booke)

Units
De enheder man kan booke

Price:
Prisen på hele "kategorien" (dvs. alle units tilhørende den) hvor man kan oprette flere så den kan være forskellige i perioder. Kun i perioder der er indsat her kan man booke den. Dvs. er der ikke sat noget i april f.eks. så kan den ikke bookes der

Reservation
De reservationer der er lavet

----------------------

Jeg vil gerne hente alle de units (bruName) samt ressourcens navn (brName) på alle de ledige units
Dvs. den skal:
- Tjekke tjekke om startdato ligger inden for en startdato der er oprettet i price tabellen
- Tjekke om slutdato ligger inden for en dato der er oprettet i price tabellen
- Tjekke om de mellemlæggende dage er i perioder der er oprettet i pricetabel
- Tjekke om de enkelte units er booket hele eller dele af den valgte periode ved at kigge i reservationstabellen

Der skal måske bruges flere forspørgsler - men håber der er en der er stærk til SQL der kan knække den :)

----------------------
TABELOPBYGNING
----------------------
b_resources:
----------------------
brId
brName
...

----------------------
b_resource_units
----------------------
bruId
bruResourcesId
bruName
...

----------------------
b_resource_price
----------------------
brpId
brpResourceId
brpDateFrom
brpDateTo
brpPrice
....

----------------------
b_reservation
----------------------
bresId
bresName
bresResourceId (behøves nok ikke)
bresUnitId
bresBookedFrom
bresBookedTo
....
Avatar billede Slettet bruger
27. juli 2017 - 02:36 #1
tag det fra bunden af:

select * from tabelnavn WHERE dit AND dat er tilfældet IF noget andet er tilfældet AND visse kriterier er ok

det er mere eller mindre hvad der skal skrives..
så det skal bare oversættes, du kan bare gøre det fra en ende af
nøgleorden er WHERE AND IF AND og ='nogetbestemt'

?

der kommer sikkert en anden og giver dig den totale løsning.. :)
Avatar billede acore Ekspert
27. juli 2017 - 13:05 #2
#1: Et generisk svar, der kan bruges til en del andre spørgsmål her om sql, men som ikke så passer godt på dette problem, der ikke er helt trivielt.

Det er forholdsvist enkelt, at finde units, der er ledige. Det ville også være nemt at sikre, at en unit er åben (=der er price), hvis ikke det var fordi der kan være intervaller, der støder op til hinanden. Det er det, der er omfattet af dit punkt "Tjekke om de mellemlæggende dage er i perioder der er oprettet i pricetabel".

Er det en mulighed at tilføje en tabel med:

----------------------
b_resource_closed
----------------------
brcId
brcResourceId
brcDateFrom
brcDateTo

og så bruge den i stedet for price-tabellen? (det kan også være en b_resource_open tabel).

Alternativt skal der laves et query, der laver ovenstående tabel fra b_resource_price. Det er sikkert muligt, men ville lige lufte den anden mulighed først.
Avatar billede Mik2000 Professor
27. juli 2017 - 15:19 #3
acore - lige netop problemet med intervallerne :)

Jeg har sikret perioder ikke kan ligge oven i hinanden i price (åben) tabellen

Kunne være en løsning med en lukket tabel, meen som jeg ser det giver det nogle andre problemer
Rent brugervenligheds mæssigt vil det blive lidt noget rod. Så skal brugeren jo huske at tilføje alle perioder der ikke kan bookes til den. Og hvis de så skal kunne booke på et nyt tidspunkt skal der slettes noget fra den inden det kan oprettes i den anden (måske kun nogle dage midt i perioder)

Så hvis det andet kunne lade sig gøre hvor man kun har åben tabel, så vil det helt sikkert være at foretrække.
Håber det lige er til at gennemskue :)
På forhånd tak for hjælpen :)
Avatar billede acore Ekspert
27. juli 2017 - 15:28 #4
Åben tabel er også ok - problemet er, du du nu kan have noget i stil med

Id ResourceId DateFrom DateTo        Price
1 1                  27-7-2017 28-7-2017  20
2 1                  28-7-2017 30-7-2017  25

(bruger datoer isf timestamp for læsbarhedens skyld)

Problemet er, at der er åbent fra 27-7 til 30-7 kontinuert, men prisen stiger undervejs. Den tabel er svær at bruge, så i stedet vil jeg have den erstattet med

Id ResourceId DateFrom DateTo
1 1                  27-7-2017 30-7-2017

altså uden pris og i en separat tabel.

Uden at kende din business-logic er det svært at vurdere fordele/ulemper/muligheder - derfor spurgte jeg.

Jeg tror godt, at tabellen kan laves med en query - har bare ikke lige mulighed for at kigge nærmere på det lige nu.
Avatar billede Mik2000 Professor
27. juli 2017 - 18:35 #5
Ja du skal endelig også bare spørge - havde slet ikke tænkt over det - var bare mine tanker lige der :)

Man kunne sagtens tage prisen ud

Så hvis man i første omgang gjorde det, og så bare skulle have ud om de kunne lejes (ligger i perioder der er oprettet, og ikke har en bookning i forvejen) - vil du have tid til at hjælpe med det :)
Avatar billede acore Ekspert
28. juli 2017 - 23:26 #6
Hvis du ikke har den problemstilling, jeg har nævnt i #4, og du vil finde ledige units for en booking fra @from til @to, så prøv

SET @from=xxx, @to=yyy;
SELECT * FROM
(
  SELECT bruId, bruName, brId, brName FROM b_resource_price AS p
  LEFT JOIN b_resource_units AS u ON p.brpResourceId=u.bruResourcesId
  LEFT JOIN b_resources AS r ON p.brpResourceId=r.brId
  LEFT JOIN
  (
    SELECT * FROM b_reservation AS b
    WHERE
    (@from < bresBookedFrom AND @to > bresBookedFrom) OR
    (@from < bresBookedTo AND @to > bresBookedTo)
  ) AS bb ON bb.bresUnitId=bruId
  WHERE
    @from >= brpDateFrom AND @from <= brpDateTo AND @to >= brpDateFrom AND @to <= brpDateTo
) AS f
WHERE bresId IS NULL
Avatar billede Mik2000 Professor
31. juli 2017 - 15:21 #7
Hej - tak fordi du gider hjælpe - er jeg glad for :)

Den får en
#1054 - Unknown column 'bresId' in 'where clause'

Jeg prøvede at sætte den ind i denne
SELECT bresId, bruId, bruName, brId, brName FROM b_resource_price AS p
Men jeg får tom ud uanset hvilke datoer der søges
Avatar billede acore Ekspert
01. august 2017 - 08:02 #8
Undskyld - min fejl! Det var den forkerte udgave - en af dem, der ikke virkede. Det er ikke sådan en, jeg lige kan lave uden test. Så jeg har brugt http://sqlfiddle.com/.

Hvis du derinde sætter det her ind i boxen til venstre og trykker "Build schema":

CREATE TABLE IF NOT EXISTS `b_resources` (
  `brId` int(6) unsigned NOT NULL,
  `brName` varchar(20) NOT NULL,
  PRIMARY KEY (`brId`)
) DEFAULT CHARSET=utf8;
INSERT INTO `b_resources` (`brId`, `brName`) VALUES
  ('1', 'tables'),
  ('2', 'chairs'),
  ('3', 'lamps');

CREATE TABLE IF NOT EXISTS `b_resource_units`(
  `bruId` int(6) unsigned NOT NULL,
  `bruResourcesId` int(6) unsigned not null,
  `bruName` varchar(20) NOT NULL,
  PRIMARY KEY (`bruId`)
) DEFAULT CHARSET=utf8;
INSERT INTO `b_resource_units` (`bruId`, `bruResourcesId`, `bruName`) VALUES
  ('1', '1', 'chair1'),
  ('2', '1', 'chair2'),
  ('3', '1', 'chair3'),
  ('4', '2', 'table1'),
  ('5', '2', 'table2'),
  ('6', '2', 'table3'),
  ('7', '3', 'lamp1'),
  ('8', '3', 'lamp2');


CREATE TABLE IF NOT EXISTS `b_resource_price`(
  `brpId` int(6) unsigned NOT NULL,
  `brpResourceId` int(6) unsigned not null,
  `brpDateFrom` int(6) unsigned not null,
  `brpDateTo` int(6) unsigned not null,
  PRIMARY KEY (`brpId`)
) DEFAULT CHARSET=utf8;
INSERT INTO `b_resource_price` (`brpId`, `brpResourceId`, `brpDateFrom`, `brpDateTo`) VALUES
  ('1', '1', '1', '25'),
  ('2', '2', '1', '5'),
  ('3', '2', '20', '25'),
  ('4', '3', '10', '15');

CREATE TABLE IF NOT EXISTS `b_reservation`(
`bresId` int(6) unsigned NOT NULL,
`bresName` varchar(20) NOT NULL,
`bresUnitId` int(6) unsigned NOT NULL,
`bresBookedFrom` int(6) unsigned NOT NULL,
`bresBookedTo` int(6) unsigned NOT NULL,
  PRIMARY KEY (`bresId`)
) DEFAULT CHARSET=utf8;
INSERT INTO `b_reservation` (`bresId`, `bresName`, `bresUnitId`, `bresBookedFrom`, `bresBookedTo`) VALUES
  ('1', 'Møde1', '1', '20', '22'),
  ('2', 'Møde1', '3', '20', '22');

og så sætter det her ind i boxen til højre og trykker "Run sql":

SET @from=19, @to=21;
SELECT bruId, bruName, brId, brName FROM
(
  SELECT bruId, bruName, brId, bruName FROM b_resource_price AS p
  LEFT JOIN b_resource_units AS u ON p.brpResourceId=u.bruResourcesId
  LEFT JOIN b_resources AS r ON p.brpResourceId=r.brId
  WHERE
    @from >= brpDateFrom AND @from <= brpDateTo AND @to >= brpDateFrom AND @to <= brpDateTo
) AS f
LEFT JOIN
(
  SELECT bresId, bresUnitId FROM b_reservation AS b
  WHERE
  (@from < bresBookedFrom AND @to > bresBookedFrom) OR
  (@from < bresBookedTo AND @to > bresBookedTo)
) AS bb ON bb.bresUnitId=bruId
WHERE bresId IS NULL

Lige den her skulle gerne give:

bruId    bruName    brId    bruName
2        chair2    1    chair2
3         chair3    1    chair3
Avatar billede acore Ekspert
01. august 2017 - 08:11 #9
Det går ikke så godt - vi prøver lige igen:

CREATE TABLE IF NOT EXISTS `b_resources` (
  `brId` int(6) unsigned NOT NULL,
  `brName` varchar(20) NOT NULL,
  PRIMARY KEY (`brId`)
) DEFAULT CHARSET=utf8;
INSERT INTO `b_resources` (`brId`, `brName`) VALUES
  ('1', 'chairs'),
  ('2', 'tables'),
  ('3', 'lamps');

CREATE TABLE IF NOT EXISTS `b_resource_units`(
  `bruId` int(6) unsigned NOT NULL,
  `bruResourcesId` int(6) unsigned not null,
  `bruName` varchar(20) NOT NULL,
  PRIMARY KEY (`bruId`)
) DEFAULT CHARSET=utf8;
INSERT INTO `b_resource_units` (`bruId`, `bruResourcesId`, `bruName`) VALUES
  ('1', '1', 'chair1'),
  ('2', '1', 'chair2'),
  ('3', '1', 'chair3'),
  ('4', '2', 'table1'),
  ('5', '2', 'table2'),
  ('6', '2', 'table3'),
  ('7', '3', 'lamp1'),
  ('8', '3', 'lamp2');


CREATE TABLE IF NOT EXISTS `b_resource_price`(
  `brpId` int(6) unsigned NOT NULL,
  `brpResourceId` int(6) unsigned not null,
  `brpDateFrom` int(6) unsigned not null,
  `brpDateTo` int(6) unsigned not null,
  PRIMARY KEY (`brpId`)
) DEFAULT CHARSET=utf8;
INSERT INTO `b_resource_price` (`brpId`, `brpResourceId`, `brpDateFrom`, `brpDateTo`) VALUES
  ('1', '1', '1', '25'),
  ('2', '2', '1', '5'),
  ('3', '2', '20', '25'),
  ('4', '3', '10', '15');

CREATE TABLE IF NOT EXISTS `b_reservation`(
`bresId` int(6) unsigned NOT NULL,
`bresName` varchar(20) NOT NULL,
`bresUnitId` int(6) unsigned NOT NULL,
`bresBookedFrom` int(6) unsigned NOT NULL,
`bresBookedTo` int(6) unsigned NOT NULL,
  PRIMARY KEY (`bresId`)
) DEFAULT CHARSET=utf8;
INSERT INTO `b_reservation` (`bresId`, `bresName`, `bresUnitId`, `bresBookedFrom`, `bresBookedTo`) VALUES
  ('1', 'Møde1', '1', '20', '22'),
  ('2', 'Møde1', '3', '18', '19');

og

SET @from=19, @to=21;

SELECT bruId, bruName, brId, brName FROM
(
  SELECT bruId, bruName, brId, brName FROM b_resource_price AS p
  LEFT JOIN b_resource_units AS u ON p.brpResourceId=u.bruResourcesId
  LEFT JOIN b_resources AS r ON p.brpResourceId=r.brId
  WHERE
    @from >= brpDateFrom AND @from <= brpDateTo AND @to >= brpDateFrom AND @to <= brpDateTo
) AS f
LEFT JOIN
(
  SELECT bresId, bresUnitId FROM b_reservation AS b
  WHERE
  (@from < bresBookedFrom AND @to > bresBookedFrom) OR
  (@from < bresBookedTo AND @to > bresBookedTo)
) AS bb ON bb.bresUnitId=bruId
WHERE bresId IS NULL

Med resultat

bruId    bruName    brId    brName
2        chair2    1    chairs
3        chair3    1    chairs
Avatar billede acore Ekspert
28. august 2017 - 19:59 #10
Nu var vi lige så tæt på, synes jeg. Gik du helt i stå eller...?
Avatar billede Mik2000 Professor
29. august 2017 - 01:09 #11
Undskyld - den var lige sat på hold pga. travlhed på arbejdet :).
Fik testet den men fik ikke noget output - men gør når jeg tester med din (med de oprettede tabeller)

Eneste forskel jeg umiddelbart kan se er at jeg har dato mv. som timestamp

Men prøver lige lidt mere og ser om det ikke kan lykkes, og så skal jeg selvfølgelig nok vende tilbage :)
Avatar billede acore Ekspert
29. august 2017 - 08:27 #12
Godt at høre fra dig :) Ja, det arbejde kan være stærkt generende ;)

Det med timestamp burde ikke gøre en forskel. Du kan evt overveje midlertidigt at sætte en integer hjælpekolonne ind, og prøve med den.

Som du kan se, er der to SELECT med en LEFT JOIN imellem inde i en SELECT.

De to indvendige SELECT kan gøres hver for sig i fiddle og hos dig med henblik på at identificere forskellen. Bare en idé.
Avatar billede Mik2000 Professor
18. november 2017 - 13:54 #13
Hej

Så blev der lidt ro på arbejdet og tid til igen at bruge lidt tid på det der er lidt sjovere ;)

En enkelt ting omkring resultatet:
Chair2 har kun noget i price tabellen fra 1-5 og 20-25, så man bør ikke kunne booke den fra 19-21 da 19 ikke er i de perioder

Chair3 har kun noget i price tabellen for 10-15 så den bør heller ikke kunne bookes mellem 19 og 21

Price tabellen bruges jo til at sætte pris de dage der er åbent, så hvis de ikke er i hele den periode, så er det fordi der er lukket
Det kan dog godt være på tværs dvs. en price periode fra 1-20 og en fra 21-30 (eller 20-30) - så kan man godt booke 18-22
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester