CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Haminteu
Haminteu

Reputation: 1334

SUM from Specific Date until the end of the month SQL

I have the following table:

ID   GROUPID    oDate       oValue
1    A          2014-06-01  100
2    A          2014-06-02  200
3    A          2014-06-03  300
4    A          2014-06-04  400
5    A          2014-06-05  500
FF. until the end of the month
30   A          2014-06-30  600

I have 3 kinds of GROUPID, and each group will create one record per day.
I want to calculate the total of oValue from the 2nd day of each month until the end of the month. So the total of June would be from 2/Jun/2014 until 30/Jun/2014. If July, then the total would be from 2/Jul/2014 until 31/Jul/2014.

The output will be like this (sample):

GROUPID   MONTH   YEAR   tot_oValue
A         6       2014   2000
A         7       2014   3000
B         6       2014   1500
B         7       2014   5000

Does anyone know how to solve this with sql syntax?
Thank you.

Upvotes: 0

Views: 2530

Answers (4)

Rimas
Rimas

Reputation: 6024

This query produces required output:

SELECT GROUPID, MONTH(oDate) AS "Month", YEAR(oDate) AS "Year", SUM(oValue) AS tot_oValue
  FROM table_name
  WHERE DAY(oDate) > 1
  GROUP BY GROUPID, YEAR(oDate), MONTH(oDate)
  ORDER BY GROUPID, YEAR(oDate), MONTH(oDate)

Upvotes: 1

GarethD
GarethD

Reputation: 69819

You can use a correlated subquery to get this:

SELECT  T.ID,
        T.GroupID,
        t.oDate,
        T.oValue,
        ct.TotalToEndOfMonth
FROM    T
        OUTER APPLY
        (   SELECT  TotalToEndOfMonth = SUM(oValue)
            FROM    T AS T2
            WHERE   T2.GroupID = T.GroupID
            AND     T2.oDate >= T.oDate
            AND     T2.oDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, T.oDate) + 1, 0)
        ) AS ct;

For your example data this gives:

ID  GROUPID ODATE       OVALUE  TOTALTOENDOFMONTH
1   A       2014-06-01  100     2100
2   A       2014-06-02  200     2000
3   A       2014-06-03  300     1800
4   A       2014-06-04  400     1500
5   A       2014-06-05  500     1100
30  A       2014-06-30  600     600

Example on SQL Fiddle

For future reference if you ever upgrade, in SQL Server 2012 (and later) this becomes even easier with windowed aggregate functions that allow ordering:

SELECT  T.*,
        TotalToEndOfMonth = SUM(oValue) 
                                OVER (PARTITION BY GroupID, 
                                                    DATEPART(YEAR, oDate), 
                                                    DATEPART(MONTH, oDate) 
                                        ORDER BY oDate DESC)
FROM    T
ORDER BY oDate;

Example on SQL Fiddle

EDIT

If you only want this for the 2nd of each month, but still need all the fields then you can just filter the results of the first query I posted:

SELECT  T.ID,
        T.GroupID,
        t.oDate,
        T.oValue,
        ct.TotalToEndOfMonth
FROM    T
        OUTER APPLY
        (   SELECT  TotalToEndOfMonth = SUM(oValue)
            FROM    T AS T2
            WHERE   T2.GroupID = T.GroupID
            AND     T2.oDate >= T.oDate
            AND     T2.oDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, T.oDate) + 1, 0)
        ) AS ct
WHERE   DATEPART(DAY, T.oDate) = 2;

Example on SQL Fiddle

If you are only concerned with the total then you can use:

SELECT  T.GroupID,
        [Month] = DATEPART(MONTH, oDate),
        [Year] = DATEPART(YEAR, oDate),
        tot_oValue = SUM(T.oValue)
FROM    T
WHERE   DATEPART(DAY, T.oDate) >= 2
GROUP BY T.GroupID, DATEPART(MONTH, oDate), DATEPART(YEAR, oDate);

Example on SQL Fiddle

Upvotes: 3

jpw
jpw

Reputation: 44921

If you want grouped per GROUPID, year and month this should do it:

SELECT 
    GROUPID, 
    [MONTH] = MONTH(oDate), 
    [YEAR]  = YEAR(oDate), 
    tot_oValue = SUM(ovalue) 
FROM your_table
WHERE DAY(odate) > 1
GROUP BY GROUPID, YEAR(oDate), MONTH(oDate)
ORDER BY GROUPID, YEAR(oDate), MONTH(oDate)

Upvotes: 1

huMpty duMpty
huMpty duMpty

Reputation: 14470

Not sure whether you have data for different years

Select YEAR(oDate),MONTH(oDate),SUM(Value) 
From #Temp
Where DAY(oDate)>1
Group By YEAR(oDate),MONTH(oDate)

Upvotes: 1

Related Questions