SQL查询以一年为基础从表中获取数据. [英] Sql query to get the data from table in quarterly basis of a year.

问题描述

考虑一个包含员工列表和员工完成的销售的表，问题是我需要根据日期(即按季度)显示他们的销售详细信息. (一年四个季度).


例如:

Date        | EmpName  | ProductSell
--------------------------------------------- 
1st Quarter | A        |    3
            | B        |    6
----------------------------------------------
2nd Quarter | C        |    12 
-----------------------------------------------

请对此做答复.

我尝试过的事情:

我已经完成了联接表，并通过[[In(''january''，``february''，``march'')]等in()获得了月份的名称，并按月份使用了分组名称. class ="h2_lin">解决方案

CREATE TABLE #Sales(Empname CHAR(2),ProductSell INT,SellingDate DATE);

INSERT INTO #Sales 
  VALUES(''A'',7,''2017-11-01''),
        (''A'',5,''2016-12-14''),
        (''C'',5,''2017-02-11''),
        (''A'',5,''2017-05-11''),
        (''B'',10,''2016-11-04''),
        (''B'',3,''2017-09-05''),
        (''D'',1,''2017-11-05''),
         (''A'',4,''2017-11-05'');

SELECT Empname,SUM(productsell) AS productsell,YEAR(SellingDate) AS SaleYear,
       (CASE WHEN DATEPART(QQ,SellingDate)=1 THEN ''1st QUARTER''
            WHEN DATEPART(QQ,SellingDate)=2 THEN ''2nd QUARTER''
            WHEN DATEPART(QQ,SellingDate)=3 THEN ''3rd QUARTER''
            WHEN DATEPART(QQ,SellingDate)=4 THEN ''4th QUARTER''
                  END) AS Sale_QUARTER,
       MAX(DateName(MOnth,SellingDate)) AS Sale_MONTH
  FROM #Sales
     GROUP BY YEAR(SellingDate), DATEPART(QQ,SellingDate),
               DATEPART(MONTH,SellingDate), Empname 
      ORDER BY YEAR(SellingDate),DATEPART(QQ,SellingDate),DATEPART(MONTH,SellingDate); 

--------------------------------------------------------------------
Empname	productsell	SaleYear	Sale_QUARTER	Sale_MONTH
------------------------------------------------------------------------
B 	10	2016	4th QUARTER	November
A 	5	2016	4th QUARTER	December
C 	5	2017	1st QUARTER	February
A 	5	2017	2nd QUARTER	May
B 	3	2017	3rd QUARTER	September
A 	11	2017	4th QUARTER	November
D 	1	2017	4th QUARTER	November

Consider a table containing a list of employees and sales done by employees, the question is I need to display their sales details depend on the date i.e. on Quarter basis. (all four quarters of a year).


For example:

Date        | EmpName  | ProductSell
--------------------------------------------- 
1st Quarter | A        |    3
            | B        |    6
----------------------------------------------
2nd Quarter | C        |    12 
-----------------------------------------------

Please do reply a query for it.

What I have tried:

I have done with join tables and get their name of the month and by in() like [In(''january'',''february'',''march'')] and used group by month name.

解决方案

CREATE TABLE #Sales(Empname CHAR(2),ProductSell INT,SellingDate DATE);

INSERT INTO #Sales 
  VALUES(''A'',7,''2017-11-01''),
        (''A'',5,''2016-12-14''),
        (''C'',5,''2017-02-11''),
        (''A'',5,''2017-05-11''),
        (''B'',10,''2016-11-04''),
        (''B'',3,''2017-09-05''),
        (''D'',1,''2017-11-05''),
         (''A'',4,''2017-11-05'');

SELECT Empname,SUM(productsell) AS productsell,YEAR(SellingDate) AS SaleYear,
       (CASE WHEN DATEPART(QQ,SellingDate)=1 THEN ''1st QUARTER''
            WHEN DATEPART(QQ,SellingDate)=2 THEN ''2nd QUARTER''
            WHEN DATEPART(QQ,SellingDate)=3 THEN ''3rd QUARTER''
            WHEN DATEPART(QQ,SellingDate)=4 THEN ''4th QUARTER''
                  END) AS Sale_QUARTER,
       MAX(DateName(MOnth,SellingDate)) AS Sale_MONTH
  FROM #Sales
     GROUP BY YEAR(SellingDate), DATEPART(QQ,SellingDate),
               DATEPART(MONTH,SellingDate), Empname 
      ORDER BY YEAR(SellingDate),DATEPART(QQ,SellingDate),DATEPART(MONTH,SellingDate); 

--------------------------------------------------------------------
Empname	productsell	SaleYear	Sale_QUARTER	Sale_MONTH
------------------------------------------------------------------------
B 	10	2016	4th QUARTER	November
A 	5	2016	4th QUARTER	December
C 	5	2017	1st QUARTER	February
A 	5	2017	2nd QUARTER	May
B 	3	2017	3rd QUARTER	September
A 	11	2017	4th QUARTER	November
D 	1	2017	4th QUARTER	November

这篇关于SQL查询以一年为基础从表中获取数据.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！