asked on

SQL Syntax to Group Data by Month and Year and SUM and Date Criteria

Hi All,

Leading on from my previous question 'SQL Syntax to Group Data by Month and Year and SUM', how would I extend the SQL syntax included to enable the data to be ordered by the date ASC and to include a BETWEEN date period criteria?

My original table was this:

RecordID Customer ColDate Cost
1 so 01/02/2009 10
2 po 28/12/2008 2
3 so 12/02/2009 8
4 cd 13/02/2009 10
5 so 14/01/2009 3

The results should appear as follows if between January 2009 and March 2009 was used as a criteria:

so January 2009 3
so February 2009 18
cd February 2009 10

Many thanks,

Rit

SELECT 
          Customer, 
          DATENAME (month, ColDate )+ DATENAME(year, ColDate ), 
          SUM(Cost) as cost
 
from urTable 
 
GROUP BY 
          DATENAME (month, ColDate )+ DATENAME(year, ColDate ), 
          Customer

Open in new window

Patrick Matthews

SELECT Customer, YEAR(ColDate) AS Yr, MONTH(ColDate) AS Mon, SUM(Cost) AS Cost
FROM urTable
GROUP BY Customer, YEAR(ColDate), MONTH(ColDate)
ORDER BY Customer, YEAR(ColDate), MONTH(ColDate)

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

shru_0409

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TheGD

SELECT
Customer,
DATENAME(MONTH, ColDate)+Cast(YEAR(ColDate) as varchar) as ColDate,
SUM(Cost) as Cost,
MONTH(ColDate) as "Month No.",
Year(ColDate) as "Year"
from urTable
GROUP BY
Customer,
DATENAME(MONTH, ColDate)+Cast(YEAR(ColDate) as varchar) as ColDate,
SUM(Cost) as Cost,
MONTH(ColDate) as "Month No.",
Year(ColDate) as "Year"
Order By
Customer,
Year(ColDate) as "Year",
MONTH(ColDate) as "Month No.",
SUM(Cost) as Cost,
DATENAME(MONTH, ColDate)+Cast(YEAR(ColDate) as varchar) as ColDate

This query will display result exactly as you required, but additionally it will display two more columns MONTH(ColDate) as "Month No.", Year(ColDate) as "Year" just for correct ordering.

SOLUTION

TheGD

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TheGD

If you ultimately do not require the additional columns and still keep the ordering sequence then a possible solution will be as follows:
(I wont recommend it as it will take a bit more execution time specially on larger scale)

Select Customer, ColDate, Cost From
(
SELECT 
          Customer, 
          DATENAME(MONTH, ColDate)+Cast(YEAR(ColDate) as varchar) as ColDate, 
          SUM(Cost) as Cost,
          MONTH(ColDate) as "Month No.",
          Year(ColDate) as "Year"
from urTable  
GROUP BY 
          Customer, 
          DATENAME(MONTH, ColDate)+Cast(YEAR(ColDate) as varchar) as ColDate, 
          MONTH(ColDate) as "Month No.",
          Year(ColDate) as "Year"
Order By
          Customer,
          Year(ColDate) as "Year", 
          MONTH(ColDate) as "Month No.",
          DATENAME(MONTH, ColDate)+Cast(YEAR(ColDate) as varchar) as ColDate
) as BaseResult

Open in new window

rito1

ASKER

Thanks all, I ended up using pieces from each solution.

Rit