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

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.

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

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

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

You can use an alias like that. You need to use something like this:

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)

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