CODEWITHSUNDEEP
sqlsql-servergroup-by
Bill Gates
Bill Gates

Reputation: 857

Group data by month

I want to group by results of sales by YEAR-MONTH. My current query which does the results looks like :

SELECT Cast(Year(s.datekey) AS VARCHAR(4)) + '-' 
       + Cast(Month(s.datekey) AS VARCHAR(2)) AS Mjesec, 
       e.employeekey, 
       Sum(s.totalcost)                       AS SalesAmount 
FROM   factsales s 
       INNER JOIN dimstore st 
               ON s.storekey = st.storekey 
       INNER JOIN dimemployee e 
               ON e.employeekey = st.storemanager 
WHERE  s.datekey BETWEEN '2007-01-01' AND '2007-01-05' 
GROUP  BY e.employeekey, 
          Cast(Year(s.datekey) AS VARCHAR(4)) + '-' 
          + Cast(Month(s.datekey) AS VARCHAR(2)) 
ORDER  BY employeekey

I am wondering if there are any other better and more elegant way to achieve same results? Perhaps, more friendly format for DateTime in c#?

Upvotes: 0

Views: 490

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Much more efficient to strip time by using date math than converting to a string. Also much more efficient to leave the particular string formatting to the end (or better yet, doing it in C# using Format()).

;WITH c AS
(
  SELECT m = DATEADD(MONTH, DATEDIFF(MONTH, 0, s.datekey), 0),
   e.employeekey, s.totalcost
  FROM dbo.factsales AS s
  INNER JOIN dbo.dimstore AS st
  ON s.storekey = st.storekey
  INNER JOIN dimemployee AS e
  ON e.employeekey = st.storemanager
  WHERE s.datekey >= '20070101' AND s.datekey < '20070106'
),
d AS
(
  SELECT m, employeekey, SalesAmount = SUM(totalcost)
    FROM c
    GROUP BY m, employeekey
)
SELECT 
  Mjesec = CONVERT(CHAR(7), m, 120), 
  employeekey, 
  SalesAmount
FROM d
ORDER BY employeekey;

If you can do the formatting in your app, then you can collapse this to:

;WITH c AS
(
  SELECT m = DATEADD(MONTH, DATEDIFF(MONTH, 0, s.datekey), 0),
   e.employeekey, s.totalcost
  FROM dbo.factsales AS s
  INNER JOIN dbo.dimstore AS st
  ON s.storekey = st.storekey
  INNER JOIN dimemployee AS e
  ON e.employeekey = st.storemanager
  WHERE s.datekey >= '20070101' AND s.datekey < '20070106'
)
SELECT Mjesec = m, employeekey, SalesAmount = SUM(totalcost)
  FROM c
  GROUP BY m, employeekey
  ORDER BY employeekey;

Or even:

  SELECT Mjesec = DATEADD(MONTH, DATEDIFF(MONTH, 0, s.datekey), 0),
   e.employeekey, s.totalcost
  FROM dbo.factsales AS s
  INNER JOIN dbo.dimstore AS st
  ON s.storekey = st.storekey
  INNER JOIN dimemployee AS e
  ON e.employeekey = st.storemanager
  WHERE s.datekey >= '20070101' AND s.datekey < '20070106'
  GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, s.datekey, 0), 0), e.employeekey
  ORDER BY employeekey;

Upvotes: 1

Lamak
Lamak

Reputation: 70638

Do:

SELECT CONVERT(CHAR(7),s.DateKey,120) AS Mjesec....
FROM ....
GROUP BY CONVERT(CHAR(7),s.DateKey,120)....
ORDER BY EmployeeKey

Upvotes: 1

Related Questions