Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

Dates sorting by months

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
Czher
Asked:
Czher
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
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
 
Om PrakashCommented:
Try the following:

SELECT Year(DateField), Month(DateField), Count(Hours_accumulated) FROM Table_Name
 GROUP BY Year(DateField), Month(DateField)
0
 
CzherAuthor Commented:
those answers are both accepted

Thanks.....
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now