Query to summarize/Count by month

  • Thread starter Thread starter Pinacle
  • Start date Start date
P

Pinacle

I have a table that contains schedule dates entered by user for 'Work Start
Date','Expected Completion Date', 'Completion Date' (3 columns)...I am trying
to summarize the above data and create another table to show the count of
months in column.

I have acheived this by using the below query ...but i am able to run the
query for only one column, i want to summarize all three columns together.
SELECT Count(Format(['Work Start Date],"mmm"))
FROM Emp
GROUP BY Format(['Work Start Date],"mmm");

My O/p should be like this...
Month WorkStDate ExpCompDate CompDate
Jan 1 1 1
Feb 0 1 1
Mar 2 1 1
Apr 4 3 2

Please help me....if you need further information to understand my problem i
am glad to explain.

Thanks in Advance
 
I am trying to summarize the above data and create another table to show
the count of months in column.
What do the numbers in the columns represent?

You posted what you want as results but what does your input data look like?

The query you posted can not give you the results you posted.
 
Karl,

My input table looks like this

workStartDate ExpectedCompletionDate ActualCompletionDate
3-Jan-09 6-feb-09 6-feb-09
12-Apr-09 12-apr-09 17-may-09
22-jun-09 28-july-09 26-jun-09
1-sep-09 20-sep-09 15-oct-09
10-apr-09 12-apr-09 10-may-09

O/p should look like this
What do the numbers in the columns represent?
Count of the month appreared in a particular column. For example i have
month 'April' appeared twice in workStartDate column thats why i have count =
2 in the output.
Month workStartDate ExpectedCompletionDate ActualCompletionDate
Jan 1 0
0
Feb 0 1
1
Mar 0 0
0
Apr 2 2
0
May 0 0
2
Jun 1 0
1
Jul 0 1
0
Aug 0 0
0
Sep 1 1
0
Oct 0 0
1
Nov 0 0
0
Dec 0 0
0

Thanks.








KARL DEWEY said:
the count of months in column.
What do the numbers in the columns represent?

You posted what you want as results but what does your input data look like?

The query you posted can not give you the results you posted.

--
Build a little, test a little.


Pinacle said:
I have a table that contains schedule dates entered by user for 'Work Start
Date','Expected Completion Date', 'Completion Date' (3 columns)...I am trying
to summarize the above data and create another table to show the count of
months in column.

I have acheived this by using the below query ...but i am able to run the
query for only one column, i want to summarize all three columns together.
SELECT Count(Format(['Work Start Date],"mmm"))
FROM Emp
GROUP BY Format(['Work Start Date],"mmm");

My O/p should be like this...
Month WorkStDate ExpCompDate CompDate
Jan 1 1 1
Feb 0 1 1
Mar 2 1 1
Apr 4 3 2

Please help me....if you need further information to understand my problem i
am glad to explain.

Thanks in Advance
Click to expand...
Click to expand...
 
Build a table like this --
M Mon Month Mn mmm
1 Jan January 01 1/1/2009
2 Feb February 02 2/2/2009
3 Mar March 03 3/3/2009
4 Apr April 04 4/4/2009
5 May May 05 5/5/2009
6 Jun June 06 6/6/2009
7 Jul July 07 7/7/2009
8 Aug August 08 8/8/2009
9 Sep September 09 9/9/2009
10 Oct October 10 10/10/2009
11 Nov November 11 11/11/2009
12 Dec December 12 12/12/2009

Then this query using your table name ---
SELECT Month_List.Mon, Sum(IIf(Format([workStartDate],"mmm")=[Mon],1,0)) AS
workStartDate_, Sum(IIf(Format([ExpectedCompletionDate],"mmm")=[Mon],1,0)) AS
ExpectedCompletionDate_,
Sum(IIf(Format([ActualCompletionDate],"mmm")=[Mon],1,0)) AS
ActualCompletionDate_
FROM Month_List, Pinacle
GROUP BY Month_List.Mon, Month_List.M
ORDER BY Month_List.M;

--
Build a little, test a little.


Pinacle said:
Karl,

My input table looks like this

workStartDate ExpectedCompletionDate ActualCompletionDate
3-Jan-09 6-feb-09 6-feb-09
12-Apr-09 12-apr-09 17-may-09
22-jun-09 28-july-09 26-jun-09
1-sep-09 20-sep-09 15-oct-09
10-apr-09 12-apr-09 10-may-09

O/p should look like this
What do the numbers in the columns represent?
Count of the month appreared in a particular column. For example i have
month 'April' appeared twice in workStartDate column thats why i have count =
2 in the output.
Month workStartDate ExpectedCompletionDate ActualCompletionDate
Jan 1 0
0
Feb 0 1
1
Mar 0 0
0
Apr 2 2
0
May 0 0
2
Jun 1 0
1
Jul 0 1
0
Aug 0 0
0
Sep 1 1
0
Oct 0 0
1
Nov 0 0
0
Dec 0 0
0

Thanks.








KARL DEWEY said:
I am trying to summarize the above data and create another table to show
Click to expand...
the count of months in column.
What do the numbers in the columns represent?

You posted what you want as results but what does your input data look like?

The query you posted can not give you the results you posted.

--
Build a little, test a little.


Pinacle said:
I have a table that contains schedule dates entered by user for 'Work Start
Date','Expected Completion Date', 'Completion Date' (3 columns)...I am trying
to summarize the above data and create another table to show the count of
months in column.

I have acheived this by using the below query ...but i am able to run the
query for only one column, i want to summarize all three columns together.
SELECT Count(Format(['Work Start Date],"mmm"))
FROM Emp
GROUP BY Format(['Work Start Date],"mmm");

My O/p should be like this...
Month WorkStDate ExpCompDate CompDate
Jan 1 1 1
Feb 0 1 1
Mar 2 1 1
Apr 4 3 2

Please help me....if you need further information to understand my problem i
am glad to explain.

Thanks in Advance
Click to expand...
Click to expand...
Click to expand...
 
Back
Top