14 Replies Latest reply on Feb 14, 2018 2:09 PM by Joshua Milligan

    Experimenting a complex SQL query on Project Maestro

    ShivaRam Chennapragada

      Hi, I am currently working on a small project where initial step is to build a data source that has a sample of our POS data. I already have a SQL query (stored proc) that does the job for me, but I just got an idea of implementing similar process using Project Maestro - the query is relatively complex with some aggregations and pivoting. Here's the query, I renamed the fields for confidentiality purposes. I am using the latest Beta version and testing out scenarios on how we can leverage the application.

       

      SELECT
            td.[BusinessDate]
        , s.StoreNumber
        , p.AccountNumber
        , COUNT(DISTINCT(CASE WHEN td.ASaleIndicator = 1 THEN td.POSHeaderKey ELSE NULL END)) AS 'A Sales Transaction Count'
        , COUNT(DISTINCT(CASE WHEN td.BSaleIndicator = 1 THEN td.POSHeaderKey ELSE NULL END)) AS 'B Sales Transaction Count'
        , COUNT(DISTINCT(td.CustomerNumber)) AS 'Total Customer Count'
        , COUNT(DISTINCT(CASE WHEN td.ASaleIndicator = 1 THEN td.CustomerNumber ELSE NULL END)) AS 'A Sales Customer Count'
        , COUNT(DISTINCT(CASE WHEN td.BSaleIndicator = 1 THEN td.CustomerNumber ELSE NULL END)) AS 'B Sales Customer Count'
        , SUM(CASE WHEN td.ASaleIndicator = 1 THEN td.SalesAmount ELSE NULL END) AS 'A Sales Amount'
        , SUM(CASE WHEN td.BSaleIndicator = 1 THEN td.SalesQuantity ELSE NULL END) AS 'A Sales Quantity'
        , SUM(CASE WHEN td.BIndicator = 1 THEN td.SalesQuantity ELSE NULL END) AS 'B Quantity Sold'
        , s.Latitude
        , s.Longitude
      FROM
           XXX.ABC.[TransactionsTable] td 
           INNER JOIN
           XXX.ABC.StoreInfo s 
           ON td.StoreNumber = s.StoreNumber
           INNER JOIN
           XXX.ABC.ProductTable p
           ON td.ProductKey = p.ProductKey
      WHERE
           s.BusGroup = 'Open Stores'
           AND s.BusSegment = 'Con Stores'
           AND td.BusinessDate > DATEADD(DD, -14, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()),0)) AND td.BusinessDate < DATEADD(DD,DATEDIFF(DD,1,GETDATE()),0)
           AND td.LoyaltyRegisteredIndicator = 1
           AND td.LoyaltyIndicator = 1
           AND td.SaleIndicator = 1
      GROUP BY
         td.[BusinessDate]
        , s.StoreNumber
        , p.AccountNumber
        , s.Latitude
        , s.Longitude
      

       

      The WHERE & JOINs part was fairly straight forward but the challenging part is to implement the aggregations mentioned in the SELECT statement - When I try to add a 'Aggregate Step' it doesn't allow me to write a calculated field where I can implement similar logic, rather it gives me an option to either GROUP or Aggregate the specified fields which doesn't serve the purpose. As you can see in the below screenshot I have an 'Aggregate' step for each SELECT statement - I could do a DISTINCT COUNT on POSHeaderKey as a whole but what I really want is that to be filtered based on the flags set in the above SELECT statements. Any ideas or suggestions are highly appreciated. At this point I'm not even sure if I'm moving in right direction.

       

      Thank you!