Hi All
I'm new to the forum but hoping you guys and girls can help me out.
Form a couple of days now I'm strugling to get an sql querry working.
In a fleet management app I'm trying to display all the fines/speeding tickets a drivers has on his car.
The problem is here that the cars are swapped between different drivers so I end up with 3 tables.
One that holds my dirvers, one that holds the fines, and one that tracks who drove what when.
--------------------------------------------------
CREATE TABLE #drivers (
id int NOT NULL,
drivercode varchar(30) NOT NULL,
name varchar(50) NOT NULL
)
INSERT INTO #drivers VALUES
(1, 'snowjo', 'John Snow'),
(2, 'yeti', 'Sasquatch')
--------------------------------------------------
CREATE TABLE #carusage (
id int NOT NULL,
drivercode varchar(30) NOT NULL,
plate varchar(30) NOT NULL,
fromdate date NOT NULL,
todate date NOT NULL,
)
INSERT INTO #carusage VALUES
(1, 'snowjo', 'XXV123', '20190225', '20190228'),
(2, 'yeti', 'HAIRYBEAST', '20190224', '20190228')
--------------------------------------------------
--DROP TABLE #fines
CREATE TABLE #fines (
id int NOT NULL,
plate varchar(30) NOT NULL,
date date NOT NULL,
fineamount decimal(9, 2) NULL,
location varchar(30) NULL
)
INSERT INTO #fines VALUES
(1, 'XXV123', '20190226', 25.00, 'Texas'),
(2, 'HAIRYBEAST', '20190226', 75.00, 'Himalayas')
--------------------------------------------------
So if I want to display the fines of the driver 'snowjo' I only want to see one record, the one infraction on 2019-02-26 as he was driving the car between 2019-02-25 and 2019-02-30
I'm thinking about using an INNER JOIN or somehting?
Help would be appreciated.