November 20, 2008 at 12:23 pm
Hi All,
I have a following statement:
ItmRecvdYR2=ISNULL((SELECT SUM(Quantity) FROM dbo.GetAllReorderScannedItemsByDate() INNER JOIN (SELECT VendorID, LOC, DateYR2 FROM #TMP) TMPData ON VendorID=TMPData.VendorID AND locationno=TMPData.LOC AND DATEPART(YEAR,requisitiondate) = DATEPART(YEAR,TMPData.DateYR2) AND DATEPART(MONTH,requisitiondate) = DATEPART(MONTH,TMPData.DateYR2)),0)
The problem is since I am assigning the result to the ItnRecvdYR2,
I cannot select more than one column in the select statement, like,
ItmRecvdYR2=ISNULL((SELECT SUM(Quantity), VendorID FROM dbo.GetAllReorderScannedItemsByDate()
VendorID is needed to compare the VendorID from the Select to the TMPData.VendorID.
So how do I accomplish this?
Thanks.
November 20, 2008 at 2:09 pm
Try moving the subquery into the from clause and treating the subquery as a table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 20, 2008 at 2:14 pm
Hi Gail,
I don't understand what you mean by moving the subquery into the from clause.
Thanks.
November 20, 2008 at 4:16 pm
Something like this.
SELECT col1, col2, col3 from
t1 inner join (select col1, col2, col3 from t2) t2 on t1.col1 = t2.col1
I'm not sure I understand what you're doing. Please can you post the entire query that you're trying to use.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2008 at 8:18 am
Hi Gail,
Here is what I need to do:
ItmRecvdYR2 = ISNULL((SELECT SUM(Quantity), VendorID, LocationNo
FROM dbo.GetAllReorderScannedItemsByDate()
INNER JOIN (SELECT VendorID, LOC, DateYR2 FROM #TMP) TMPData
ON VendorID=TMPData.VendorID AND locationno=TMPData.LOC
AND DATEPART(YEAR,requisitiondate) = DATEPART(YEAR,TMPData.DateYR2)
AND DATEPART(MONTH,requisitiondate) = DATEPART(MONTH,TMPData.DateYR2)),0)
but I cannot do that because the result is returned in a variable
ItmRecvdYR2 which is an integer. Only thing that I need to return
in ItmRecvdYR2 is SUM(Qunatity).
VendorID and LocationNo is needed in the first select so that
I can join it to inner derived select using those columns.
What I am trying to avoid here is a triangular join.
Thank you for your time.
November 21, 2008 at 10:54 am
That's not a complete statement.
Is that part of a SELECT? Part of a SET? Part of an UPDATE?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2008 at 11:01 am
not sure if this is what you are looking for;
do you want the sum() of quantity grouped by vendor id?
something like this?:
[font="Courier New"]SELECT
SUM(X.Quantity) AS QTY,
TMPData.VendorID
FROM dbo.GetAllReorderScannedItemsByDate() X
INNER JOIN (SELECT VendorID, LOC, DateYR2 FROM #TMP) TMPData
ON X.VendorID=TMPData.VendorID
AND X.locationno=TMPData.LOC
AND DATEPART(YEAR,X.requisitiondate) = DATEPART(YEAR,TMPData.DateYR2)
AND DATEPART(MONTH,X.requisitiondate) = DATEPART(MONTH,TMPData.DateYR2)),0)
GROUP BY TMPData.VendorID[/font]
edited after clarification: added the locaiton i think?
[font="Courier New"]SELECT
SUM(X.Quantity) AS QTY,
TMPData.VendorID,
TMPData.LOC
FROM dbo.GetAllReorderScannedItemsByDate() X
INNER JOIN (SELECT VendorID, LOC, DateYR2 FROM #TMP) TMPData
ON X.VendorID = TMPData.VendorID
AND X.locationno = TMPData.LOC
AND DATEPART(YEAR,X.requisitiondate) = DATEPART(YEAR,TMPData.DateYR2)
AND DATEPART(MONTH,X.requisitiondate) = DATEPART(MONTH,TMPData.DateYR2)),0)
GROUP BY TMPData.VendorID,TMPData.LOC [/font]
Lowell
November 21, 2008 at 11:23 am
Hi Gail,
Here is the whole query:
UPDATE #TMP
SET
ItmRecvdYR2=ISNULL((SELECT SUM(Quantity) FROM dbo.GetAllReorderScannedItemsByDate() INNER JOIN (SELECT VendorID, LOC, DateYR2 FROM #TMP) TMPData ON VendorID=TMPData.VendorID AND locationno=TMPData.LOC and DATEPART(YEAR,requisitiondate) = DATEPART(YEAR,TMPData.DateYR2)),0),
CostRecvdYR2 = ISNULL((SELECT SUM(AmtScan) FROM dbo.GetAllReorderScannedCostByDate() INNER JOIN (SELECT VendorID, LOC, DateYR2 FROM #TMP) TMPData ON VendorID=TMPData.VendorID AND locationno=TMPData.LOC and DATEPART(YEAR,requisitiondate) = DATEPART(YEAR,TMPData.DateYR2)),0),
ItmSoldYR2=ISNULL(sru.TotalQtySold,0),
SOLDYR2=ISNULL(sru.TotalSold,0)
FROM
#TMP t
INNER JOIN ReportsData..Locations l
ON l.LocationNo=t.LOC
LEFT JOIN ReportsData..RequisitionHeader rh
ON rh.LocationNo=t.LOC
LEFT OUTER JOIN dbo.RU_SalesByVendor_Reorders sru
ON sru.VendorID=t.VendorID AND sru.LocationNo=t.LOC and DATEPART(YEAR,sru.rptdate) >= DATEPART(YEAR,t.DATEYR2)
LEFT OUTER JOIN dbo.CDC_GetAllReorderCounts_V2() cnt on cnt.VendorID=t.VendorID AND cnt.LocationNo=t.LOC
AND DATEPART(YEAR,cnt.ReqDate) = DATEPART(YEAR,t.DateYr2)
WHERE DATEPART(YEAR,rh.requisitiondate) = DATEPART(YEAR,t.DateYr2)
AND t.LOC < '00210'
Thank you.
November 21, 2008 at 11:33 am
Right. Now we can talk about this.
Consider moving the subqueries into the from clause of the update. I can't rewrite the entire things now, I'm in a presentation.
So, the general form would be something like this:
update sometable
set somecolumn = atotal
from table1 inner join (select sum(acolumn), column2 from anothertable group by column2) sub on table1.column2 = sub.column2
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2008 at 12:30 pm
Hi Gail,
I apologize. Here is the actual query:
UPDATE #TMP
SET
ItmRecvdYR2=ISNULL((SELECT SUM(Quantity), VendorID, LocationNo FROM dbo.GetAllReorderScannedItemsByDate() INNER JOIN (SELECT VendorID, LOC, DateYR2 FROM #TMP) TMPData ON VendorID=TMPData.VendorID AND locationno=TMPData.LOC and DATEPART(YEAR,requisitiondate) = DATEPART(YEAR,TMPData.DateYR2)),0)
FROM
#TMP t
INNER JOIN ReportsData..Locations l
ON l.LocationNo=t.LOC
LEFT JOIN ReportsData..RequisitionHeader rh
ON rh.LocationNo=t.LOC
LEFT OUTER JOIN dbo.RU_SalesByVendor_Reorders sru
ON sru.VendorID=t.VendorID AND sru.LocationNo=t.LOC and DATEPART(YEAR,sru.rptdate) >= DATEPART(YEAR,t.DATEYR2)
LEFT OUTER JOIN dbo.CDC_GetAllReorderCounts_V2() cnt on cnt.VendorID=t.VendorID AND cnt.LocationNo=t.LOC
AND DATEPART(YEAR,cnt.ReqDate) = DATEPART(YEAR,t.DateYr2)
WHERE DATEPART(YEAR,rh.requisitiondate) = DATEPART(YEAR,t.DateYr2)
AND t.LOC < '00210'
If you notice, in the first select I have multiple columns so that I can join on inner join select. But having multiple columns in the first select cannot work because I need to assign the whole result to an integer variable.
I can do the following but it will create a triangular join that takes enormous time:
UPDATE #TMP
SET
ItmRecvdYR1=ISNULL((SELECT SUM(Quantity) FROM dbo.GetAllReorderScannedItemsByDate() WHERE VendorID=t.VendorID AND locationno=t.LOC and DATEPART(YEAR,requisitiondate) = DATEPART(YEAR,t.DateYR1)),0)
FROM
#TMP t
INNER JOIN ReportsData..Locations l
ON l.LocationNo=t.LOC
LEFT JOIN ReportsData..RequisitionHeader rh
ON rh.LocationNo=t.LOC
LEFT OUTER JOIN dbo.RU_SalesByVendor_Reorders sru
ON sru.VendorID=t.VendorID AND sru.LocationNo=t.LOC and DATEPART(YEAR,sru.rptdate) >= DATEPART(YEAR,t.DATEYR1)
LEFT OUTER JOIN dbo.CDC_GetAllReorderCounts_V2() cnt on cnt.VendorID=t.VendorID AND cnt.LocationNo=t.LOC
AND DATEPART(YEAR,cnt.ReqDate)=DATEPART(YEAR,t.DATEYR1)
WHERE DATEPART(YEAR,rh.requisitiondate) = DATEPART(YEAR,t.DateYr1)
AND t.LOC < '00210'
I truly need your help,
Thanks.
November 21, 2008 at 3:08 pm
Don't have time to rewrite it now.
Can you try moving the subquery with the sum into the from clause and treating it as a derived table? I know it's not possible to have multiple columns in the subquery in the select, but it is possible if it's a derived table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2008 at 3:48 pm
Hi Gail,
I am not sure that I am understanding what you mean by
"moving the subquery with the sum into the from clause and treating it as a derived table". Can you give me more hint using the
same query I have. I appreciate your time.
Thank you.
November 21, 2008 at 11:57 pm
I don't have time right now to try and rewrite your entirequery, especially not without table schemas or sample data. Look at the sample update statement I posted above and see if you can get the selects that you have as columns within the update (the ones inside the isnull) and treat them as derived tables.
If you can't, post your table structure, sample data and expected results so that we can test solutions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2008 at 2:46 pm
Hi Gail,
Here is a whole stored procedure. I don't think that giving you any additional structure will help:
ALTER Procedure [dbo].[SP_Proc1]
(
@RDCCHAR(5) = '00910',
@DateOneDATETIME = '03/04/2007',
@DateTwoDATETIME = '04/02/2007',
@LocationNo CHAR(5)= 'ALL',
@Increment VARCHAR(5)='Month' --month or year
)
AS
create TABLE #TMP
(
VendorID VARCHAR(10) null,--the vendor at hand...
DateYR1 DATETIME null,--first month for comparision
DateYR2 DATETIME null,--second month for comparision
LOC CHAR(5) null,--store
ItmRecvdYR1 INT null, --Sum of Item scanned at store
ItmRecvdYR2 INT null, --Sum of Item scanned at store
CostRecvdYR1 MONEY null, --$$ Cost of scanned items
CostRecvdYR2 MONEY null, --$$ Cost of scanned items
ItmSoldYR1 INT null, -- Sum of Items sold
ItmSoldYR2 INT null, -- Sum of Items sold
SoldYR1 MONEY NULL, --$$ Sold
SoldYR2 MONEY NULL
)
CREATE NONCLUSTERED INDEX IX_DateYR1
ON #TMP (DateYR1)
CREATE NONCLUSTERED INDEX IX_DateYR2
ON #TMP (DateYR2)
CREATE NONCLUSTERED INDEX IX_VendorID
ON #TMP (VendorID)
CREATE NONCLUSTERED INDEX IX_LOC
ON #TMP (LOC)
--DECLARE @StartTime DATETIME --Timer to measure total duration
--SET @StartTime = GETDATE() --Start the timer
IF @RDC = 'ALL'
BEGIN
INSERT INTO #TMP
(
DateYr1,
DateYr2,
LOC,
VendorID
)
--connect locations and vendors using cross join
SELECT @DateOne,@DateTwo,l.LocationNo,v.VendorID
FROM ReportsData..Locations l
CROSS JOIN ReportsData..VendorMaster v
where (allowdirectorderbystore='Y' or skeletonpo='y')
AND l.LocationType='S' AND l.Status='A'
END
ELSE
IF @LocationNo = 'ALL' OR @LocationNo IS NULL
BEGIN
INSERT INTO #TMP
(
DateYr1,
DateYr2,
LOC,
VendorID
)
SELECT @DateOne,@DateTwo,l.LocationNo,v.VendorID
FROM ReportsData..Locations l
CROSS JOIN ReportsData..VendorMaster v
where (allowdirectorderbystore='Y' or skeletonpo='y')
AND l.LocationType='S' AND l.Status='A'
and l.rdclocationno=@RDC
END
ELSE
BEGIN
INSERT INTO #TMP
(
DateYr1,
DateYr2,
LOC,
VendorID
)
SELECT @DateOne,@DateTwo, l.LocationNo,v.VendorID
FROM ReportsData..Locations l
CROSS JOIN ReportsData..VendorMaster v
where (allowdirectorderbystore='Y' or skeletonpo='y')
AND l.LocationType='S' AND l.Status='A'
AND l.rdclocationno=@RDC AND l.LocationNo = @LocationNo
END
--SELECT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds duration'
IF @Increment = 'Year'
BEGIN
--combine all data into #TMP
UPDATE #TMP
SET
ItmRecvdYR1=ISNULL((SELECT SUM(Quantity) FROM dbo.GetAllReorderScannedItemsByDate() INNER JOIN (SELECT VendorID, LOC, DateYR1 FROM #TMP) TMPData ON VendorID=TMPData.VendorID AND locationno=TMPData.LOC and DATEPART(YEAR,requisitiondate) = DATEPART(YEAR,TMPData.DateYR1)),0),
CostRecvdYR1 = ISNULL((SELECT SUM(AmtScan) FROM dbo.GetAllReorderScannedCostByDate() INNER JOIN (SELECT VendorID, LOC, DateYR1 FROM #TMP) TMPData ON VendorID=TMPData.VendorID AND locationno=TMPData.LOC and DATEPART(YEAR,requisitiondate) = DATEPART(YEAR,TMPData.DateYR1)),0),
ItmSoldYR1=ISNULL(sru.TotalQtySold,0),
SOLDYR1=ISNULL(sru.TotalSold,0)
FROM
#TMP t
INNER JOIN ReportsData..Locations l
ON l.LocationNo=t.LOC
LEFT JOIN ReportsData..RequisitionHeader rh
ON rh.LocationNo=t.LOC
LEFT OUTER JOIN dbo.RU_SalesByVendor_Reorders sru
ON sru.VendorID=t.VendorID AND sru.LocationNo=t.LOC and DATEPART(YEAR,sru.rptdate) >= DATEPART(YEAR,t.DATEYR1)
LEFT OUTER JOIN dbo.CDC_GetAllReorderCounts_V2() cnt on cnt.VendorID=t.VendorID AND cnt.LocationNo=t.LOC
AND DATEPART(YEAR,cnt.ReqDate)=DATEPART(YEAR,t.DATEYR1)
WHERE DATEPART(YEAR,rh.requisitiondate) = DATEPART(YEAR,t.DateYr1)
AND t.LOC < '00210'
UPDATE #TMP
SET
ItmRecvdYR2=ISNULL((SELECT SUM(Quantity) FROM dbo.GetAllReorderScannedItemsByDate() INNER JOIN (SELECT VendorID, LOC, DateYR2 FROM #TMP) TMPData ON VendorID=TMPData.VendorID AND locationno=TMPData.LOC and DATEPART(YEAR,requisitiondate) = DATEPART(YEAR,TMPData.DateYR2)),0),
CostRecvdYR2 = ISNULL((SELECT SUM(AmtScan) FROM dbo.GetAllReorderScannedCostByDate() INNER JOIN (SELECT VendorID, LOC, DateYR2 FROM #TMP) TMPData ON VendorID=TMPData.VendorID AND locationno=TMPData.LOC and DATEPART(YEAR,requisitiondate) = DATEPART(YEAR,TMPData.DateYR2)),0),
ItmSoldYR2=ISNULL(sru.TotalQtySold,0),
SOLDYR2=ISNULL(sru.TotalSold,0)
FROM
#TMP t
INNER JOIN ReportsData..Locations l
ON l.LocationNo=t.LOC
LEFT JOIN ReportsData..RequisitionHeader rh
ON rh.LocationNo=t.LOC
LEFT OUTER JOIN dbo.RU_SalesByVendor_Reorders sru
ON sru.VendorID=t.VendorID AND sru.LocationNo=t.LOC and DATEPART(YEAR,sru.rptdate) >= DATEPART(YEAR,t.DATEYR2)
LEFT OUTER JOIN dbo.CDC_GetAllReorderCounts_V2() cnt on cnt.VendorID=t.VendorID AND cnt.LocationNo=t.LOC
AND DATEPART(YEAR,cnt.ReqDate) = DATEPART(YEAR,t.DateYr2)
WHERE DATEPART(YEAR,rh.requisitiondate) = DATEPART(YEAR,t.DateYr2)
AND t.LOC < '00210'
UPDATE #TMP SET ItmRecvdYR1=CASE WHEN ItmRecvdYR1 IS NULL THEN 0 ELSE ItmRecvdYR1 END,
ItmRecvdYR2=CASE WHEN ItmRecvdYR2 IS NULL THEN 0 ELSE ItmRecvdYR2 END,
CostRecvdYR1=CASE WHEN CostRecvdYR1 IS NULL THEN 0 ELSE CostRecvdYR1 END,
CostRecvdYR2=CASE WHEN CostRecvdYR2 IS NULL THEN 0 ELSE CostRecvdYR2 END,
ItmSoldYR1=CASE WHEN ItmSoldYR1 IS NULL THEN 0 ELSE ItmSoldYR1 END,
ItmSoldYR2=CASE WHEN ItmSoldYR2 IS NULL THEN 0 ELSE ItmSoldYR2 END,
SoldYR1=CASE WHEN SoldYR1 IS NULL THEN 0 ELSE SoldYR1 END,
SoldYR2=CASE WHEN SoldYR2 IS NULL THEN 0 ELSE SoldYR2 END
SELECTt.Loc + ' ' + upper(l.abbreviation)[Abbr],
t.VendorID,
DateYR1,
DateYR2,
t.ItmRecvdYR1,
t.ItmRecvdYR2,
t.ItmRecvdYR1 - t.ItmRecvdYR2 AS RecvdDiff,
(CASE WHEN t.ItmRecvdYR1 > t.ItmRecvdYR2 THEN
--CAST(CAST(ROUND((((t.ItmRecvdYR1 - t.ItmRecvdYR2)/(t.ItmRecvdYR1 * 1.0)) * 100),0) AS DECIMAL(9,0)) AS VARCHAR) + '%'
CAST(ROUND((((t.ItmRecvdYR1 - t.ItmRecvdYR2)/(t.ItmRecvdYR1 * 1.0)) * 100),0) AS DECIMAL(9,0))
WHEN t.ItmRecvdYR2 > t.ItmRecvdYR1 THEN
ROUND((((t.ItmRecvdYR1 - t.ItmRecvdYR2)/(t.ItmRecvdYR2 * 1.0)) * 100),0)
ELSE 0 END) AS [RecvdDiff%],
t.CostRecvdYR1,
t.CostRecvdYR2,
t.CostRecvdYR1 - t.CostRecvdYR2 AS CostdDiff,
(CASE WHEN t.CostRecvdYR1 > t.CostRecvdYR2 THEN
ROUND((((t.CostRecvdYR1 - t.CostRecvdYR2)/(t.CostRecvdYR1 * 1)) * 100),0)
WHEN t.CostRecvdYR2 > t.CostRecvdYR1 THEN
ROUND((((t.CostRecvdYR1 - t.CostRecvdYR2)/(t.CostRecvdYR2 * 1)) * 100),0)
ELSE 0 END) AS [CostDiff%],
t.ItmSoldYR1,
t.ItmSoldYR2,
t.ItmSoldYR1 - t.ItmSoldYR2 AS ItmSolddDiff,
(CASE WHEN t.ItmSoldYR1 > t.ItmSoldYR2 THEN
ROUND((((t.ItmSoldYR1 - t.ItmSoldYR2)/(t.ItmSoldYR1 * 1.0)) * 100),2)
WHEN t.ItmSoldYR2 > t.ItmSoldYR1 THEN
ROUND((((t.ItmSoldYR1 - t.ItmSoldYR2)/(t.ItmSoldYR2 * 1.0)) * 100),0)
ELSE 0 END) AS [ItmSoldDiff%],
t.SoldYR1,
t.SoldYR2,
t.SoldYR1 - t.SoldYR2 AS SoldDiff,
(CASE WHEN t.SoldYR1 > t.SoldYR2 THEN
ROUND((((t.SoldYR1 - t.SoldYR2)/(t.SoldYR1 * 1)) * 100),0)
WHEN t.SoldYR2 > t.SoldYR1 THEN
ROUND((((t.SoldYR1 - t.SoldYR2)/(t.SoldYR2 * 1)) * 100),0)
ELSE 0 END) AS [SoldDiff%]
FROM #TMP t
inner join reportsdata..locations l on l.locationno=t.loc
order by DateYR1,l.picklist
END
ELSE -- increment is month
BEGIN
--combine all data into #TMP
UPDATE #TMP
SET
--ItmRecvdYR1=ISNULL((SELECT SUM(Quantity) FROM dbo.GetAllReorderScannedItemsByDate() INNER JOIN (SELECT VendorID, LOC, DateYR1 FROM #TMP) TMPData ON VendorID=TMPData.VendorID AND locationno=TMPData.LOC AND DATEPART(YEAR,requisitiondate) = DATEPART(YEAR,TMPData.DateYR1) AND DATEPART(MONTH,requisitiondate) = DATEPART(MONTH,TMPData.DateYR1)),0),
--CostRecvdYR1 = ISNULL((SELECT SUM(AmtScan) FROM dbo.GetAllReorderScannedCostByDate() INNER JOIN (SELECT VendorID, LOC, DateYR1 FROM #TMP) TMPData ON VendorID=TMPData.VendorID AND locationno=TMPData.LOC AND DATEPART(YEAR,requisitiondate) = DATEPART(YEAR,TMPData.DateYR1) AND DATEPART(MONTH,requisitiondate) = DATEPART(MONTH,TMPData.DateYR1)),0),
ItmSoldYR1=ISNULL(sru.TotalQtySold,0),
SOLDYR1=ISNULL(sru.TotalSold,0)
FROM
#TMP t
INNER JOIN ReportsData..Locations l
ON l.LocationNo=t.LOC
LEFT JOIN ReportsData..RequisitionHeader rh
ON rh.LocationNo=t.LOC
LEFT OUTER JOIN dbo.RU_SalesByVendor_Reorders sru
ON sru.VendorID=t.VendorID AND sru.LocationNo=t.LOC
AND DATEPART(YEAR,sru.rptdate) = DATEPART(YEAR,t.DATEYR1)
AND DATEPART(MONTH,sru.rptdate) = DATEPART(MONTH,t.DATEYR1)
LEFT OUTER JOIN dbo.CDC_GetAllReorderCounts_V2() cnt on cnt.VendorID=t.VendorID AND cnt.LocationNo=t.LOC
AND DATEPART(YEAR,cnt.ReqDate) = DATEPART(YEAR,t.DATEYR1)
AND DATEPART(MONTH,cnt.ReqDate) = DATEPART(MONTH,t.DATEYR1)
WHERE DATEPART(YEAR,rh.requisitiondate) = DATEPART(YEAR,t.DateYr1)
AND DATEPART(MONTH,rh.requisitiondate) = DATEPART(MONTH,t.DateYr1)
AND t.LOC < '00210'
UPDATE #TMP
SET
ItmRecvdYR2 = ISNULL((SELECT SUM(Quantity) FROM dbo.GetAllReorderScannedItemsByDate() INNER JOIN (SELECT VendorID, LOC, DateYR2 FROM #TMP) TMPData ON VendorID=TMPData.VendorID AND locationno=TMPData.LOC AND DATEPART(YEAR,requisitiondate) = DATEPART(YEAR,TMPData.DateYR2) AND DATEPART(MONTH,requisitiondate) = DATEPART(MONTH,TMPData.DateYR2)),0),
CostRecvdYR2 = ISNULL((SELECT SUM(AmtScan) FROM dbo.GetAllReorderScannedCostByDate() INNER JOIN (SELECT VendorID, LOC, DateYR2 FROM #TMP) TMPData ON VendorID=TMPData.VendorID AND locationno=TMPData.LOC AND DATEPART(YEAR,requisitiondate) = DATEPART(YEAR,TMPData.DateYR2) AND DATEPART(MONTH,requisitiondate) = DATEPART(MONTH,TMPData.DateYR2)),0),
ItmSoldYR2=ISNULL(sru.TotalQtySold,0),
SOLDYR2=ISNULL(sru.TotalSold,0)
FROM
#TMP t
INNER JOIN ReportsData..Locations l
ON l.LocationNo=t.LOC
LEFT JOIN ReportsData..RequisitionHeader rh
ON rh.LocationNo=t.LOC
LEFT OUTER JOIN dbo.RU_SalesByVendor_Reorders sru
ON sru.VendorID=t.VendorID AND sru.LocationNo=t.LOC
AND DATEPART(YEAR,sru.rptdate) = DATEPART(YEAR,t.DATEYR2)
AND DATEPART(MONTH,sru.rptdate) = DATEPART(MONTH,t.DATEYR2)
LEFT OUTER JOIN dbo.CDC_GetAllReorderCounts_V2() cnt on cnt.VendorID=t.VendorID AND cnt.LocationNo=t.LOC
AND DATEPART(YEAR,cnt.ReqDate) = DATEPART(YEAR,t.DateYr2)
AND DATEPART(MONTH,cnt.ReqDate) = DATEPART(MONTH,t.DateYr2)
WHERE DATEPART(YEAR,rh.requisitiondate) = DATEPART(YEAR,t.DateYr2)
AND DATEPART(MONTH,rh.requisitiondate) = DATEPART(MONTH,t.DateYr2)
AND t.LOC < '00210'
UPDATE #TMP SET ItmRecvdYR1=CASE WHEN ItmRecvdYR1 IS NULL THEN 0 ELSE ItmRecvdYR1 END,
ItmRecvdYR2=CASE WHEN ItmRecvdYR2 IS NULL THEN 0 ELSE ItmRecvdYR2 END,
CostRecvdYR1=CASE WHEN CostRecvdYR1 IS NULL THEN 0 ELSE CostRecvdYR1 END,
CostRecvdYR2=CASE WHEN CostRecvdYR2 IS NULL THEN 0 ELSE CostRecvdYR2 END,
ItmSoldYR1=CASE WHEN ItmSoldYR1 IS NULL THEN 0 ELSE ItmSoldYR1 END,
ItmSoldYR2=CASE WHEN ItmSoldYR2 IS NULL THEN 0 ELSE ItmSoldYR2 END,
SoldYR1=CASE WHEN SoldYR1 IS NULL THEN 0 ELSE SoldYR1 END,
SoldYR2=CASE WHEN SoldYR2 IS NULL THEN 0 ELSE SoldYR2 END
SELECTt.Loc + ' ' + upper(l.abbreviation)[Abbr],
t.VendorID,
DateYR1,
DateYR2,
t.ItmRecvdYR1,
t.ItmRecvdYR2,
t.ItmRecvdYR1 - t.ItmRecvdYR2 AS RecvdDiff,
(CASE WHEN t.ItmRecvdYR1 > t.ItmRecvdYR2 THEN
CAST(ROUND((((t.ItmRecvdYR1 - t.ItmRecvdYR2)/(t.ItmRecvdYR1 * 1.0)) * 100),0) AS DECIMAL(9,0))
WHEN t.ItmRecvdYR2 > t.ItmRecvdYR1 THEN
ROUND((((t.ItmRecvdYR1 - t.ItmRecvdYR2)/(t.ItmRecvdYR2 * 1.0)) * 100),0)
ELSE 0 END) AS [RecvdDiff%],
t.CostRecvdYR1,
t.CostRecvdYR2,
t.CostRecvdYR1 - t.CostRecvdYR2 AS CostdDiff,
(CASE WHEN t.CostRecvdYR1 > t.CostRecvdYR2 THEN
ROUND((((t.CostRecvdYR1 - t.CostRecvdYR2)/(t.CostRecvdYR1 * 1)) * 100),0)
WHEN t.CostRecvdYR2 > t.CostRecvdYR1 THEN
ROUND((((t.CostRecvdYR1 - t.CostRecvdYR2)/(t.CostRecvdYR2 * 1)) * 100),0)
ELSE 0 END) AS [CostDiff%],
t.ItmSoldYR1,
t.ItmSoldYR2,
t.ItmSoldYR1 - t.ItmSoldYR2 AS ItmSolddDiff,
(CASE WHEN t.ItmSoldYR1 > t.ItmSoldYR2 THEN
ROUND((((t.ItmSoldYR1 - t.ItmSoldYR2)/(t.ItmSoldYR1 * 1.0)) * 100),2)
WHEN t.ItmSoldYR2 > t.ItmSoldYR1 THEN
ROUND((((t.ItmSoldYR1 - t.ItmSoldYR2)/(t.ItmSoldYR2 * 1.0)) * 100),0)
ELSE 0 END) AS [ItmSoldDiff%],
t.SoldYR1,
t.SoldYR2,
t.SoldYR1 - t.SoldYR2 AS SoldDiff,
(CASE WHEN t.SoldYR1 > t.SoldYR2 THEN
ROUND((((t.SoldYR1 - t.SoldYR2)/(t.SoldYR1 * 1)) * 100),0)
WHEN t.SoldYR2 > t.SoldYR1 THEN
ROUND((((t.SoldYR1 - t.SoldYR2)/(t.SoldYR2 * 1)) * 100),0)
ELSE 0 END) AS [SoldDiff%]
FROM #TMP t
inner join reportsdata..locations l on l.locationno=t.loc
order by DateYR1,l.picklist
END
DROP TABLE #TMP
Thanks.
November 25, 2008 at 9:27 am
I didn't ask for the entire proc. I said, if you want us to do all of the work for you, give us table definitions (as create table statements), sample data (as insert statements) and your desired result.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply