How create a table with all days ?
-
Hi,
I have a table with tripinformation in it like shift_date, shift_num (1 or 2), truck, km, time, etc. and a table with all days of 2004 (date, daytype (like workday, saterday, sunday or holiday))
What I want to achieve is a table that gives me by month ALL days and then the information from the tripinformation table.
like this :
Truck Date Shift KM
900 2004-08-01 1 493
900 2004-08-01 2 264
900 2004-08-02 1 0 (as it has not driven that shift)
900 2004-08-02 2 0 (as it has not driven that shift)
900 2004-08-03 1 318
900 2004-08-03 2 0 (as it has not driven that shift)
... to 2004-08-31
901 2004-08-01 1 285
901 2004-08-01 2 357
901 2004-08-02 1 157
... to 2004-08-31
etc
I tried already to start from the day table and the diffent JOIN expressions to the tripinfo table but I get always only the days where there is information in the tripinfo table.
Can someone give me some help to solve this?
Thanks
Jan
-
What you need to do is to create a DATE TABLE with all the cvolumns you require. This is done in olap processing where you can query by day, month, period etc.
On your base table that you are collecting the main data in, simply put in an ID to the DATE TABLE.
E.g
tblBASE
truck
km
shift
timeid
tblTIME
timeid
the_day
the_month
the_year
the_day_name
Make sure you join the table with an outer join on the date table to get back a line for every date irrelevant of whether there is data or not.
If you have analysis services on your server, take a look at the samples. They all use date dimensions.
------------------------------
The Users are always right - when I'm not wrong! -
First: Do you have a table of all trucks? That will make one of the steps below much more efficient.
Table tripinformation:
- shift_date
- shift_num
- truck
- km
- time
Table alldays
- date
- datetype
Table trucks (assumed)
- truck
1. Get a list of all shifts. This is a union of two copies of the alldays table.
(SELECT ALL date, 1 AS shift
FROM alldays
UNION ALL SELECT ALL date, 2 AS shift
FROM alldays) AS allshifts
2. Get a list of all truck-shift combinations. This is a cartesion join (all combinations of trucks and shifts from both tables with no selection or join criteria).
(SELECT DISTINCT truck, date, shift
FROM tripinformation, allshifts) truckshift
This will be way more efficient if you have a table of trucks:
(SELECT DISTINCT truck, date, shift
FROM trucks, allshifts) truckshift
In either case, you can make a temp table for allshift, or this should work:
(SELECT DISTINCT truck, date, shift
FROM tripinformation, -- or: trucks,
(SELECT ALL date, 1 AS shift
FROM alldays
UNION ALL SELECT ALL date, 2 AS shift
FROM alldays) AS allshifts
) truckshifts
3. Finally, outer join your truckshift listing with the tripinformation:
SELECT ts.truck, ts.date, ts.shift, isnull(trip.km,0) AS _KM_
FROM truckshifts AS ts
LEFT JOIN tripinformation AS trip
ON ts.truck = trip.truck
AND ts.date = trip.shift_date
AND ts.shift = trip.shift_num
4. Putting it all together:
- You can save the tables allshifts and truckshifts as temporary tables
- You can put it all into a single SQL statement, like this:
SELECT ts.truck, ts.date, ts.shift, isnull(trip.km,0) as _KM_
FROM
(SELECT DISTINCT truck, date, shift
FROM trucks,
(SELECT ALL date, 1 AS shift
FROM alldays
UNION ALL SELECT ALL date, 2 AS shift
FROM alldays) AS allshifts
)
AS ts
LEFT JOIN tripinformation AS trip
ON ts.truck = trip.truck
AND ts.date = trip.shift_date
AND ts.shift = trip.shift_num
Good luck. You should be able to test out the results of steps #1 and #2 independently, so you can make sure you're on the right track.
Please note - some of the syntax is from Access, as I'm not on SQL Server at the moment. I not sure about the 'AS' words and the isnull() function. If those don't work, use the SQL Server equivalent.
Regards,Bob Monahon
-
Hi
Sounds like you just need to use an OUTER join between Days and Trips tables
eg. FROM Days LEFT JOIN Trips ON Days.Date = Trips.Date
this will include all rows from Days even if no matching Trips, which I think is what you want?
hope this helps
Neil
-
Jonathan,
Thanks for the tip but I prefer to Bob's explanation as it's much clearer for me (as newbie)
Neil,
This is just the strange thing that that doesn't work (I used it for other tables without problems but on this one........:crazy
Bob,
Thanks, this was the tip I needed. I have created already all days with the truck and shift but I have now something strange when i do a SUM on the km. But before I want to post something I first want to try it out myself. It must have to do something with the joins)
Regars,
Jan
-
I think the outer join will not work if you have fields from the Trips table in the WHERE clause...(not sure though
-
Bob,
this is the query I have and it's already a good start (thanks again for the explanation) but how must I include the link between the truck table and the trip table on the field 'truck_num' ?
Because as I do now a SUM on km it gives me not the correct sum.
(I included the WHERE clausule to have a better view on 1 month and 1 truck, I can skip this later on)
SELECT DISTINCT
day.country_code,
truck.haulier_code,
truck.truck_num,
day.day_date,
day.day_holiday,
shift,
sum(trip.trip_km_total) as km,
sum(trip.trip_time) as [time]
FROM
dbo.dwl_truck as truck,
(SELECT ALL
country_code,
day_date,
day_holiday,
1 AS shift
FROM dbo.dwl_days
WHERE
country_code = '1'
AND DATEPART(YEAR,day_date) = '2004'
AND DATEPART(MONTH,day_date) = '8'
UNION ALL
SELECT ALL
country_code,
day_date,
day_holiday,
2 AS shift
FROM dbo.dwl_days
WHERE
country_code = '1'
AND DATEPART(YEAR,day_date) = '2004'
AND DATEPART(MONTH,day_date) = '8'
 
AS [day]LEFT JOIN dbo.dwl_tripinfo_clean AS trip
ON day.country_code = trip.country_code
AND day.day_date = trip.shift_date
AND day.shift = trip.shift_num
WHERE
day.country_code = '1'
AND DATEPART(YEAR,day.day_date) = '2004'
AND DATEPART(MONTH,day.day_date) = '8'
AND truck.truck_num = '734'
GROUP BY
day.country_code,
truck.haulier_code,
truck.truck_num,
day.day_date,
day.day_holiday,
shift
ORDER BY
4,6
Thanks,
Jan
-
Try this. Note that there are two levels of parentheses. Working outward from the inner-most pair:
- First you union the days to get the table [all_shifts].
-Then you cartesian-join table [trucks] to table [all_shifts] to get table [ts].
- Finally, the main select left-joins table [ts] to table [trip]. Note that the main select, since it uses GROUP BY, does not need DISTINCT. I'd remove it.
- ALSO: Add the truck_num to the LEFT JOIN.
SELECT
ts.country_code,
ts.haulier_code,
ts.truck_num,
ts.day_date,
ts.day_holiday,
ts.shift,
sum(trip.trip_km_total) as km,
sum(trip.trip_time) as [time]
FROM
(SELECT DISTINCT truck.haulier_code, truck.truck_num, all_shifts.*
FROM dbo.dwl_truck as truck,
(SELECT ALL country_code, day_date, day_holiday, 1 AS shift
FROM dbo.dwl_days
WHERE country_code = '1'
AND DATEPART(YEAR,day_date) = '2004'
AND DATEPART(MONTH,day_date) = '8'
UNION ALL SELECT ALL
country_code, day_date, day_holiday, 2 AS shift
FROM dbo.dwl_days
WHERE country_code = '1'
AND DATEPART(YEAR,day_date) = '2004'
AND DATEPART(MONTH,day_date) = '8'
) AS [all_shifts]
) as ts
LEFT JOIN dbo.dwl_tripinfo_clean AS trip
ON ts.country_code = trip.country_code
AND ts.truck_num = trip.truck_num
AND ts.day_date = trip.shift_date
AND ts.shift = trip.shift_num
WHERE
ts.country_code = '1'
AND DATEPART(YEAR,ts.day_date) = '2004'
AND DATEPART(MONTH,ts.day_date) = '8'
AND ts.truck_num = '734'
GROUP BY
ts.country_code,
ts.haulier_code,
ts.truck_num,
ts.day_date,
ts.day_holiday,
ts.shift
ORDER BY
4,6
Regards,Bob Monahon
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply