'Query to calculate the weekly wise data of a fields

I need to build a query to calculate the sum/total of a product_id, Product_Name fields based on Released_Date, revision_date, IS_UPDATED and display the output like the following. And I would appreciate if it shows the starting date and ending date instead of Week-1, Week-2.

Table structure and insert script as below.

create table products (
    Released_Date varchar(40)
    , product_id varchar(40)
    , Product_Name varchar(40)
    , revision_date varchar(40)
    , IS_UPDATED varchar(2)
)

+-----------------+------------+-------------+--------------------------+---------------

 insert into products values('2018-04-25 00:00:0','Pega','Pega5.0','2018-04-27 00:00:00','N');
 insert into products values('2018-05-11 00:00:00','Oracle','orace11g','2018-05-13 00:00:00','Y');
 insert into products values('2018-04-04 00:00:00','Oracle',' OracleBPM','2018-04-06 00:00:00','Y');
 insert into products values('2018-06-05 00:00:00','Ibm','Cognos','2018-06-08 00:00:00','Y');
 insert into products values('2018-05-03 00:00:00','Microsoft','C++','2018-05-05 00:00:00','Y'); 
 insert into products values('2018-05-21 00:00:00','Microsoft',' C#','2018-05-25 00:00:00','Y');
 insert into products values('2018-04-10 00:00:00','Salesforce','CPQ','2018-04-13 00:00:00','Y'); 
 insert into products values('2018-03-12 00:00:00','Java',' Struts','2018-03-15 00:00:00','Y');  
 insert into products values('2018-04-12 00:00:00','Salesforce','Analytics','2018-04-13 00:00:00','Y'); 
 insert into products values('2018-05-09 00:00:00','Microsoft','Asp','2018-05-11 00:00:00','Y');  
 insert into products values('2018-05-28 00:00:00','Salesforce','Marketing','2018-05-31 00:00:00','N');
 insert into products values('2018-04-11 00:00:00','ETL',' Informatica','2018-04-12 00:00:00',' Y');  
 insert into products values('2018-03-26 00:00:00','Oracle',' orace11g','2018-03-30 00:00:00','Y'); 
 insert into products values('2018-04-19 00:00:00','Oracle',' obiee','2018-04-20 00:00:00','Y');
 insert into products values('2018-04-16 00:00:00','Ibm','Datastage','2018-04-17 00:00:00','N');
 insert into products values('2018-06-18 00:00:00','Microsoft','C#','2018-06-21 00:00:00','Y');
 insert into products values('2018-06-19 00:00:00','ETL',' Informatica','2018-06-24 00:00:00','Y');
 insert into products values('2018-06-22 00:00:00','Microsoft','WCF','2018-06-23 00:00:00','Y');
 insert into products values('2018-04-19 00:00:00','Hadoop',' Hive','2018-04-20 00:00:00',' Y');
 insert into products values('2018-04-16 00:00:00','Testing','Database','2018-04-20 00:00:00','N'); 
 insert into products values('2018-04-24 00:00:00','Ibm','Cognos',' 2018-04-27 00:00:00','Y');
 insert into products values('2018-06-07 00:00:00','Microsoft','C#','2018-06-08 00:00:00','Y');  
 insert into products values('2018-04-02 00:00:00','Java','Struts','2018-04-05 00:00:00','Y');
 insert into products values('2018-05-01 00:00:00','Microsoft','C++','2018-05-04 00:00:00','Y'); 
 insert into products values('2018-04-10 00:00:00','ETL',' Datastage','2018-04-14 00:00:00','N');  
 insert into products values('2018-04-23 00:00:00','Ibm','AI','2018-04-25 00:00:00','Y');  
 insert into products values('2018-04-03 00:00:00','JAVA','Struts','2018-04-04 00:00:00','N');
 insert into products values('2018-04-23 00:00:00','Pega','Pega5.4','2018-04-25 00:00:00','N');  
 insert into products values('2018-05-28 00:00:00','Java',' Jasperreports','2018-05-30 00:00:00','Y'); 
 insert into products values('2018-05-28 00:00:00','IBM','Watson','2018-05-29 00:00:00','Y');
 insert into products values('2018-05-30 00:00:00','Salesforce','Paradot','2018-05-31 00:00:00','Y');
 insert into products values('2018-05-10 00:00:00','Oracle',' orace12c','2018-05-11 00:00:00','Y');
 insert into products values('2018-06-11 00:00:00','Ibm','Cognos',' 2018-06-13 00:00:00','Y');
 insert into products values('2018-06-13 00:00:00','Ibm','Datastage','2018-06-17 00:00:00','Y');

