SQL help

Fri Jan 3 17:46:54 CST 2014

Seems like an entertaining riddle.
I think a view could be an option. Or maybe a simple query will do.

 If possible can you sent me offlist a database with (a sub) set of data and a concrete question. Or write an sqlfiddle
I think I could have a look at it sunday .

I wrote some more comments inline your answer below

my mail: bart(at)m-3(dot)be
— 
Our office will be closed on:
24 (afternoon) - 25 - 31/12/2013 & 01/01/2014

Bart Pietercil
Sent with aMac

On 4 Jan 2014 at 00:14:58, william humphrey (bill at bluewatermaritime.com) wrote:

The problem is that the voyage ID I am using is the one for U.S. 
customs which is a one letter code for vessel (even though in the 
database each vessel has a number ID) and then two digits for year and 
then two digits for the sequence of the vessel arrival for that year. 

For example H1401 is the first arrival for vessel "H" actually that 
ship the Midnight Coast is number 138 in the vessel table. It turns 
out very sub-optimal. I should have had it be vessel number 138-033 
and so on for ever single voyage of that vessel for my ID field in the 
voyages and not use the voyage ID required by Customs (even though it 
is unique) as it's difficult to extract from H1401 that it is the 33nd 
voyage of vessel number 138. So now the best thing to do would be make 
an auto-increment field that is a combination of vessel ID and 
sequence of that vessel. 

This kind of sub-optimal database design happens sometimes. So the 
problem I'm having is easily asking for the voyage immediately 
preceding a particular voyage of a particular vessel. It isn't easy to 
extract that using the voyage ID as that changes increment each year. 
It also is a different format depending on whether it's a passenger 
vessel or a cargo vessel (passenger vessel has a two-letter code). 
So the FOUR LAST characters determine the year and vessel arrival. This can be easily extracted using string functions (look at right and length) so you could make seqnr and year method fields.

Cast the calculations to numeric output. So now you can order by those fields (do not forget to index)

If your seqnr > 1 then you could look for seqnr -1 when  seqnr = 1 then look for year -1 and max(seqnr)

ex H1401 -> year = 14 and seq = 1

so we need to look for year = 14-1 and seq = max(seq) where vessel = ‘H’ and year = 13

right?

off to sleep now, as said tomorrow busy day, I’ll look at your answer sunday ( now + 36h)

Bill 

