Forum Discussion

lovishsood1's avatar
lovishsood1
Icon for Resolver I rankResolver I
2 years ago
Solved

Not able to save CTE as Table or View

Hello Everyone,   I have created a CTE as follows;   WITH cte as ( SELECT Month_Name, Month_Number, Project_Category, 100.0 * Actual_Hours / NULLIF(SUM(A...
Data Integration
data science
data warehouse
one lake
power bi
  • AndyDDC's avatar
    AndyDDC
    2 years ago

    I think this is now a Power BI issue rather than a Synapse issue. You will be better served posting this logic issue in the Power BI forum

lovishsood1's avatar
lovishsood1
Icon for Resolver I rankResolver I
2 years ago

I want to save this SCRIPT as TABLE so that i can use the OUTPUT in Power BI.

;

WITH cte AS
(
    SELECT
        Month_Name,
        Month_Number,
        Project_Category,
        100.0 * Actual_Hours / NULLIF(SUM(Actual_Hours) OVER (PARTITION BY Month_Name, Month_Number), 0) AS Percentage
    FROM
        [DW_MonthlyReport].[dbo].[TimelogByType]
    -- GROUP by Month_Name,Month_Number,Project_Category
)

SELECT
    cte.Month_Name,
    cte.Month_Number,
    cte.Project_Category,
    SUM(cte.Percentage) AS Percentage
FROM
    cte
GROUP BY
    cte.Month_Name,
    cte.Month_Number,
    cte.Project_Category;
  • HimanshuS-msft's avatar
    HimanshuS-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    2 years ago

    Hello lovishsood1 
    When you say "I want to save this SCRIPT as TABLE "  , I am asuming that the intend is just to have these rows on some report , please correct me if I am wrong . I think we can use the view route for this for now.

     

    Since you have a CTE which you are running and i suggest you can create a view using the the CTE ( AndyDDC  was also pointing that out ) and the use that view to get the data in the report . I have some dummy data with which i tried and it worked . 

    Create TABLE TimelogByType
    (Month_Name varchar(100) ,Month_Number int ,Project_Category varchar(100) , Actual_Hours int  )
     
     
    INSERT INTO TimelogByType values ('Jan',202301,'AAA',8)
    INSERT INTO TimelogByType values ('Jan',202301,'AAA',18)
    INSERT INTO TimelogByType values ('Feb',202302,'BBB',118)
    INSERT INTO TimelogByType values ('Feb',202302,'CCC',1118)
     
    CREATE view dbo.vwGetData
    As

    WITH cte as
    (
    SELECT
            Month_Name,
            Month_Number,
            Project_Category,
           
            100.0 * Actual_Hours / NULLIF(SUM(Actual_Hours) OVER (PARTITION BY Month_Name, Month_Number), 0) AS Percentage
        FROM
            [dbo].[TimelogByType]
            )
            select cte.Month_Name,
            cte.Month_Number,
            cte.Project_Category,Sum(cte.Percentage) AS Percentage from cte GROUP by cte.Month_Name,cte.Month_Number,cte.Project_Category;
     
    select * from dbo.vwGetData
     

     

     You can also call this a inn powerBI report as this . 

     



     let me know if you have any question .
    Thanks 
    Himanshu 









     

     

     

     

     

    • lovishsood1's avatar
      lovishsood1
      Icon for Resolver I rankResolver I
      2 years ago

      I think this worked but problem is 

      I'm still not able to resolve what I actually I want to achieve.

       

      This should be my end output:

      But the Problem is My data is coming out of 100% and Above Output is showing DATA according to Total Hours for that month.

       

      I hope I'm able to describe my problem statement.

      Current Table : 

       

       

      Output Table : 

       

      I want to achieve like this so that I can create the graph as shown above.

      You can ignore the Month Column of Output table as I'm okay with using Month Name & Month Number column as it is.

       

      I hope my problem is clear.

       

       

      Script I'm using I'm already mentioned above.

      • AndyDDC's avatar
        AndyDDC
        Icon for Most Valuable Professional rankMost Valuable Professional
        2 years ago

        I think this is now a Power BI issue rather than a Synapse issue. You will be better served posting this logic issue in the Power BI forum

    • Jack_Sullivan's avatar
      Jack_Sullivan
      New Member
      1 year ago

      Hi Himanshu,

       

      I am getting the same error as above. But I cannot fix it with the solution you have presented.

      My code is below and it runs perfectly when I hit run on the SQL analytics endpoint.

      However, when I select the code and hit "save as view" I get the following error "Incorrect Syntax near the keyword 'WITH'"(same error as above) 

       
      My code is as follows:
      --gets interval value with latest ReadingReplacementVersionNumber for each mprn and timestamp
      --matches datetime of interval value to datetime of prices, then calculates credit for each period for each mprn
      --returns export and credit grouped by mprn and generation month

      WITH latest_export AS (
          SELECT
              MPRN,
              IntervalPeriodTimestamp,
              MAX(ReadingReplacementVersionNumber) AS MaxVersion
          FROM ELEC_MM_Tables.TABLE_344
          GROUP BY MPRN, IntervalPeriodTimestamp
      ),

      export_prices_merged AS (
          SELECT
              export.MPRN,
              export.IntervalPeriodTimestamp,
              export.IntervalValue / 2 AS IntervalValue_kWh,  -- Converting kW to kWh
              export.ReadingReplacementVersionNumber,
              BM_prices.IMBALANCE_SETTLEMENT_PRICE,
              (export.IntervalValue / 2 * BM_prices.IMBALANCE_SETTLEMENT_PRICE/1000) AS Credit_Eur -- prices in €/MWh, export in kW. Dividing by 2 for kW - kWh and by 1000 for kWh - MWh
          FROM ELEC_MM_Tables.TABLE_344 AS export
          INNER JOIN latest_export le
              ON export.MPRN = le.MPRN
              AND export.IntervalPeriodTimestamp = le.IntervalPeriodTimestamp
              AND export.ReadingReplacementVersionNumber = le.MaxVersion
          LEFT JOIN dbo.ISP_PRICING BM_prices
              ON export.IntervalPeriodTimestamp = BM_prices.DATE_TIME
      )
      SELECT
          MPRN,
          FORMAT(IntervalPeriodTimestamp, 'yyyy-MM') AS Month,  -- Group by Month
          ROUND(SUM(IntervalValue_kWh),3) AS Total_Export_kWh,
          ROUND(SUM(Credit_Eur),3) AS Total_Credit_Eur,
          MIN(IntervalPeriodTimestamp) as Minimum_Read_Date_In_Month,
          MAX(IntervalPeriodTimestamp) as Maximum_Read_Date_In_Month
      FROM export_prices_merged
      GROUP BY MPRN, FORMAT(IntervalPeriodTimestamp, 'yyyy-MM')
      ORDER BY MPRN, Month