+-----------------+------------+-------------+--------------------------+---------------

Created_Date        product_id     Product_Name    Released_Date       IS_UPDATED
+-----------------+------------+---------------+--------------------------+---------------
 2018-04-25 00:00:00      Pega          Pega5.0     2018-04-27 00:00:00       N   
 2018-05-11 00:00:00     Oracle       orace11g      2018-05-13 00:00:00       Y  
 2018-04-04 00:00:00     Oracle       OracleBPM     2018-04-06 00:00:00       Y
 2018-06-05 00:00:00      Ibm          Cognos       2018-06-08 00:00:00       Y
 2018-05-03 00:00:00   Microsoft        C++         2018-05-05 00:00:00       Y 
 2018-05-21 00:00:00   Microsoft        C#          2018-05-25 00:00:00       Y
 2018-04-10 00:00:00   Salesforce      CPQ          2018-04-13 00:00:00       Y 
 2018-03-12 00:00:00   Java             Struts      2018-03-15 00:00:00       Y  
 2018-04-12 00:00:00   Salesforce      Analytics    2018-04-13 00:00:00       Y 
 2018-05-09 00:00:00   Microsoft        Asp         2018-05-11 00:00:00       Y  
 2018-05-28 00:00:00   Salesforce      Marketing    2018-05-31 00:00:00       N
 2018-04-11 00:00:00     ETL         Informatica    2018-04-12 00:00:00       Y  
 2018-03-26 00:00:00     Oracle       orace11g      2018-03-30 00:00:00       Y       
 2018-04-19 00:00:00     Oracle       obiee         2018-04-20 00:00:00       Y
 2018-04-16 00:00:00      Ibm          Datastage    2018-04-17 00:00:00       N
 2018-06-18 00:00:00   Microsoft        C#          2018-06-21 00:00:00       Y
 2018-06-19 00:00:00   ETL            Informatica   2018-06-24 00:00:00       Y
 2018-06-22 00:00:00   Microsoft        WCF         2018-06-23 00:00:00       Y
 2018-04-19 00:00:00     Hadoop       Hive          2018-04-20 00:00:00       Y
 2018-04-16 00:00:00      Testing      Database    2018-04-20 00:00:00        N 
 2018-04-24 00:00:00      Ibm          Cognos       2018-04-27 00:00:00       Y   
 2018-06-07 00:00:00   Microsoft        C#          2018-06-08 00:00:00       Y  
 2018-04-02 00:00:00   Java             Struts      2018-04-05 00:00:00       Y
 2018-05-01 00:00:00   Microsoft        C++         2018-05-04 00:00:00       Y 
 2018-04-10 00:00:00     ETL            Datastage   2018-04-14 00:00:00       N  
 2018-04-23 00:00:00      Ibm          AI           2018-04-25 00:00:00       Y  
 2018-04-03 00:00:00    JAVA          Struts        2018-04-04 00:00:00       N
 2018-04-23 00:00:00      Pega          Pega5.4     2018-04-25 00:00:00       N  
 2018-05-28 00:00:00     Java       Jasperreports   2018-05-30 00:00:00       Y 
 2018-05-28 00:00:00    IBM           Watson        2018-05-29 00:00:00       Y
 2018-05-30 00:00:00   Salesforce      Paradot      2018-05-31 00:00:00       Y
 2018-05-10 00:00:00     Oracle       orace12c      2018-05-11 00:00:00       Y
 2018-06-11 00:00:00      Ibm          Cognos       2018-06-13 00:00:00       Y
 2018-06-13 00:00:00      Ibm          Datastage    2018-06-17 00:00:00       Y

