0

under Postgres 9.3 I am trying to run a query to summarize some data from table a:

| Entry date-Date | Customer ID-charvar | received-Boolean | Processed-Boolean | finalized-Boolean | billed-Date |

I would like use count(case when) to count all the records by the current week, month, then all of time into table b:

|report date | Total Received Weekly | Received Monthly | Received all of time | so on...

I am having a difficult time wrapping my head around how to write that query. I have thought about extracting the week day and month number to determine my records to sum for each length of time but then how would I make it repeatable without having to change the dates so I could automate it to run weekly through either PgAgent or CRON?

0

Too long for a comment now: I don't speak PGSQL so try adapting the following for your date extraction functions:

SELECT
     MAX(CurrentWeekCount) As CurrentWeekCount
    ,MAX(CurrentMonthCount) As CurrentMonthCount
    ,MAX(AllCount) As AllCount
FROM
(

count all the records by the current week

    SELECT
     COUNT(CustomerID) As CurrentWeekCount
    ,NULL As CurrentMonthCount
    ,NULL As AllCount
    FROM YourTable
    WHERE DATEPART(week, YourDateColumn) = DATEPART(week, GETDATE())

    UNION ALL

month

SELECT
     NULL As CurrentWeekCount
    ,COUNT(CustomerID) As CurrentMonthCount
    ,NULL As AllCount
    FROM YourTable
    WHERE DATEPART(month, YourDateColumn) = DATEPART(month, GETDATE())

    UNION ALL

all of time

    SELECT
     NULL As CurrentWeekCount
    ,NULL As CurrentMonthCount
    ,COUNT(CustomerID) As AllCount
    FROM YourTable
) x

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.