locked
Trying to understand Code RRS feed

  • Question

  • Hello Community,

    Someone from this community has kindly re-written some code for me as follows:

    SELECT  CTE.LastDayDate,
            SUM (SLS.SalePrice) AS TotalDailySales
      FROM  Data.SalesByCountry SLS
           INNER JOIN  (SELECT EOMONTH (DATEFROMPARTS(2016, ID, 1)) AS LastDayDate
                               FROM (SELECT ROW_NUMBER() OVER (ORDER BY StockCode) AS ID
                                          FROM Data.Stock
    ) as TallyTable WHERE ID <= 12
    ) as CTE ON CTE.LastDayDate = CAST (SLS.SaleDate AS DATE) GROUP BY CTE.LastDayDate ORDER BY CTE.LastDayDate;

    The code works fine, however I don't understand what part the ID plays in the code. 

    Is there a way of further re-writing the code without ID or the DATEFROMPARTS function.

    I know this sounds weird, but I'm trying to get the code to work in spark.sql but Spark does not know how to interpret DATEFROMPARTS.

    So, if someone could get the code to work without DATEFROMPARTS function that would be great.

    Thanks


    carlton

    Sunday, August 11, 2019 1:13 PM

Answers

  • Deleted
    • Marked as answer by cpatte7372 Monday, August 12, 2019 10:50 AM
    Sunday, August 11, 2019 6:58 PM
  • The code works fine, however I don't understand what part the ID plays in the code. 

    Is there a way of further re-writing the code without ID or the DATEFROMPARTS function.

    I know this sounds weird, but I'm trying to get the code to work in spark.sql but Spark does not know how to interpret DATEFROMPARTS.

    So, if someone could get the code to work without DATEFROMPARTS function that would be great.

    The purpose of "ID" in the query is to provide an incremental value from 1 through 12 in order to generate a sequence of 12 months. You will need such a sequence regardless of whether you use DATEFROMPARTS or another method. Although there are many ways to generate the sequence, you should just be able to keep your working query use DATEADD for the EOMONTH expression:

    EOMONTH(DATEADD(month, ID-1, '20160101'))


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Sunday, August 11, 2019 1:46 PM
    • Marked as answer by cpatte7372 Monday, August 12, 2019 10:50 AM
    Sunday, August 11, 2019 1:45 PM

All replies

  • The code works fine, however I don't understand what part the ID plays in the code. 

    Is there a way of further re-writing the code without ID or the DATEFROMPARTS function.

    I know this sounds weird, but I'm trying to get the code to work in spark.sql but Spark does not know how to interpret DATEFROMPARTS.

    So, if someone could get the code to work without DATEFROMPARTS function that would be great.

    The purpose of "ID" in the query is to provide an incremental value from 1 through 12 in order to generate a sequence of 12 months. You will need such a sequence regardless of whether you use DATEFROMPARTS or another method. Although there are many ways to generate the sequence, you should just be able to keep your working query use DATEADD for the EOMONTH expression:

    EOMONTH(DATEADD(month, ID-1, '20160101'))


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Sunday, August 11, 2019 1:46 PM
    • Marked as answer by cpatte7372 Monday, August 12, 2019 10:50 AM
    Sunday, August 11, 2019 1:45 PM
  • Deleted
    • Marked as answer by cpatte7372 Monday, August 12, 2019 10:50 AM
    Sunday, August 11, 2019 6:58 PM
  • Hi capatte,

    ID is used for creating a a sequence of number ranging from 1-12 (generate 12 months). You could also use this code: 

    DECLARE @MM TINYINT 
    SET @MM = 12
    
    ;with cte as (
    SELECT FORMAT(DATEADD(dd, -1, DATEADD(month, n.n - @MM+1 + DATEDIFF(month, 0, '2016-12-31'),0)), 'yyyy-MM-dd') AS LastDayDate
    FROM (SELECT TOP(@MM) n = ROW_NUMBER() OVER (ORDER BY NAME)
    FROM master.dbo.syscolumns) n )
    
    
    SELECT  CTE.LastDayDate,
    SUM (SLS.SalePrice) AS TotalDailySales
     FROM  SalesByCountry SLS
    INNER JOIN  (SELECT * from cte) as CTE
                 ON CTE.LastDayDate = CAST (SLS.SaleDate AS DATE)
      GROUP BY CTE.LastDayDate
      ORDER BY CTE.LastDayDate;
    Sabrina

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 12, 2019 6:21 AM
  • Thanks Jose

    carlton

    Monday, August 12, 2019 10:50 AM