Power Platform Community Forum Thread Details

My SQL is failing during the week:

Can someone advise? Pulling my hair out over here 😄

DECLARE @startdate DATE;
DECLARE @enddate DATE;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET DATEFIRST 1; -- Monday is first day of the week
if datepart(weekday,getdate())=1 -- for Monday, do LAST week
BEGIN
SET @startdate=CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, Getdate()) -1, 0));
SET @enddate=CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, Getdate()) -0, 0));
END
else
BEGIN
SET @startdate=CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, Getdate()) -0, 0));
SET @enddate=CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, Getdate()) +1, 0));
END

select
case
when p.prd_brandId=75 then 'Cernucci'
when p.prd_brandId=74 then 'Cernucci Clothing'
when p.prd_brandId=76 then 'Cernucci Packaging'
else '' end as Brand,
isnull((select ocf_value from tblOrderCustomField ocf where ol.orl_ord_id=ocf.ocf_ord_id and ocf_name='PCF_REPLENOR'),'') as 'NewOrReplen',
ol.orl_productSku,
ol.orl_productName,
ol.orl_quantity as Qty_Ordered,
gin_quantity as QtyReceived,
ol.orl_quantity-gin_quantity as Qty_outstanding,
orl_itemCostCurrencyCode as Cost_Currency,
orl_itemCostValue as cost_per_Unit,
format(gin_createdon,'dd/MM/yyyy') as ReceivedDate,
o.ord_id as PO_NUmber,
o.ord_reference as PO_Reference,
(select con_organisationName from tblContact c where c.con_isSupplier=1 and c.con_id=p.prd_primarySupplierID) as Supplier

from tblGoodsInNote gin
join tblOrder o on gin_ord_id=o.ord_id
join tblorderline ol on ol.orl_ord_id=o.ord_id and gin_productid=ol.orl_productId
join tblProduct p on ol.orl_productId=p.prd_id
where o.ord_orderTypeCode='PO'
and o.ord_orderStatusName not in ('Cancelled')
and gin_createdOn>=@startdate
and gin_createdOn<@enddate

order by gin_createdOn DESC

Categories:

General topics