Required output:-Based on below conditions.

for Total_productIds, the Created_Date should be greater than 2018-04-01 00:00:00 and Created_Date should be less than 2018-06-30 00:00:00. i.e Created_Date>2018-04-01 00:00:00 and Created_Date<2018-06-30 00:00:00.
for Total_ProductNames, the Created_Date should be greater than 2018-04-01 00:00:00 and Released_Date should be revision_date<2018-06-30 00:00:00 Created_Date>2018-04-01 00:00:00 and Released_Date< 2018-06-30 00:00:00
for Total_IS_Updated, the Created_Date should be greater than 2018-04-01 00:00:00 and Created_Date should be less than 2018-06-30 00:00:00. i.e Created_Date>2018-04-01 00:00:00 and Created_Date<2018-06-30 00:00:00. and IS_UPDATED='Y'

    WEEK NO.                         Total_productIds   Total_ProductNames   Total_IS_Updated(if 'Y')

    Firstweek(2018-04-01)                    0                     0              0
    Secondweek(2018-04-02 to 2018-04-08)     3                     2              2
    Thirdweek(2018-04-09 to 2018-04-15)      3                     5              4
    Fourthweek(2018-04-16 to 2018-04-22)     4                     4              2
    Fifthweek(2018-04-23 to 2018-04-29)      3                     4              2
    
    Firstweek(2018-05-01 to 2018-05-06)      1                     2              2
    Secondweek(2018-05-07 to 2018-05-13)     2                     3              3
    Thirdweek(2018-05-14 to 2018-05-20)      0                     0              0
    Fourthweek(2018-05-21 to 2018-05-27)     1                     1              0
    Fifthweek(2018-05-28 to 2018-05-31)      3                     4              3
    
    Firstweek(2018-06-01 to 2018-06-03)      0                     0              0
    Secondweek(2018-06-04 to 2018-06-10)     2                     2              2
    Thirdweek(2018-06-11 to 2018-06-17)      1                     2              2
    Fourthweek(2018-06-18 to 2018-06-24)     2                     3              3
    Fifthweek(2018-06-25 to 2018-06-30)      0                     0              0

sql sql-server

Solution 1:^[1]

As you have mentioned interval conditions so I have hardcoded that. This query will fetch data and group it weekly.

I have replaced column format of week_no from Firstweek(2018-04-01) to week 1 of 04/2018 to make it fast.

SELECT week + DATEPART('wk', Created_Date) - DATEPART('wk', DATEADD(day, 1, 
EOMONTH(Created_Date, - 1))) + 'of ' + DATEPART('mm', Created_Date) + '/' + 
DATEPART('mm', Created_Date) as WEEK_NO, --- will result week 1 of 04/2018
sum(CASE 
        WHEN Created_Date > '2018-04-01 00:00:00'
            AND Created_Date < '2018-06-30 00:00:00'
            THEN 1
        ELSE 0
        END) AS Total_productIds, sum(CASE 
        WHEN Created_Date > '2018-04-01 00:00:00'
            AND Released_Date < '2018-06-30 00:00:00'
            THEN 1
        ELSE 0
        END) AS Total_ProductNames, sum(CASE 
        WHEN Created_Date > '2018-04-01 00:00:00'
            AND Created_Date < '2018-06-30 00:00:00'
            AND IS_UPDATED = 'Y'
            THEN 1
        ELSE 0
        END) AS Total_ProductNames
FROM products
GROUP BY DATEPART('wk', Created_Date)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Dale K

'Query to calculate the weekly wise data of a fields

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]