Re: [firebird-support] How do I get the differences between 2 timestamps as a timestamp.
Hi
I am not sure if it is really "legal" and if it will always work, but
this works for me for now.
Period = dateadd(minute, datediff(minute, Start_Date, End_Date),
cast('30.12.1899 00:00' as Timestamp))
will change the Period to Integer and save the DateDiff(minute ...) in
it, when I have the time to convert the application
Regards
Cornie
On 2013/06/04 02:13, Helen Borrie wrote:
>
> At 12:01 p.m. 4/06/2013, cornievs wrote:
> >I have the following table (Staff_Events)
> >
> >...
> >Start_Date TIMESTAMP,
> >End_Date TIMESTAMP,
> >Period TIMESTAMP,
> >
> >
> >and need to set Period to the time difference between End_Date and
> Start_Date. End_Date will always be later than Start_Date. (Enforce by
> on Insert/Update triggers.)
> >
> >I know I can the get difference with DATEDIFF, but I need the result
> as a TimeStamp.
>
> It is not possible. The difference between two timestamps gives you a
> number representing days and parts thereof. Your Period column is the
> wrong type. Make it a CHAR of a suitable length and use an expression
> to convert the difference into the string you want.
>
> Helen Borrie, Support Consultant, IBPhoenix (Pacific)
> Author of "The Firebird Book" and "The Firebird Book Second Edition"
> http://www.firebird-books.net
> __
>
>
[Non-text portions of this message have been removed]
Re: [firebird-support] How do you 0/null results to a GROUP BY query
>>> I have query which extract the sales per day from a table > > >>> > >>> Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) > AS DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. > >>> > >>> It works 100%, but only returns the dates with sales, I need it to > also include the dates with zero sales, for example: > >>> > >>> YEARNO DAYNO SUM > >>> 2012 01 5000 > >>> 2012 02 6000 > >>> 2012 03 0 (or null will be fine) > >>> 2012 04 7000 > >> I would crate a reference table that contains all the years and days > >> that you want to report on. Then join the data you have to that table > >> using an outer join so that all dates in the reference table are in the > >> result set and those that have data from the client invoices will > show it. > >> > > I used to do this way, but with CTE you create that "table" > dynamically, > > another option is using EXECUTE STATEMENT or a SP to loop trough the > days > I saw that. However, I don't see the advantage. Using a reference table > is simpler and clearer than using CTE or other code to generate the > reference values. > Thanks! I will most likely go with the reference table(s) option, but I will test both. Regards Cornie [Non-text portions of this message have been removed]
RE: [firebird-support] How do you get a summary SUM() from a union of tables
>I have 2 (and more) tables with identical structure: > >Simplified structure and data: >CODE VARCHAR(15) >QUAN INTEGER > >Table A >Item1, 50 >Item1, 40 >Item2, 70 > >Table B >Item1, 50 >Item2, 100 >Item2, 30 > >I need to get the sum(QUAN) for all the rows in all the tables group by CODE > >Item1, 140 >Item2, 200 > >Is there a way I can do it with a UNION? Or is there a better way? > Hi Cornie! This can be solved several ways, one simple solution would be: WITH TMP AS (SELECT CODE, QUAN FROM TableA UNION SELECT CODE, QUAN FROM TableB) SELECT CODE, SUM(QUAN) FROM TMP GROUP BY CODE HTH, Set Set thanks for the reply, I just have one problem, the union removes duplicates. So if both tables have Item1, 50 the result is Item1, 50 and not Item1, 100 as I require. Regards Cornie
Re: [firebird-support] 2.1.x 32bits to 2.1.x 64bits
On 2012/06/19 09:07, Thomas Steinmaurer wrote: >> I've a database on firebird 2.1.1 32bits and i want to migrate to >> firebird 2.1.4 64bits. Do I need a backup and restore database for >> porting to 2.1.4 64bits? > No. > >> If i move from 2.1.4 64bits to 2.1.4 32bits i need to do backup and >> restore? > No. > > Mind you, if you are using UDFs, you need libraries compiled for 64-bit. > > > Regards, > Thomas > > > Thomas I differ from you. I know according to the manual its the same ODS, but in practice I found that sometimes I could not open the database when switching between 32/64. To date I could not found out why, as I use no UDF's and standard supperserver installations. So I would recommend to go with backup/restore. Regards Cornie van Schoor (InfoStar Software) ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Problem in fetch records
On 03/09/2011 13:54, trskopo wrote: >> Hi all, >> >> Here is the problem, when I issued Select SQL statement, sometime, not >> all records fetched, but this was not always happen, sometime, all >> records fetched ok. Check your dataset in c++, maybe it set to retrieve in only x of amount of records per fetch. PacketRecords = 100 (-1 means fetch all). If FetchOnDemand is True, scrolling a grid for example, will force fetching of more record as required. Regards Cornie ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
