sqlfan wrote:
>
> Thank you so so so so so so so much!!!! So much. Even when I have your
> code in front of me, it's still very very hard for me to understand it.
> This is hard stuff! So much harder than a spreadsheet, but hopefully it
> will work out in the end, because I'm programming in Python so I should have
> a lot of power...
>
You're welcome. I think you will have fun with Python.
> Here are some basic questions: what is the "null" that you insert into
> Booking as the first column of the test records??? You don't insert it into
> the resource records, even though it seems to me they have the same
> structure!
>
> (their first column is a unique integer resource_id and booking_id
> respectively... but then you insert into the first by
>
> insert into resource values (1, 'resource 1');
>
> and the second by
>
> insert into booking values (null, 1, '2008-04-10 09:00:00', '2008-04-11
> 04:00:00', 'test 3')
>
Null is an unspecified or unknown value. For columns declared as
"integer primary key" SQLite will assign a unique integer value when you
insert null.
Yes, I could have inserted null's into the resource table as well, and
SQLite would have assigned the same id numbers I inserted manually.
I tend to use manual id numbers when I have a known fixed number of
entries, and I want to refer to them directly by that id number. If I
let SQLite assign the numbers, then I would, in general, have to do a
lookup using the resource name to get the id number that had been
assigned. A typical insert into the booking table would then look like this:
insert into booking values (null,
(select resource_id from resource
where resource_name = 'resource 1'),
'2008-04-14 09:00:00', '2008-04-13 04:00:00', 'test 3');
where the resource id is replaced by a sub-select that retrieves the id
assigned to the resource.
>
> 2) I'd like to prepare an HTML table by dates instead of by resource...
> How can I "pivot" the table so that each row I fetch to add to the table
> starts with a date in the first column... This way, my presentation could be
> similar to my current spreadsheet, which is really quite useful. I'd just
> like it not to be so "dumb". So, if I'm looking at June 12-18, for example,
> I'd like
> Resource1
> 2008-06-12 no booking
> 2008-06-13 no booking
> 2008-06-14 booking #72
> 2008-06-15 booking #72
> 2008-06-16 no booking
> 2008-06-17 booking #73
> 2008-06-18 no booking
> I'm just showing 1 resource, because I don't know if my "ascii art" table
> here will get mangled in transmission, but you can imagine I'd like the rest
> of the columns to be the other resources.....
>
> How could I get this view?
>
This is a little trickier. :-)
First you need to generate a temporary table with the range of dates
that you want to report. This will create a temporary table with 32 day
starting from the current date.
create temp table date_range(
id integer primary key,
date datetime
);
insert into date_range values(1, date('now'));
insert into date_range select null, date from date_range; --x2
insert into date_range select null, date from date_range; --x4
insert into date_range select null, date from date_range; --x8
insert into date_range select null, date from date_range; --x16
insert into date_range select null, date from date_range; --x32
update date_range
set date = date(julianday(date) + id - 1);
Next you need to join this table to the booking table several times,
once for each column in the output.
select d.date as date,
r1.info as 'resource 1',
r2.info as 'resource 2',
r3.info as 'resource 3',
r4.info as 'resource 4',
r5.info as 'resource 5'
from date_range as d
left join booking as r1 on r1.resource_id = 1
and d.date between r1.begins and r1.ends
left join booking as r2 on r2.resource_id = 2
and d.date between r2.begins and r2.ends
left join booking as r3 on r3.resource_id = 3
and d.date between r3.begins and r3.ends
left join booking as r4 on r4.resource_id = 4
and d.date between r4.begins and r4.ends
left join booking as r5 on r5.resource_id = 5
and d.date between r5.begins and r5.ends
order by d.date;
If you execute this in the shell you can set the mode to column and turn
on the headers to get a nicely formatted table. The following trace
shows this query in action.
sqlite> insert into booking values (null, 1,
...> '2008-04-07', '2008-04-10', 'test 1');
sqlite> insert into booking values (null, 2,
...> '2008-04-08', '2008-04-09', 'test 2');
sqlite> insert into booking values (null, 4,
...> '2008-04-08', '2008-04-12', 'test 3');
sqlite> insert into booking values (null, 5,
...> '2008-04-15', '2008-04-20', 'test 4');
sqlite> insert into booking values (null, 1,
...> '2008-04-14', '2008-04-18', 'test 5');
sqlite> insert into booking values (null, 3,
...> '2008-04-25', '2008-04-30', 'test 6');
sqlite>
sqlite> .mode column
sqlite> .header on
sqlite> select d.date as date,
...> r1.info as 'resource 1',
...> r2.info as 'resource 2',
...> r3.info as 'resource 3',
...> r4.info as 'resource 4',
...> r5.info as 'resource 5'
...> from date_range as d
...> left join booking as r1 on r1.resource_id = 1
...> and d.date between r1.begins and r1.ends
...> left join booking as r2 on r2.resource_id = 2
...> and d.date between r2.begins and r2.ends
...> left join booking as r3 on r3.resource_id = 3
...> and d.date between r3.begins and r3.ends
...> left join booking as r4 on r4.resource_id = 4
...> and d.date between r4.begins and r4.ends
...> left join booking as r5 on r5.resource_id = 5
...> and d.date between r5.begins and r5.ends
...> order by d.date;
date resource 1 resource 2 resource 3 resource 4 resource 5
---------- ---------- ---------- ---------- ---------- ----------
2008-04-07 test 1
2008-04-08 test 1 test 2 test 3
2008-04-09 test 1 test 2 test 3
2008-04-10 test 1 test 3
2008-04-11 test 3
2008-04-12 test 3
2008-04-13
2008-04-14 test 5
2008-04-15 test 5 test 4
2008-04-16 test 5 test 4
2008-04-17 test 5 test 4
2008-04-18 test 5 test 4
2008-04-19 test 4
2008-04-20 test 4
2008-04-21
2008-04-22
2008-04-23
2008-04-24
2008-04-25 test 6
2008-04-26 test 6
2008-04-27 test 6
2008-04-28 test 6
2008-04-29 test 6
2008-04-30 test 6
2008-05-01
2008-05-02
2008-05-03
2008-05-04
2008-05-05
2008-05-06
2008-05-07
2008-05-08
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