On Fri, Jan 3, 2014 at 7:07 PM, Bart Pietercil 
<bart.pietercil at cognosis.be> wrote: 
> Hmm, it seems to me that your db schema is suboptimal here but without 
> knowing the schema this remains of course guessing. 
> I would expect your schema to be something like this 
> 
> tbl_voyages 
> 
> voyage_id, 
> fk_vessel_id, 
> from, 
> to, 
> departure, 
> arrival, 
> …. 
> 
> 
> tbl_vessels 
> 
> vessel_id, 
> vessel_name, 
> vessel_capacity, 
> … 
> 
> both voyage_id and vessel_id are sequences. 
> fk_vessel_id is the foreign key holding the vessel_id for the vessel that is 
> doing the voyage (voyage_id). 
> 
> If this is your schema then please elaborate on the problem your having. 
> 
> regards 
> 
> bart 
> 
> 
> — 
> Our office will be closed on: 
> 24 (afternoon) - 25 - 31/12/2013 & 01/01/2014 
> 
> Bart Pietercil 
> Sent with aMac 
> 
> On 3 Jan 2014 at 23:55:08, william humphrey (bill at bluewatermaritime.com) 
> wrote: 
> 
> Bart 
> 
> Thank you for the clear description. I'm still trying to decide which 
> would be more useful for me. There are like thirty vessels and each 
> new voyage will be for one of the vessels. Only one vessel in each new 
> voyage. But if I just auto increment for voyages it is not as useful 
> as auto increment that is unique only to each vessel for that voyage. 
> That would mean the field would not be a unique value for the voyages 
> as it would only be the combination of vessel number and voyage number 
> only that is unique. 
> 
> Bill 
> 
> On Fri, Jan 3, 2014 at 5:56 PM, Bart Pietercil 
> <bart.pietercil at cognosis.be> wrote: 
>> Theoretically you can. What I don’t know is whether the Valentina Engine 
>> already supports it. 
>> The sequence generator has been added to vStudio in order to support the 
>> Postgresql engine. 
>> In Postgresql you would do 
>> 
>> add field vessel_nr serial (or big serial). 
>> This is shorthand for 
>> 
>> add field vessel_nr integer (or bigint) unique 
>> create sequence seq_vessel_nr 
>> set default value of field vessel_nr to nextval(seq_vessel_nr) 
>> 
>> so now you have a field vessel_nr with an autoincrementing value on every 
>> insert (creation of vessel trip?) 
>> the only thing left to do is , if you already have data in your vessel 
>> table, generate numbers for the existing values. 
>> And the last thing to do is set the startvalue of seq_vessel_nr to 
>> max(vessel_nr)+1. 
>> 
>> From hereon your vessel_nr will autoincrement with the correct number 
>> 
>> hth 
>> 
>> bart 
>> — 
>> Our office will be closed on: 
>> 24 (afternoon) - 25 - 31/12/2013 & 01/01/2014 
>> 
>> Bart Pietercil 
>> Sent with aMac 
>> 
>> On 3 Jan 2014 at 16:47:59, william humphrey (bill at bluewatermaritime.com) 
>> wrote: 
>> 
>> Can that kind of create sequence be done after the fact of table 
>> finished and data already in? 
>> 
>> On Fri, Jan 3, 2014 at 11:23 AM, Ruslan Zasukhin 
>> <ruslan_zasukhin at valentina-db.com> wrote: 
>>> On 1/3/14, 4:46 PM, "william humphrey" <bill at bluewatermaritime.com> 
>>> wrote: 
>>> 
>>>> I've been thinking that a sequence maintained with a trigger might be 
>>>> very useful for other things too. I never thought of numbering 
>>>> sequentially the voyages of each vessel. It solves some other 
>>>> problems. Right now voyage numbers increment from zero at the start of 
>>>> each new year (which is what caused the problem) but there is no 
>>>> reason why I can't have an extra field which has every voyage for that 
>>>> vessel down through the years. 
>>> 
>>> Do you talk about CERATE SEQUENCE? 
>>> 
>>> 
>>> For simple case, consider SERIAL32 or SERIAL64 pseudo-types, 
>>> As even more simple things. 
>>> 
>>> 
>>> -- 
>>> Best regards, 
>>> 
>>> Ruslan Zasukhin 
>>> VP Engineering and New Technology 
>>> Paradigma Software, Inc 
>>> 
>>> Valentina - Joining Worlds of Information 
>>> http://www.paradigmasoft.com 
>>> 
>>> [I feel the need: the need for speed] 
>>> 
>>> 
>>> _______________________________________________ 
>>> Valentina mailing list 
>>> Valentina at lists.macserve.net 
>>> http://lists.macserve.net/mailman/listinfo/valentina 
>> 
>> 
>> 
>> -- 
>> http://www.bluewatermaritime.com 
>> _______________________________________________ 
>> Valentina mailing list 
>> Valentina at lists.macserve.net 
>> http://lists.macserve.net/mailman/listinfo/valentina 
>> 
>> 
>> _______________________________________________ 
>> Valentina mailing list 
>> Valentina at lists.macserve.net 
>> http://lists.macserve.net/mailman/listinfo/valentina 
>> 
> 
> 
> 
> -- 
> http://www.bluewatermaritime.com 
> _______________________________________________ 
> Valentina mailing list 
> Valentina at lists.macserve.net 
> http://lists.macserve.net/mailman/listinfo/valentina 
> 
> 
> _______________________________________________ 
> Valentina mailing list 
> Valentina at lists.macserve.net 
> http://lists.macserve.net/mailman/listinfo/valentina 
> 

-- 
http://www.bluewatermaritime.com 
_______________________________________________ 
Valentina mailing list 
Valentina at lists.macserve.net 
http://lists.macserve.net/mailman/listinfo/valentina 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20140104/2a860cb8/attachment-0001.html>