How to group dates into quarters AND outer join data to itself?

[adam0101]

I have a table that looks like this:

ProjectID Phase          DollarAmount DateCreated
--------- -------------- ------------ -----------
1         Submitted      100.00       1/1/2002
2         Submitted      111.00       1/3/2002
3         In Development 225.00       1/2/2002
4         In Testing     125.00       5/5/2002

The query needs to do the following:
- Group the dates into quarters of the year.
- Display all phases for each quarter even if there are no projects created during that quarter. (This is the one I'm having problems with)

The query results should look something like this:

Quarter        Phase          SumDollarAmount
-------------- -------------- ---------------
First Quarter  Submitted      211.00
First Quarter  In Development 225.00
First Quarter  In Testing     0.00
Second Quarter Submitted      0.00
Second Quarter In Development 0.00
Second Quarter In Testing     125.00

 

[angiole]

You might try this (which would make it easier to read:

Select Phase,
sum(case when month(date_created)
between 1 and 3
then amount else 0)) as QTR1,
sum(case when month(date_created)
between 4 and 6
then amount else 0)) as QTR2,
sum(case when month(date_created)
between 7 and 9
then amount else 0)) as QTR3,
sum(amount) as All_QTRs
From Projects
Group by Phase

Keep in mind you may have to group by year if your data/report requires it
Cheers
AA 8~)

 

[r937]

the part you're having problems with, to include all the phases each quarter, even if there are no projects created during that quarter for that phase, is solved by using a left outer join from a table that contains all the phases and all the quarters

assuming the projects table had a quarter column, it would be something like

select p.phase, q.quarter
     , sum(coalesce(x.DollarAmt,0.00)) 
          as SumDollarAmt
   from (  ( select 'Submitted'   as phase
             union all
             select 'In Development'
             union all
             select 'In Testing' ) as p
          cross join
           ( select 'Q1'          as quarter
             union all
             select 'Q2'
             union all
             select 'Q3'
             union all
             select 'Q4' ) as q
        )
  left outer join  projects x
    on p.phase = x.phase
     and q.quarter = x.quarter
group
    by p.phase, q.quarter

and you would have to do another substitution to embody the CASE construct to transform the date into a quarter

rudy

http://rudy.ca/

 

[adam0101]

Thanks r937,
I've actually used that technique before and it works great, however, I forgot to mention that I have to work in Access for this project. Can you do this in Access?

 

[r937]

when you must select from a table, create or use a table with just one row

in Oracle, you can use the DUAL table, although it's often better to declare one yourself

in it, you can store all the "single value" pieces of information that sometimes are hard-coded into applications

for example, discount rates for customers

create table ooak
( discount_bestcustomers integer not null
, discount_goodcustomers integer not null
);
insert into ooak
values (20, 15);

"ooak" stands for one of a kind (i used to work on idms databases)

you would use these discount rates in any query, just like a variable, and all you have to do is join the ooak table to the rest of the tables -- you don't even need a join condition, since without it, you get a cross-join, joining every row of the rest of the query with all the rows (i.e. just one) of the ooak table

anyhow, getting back to your question...

neither do you need to select any columns from the ooak table

for example,

select 'Submitted'   as phase
  from ooak
union all
select 'In Development'
  from ooak
union all
select 'In Testing'
  from ooak

rudy

 

[angiole]

Why This forum gives you a 3x3 inch dialog box to answer questions I'll never know.

With the following you will retrieve all project phases existing, ( I added 2 more phases to demonstrate the no dollar amount scenario, for 'Peer Review')

ProjectID Phase Dollar_Amount Date_Created
1 Submitted 100 2002-01-01
2 Submitted 111 2002-03-01
3 In Development 225 2002-02-01
4 Testing 125 2002-05-05
5 Peer Review 0
6 Production 1000 2002-11-11

Which eventually gives the following result:

Phase QTR1 QTR2 QTR3 QTR4 All_QTRS
In Development 225 0 0 0 225
Peer Review 0 0 0 0 0
Production 0 0 0 1000 1000
Submitted 211 0 0 0 211
Testing 0 125 0 0 125

Query:

SELECT QP.Phase, QQ.QTR1, QQ.QTR2, QQ.QTR3, QQ.QTR4, QQ.All_QTRS
FROM [Select distinct Phase
From Projects]. AS QP, [Select Phase,
sum(iif(month(date_created) between 1 and 3,dollar_amount,0)) AS QTR1,
sum(iif(month(date_created) between 4 and 6,dollar_amount,0)) AS QTR2,
sum(iif(month(date_created) between 7 and 9,dollar_amount,0)) AS QTR3,
sum(iif(month(date_created) between 10 and 12,dollar_amount,0)) AS QTR4,
sum(dollar_amount) AS All_QTRS
From Projects
Group by Phase]. AS QQ
WHERE (((QP.Phase)=[QQ].[Phase]));


Cheers AA 8~)

 

[r937]

that's quite elegant, angiole -- well, except for all the typos ;-)

however, the SELECT DISTINCT PHASE FROM PROJECTS subquery will bring back only those phases actually in the projects table at the time

if there's no project in Testing at the time of the report, then the Testing line will be missing, and most report users will come right back and ask you where it went

i may be wrong but i think that was part of adam0101's original requirement

rudy

 

[angiole]

R937, you are correct, and the fix is to change query QP to select the distinct list of project phases from a 'Project Phases List' table(?).

You get the idea... AA 8~)

 

[r937]

yes, a "project phases" table works well -- you wouldn't need the DISTINCT, though, right? [grin]

in general, a good database design will have several of these "type" or "description" tables, which will be used primarily for decoding the keys used in fact tables, but can also be used for ensuring all occurrences are present in a query result set

the type table would probably not come into the query as a subselect, but rather as a "driving" table in a cross-join

if there were a Quarter table, then you could also easily select which quarters to report on with a simple WHERE condition, whereas with the SUM(CASE) approach, you'd have to perform major surgery on the query ;o)


rudy

 

[angiole]

Oui, ç'est vrai .. AA 8~)