After doing a direct query on a database :
-- POPULATE PROJECT REFERENCE AND TOTAL WATER OFFERED --
WITH WaterOffered AS (
SELECT
Project_Reference,
SUM(Water_Total_AV) AS Total_Water_Offered_AV
FROM [Last_Mile].[dbo].[vwWater_AV_Current]
WHERE Effective_To_Date IS NULL
GROUP BY Project_Reference
),
-- DOMESTIC AMOUNT PAID --
DomesticWaterPaid AS (
SELECT
Project_Reference,
SUM(Asset_Value_Paid) AS Domestic_Water_AV_Paid
FROM [Last_Mile].[dbo].[vwWater_Asset_Value_Paid_Domestic]
GROUP BY Project_Reference
),
-- EARLIEST ACCEPTED DATE PER PROJECT --
EarliestAcceptedDate AS (
SELECT
Project_Reference,
MIN(Acceptance_Date) AS Acceptance_Date
FROM [Last_Mile].[dbo].[Adopt_TbleProject_Details]
GROUP BY Project_Reference
),
-- EARLIEST CONNECTION DATE PER PROJECT --
EarliestWaterConnection AS (
SELECT
pd.Project_Reference,
MIN(bwsp.Meter_Installation_Date) AS Earliest_WaterClean_Conn
FROM [Last_Mile].[dbo].[Adopt_TbleProject_Details] pd
INNER JOIN Billing_Water_Supply_Points bwsp
ON pd.Project_Reference = bwsp.Project_Reference
WHERE bwsp.Water_Flag = 'YES'
GROUP BY pd.Project_Reference
),
-- AGED DATA FOR DOMESTIC --
AgeData AS (
SELECT
pd.Project_Reference,
bwsp.LMAM_Water_Supply_Point_No,
ead.Acceptance_Date,
bwsp.Occupancy_Date,
DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) AS AgeDays,
CASE
WHEN DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) IS NULL THEN 'Not Connected'
WHEN DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) >= 0
AND DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) <= 50 THEN '0-50'
WHEN DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) >= 51
AND DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) <= 125 THEN '51-125'
WHEN DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) >= 126
AND DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) <= 250 THEN '126-250'
WHEN DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) >= 251
AND DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) <= 1000 THEN '251-1000'
WHEN DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) > 1000 THEN '1001+'
ELSE 'Error'
END AS AgeBuckets
FROM [Last_Mile].[dbo].[Adopt_TbleProject_Details] pd
INNER JOIN Billing_Water_Supply_Points bwsp
ON pd.Project_Reference = bwsp.Project_Reference
INNER JOIN EarliestAcceptedDate ead
ON pd.Project_Reference = ead.Project_Reference
WHERE pd.Project_Type != 'Commercial'
AND bwsp.Water_Flag = 'YES'
)
-- FINAL SELECT TO OUTPUT ALL DATA --
SELECT
wd.Project_Reference,
wd.Total_Water_Offered_AV,
dw.Domestic_Water_AV_Paid,
ead.Acceptance_Date,
ewc.Earliest_WaterClean_Conn,
ad.LMAM_Water_Supply_Point_No,
ad.Occupancy_Date,
ad.AgeDays,
ad.AgeBuckets
FROM WaterOffered wd
LEFT JOIN DomesticWaterPaid dw
ON wd.Project_Reference = dw.Project_Reference
LEFT JOIN EarliestAcceptedDate ead
ON wd.Project_Reference = ead.Project_Reference
LEFT JOIN EarliestWaterConnection ewc
ON wd.Project_Reference = ewc.Project_Reference
LEFT JOIN AgeData ad
ON wd.Project_Reference = ad.Project_Reference;
its showing me an error while applying this query in BI :
Microsoft SQL: Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near ','. Incorrect syntax near ','. Incorrect syntax near ','. Incorrect syntax near ','. Incorrect syntax near ')'.
Query works fine in SSMS and also in BI - power query loads an ouput for it but when i hit Apply and close it shows me this annoting error.
can someone tell me whats wrong here !!!!! please ❤️
