Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2011
    Posts
    42

    Unanswered: Datediff query in Postgres 9.0

    Hello all,

    I am using the following query to find Purchase frequency


    The tablename is purchases and it looks like :
    itemname
    pdate
    qty
    rate
    total

    The query is

    SELECT itemname as Product,
    COUNT(*) as NumTran,
    Min(pdate) as Startdate,
    Max(pdate) as Lastdate,
    DATEDIFF(dd, Startdate,Lastdate)/NumTran as AvgTransactionPerDay
    FROM purchases
    GROUP BY itemname
    order by Transactions desc

    The problem is indicated in
    DATEDIFF(dd, Startdate,Lastdate)/NumTran as AvgTransactionPerDay
    without this statement, the query works fine.


    any help will be appreciated.
    Thanx
    Last edited by arvindps; 12-04-11 at 12:30.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    There is no DATEDIFF in PostgreSQL.

    What are you expecting as the result there?
    Where does the dd thing come from?

  3. #3
    Join Date
    Aug 2011
    Posts
    42
    Quote Originally Posted by shammat View Post
    There is no DATEDIFF in PostgreSQL.

    What are you expecting as the result there?
    Where does the dd thing come from?
    I copied it from an SQl query on net. It says to get difference of two dates in days pass dd as code.

    Is there another way to put this query in Postgres, then let me know. It is just for this line, the report is on hold.

    It is to finds difference between Startdate and Lastdate, and divide it with number of entries. to get average transaction per day. So in simple terms, to find how many times a item is required. So if Startdate is 01/01/2000 and Lastdate is 10/01/2000 Numtran is 2. In that case the result is (10/02/2000-01/01/2000)/2 = 5 This means the item is purchase once in 5 days.
    Last edited by arvindps; 12-04-11 at 12:30.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by arvindps View Post
    It says to get difference of two dates in days pass dd as code.
    If Startdate and Lastdate are of type DATE, then you can simply use Startdate - Lastdate.

    All date/time arithmetics and available functions are documented in the manual, so you should check that for more details:

    http://www.postgresql.org/docs/curre...-datetime.html

  5. #5
    Join Date
    Aug 2011
    Posts
    42
    Quote Originally Posted by shammat View Post
    If Startdate and Lastdate are of type DATE, then you can simply use Startdate - Lastdate.

    All date/time arithmetics and available functions are documented in the manual, so you should check that for more details:

    PostgreSQL: Documentation: Manuals: Date/Time Functions and Operators
    I tried thequery as per your instructions

    SELECT itemname as Product,
    COUNT(*) as NumTran,
    Min(edate) as Startdate,
    Max(edate) as Lastdate,
    (Startdate-Lastdate)/NumTran as AvgTransactionPerDay
    FROM issue_stock
    GROUP BY itemname
    order by Transactions desc

    but a error has occured
    ERROR: column "startdate" does not exist
    SQL state: 42703
    Character: 100

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by arvindps View Post
    but a error has occured
    ERROR: column "startdate" does not exist
    What's unclear about that message?

  7. #7
    Join Date
    Aug 2011
    Posts
    42
    Quote Originally Posted by shammat View Post
    What's unclear about that message?
    i have already calculated startdate as max(pdate) in the query , and i want to reused that value in calculation. same for lastdate and numtrans

    (PS : i even tried (max(pdate)-min(pdate))/count(*)

    thanx for your support

  8. #8
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    You can use an alias like that. You need to use something like this:
    Code:
    SELECT product,
           numtran,
           startdate,
           lastdate,
           (Startdate-Lastdate)/NumTran as AvgTransactionPerDay
    FROM (
        SELECT itemname as Product,
               COUNT(*) as NumTran,
               Min(edate) as Startdate,
               Max(edate) as Lastdate
        FROM issue_stock
        GROUP BY itemname
    ) 
    order by numtrans desc

  9. #9
    Join Date
    Aug 2011
    Posts
    42

    Wink

    oh, thankx a zillion


    i have successfully executed the report. i guess it would not have been without your help. so thanx again

    the final query looks like

    SELECT product,
    numtran,
    startdate,
    lastdate,
    (Lastdate-Startdate)/NumTran as AvgTransactionPerDay
    FROM (
    SELECT itemname as Product,
    COUNT(*) as NumTran,
    Min(edate) as Startdate,
    Max(edate) as Lastdate
    FROM issue_stock
    GROUP BY itemname
    ) d
    order by NumTran

    (desc in order would mean lowest purchased product comes at top, plus i added d to make it a derived table)
    (i will further test the query on test data)
    Last edited by arvindps; 12-05-11 at 10:43.

  10. #10
    Join Date
    Aug 2011
    Posts
    42
    The final query after applying on test data is

    SELECT product,
    numtran,
    startdate,
    lastdate,
    (Lastdate-Startdate) as Totaldays,
    (Lastdate-Startdate)/NumTran as PurchaseCycle
    FROM (
    SELECT itemname as Product,
    COUNT(*) as NumTran,
    Min(pdate) as Startdate,
    Max(pdate) as Lastdate
    FROM purchases
    GROUP BY itemname having max(pdate) > min(pdate)
    ) d
    order by Totaldays desc


    ordering on totaldays seems more practical. Because there are items that have been purchased multipletimes with a shortpurchase span and although they have a low purchase requirement but they show a high purchase frequency for ex.

    Cardamom was purchased from 10/10/2010 to 14/10/2010 atleast 4 times.
    it means the purchasecycle is (14/10 - 10/10)/4 = 1 i.e. a daily cycle, however they were never purchased after 10/10/2010 and yet they seem important when on top of list.

    on the other hand Potatoes have 01/10/2010 to 25/10/2010 atleast 8 times.
    they have a frequency of 3. i.e. purchases once in 3 days , but they are a consistent purchase and yet fall below cardamom

    I hope i have complete my analysis and submitting the right report.

    regards

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •