SQL SERVER query execution
Budget: $10 – $30 USD
Hi
I have a sql server query that I am running in beaver , its a big query and I need to cut it in smaller parts and get the query running to analyze some discrepancies , and I am unable to run the query and find the discrepancy, so need help
query is:
SELECT
c.month_beginning
, c.week_ending
-- , trim(concat(case when V.Vendor_Name is null then "" else V.Vendor_Name end,' (',AA.Vendor_Nbr,')')) Vendor
, Is_Pack_Received
, Is_Item_Refused
, Is_Audit_Required
, Audit_State
, CASE
WHEN Shipped_Unit_Qty > COALESCE(Finalized_Qty,0) THEN 'Short'
WHEN Shipped_Unit_Qty < COALESCE(Finalized_Qty,0) THEN 'Over'
WHEN Shipped_Unit_Qty = COALESCE(Finalized_Qty,0) THEN 'Accurate'
ELSE 'Error' END Audit_Result
, CASE
WHEN AA.Item_Cost IS NULL AND PL.Item_Cost IS NULL THEN 'No price available'
WHEN AA.Item_Cost = PL.Item_Cost THEN 'ASN_Audit matches PO_Line'
WHEN AA.Item_Cost is not NULL THEN 'ASN_Audit used'
WHEN PL.Distinct_Prices > 1 THEN 'PO_Line Avg used - Conflicting Prices'
ELSE 'PO_Line used'
END Has_COGs_Detail
, SUM(Shipped_Unit_Qty) Expected_Qty
, SUM(Finalized_Qty) Received_Qty
, SUM(COALESCE(AA.Item_Cost,PL.Item_Cost) * Shipped_Unit_Qty) Expected_COGs
, SUM(COALESCE(AA.Item_Cost,PL.Item_Cost) * Finalized_Qty) Received_COGs
, GETDATE() Data_Refreshed
FROM
poreceivings.asn_audit AA
LEFT JOIN
( select distinct vendor_nbr, vendor_name
from poreceivings.vendors
where trim(country_code) = "US"
and trim(vendor_status_code) = "A"
group by 1,2
) V
ON V.Vendor_Nbr = AA.Vendor_Nbr
LEFT JOIN
( -- Get prices from PO_Line when not in ASN_Audit
SELECT
PL.PO_Nbr
, PL.Club_Nbr
, PL.Item_Nbr
, AVG(PL.Whpk_SellCost) Item_Cost
, COUNT(DISTINCT PL.Whpk_SellCost) Distinct_Prices
FROM
-- Isolate to relevant records for query optimization
( SELECT AA.PO_Nbr, AA.Club_Nbr, AA.Item_Nbr
FROM poreceivings.asn_audit AA
--WHERE 1 = 1
AND TRIM(left(AA.PO_Receive_Status,10)) = 'FN'
--AND CAST(left(AA.Last_Modified_Date,10) AS date) >= CAST((SELECT DATEPART(yyyy, GETDATE())-1 AS string)||"-02-01") AS date)
GROUP BY 1, 2, 3
) AA
INNER JOIN
poreceivings.po_line PL
ON AA.PO_Nbr = PL.PO_Nbr
AND AA.Club_Nbr = PL.Club_Nbr
AND AA.Item_Nbr = PL.Item_Nbr
GROUP BY 1,2,3
) PL
ON AA.PO_Nbr = PL.PO_Nbr
AND AA.Club_Nbr = PL.Club_Nbr
AND AA.Item_Nbr = PL.Item_Nbr
INNER JOIN
poreceivings.po_data P
ON P.po_nbr = AA.PO_Nbr
AND P.club_nbr = AA.Club_Nbr
left join (select a.gregorian_date, b.month_beginning, c.week_ending
from
select gregorian_date, wm_month, fiscal_year, wm_week, c.wm_year
from poreceivings.sipt1_calendar_day c) a,
(select min(gregorian_date) as month_beginning, wm_month, fiscal_year
from poreceivings.sipt1_calendar_day group by 2,3) b,
(select max(gregorian_date) as week_ending, wm_week, wm_year
from wmt-edw-sandbox.sipt1_ad_hoc.sipt1_calendar_day c group by 2,3) c
where 1=1
and a.wm_month = b.wm_month
and a.fiscal_year = b.fiscal_year
and a.wm_week = c.wm_week
and a.wm_year = c.wm_year(
and cast(a.gregorian_Date as string) >= (select cast(extract(year from current_Date())-1 as string)||"-02-01")
group by 1,2,3) c
on date(p.receive_date) = c.gregorian_date
WHERE 1 = 1
AND cast(left(P.Receive_Date,10) as date) >= cast((select cast(extract(year from current_Date())-1 as string)||"-02-01") as date) -- Pull trailing 52 weeks
AND cast(left(AA.Last_Modified_Date,10) as date) >= cast((select cast(extract(year from current_Date())-1 as string)||"-02-01") as date) -- Pull trailing 52 weeks
AND trim(AA.PO_Receive_Status) = 'FN'
and aa.club_nbr not in (4041,6625,6543,6689)
GROUP BY 1,2,3,4,5,6,7,8,9
I have a sql server query that I am running in beaver , its a big query and I need to cut it in smaller parts and get the query running to analyze some discrepancies , and I am unable to run the query and find the discrepancy, so need help
query is:
SELECT
c.month_beginning
, c.week_ending
-- , trim(concat(case when V.Vendor_Name is null then "" else V.Vendor_Name end,' (',AA.Vendor_Nbr,')')) Vendor
, Is_Pack_Received
, Is_Item_Refused
, Is_Audit_Required
, Audit_State
, CASE
WHEN Shipped_Unit_Qty > COALESCE(Finalized_Qty,0) THEN 'Short'
WHEN Shipped_Unit_Qty < COALESCE(Finalized_Qty,0) THEN 'Over'
WHEN Shipped_Unit_Qty = COALESCE(Finalized_Qty,0) THEN 'Accurate'
ELSE 'Error' END Audit_Result
, CASE
WHEN AA.Item_Cost IS NULL AND PL.Item_Cost IS NULL THEN 'No price available'
WHEN AA.Item_Cost = PL.Item_Cost THEN 'ASN_Audit matches PO_Line'
WHEN AA.Item_Cost is not NULL THEN 'ASN_Audit used'
WHEN PL.Distinct_Prices > 1 THEN 'PO_Line Avg used - Conflicting Prices'
ELSE 'PO_Line used'
END Has_COGs_Detail
, SUM(Shipped_Unit_Qty) Expected_Qty
, SUM(Finalized_Qty) Received_Qty
, SUM(COALESCE(AA.Item_Cost,PL.Item_Cost) * Shipped_Unit_Qty) Expected_COGs
, SUM(COALESCE(AA.Item_Cost,PL.Item_Cost) * Finalized_Qty) Received_COGs
, GETDATE() Data_Refreshed
FROM
poreceivings.asn_audit AA
LEFT JOIN
( select distinct vendor_nbr, vendor_name
from poreceivings.vendors
where trim(country_code) = "US"
and trim(vendor_status_code) = "A"
group by 1,2
) V
ON V.Vendor_Nbr = AA.Vendor_Nbr
LEFT JOIN
( -- Get prices from PO_Line when not in ASN_Audit
SELECT
PL.PO_Nbr
, PL.Club_Nbr
, PL.Item_Nbr
, AVG(PL.Whpk_SellCost) Item_Cost
, COUNT(DISTINCT PL.Whpk_SellCost) Distinct_Prices
FROM
-- Isolate to relevant records for query optimization
( SELECT AA.PO_Nbr, AA.Club_Nbr, AA.Item_Nbr
FROM poreceivings.asn_audit AA
--WHERE 1 = 1
AND TRIM(left(AA.PO_Receive_Status,10)) = 'FN'
--AND CAST(left(AA.Last_Modified_Date,10) AS date) >= CAST((SELECT DATEPART(yyyy, GETDATE())-1 AS string)||"-02-01") AS date)
GROUP BY 1, 2, 3
) AA
INNER JOIN
poreceivings.po_line PL
ON AA.PO_Nbr = PL.PO_Nbr
AND AA.Club_Nbr = PL.Club_Nbr
AND AA.Item_Nbr = PL.Item_Nbr
GROUP BY 1,2,3
) PL
ON AA.PO_Nbr = PL.PO_Nbr
AND AA.Club_Nbr = PL.Club_Nbr
AND AA.Item_Nbr = PL.Item_Nbr
INNER JOIN
poreceivings.po_data P
ON P.po_nbr = AA.PO_Nbr
AND P.club_nbr = AA.Club_Nbr
left join (select a.gregorian_date, b.month_beginning, c.week_ending
from
select gregorian_date, wm_month, fiscal_year, wm_week, c.wm_year
from poreceivings.sipt1_calendar_day c) a,
(select min(gregorian_date) as month_beginning, wm_month, fiscal_year
from poreceivings.sipt1_calendar_day group by 2,3) b,
(select max(gregorian_date) as week_ending, wm_week, wm_year
from wmt-edw-sandbox.sipt1_ad_hoc.sipt1_calendar_day c group by 2,3) c
where 1=1
and a.wm_month = b.wm_month
and a.fiscal_year = b.fiscal_year
and a.wm_week = c.wm_week
and a.wm_year = c.wm_year(
and cast(a.gregorian_Date as string) >= (select cast(extract(year from current_Date())-1 as string)||"-02-01")
group by 1,2,3) c
on date(p.receive_date) = c.gregorian_date
WHERE 1 = 1
AND cast(left(P.Receive_Date,10) as date) >= cast((select cast(extract(year from current_Date())-1 as string)||"-02-01") as date) -- Pull trailing 52 weeks
AND cast(left(AA.Last_Modified_Date,10) as date) >= cast((select cast(extract(year from current_Date())-1 as string)||"-02-01") as date) -- Pull trailing 52 weeks
AND trim(AA.PO_Receive_Status) = 'FN'
and aa.club_nbr not in (4041,6625,6543,6689)
GROUP BY 1,2,3,4,5,6,7,8,9