SUM from the first month of the year until selected month of the year SQL

Question

I have a big data to select. The data is hourly based. In a day would be 24 records, So in a month would be 24 * 30 = 720 records. This is only for one group, I've 3 groups in the table. Let say, Group A, B, and C. So the total for a month would be 720 * 3 = 2,160 records.
The data has been run since 2008.

Currently, I have to calculate the total of one of the field in that table. Now let say the table name is 'oTable'. I've the following t-sql:

;WITH ff AS (
  SELECT GroupCode, 
  DateAdd(hh,-1,DateAdd(hh,DatePart(hh,myTime),myDate)) AS newDate, Value
  FROM oTable
  )
SELECT GroupCode, CAST(newDate AS DATE) AS MainDate, SUM(Value)
FROM ff
GROUP BY GroupCode, CAST(newDate AS DATE)

It will calculate the value from 01:00:00 to 00:00:00 on the next day. It will come a daily base data.

To prevent load to much data, I'm using this t-sql:

--=========================================
DECLARE @date DATE;
DECLARE @MonthID int = 3;
DECLARE @Year int = 2014;

SELECT @date = Cast (CONVERT(VARCHAR(4), @Year) + '-' + CONVERT(VARCHAR(2), @MonthID) + '-01' AS DATE)

SELECT GroupCode, myDate, myTime, Value 
INTO #TempTable
FROM oTable
WHERE myDate BETWEEN Dateadd(day, -1, @date) AND Dateadd(day, 1, Dateadd(day, -Day(@date), Dateadd(month, 1, @date)))

;WITH ff AS (
      SELECT GroupCode, 
      DateAdd(hh,-1,DateAdd(hh,DatePart(hh,myTime),myDate)) AS newDate, Value
      FROM #TempTable
      )
    SELECT GroupCode, CAST(newDate AS DATE) AS MainDate, SUM(Value)
    FROM ff
    GROUP BY GroupCode, CAST(newDate AS DATE)
--========================================

My question is, I want to calculate the total of 'value' start from the beginning of the year from the parameter. Let say, the parameter are @monthid = '3' and @year = '2014'... I need to know the total of value start from 1, 2014 until 3, 2014.

Does anyone know how to do this?If I didn't use the script above (to prevent load to much data), it is easy, but extremely slow.
Thank you.

score 1 · Accepted Answer · answered Jan 20 '15 at 05:31

DECLARE @date1 DATE;
DECLARE @date2 DATE;
DECLARE @MonthID int = 3;
DECLARE @Year int = 2014;
SELECT @date1=DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(CAST(@MonthID AS VARCHAR)+'/01/'+CAST(@Year AS VARCHAR)AS DATE)))
SELECT @date2=CAST('01/01/'+CAST(@Year AS VARCHAR)AS DATE)
SELECT @date1,@date2


SELECT SUBSTRING(CONVERT(VARCHAR,myDate,113),4,8) MonthYear,GroupCode,SUM(Value)
FROM oTable
WHERE myDate BETWEEN @Date2 AND @Date1
GROUP BY SUBSTRING(CONVERT(VARCHAR,myDate,113),4,8),GroupCode

score 0 · Answer 2 · edited May 23 '17 at 12:12

0

Does your table have a clustered index on the date field? That data is incremental, so it would be a great candidate. That should really improve the performance of your queries.

Can you modify the table? Try adding a persisted computed column to represent the month/year (Convert date to YYYYMM format), put an index on that, and then use it in your query.

edited May 23 '17 at 12:12

Community

1
1

answered Jan 20 '15 at 06:34

jlee-tessik

1,510
12
16

The table has a field which is identity field. I didn't get you. "Can you modify the table?" – Haminteu Jan 21 '15 at 01:46
1

Sometimes people are working with databases that they cannot change (third party software, for instance), so I was just making sure you could modify the DDL of the table. Do look into proper indexing on the table, it will make your query run a lot smoother. – jlee-tessik Feb 04 '15 at 16:34

SUM from the first month of the year until selected month of the year SQL

2 Answers2