Solved

Dates sorting by months

Posted on 2012-04-09
3
400 Views
Last Modified: 2012-04-09
Hello experts,

i have a table that is having a a daily collection of numbers of employee hours, and I want to create a query that will give me a sum of total hours of employees sorted by months,

below is the sample of query that i want to create

Jan-12      11904
Feb-12      11376
Mar-12      10912

here is the sample data that i have in the table

     Date           noe;      Hours accumulated
01/01/2012      50         400
02/01/2012      50    400
03/01/2012      50    400
04/01/2012      50      400
05/01/2012      50      400
06/01/2012      60      480
07/01/2012      50      400
08/01/2012      50      400
09/01/2012      50      400
10/01/2012      50      400
11/01/2012      45      360
12/01/2012      50      400
13/01/2012      50      400
14/01/2012      50      400
15/01/2012      13      104
16/01/2012      50      400
17/01/2012      50      400
18/01/2012      50      400
19/01/2012      50      400
20/01/2012      50      400
21/01/2012      50      400
22/01/2012      20      160
23/01/2012      50      400
24/01/2012      50      400
25/01/2012      50      400
26/01/2012      50      400
27/01/2012      50      400
28/01/2012      50      400
29/01/2012      50      400
30/01/2012      50      400
31/01/2012      50      400
01/02/2012      50      400
02/02/2012      50      400
03/02/2012      50      400
04/02/2012      50      400
05/02/2012      35      280
06/02/2012      50      400
07/02/2012      50      400
08/02/2012      50      400
09/02/2012      50      400
10/02/2012      50      400
11/02/2012      50      400
12/02/2012      50      400
13/02/2012      23      184
14/02/2012      50      400
15/02/2012      89      712
16/02/2012      50      400
17/02/2012      67      536
18/02/2012      50      400
19/02/2012      45      360
20/02/2012      50      400
21/02/2012      67      536
22/02/2012      50      400
23/02/2012      56      448
24/02/2012      50      400
25/02/2012      34      272
26/02/2012      50      400
27/02/2012      6      48
28/02/2012      50      400
29/02/2012      50      400
01/03/2012      5      40
02/03/2012      50      400
03/03/2012      10      80
04/03/2012      50      400
05/03/2012      50      400
06/03/2012      13      104
07/03/2012      50      400
08/03/2012      20      160
09/03/2012      50      400
10/03/2012      50      400
11/03/2012      50      400
12/03/2012      29      232
13/03/2012      50      400
14/03/2012      75      600
15/03/2012      50      400
16/03/2012      56      448
17/03/2012      50      400
18/03/2012      25      200
19/03/2012      50      400
20/03/2012      34      272
21/03/2012      50      400
22/03/2012      67      536
23/03/2012      50      400
24/03/2012      89      712
25/03/2012      50      400
26/03/2012      78      624
27/03/2012      50      400
28/03/2012      31      248
29/03/2012      50      400
30/03/2012      32      256

I hope that you will help me with this

Thanks...

Czher
0
Comment
Question by:Czher
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 350 total points
ID: 37823216
try this query


SELECT Format([Date],"mmm-yy") AS dDate, Sum(yourTable.[hours]) AS [Hours accumulated]
FROM yourTable
GROUP BY Format([Date],"mmm-yy"), Format([Date],"yyyymm")
ORDER BY Format([Date],"yyyymm")
0
 
LVL 22

Assisted Solution

by:Om Prakash
Om Prakash earned 150 total points
ID: 37823229
Try the following:

SELECT Year(DateField), Month(DateField), Count(Hours_accumulated) FROM Table_Name
 GROUP BY Year(DateField), Month(DateField)
0
 

Author Closing Comment

by:Czher
ID: 37823295
those answers are both accepted

Thanks.....
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question