Help with between statement in a select

Posted on 2013-08-30
Medium Priority
Last Modified: 2013-08-30
I have a table with a list of dates and hours associated with those dates. I want to produce a report that sums the hours for a month. I want to change all the dates for a month to the same date for a group by statement. How can I accomplish this?
Question by:NickMalloy
  • 2
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39452931
SELECT  CONVERT(varchar(6), datecolumn, 112) +'01' as date, sum(hrs) totl
from yourTable
GROUP BY CONVERT(varchar(6), datecolumn, 112)

** Note : in future, it will always better if you provide the table structure or sample data'
LVL 72

Accepted Solution

Qlemo earned 1600 total points
ID: 39452938
One way is to use:
select year(datecol), month(datecol), sum(hourcol)
from tbl
group by year(datecol), month(datecol)

Open in new window

select yr, mt, sum(hourcol)
from (
  select yr=year(datecol), mt=month(datecol), *
  from tbl
) grptbl
group by yr, mt

Open in new window

LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39453078
Did you try the first one ?

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

627 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