t-sql 2012 data stays the same with different parameters

Question

Post reply

t-sql 2012 data stays the same with different parameters

wendy elizabeth

SSCoach

Points: 16915
More actions
September 25, 2015 at 9:18 pm

#321495

In the t-sql 2012 that is attached message, is a query that always comes up with the same results. It does not make any difference what start and end dates are given to this t-sql. The results are always the same. Note: I placed the important parts of the t-sql in thus area since it is too long to post to this forum). Thus can you tell me what is wrong with the t-sql and/or tell me how I can run only 'parts' of the sql so that I can see what is wrong?
DECLARE @StartDateDATETIME
DECLARE @EndDateDATETIME
SET@StartDate= '2013-07-01'
SET@EndDate= '2015-08-01'
; WITH Com_House_1 AS (
SELECT
CW.ItemMonth
,CW.ItemID
,CW.Active
,CW.RFSCode
,CW.OldUSDAAS OldUSDA_CW
,CW.NewUSDAAS NewUSDA_CW
,CW.ProductName
,ISNULL(CW.BeginningInventory,0)AS BeginningInventory
,ISNULL(CW.TotalInventory,0)AS TotalInventory
,ISNULL(CW.PhysicalInventory,0)AS PhysicalInventory
,ISNULL(CW.SpreadsheetInventory,0)AS EndingInventory
,ISNULL(CW.Variance,0)AS Variance
,ISNULL(I.Processed,0)AS Processed
,AN.NameAS AccountName
,AN.AccountNumber
--Beginning Inventory Adj
, ISNULL(cw.SpreadsheetInventory,0) -
CASE
WHEN MONTH(cw.ItemMonth) = 1 THEN ISNULL(cw.ReceivedJanuary,0)
WHEN MONTH(cw.ItemMonth) = 2 THEN ISNULL(cw.ReceivedFebruary,0)
WHEN MONTH(cw.ItemMonth) = 3 THEN ISNULL(cw.ReceivedMarch,0)
WHEN MONTH(cw.ItemMonth) = 4 THEN ISNULL(cw.ReceivedApril,0)
WHEN MONTH(cw.ItemMonth) = 5 THEN ISNULL(cw.ReceivedMay,0)
WHEN MONTH(cw.ItemMonth) = 6 THEN ISNULL(cw.ReceivedJune,0)
WHEN MONTH(cw.ItemMonth) = 7 THEN ISNULL(cw.ReceivedJuly,0)
WHEN MONTH(cw.ItemMonth) = 8 THEN ISNULL(cw.ReceivedAugust,0)
WHEN MONTH(cw.ItemMonth) = 9 THEN ISNULL(cw.ReceivedSeptember,0)
WHEN MONTH(cw.ItemMonth) = 10 THEN ISNULL(cw.ReceivedOctober,0)
WHEN MONTH(cw.ItemMonth) = 11 THEN ISNULL(cw.ReceivedNovember,0)
WHEN MONTH(cw.ItemMonth) = 12 THEN ISNULL(cw.ReceivedDecember,0)
END
END AS BeginningInventoryAdj
--MontlyUsage
,CASE
WHEN MONTH(ItemMonth) = 1 THEN ISNULL(cw.ReceivedJanuary,0)
WHEN MONTH(ItemMonth) = 2 THEN ISNULL(cw.ReceivedFebruary,0)
WHEN MONTH(ItemMonth) = 3 THEN ISNULL(cw.ReceivedMarch,0)
WHEN MONTH(ItemMonth) = 4 THEN ISNULL(cw.ReceivedApril,0)
WHEN MONTH(ItemMonth) = 5 THEN ISNULL(cw.ReceivedMay,0)
WHEN MONTH(ItemMonth) = 6 THEN ISNULL(cw.ReceivedJune,0)
WHEN MONTH(ItemMonth) = 7 THEN ISNULL(cw.ReceivedJuly,0)
WHEN MONTH(ItemMonth) = 8 THEN ISNULL(cw.ReceivedAugust,0)
WHEN MONTH(ItemMonth) = 9 THEN ISNULL(cw.ReceivedSeptember,0)
WHEN MONTH(ItemMonth) = 10 THEN ISNULL(cw.ReceivedOctober,0)
WHEN MONTH(ItemMonth) = 11 THEN ISNULL(cw.ReceivedNovember,0)
WHEN MONTH(ItemMonth) = 12 THEN ISNULL(cw.ReceivedDecember,0)
ELSE 0 END AS MonthlyReceipts
,CASE WHEN MONTH(ItemMonth) = 1 THEN ISNULL(ShippedJanuary,0)
WHEN MONTH(ItemMonth) = 2 THEN ISNULL(ShippedFebruary,0)
WHEN MONTH(ItemMonth) = 3 THEN ISNULL(ShippedMarch,0)
WHEN MONTH(ItemMonth) = 4 THEN ISNULL(ShippedApril,0)
WHEN MONTH(ItemMonth) = 5 THEN ISNULL(ShippedMay,0)
WHEN MONTH(ItemMonth) = 6 THEN ISNULL(ShippedJune,0)
WHEN MONTH(ItemMonth) = 7 THEN ISNULL(ShippedJuly,0)
WHEN MONTH(ItemMonth) = 8 THEN ISNULL(ShippedAugust,0)
WHEN MONTH(ItemMonth) = 9 THEN ISNULL(ShippedSeptember,0)
WHEN MONTH(ItemMonth) = 10 THEN ISNULL(ShippedOctober,0)
WHEN MONTH(ItemMonth) = 11 THEN ISNULL(ShippedNovember,0)
WHEN MONTH(ItemMonth) = 12 THEN ISNULL(ShippedDecember,0)
ELSE 0 END AS MonthlyUsage
-- Usage Processing
, CASE WHEN (SELECT SUM(a.CasesDelivered) FROM (
SELECT TOP 1 bola.CasesDelivered FROM [NC].[Lad] bola
WHERE bola.Active = 1
AND bola.RFSCode = cw.RFSCode
AND bola.ItemMonth <= CW.ItemMonth
ORDER BY bola.BillOfLadingDate DESC) a) > cw.Inven
THEN (SELECT ROUND(SUM(b.ProcessingCharge)/NULLIF(SUM(b.CasesDelivered),0), 4) AS WeightedAvereage FROM (
SELECT TOP 1 bolb.CasesDelivered, bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge FROM [NC].[Lad] bolb
WHERE bolb.Active = 1
AND bolb.RFSCode = cw.RFSCode
AND bolb.ItemMonth <= CW.ItemMonth
ORDER BY bolb.BillOfLadingDate DESC) b) * CASE WHEN MONTH(CW.ItemMonth) = 1 THEN ISNULL(ShippedJanuary,0)
WHEN MONTH(CW.ItemMonth) = 2 THEN ISNULL(ShippedFebruary,0)
WHEN MONTH(CW.ItemMonth) = 3 THEN ISNULL(ShippedMarch,0)
WHEN MONTH(CW.ItemMonth) = 4 THEN ISNULL(ShippedApril,0)
WHEN MONTH(CW.ItemMonth) = 5 THEN ISNULL(ShippedMay,0)
WHEN MONTH(CW.ItemMonth) = 6 THEN ISNULL(ShippedJune,0)
WHEN MONTH(CW.ItemMonth) = 7 THEN ISNULL(ShippedJuly,0)
WHEN MONTH(CW.ItemMonth) = 8 THEN ISNULL(ShippedAugust,0)
WHEN MONTH(CW.ItemMonth) = 9 THEN ISNULL(ShippedSeptember,0)
WHEN MONTH(CW.ItemMonth) = 10 THEN ISNULL(ShippedOctober,0)
WHEN MONTH(CW.ItemMonth) = 11 THEN ISNULL(ShippedNovember,0)
WHEN MONTH(CW.ItemMonth) = 12 THEN ISNULL(ShippedDecember,0)
END
WHEN (SELECT SUM(a.CasesDelivered) FROM (
SELECT TOP 2 bola.CasesDelivered FROM [NC].[Lad] bola
WHERE bola.Active = 1
AND bola.RFSCode = cw.RFSCode
AND bola.ItemMonth <= CW.ItemMonth
ORDER BY bola.BillOfLadingDate DESC) a) > cw.Inven
THEN (SELECT ROUND(SUM(b.ProcessingCharge)/NULLIF(SUM(b.CasesDelivered),0), 4) AS WeightedAvereage FROM (
SELECT TOP 2 bolb.CasesDelivered, bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge FROM [NC].[Lad] bolb
WHERE bolb.Active = 1
AND bolb.RFSCode = cw.RFSCode
AND bolb.ItemMonth <= CW.ItemMonth
ORDER BY bolb.BillOfLadingDate DESC) b) * CASE WHEN MONTH(CW.ItemMonth) = 1 THEN ISNULL(ShippedJanuary,0)
WHEN MONTH(CW.ItemMonth) = 2 THEN ISNULL(ShippedFebruary,0)
WHEN MONTH(CW.ItemMonth) = 3 THEN ISNULL(ShippedMarch,0)
WHEN MONTH(CW.ItemMonth) = 4 THEN ISNULL(ShippedApril,0)
WHEN MONTH(CW.ItemMonth) = 5 THEN ISNULL(ShippedMay,0)
WHEN MONTH(CW.ItemMonth) = 6 THEN ISNULL(ShippedJune,0)
WHEN MONTH(CW.ItemMonth) = 7 THEN ISNULL(ShippedJuly,0)
WHEN MONTH(CW.ItemMonth) = 8 THEN ISNULL(ShippedAugust,0)
WHEN MONTH(CW.ItemMonth) = 9 THEN ISNULL(ShippedSeptember,0)
WHEN MONTH(CW.ItemMonth) = 10 THEN ISNULL(ShippedOctober,0)
WHEN MONTH(CW.ItemMonth) = 11 THEN ISNULL(ShippedNovember,0)
WHEN MONTH(CW.ItemMonth) = 12 THEN ISNULL(ShippedDecember,0)
END
WHEN (SELECT SUM(a.CasesDelivered) FROM (
SELECT TOP 3 bola.CasesDelivered FROM [NC].[Lad] bola
WHERE bola.Active = 1
AND bola.RFSCode = cw.RFSCode
AND bola.ItemMonth <= CW.ItemMonth
ORDER BY bola.BillOfLadingDate DESC) a) > cw.Inven
THEN (SELECT ROUND(SUM(b.ProcessingCharge)/NULLIF(SUM(b.CasesDelivered),0), 4) AS WeightedAvereage FROM (
SELECT TOP 3 bolb.CasesDelivered, bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge FROM [NC].[Lad] bolb
WHERE bolb.Active = 1
AND bolb.RFSCode = cw.RFSCode
AND bolb.ItemMonth <= CW.ItemMonth
ORDER BY bolb.BillOfLadingDate DESC) b) * CASE WHEN MONTH(CW.ItemMonth) = 1 THEN ISNULL(ShippedJanuary,0)
WHEN MONTH(CW.ItemMonth) = 2 THEN ISNULL(ShippedFebruary,0)
WHEN MONTH(CW.ItemMonth) = 3 THEN ISNULL(ShippedMarch,0)
WHEN MONTH(CW.ItemMonth) = 4 THEN ISNULL(ShippedApril,0)
WHEN MONTH(CW.ItemMonth) = 5 THEN ISNULL(ShippedMay,0)
WHEN MONTH(CW.ItemMonth) = 6 THEN ISNULL(ShippedJune,0)
WHEN MONTH(CW.ItemMonth) = 7 THEN ISNULL(ShippedJuly,0)
WHEN MONTH(CW.ItemMonth) = 8 THEN ISNULL(ShippedAugust,0)
WHEN MONTH(CW.ItemMonth) = 9 THEN ISNULL(ShippedSeptember,0)
WHEN MONTH(CW.ItemMonth) = 10 THEN ISNULL(ShippedOctober,0)
WHEN MONTH(CW.ItemMonth) = 11 THEN ISNULL(ShippedNovember,0)
WHEN MONTH(CW.ItemMonth) = 12 THEN ISNULL(ShippedDecember,0)
END
WHEN (SELECT SUM(a.CasesDelivered) FROM (
SELECT TOP 4 bola.CasesDelivered FROM [NC].[Lad] bola
WHERE bola.Active = 1
AND bola.RFSCode = cw.RFSCode
AND bola.ItemMonth <= CW.ItemMonth
ORDER BY bola.BillOfLadingDate DESC) a) > cw.Inven
THEN (SELECT ROUND(SUM(b.ProcessingCharge)/NULLIF(SUM(b.CasesDelivered),0), 4) AS WeightedAvereage FROM (
SELECT TOP 4 bolb.CasesDelivered, bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge FROM [NC].[Lad] bolb
WHERE bolb.Active = 1
AND bolb.RFSCode = cw.RFSCode
AND bolb.ItemMonth <= CW.ItemMonth
ORDER BY bolb.BillOfLadingDate DESC) b) * CASE WHEN MONTH(CW.ItemMonth) = 1 THEN ISNULL(ShippedJanuary,0)
WHEN MONTH(CW.ItemMonth) = 2 THEN ISNULL(ShippedFebruary,0)
WHEN MONTH(CW.ItemMonth) = 3 THEN ISNULL(ShippedMarch,0)
WHEN MONTH(CW.ItemMonth) = 4 THEN ISNULL(ShippedApril,0)
WHEN MONTH(CW.ItemMonth) = 5 THEN ISNULL(ShippedMay,0)
WHEN MONTH(CW.ItemMonth) = 6 THEN ISNULL(ShippedJune,0)
WHEN MONTH(CW.ItemMonth) = 7 THEN ISNULL(ShippedJuly,0)
WHEN MONTH(CW.ItemMonth) = 8 THEN ISNULL(ShippedAugust,0)
WHEN MONTH(CW.ItemMonth) = 9 THEN ISNULL(ShippedSeptember,0)
WHEN MONTH(CW.ItemMonth) = 10 THEN ISNULL(ShippedOctober,0)
WHEN MONTH(CW.ItemMonth) = 11 THEN ISNULL(ShippedNovember,0)
WHEN MONTH(CW.ItemMonth) = 12 THEN ISNULL(ShippedDecember,0)
END
WHEN (SELECT SUM(a.CasesDelivered) FROM (
SELECT TOP 5 bola.CasesDelivered FROM [NC].[Lad] bola
WHERE bola.Active = 1
AND bola.RFSCode = cw.RFSCode
AND bola.ItemMonth <= CW.ItemMonth
ORDER BY bola.BillOfLadingDate DESC) a) > cw.Inven
THEN (SELECT ROUND(SUM(b.ProcessingCharge)/NULLIF(SUM(b.CasesDelivered),0), 4) AS WeightedAvereage FROM (
SELECT TOP 5 bolb.CasesDelivered, bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge FROM [NC].[Lad] bolb
WHERE bolb.Active = 1
AND bolb.RFSCode = cw.RFSCode
AND bolb.ItemMonth <= CW.ItemMonth
ORDER BY bolb.BillOfLadingDate DESC) b) * CASE WHEN MONTH(CW.ItemMonth) = 1 THEN ISNULL(ShippedJanuary,0)
WHEN MONTH(CW.ItemMonth) = 2 THEN ISNULL(ShippedFebruary,0)
WHEN MONTH(CW.ItemMonth) = 3 THEN ISNULL(ShippedMarch,0)
WHEN MONTH(CW.ItemMonth) = 4 THEN ISNULL(ShippedApril,0)
WHEN MONTH(CW.ItemMonth) = 5 THEN ISNULL(ShippedMay,0)
WHEN MONTH(CW.ItemMonth) = 6 THEN ISNULL(ShippedJune,0)
WHEN MONTH(CW.ItemMonth) = 7 THEN ISNULL(ShippedJuly,0)
WHEN MONTH(CW.ItemMonth) = 8 THEN ISNULL(ShippedAugust,0)
WHEN MONTH(CW.ItemMonth) = 9 THEN ISNULL(ShippedSeptember,0)
WHEN MONTH(CW.ItemMonth) = 10 THEN ISNULL(ShippedOctober,0)
WHEN MONTH(CW.ItemMonth) = 11 THEN ISNULL(ShippedNovember,0)
WHEN MONTH(CW.ItemMonth) = 12 THEN ISNULL(ShippedDecember,0)
END
WHEN (SELECT SUM(a.CasesDelivered) FROM (
SELECT TOP 1 bola.CasesDelivered FROM [NC].[Lad] bola
WHERE bola.Active = 1
AND bola.RFSCode = cw.RFSCode
AND bola.ItemMonth <= CW.ItemMonth
ORDER BY bola.BillOfLadingDate DESC) a) <= cw.Inven
THEN (SELECT ROUND(SUM(b.ProcessingCharge)/NULLIF(SUM(b.CasesDelivered),0), 4) AS WeightedAvereage FROM (
SELECT TOP 1 bolb.CasesDelivered, bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge FROM [NC].[Lad] bolb
WHERE bolb.Active = 1
AND bolb.RFSCode = cw.RFSCode
AND bolb.ItemMonth <= CW.ItemMonth
ORDER BY bolb.BillOfLadingDate DESC) b) * CASE WHEN MONTH(CW.ItemMonth) = 1 THEN ISNULL(ShippedJanuary,0)
WHEN MONTH(CW.ItemMonth) = 2 THEN ISNULL(ShippedFebruary,0)
WHEN MONTH(CW.ItemMonth) = 3 THEN ISNULL(ShippedMarch,0)
WHEN MONTH(CW.ItemMonth) = 4 THEN ISNULL(ShippedApril,0)
WHEN MONTH(CW.ItemMonth) = 5 THEN ISNULL(ShippedMay,0)
WHEN MONTH(CW.ItemMonth) = 6 THEN ISNULL(ShippedJune,0)
WHEN MONTH(CW.ItemMonth) = 7 THEN ISNULL(ShippedJuly,0)
WHEN MONTH(CW.ItemMonth) = 8 THEN ISNULL(ShippedAugust,0)
WHEN MONTH(CW.ItemMonth) = 9 THEN ISNULL(ShippedSeptember,0)
WHEN MONTH(CW.ItemMonth) = 10 THEN ISNULL(ShippedOctober,0)
WHEN MONTH(CW.ItemMonth) = 11 THEN ISNULL(ShippedNovember,0)
WHEN MONTH(CW.ItemMonth) = 12 THEN ISNULL(ShippedDecember,0)
END
FROMNC.ComsWarehouse CW
LEFT OUTER JOIN
NC.Items I
ON CW.RFSCode = I.RFSCode
LEFT OUTER JOIN
NC.AccountNumbers AN
ON AN.AccountNumberID = I.AccountNumberID
WHERE
(ISNULL(CW.BeginningInventory,0) <> 0 OR ISNULL(CW.SpreadsheetInventory,0) <> 0 )
AND ISNULL(CW.Active,0) = 1
ANDISNULL(I.Active, 0) = 1
), Delivered_Items AS (
SELECT DISTINCT
RFSCode
,MAX(OldUSDA) AS OldUSDA_DI
,MAX(NewUSDA) AS NewUSDA_DI
FROMNC.DeliveredItems
WHEREActive = 1
GROUP BYRFSCode
), Com_House_2 AS (
SELECT
CW.ItemMonth
,CW.ItemID
,CW.ProductName
,CW.RFSCode
,CASE
WHEN CW.OldUSDA_CW IS NULL AND DI.OldUSDA_DI IS NOT NULL THEN DI.OldUSDA_DI
WHEN CW.OldUSDA_CW IS NOT NULL AND DI.OldUSDA_DI IS NULL THEN CW.OldUSDA_CW
ELSE CW.OldUSDA_CW END AS OldUSDA
,CASE
WHEN CW.NewUSDA_CW IS NULL AND DI.NewUSDA_DI IS NOT NULL THEN DI.NewUSDA_DI
WHEN CW.NewUSDA_CW IS NOT NULL AND DI.NewUSDA_DI IS NULL THEN CW.NewUSDA_CW
ELSE CW.NewUSDA_CW END AS NewUSDA
,CW.Processed
,CW.BeginningInventory
,CW.BeginningInventoryAdj
,CW.TotalInventory
,CW.PhysicalInventory
,CW.EndingInventory
,CW.Variance
,ISNULL(CW.AccountNumber,0)AS AccountNumber
,ISNULL(CW.AccountName,'Unknown')AS AccountName
,CW.MonthlyReceipts
,CW.MonthlyUsage
,CW.CaseRate
,CW.ProcessingCharge
,CW.USDAStorageCharge
,CW.PerUnitCharge
,CW.EndingInventoryExtension
,CW.ReceiptValue
,CW.UsageProcessing
,CW.UsageStorage
,CW.UsageInventoryExtension
FROM
Com_House_1 CW
LEFT OUTER JOIN
Delivered_Items DI
ON CW.RFSCode = DI.RFSCode
), Com_House_Final AS (
SELECT
ItemMonth
,AccountNumber
,AccountName
,ItemID
,ProductName
,RFSCode
,OldUSDA
,NewUSDA
,ISNULL(NewUSDA,OldUSDA) AS USDA
,Processed
--,BeginningInventory
,BeginningInventoryAdj AS BeginningInventory
,EndingInventory
--,PhysicalInventory
,MonthlyReceipts
,MonthlyUsage
,CaseRate
,ProcessingCharge
,USDAStorageCharge
,PerUnitCharge
,EndingInventoryExtension
,ReceiptValue
,UsageProcessing
,UsageStorage
,UsageInventoryExtension
FROM
Com_House_2 CWF
), First_Month AS (
SELECT
FirstMonth.ItemMonth
,FirstMonth.AccountNumber
,FirstMonth.AccountName
,FirstMonth.ProductName
,FirstMonth.Processed
,FirstMonth.RFSCode
,FirstMonth.USDA
,FirstMonth.BeginningInventory
FROM
(SELECT
CWF.ItemMonth
,CWF.AccountNumber
,CWF.AccountName
,CWF.ProductName
,CWF.Processed
,CWF.RFSCode
,CWF.USDA
,CWF.BeginningInventory
,row_number() OVER (PARTITION BY CWF.RFSCode order by CWF.ItemMonth asc) as row
FROMCom_House_Final CWF
WHERE
CWF.ItemMonth>= @StartDate
AND CWF.ItemMonth<= @EndDate)AS FirstMonth
WHERE ROW = 1
), Last_Month AS (
SELECT
LastMonth.ItemMonth
,LastMonth.RFSCode
,LastMonth.EndingInventory
,LastMonth.MonthlyReceipts
,LastMonth.MonthlyUsage
,LastMonth.CaseRate
,LastMonth.ProcessingCharge
,LastMonth.USDAStorageCharge
,LastMonth.PerUnitCharge
,LastMonth.EndingInventoryExtension
,LastMonth.ReceiptValue
,LastMonth.UsageProcessing
,LastMonth.UsageStorage
,LastMonth.UsageInventoryExtension
FROM
(SELECT
CWF.ItemMonth
,CWF.RFSCode
,CWF.MonthlyReceipts
,CWF.MonthlyUsage
,CWF.EndingInventory
,CWF.CaseRate
,CWF.ProcessingCharge
,CWF.USDAStorageCharge
,CWF.PerUnitCharge
,CWF.EndingInventoryExtension
,CWF.ReceiptValue
,CWF.UsageProcessing
,CWF.UsageStorage
,CWF.UsageInventoryExtension
,row_number() OVER (PARTITION BY CWF.RFSCode order by CWF.ItemMonth desc) as row
FROMCom_House_Final CWF
WHERE
CWF.ItemMonth >= @StartDate
AND CWF.ItemMonth <= @EndDate)AS LastMonth
WHERE ROW = 1
), Monthly_Processing AS (
SELECT
CWF.AccountNumber
,CWF.AccountName
,CWF.ProductName
,CWF.RFSCode
,CWF.USDA
,SUM (CWF.MonthlyReceipts)AS Receipts
,SUM (CWF.MonthlyUsage)AS Usage
,SUM (CWF.UsageProcessing)AS UsageProcessingSum
FROMCom_House_Final CWF
WHERE
CWF.ItemMonth >= @StartDate
AND CWF.ItemMonth <= @EndDate
GROUP BY
CWF.AccountNumber
,CWF.AccountName
,CWF.ProductName
,CWF.RFSCode
,CWF.USDA
,CWF.RFSCode
)
SELECT
CONVERT(INT,FM.AccountNumber)AS AccountNumber
,FM.AccountName
,FM.ProductName
,FM.Processed
,FM.RFSCode
,FM.USDA
,FM.ItemMonthAS Month_Start
,LM.ItemMonthAS Month_End
,FM.BeginningInventory
,MP.Usage
,MP.Receipts
,FM.BeginningInventory + MP.Receipts - MP.Usage AS EndingInventory
--,LM.EndingInventory
,LM.CaseRate
,LM.ProcessingCharge
,LM.USDAStorageCharge
,LM.PerUnitCharge
,(LM.CaseRate + LM.ProcessingCharge) * (FM.BeginningInventory + MP.Receipts - MP.Usage) AS EndingInventoryExtension
--,LM.EndingInventoryExtension
,LM.ReceiptValue
,LM.UsageProcessing
,LM.UsageStorage
,LM.UsageInventoryExtension
FROM
First_Month FM
LEFT JOIN
Last_Month LM ON LM.RFSCode = FM.RFSCode
LEFT JOIN
Monthly_Processing MP
ON MP.RFSCode = FM.RFSCode

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Quick question, can you post the DDL (create table) and preferably some sample data as an insert statement?

😎

Vladan SSC-Insane Points: 22072 More actions · Answer 2

Hello,

without DDL of the objects used in this query, and sample data, we can only guess... maybe column Com_House_Final.ItemMonth is not a datetime? Maybe it does not contain any data at all, or it contains the same date in all rows that enter the query?

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Perhaps you could also post the actual query? And please do all of us a favor and the IFCode shortcuts on the left side when posting so you can format your post a lot cleaner.

Here is the sql you posted after running it through a code formatter.

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = '2013-07-01'

SET @EndDate = '2015-08-01';

WITH Com_House_1

AS (

SELECT CW.ItemMonth

,CW.ItemID

,CW.Active

,CW.RFSCode

,CW.OldUSDA AS OldUSDA_CW

,CW.NewUSDA AS NewUSDA_CW

,CW.ProductName

,ISNULL(CW.BeginningInventory, 0) AS BeginningInventory

,ISNULL(CW.TotalInventory, 0) AS TotalInventory

,ISNULL(CW.PhysicalInventory, 0) AS PhysicalInventory

,ISNULL(CW.SpreadsheetInventory, 0) AS EndingInventory

,ISNULL(CW.Variance, 0) AS Variance

,ISNULL(I.Processed, 0) AS Processed

,AN.NAME AS AccountName

,AN.AccountNumber

-- Beginning Inventory Adj

,ISNULL(cw.SpreadsheetInventory, 0) - CASE

WHEN MONTH(cw.ItemMonth) = 1

THEN ISNULL(cw.ReceivedJanuary, 0)

WHEN MONTH(cw.ItemMonth) = 2

THEN ISNULL(cw.ReceivedFebruary, 0)

WHEN MONTH(cw.ItemMonth) = 3

THEN ISNULL(cw.ReceivedMarch, 0)

WHEN MONTH(cw.ItemMonth) = 4

THEN ISNULL(cw.ReceivedApril, 0)

WHEN MONTH(cw.ItemMonth) = 5

THEN ISNULL(cw.ReceivedMay, 0)

WHEN MONTH(cw.ItemMonth) = 6

THEN ISNULL(cw.ReceivedJune, 0)

WHEN MONTH(cw.ItemMonth) = 7

THEN ISNULL(cw.ReceivedJuly, 0)

WHEN MONTH(cw.ItemMonth) = 8

THEN ISNULL(cw.ReceivedAugust, 0)

WHEN MONTH(cw.ItemMonth) = 9

THEN ISNULL(cw.ReceivedSeptember, 0)

WHEN MONTH(cw.ItemMonth) = 10

THEN ISNULL(cw.ReceivedOctober, 0)

WHEN MONTH(cw.ItemMonth) = 11

THEN ISNULL(cw.ReceivedNovember, 0)

WHEN MONTH(cw.ItemMonth) = 12

THEN ISNULL(cw.ReceivedDecember, 0)

END END AS BeginningInventoryAdj

-- MontlyUsage

,CASE

WHEN MONTH(ItemMonth) = 1

THEN ISNULL(cw.ReceivedJanuary, 0)

WHEN MONTH(ItemMonth) = 2

THEN ISNULL(cw.ReceivedFebruary, 0)

WHEN MONTH(ItemMonth) = 3

THEN ISNULL(cw.ReceivedMarch, 0)

WHEN MONTH(ItemMonth) = 4

THEN ISNULL(cw.ReceivedApril, 0)

WHEN MONTH(ItemMonth) = 5

THEN ISNULL(cw.ReceivedMay, 0)

WHEN MONTH(ItemMonth) = 6

THEN ISNULL(cw.ReceivedJune, 0)

WHEN MONTH(ItemMonth) = 7

THEN ISNULL(cw.ReceivedJuly, 0)

WHEN MONTH(ItemMonth) = 8

THEN ISNULL(cw.ReceivedAugust, 0)

WHEN MONTH(ItemMonth) = 9

THEN ISNULL(cw.ReceivedSeptember, 0)

WHEN MONTH(ItemMonth) = 10

THEN ISNULL(cw.ReceivedOctober, 0)

WHEN MONTH(ItemMonth) = 11

THEN ISNULL(cw.ReceivedNovember, 0)

WHEN MONTH(ItemMonth) = 12

THEN ISNULL(cw.ReceivedDecember, 0)

ELSE 0

END AS MonthlyReceipts

,CASE

WHEN MONTH(ItemMonth) = 1

THEN ISNULL(ShippedJanuary, 0)

WHEN MONTH(ItemMonth) = 2

THEN ISNULL(ShippedFebruary, 0)

WHEN MONTH(ItemMonth) = 3

THEN ISNULL(ShippedMarch, 0)

WHEN MONTH(ItemMonth) = 4

THEN ISNULL(ShippedApril, 0)

WHEN MONTH(ItemMonth) = 5

THEN ISNULL(ShippedMay, 0)

WHEN MONTH(ItemMonth) = 6

THEN ISNULL(ShippedJune, 0)

WHEN MONTH(ItemMonth) = 7

THEN ISNULL(ShippedJuly, 0)

WHEN MONTH(ItemMonth) = 8

THEN ISNULL(ShippedAugust, 0)

WHEN MONTH(ItemMonth) = 9

THEN ISNULL(ShippedSeptember, 0)

WHEN MONTH(ItemMonth) = 10

THEN ISNULL(ShippedOctober, 0)

WHEN MONTH(ItemMonth) = 11

THEN ISNULL(ShippedNovember, 0)

WHEN MONTH(ItemMonth) = 12

THEN ISNULL(ShippedDecember, 0)

ELSE 0

END AS MonthlyUsage

-- Usage Processing

,CASE

WHEN (

SELECT SUM(a.CasesDelivered)

FROM (

SELECT TOP 1 bola.CasesDelivered

FROM [NC].[Lad] bola

WHERE bola.Active = 1

AND bola.RFSCode = cw.RFSCode

AND bola.ItemMonth <= CW.ItemMonth

ORDER BY bola.BillOfLadingDate DESC

) a

) > cw.Inven

THEN (

SELECT ROUND(SUM(b.ProcessingCharge) / NULLIF(SUM(b.CasesDelivered), 0), 4) AS WeightedAvereage

FROM (

SELECT TOP 1 bolb.CasesDelivered

,bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge

FROM [NC].[Lad] bolb

WHERE bolb.Active = 1

AND bolb.RFSCode = cw.RFSCode

AND bolb.ItemMonth <= CW.ItemMonth

ORDER BY bolb.BillOfLadingDate DESC

) b

) * CASE

WHEN MONTH(CW.ItemMonth) = 1

THEN ISNULL(ShippedJanuary, 0)

WHEN MONTH(CW.ItemMonth) = 2

THEN ISNULL(ShippedFebruary, 0)

WHEN MONTH(CW.ItemMonth) = 3

THEN ISNULL(ShippedMarch, 0)

WHEN MONTH(CW.ItemMonth) = 4

THEN ISNULL(ShippedApril, 0)

WHEN MONTH(CW.ItemMonth) = 5

THEN ISNULL(ShippedMay, 0)

WHEN MONTH(CW.ItemMonth) = 6

THEN ISNULL(ShippedJune, 0)

WHEN MONTH(CW.ItemMonth) = 7

THEN ISNULL(ShippedJuly, 0)

WHEN MONTH(CW.ItemMonth) = 8

THEN ISNULL(ShippedAugust, 0)

WHEN MONTH(CW.ItemMonth) = 9

THEN ISNULL(ShippedSeptember, 0)

WHEN MONTH(CW.ItemMonth) = 10

THEN ISNULL(ShippedOctober, 0)

WHEN MONTH(CW.ItemMonth) = 11

THEN ISNULL(ShippedNovember, 0)

WHEN MONTH(CW.ItemMonth) = 12

THEN ISNULL(ShippedDecember, 0)

END

WHEN (

SELECT SUM(a.CasesDelivered)

FROM (

SELECT TOP 2 bola.CasesDelivered

FROM [NC].[Lad] bola

WHERE bola.Active = 1

AND bola.RFSCode = cw.RFSCode

AND bola.ItemMonth <= CW.ItemMonth

ORDER BY bola.BillOfLadingDate DESC

) a

) > cw.Inven

THEN (

SELECT ROUND(SUM(b.ProcessingCharge) / NULLIF(SUM(b.CasesDelivered), 0), 4) AS WeightedAvereage

FROM (

SELECT TOP 2 bolb.CasesDelivered

,bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge

FROM [NC].[Lad] bolb

WHERE bolb.Active = 1

AND bolb.RFSCode = cw.RFSCode

AND bolb.ItemMonth <= CW.ItemMonth

ORDER BY bolb.BillOfLadingDate DESC

) b

) * CASE

WHEN MONTH(CW.ItemMonth) = 1

THEN ISNULL(ShippedJanuary, 0)

WHEN MONTH(CW.ItemMonth) = 2

THEN ISNULL(ShippedFebruary, 0)

WHEN MONTH(CW.ItemMonth) = 3

THEN ISNULL(ShippedMarch, 0)

WHEN MONTH(CW.ItemMonth) = 4

THEN ISNULL(ShippedApril, 0)

WHEN MONTH(CW.ItemMonth) = 5

THEN ISNULL(ShippedMay, 0)

WHEN MONTH(CW.ItemMonth) = 6

THEN ISNULL(ShippedJune, 0)

WHEN MONTH(CW.ItemMonth) = 7

THEN ISNULL(ShippedJuly, 0)

WHEN MONTH(CW.ItemMonth) = 8

THEN ISNULL(ShippedAugust, 0)

WHEN MONTH(CW.ItemMonth) = 9

THEN ISNULL(ShippedSeptember, 0)

WHEN MONTH(CW.ItemMonth) = 10

THEN ISNULL(ShippedOctober, 0)

WHEN MONTH(CW.ItemMonth) = 11

THEN ISNULL(ShippedNovember, 0)

WHEN MONTH(CW.ItemMonth) = 12

THEN ISNULL(ShippedDecember, 0)

END

WHEN (

SELECT SUM(a.CasesDelivered)

FROM (

SELECT TOP 3 bola.CasesDelivered

FROM [NC].[Lad] bola

WHERE bola.Active = 1

AND bola.RFSCode = cw.RFSCode

AND bola.ItemMonth <= CW.ItemMonth

ORDER BY bola.BillOfLadingDate DESC

) a

) > cw.Inven

THEN (

SELECT ROUND(SUM(b.ProcessingCharge) / NULLIF(SUM(b.CasesDelivered), 0), 4) AS WeightedAvereage

FROM (

SELECT TOP 3 bolb.CasesDelivered

,bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge

FROM [NC].[Lad] bolb

WHERE bolb.Active = 1

AND bolb.RFSCode = cw.RFSCode

AND bolb.ItemMonth <= CW.ItemMonth

ORDER BY bolb.BillOfLadingDate DESC

) b

) * CASE

WHEN MONTH(CW.ItemMonth) = 1

THEN ISNULL(ShippedJanuary, 0)

WHEN MONTH(CW.ItemMonth) = 2

THEN ISNULL(ShippedFebruary, 0)

WHEN MONTH(CW.ItemMonth) = 3

THEN ISNULL(ShippedMarch, 0)

WHEN MONTH(CW.ItemMonth) = 4

THEN ISNULL(ShippedApril, 0)

WHEN MONTH(CW.ItemMonth) = 5

THEN ISNULL(ShippedMay, 0)

WHEN MONTH(CW.ItemMonth) = 6

THEN ISNULL(ShippedJune, 0)

WHEN MONTH(CW.ItemMonth) = 7

THEN ISNULL(ShippedJuly, 0)

WHEN MONTH(CW.ItemMonth) = 8

THEN ISNULL(ShippedAugust, 0)

WHEN MONTH(CW.ItemMonth) = 9

THEN ISNULL(ShippedSeptember, 0)

WHEN MONTH(CW.ItemMonth) = 10

THEN ISNULL(ShippedOctober, 0)

WHEN MONTH(CW.ItemMonth) = 11

THEN ISNULL(ShippedNovember, 0)

WHEN MONTH(CW.ItemMonth) = 12

THEN ISNULL(ShippedDecember, 0)

END

WHEN (

SELECT SUM(a.CasesDelivered)

FROM (

SELECT TOP 4 bola.CasesDelivered

FROM [NC].[Lad] bola

WHERE bola.Active = 1

AND bola.RFSCode = cw.RFSCode

AND bola.ItemMonth <= CW.ItemMonth

ORDER BY bola.BillOfLadingDate DESC

) a

) > cw.Inven

THEN (

SELECT ROUND(SUM(b.ProcessingCharge) / NULLIF(SUM(b.CasesDelivered), 0), 4) AS WeightedAvereage

FROM (

SELECT TOP 4 bolb.CasesDelivered

,bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge

FROM [NC].[Lad] bolb

WHERE bolb.Active = 1

AND bolb.RFSCode = cw.RFSCode

AND bolb.ItemMonth <= CW.ItemMonth

ORDER BY bolb.BillOfLadingDate DESC

) b

) * CASE

WHEN MONTH(CW.ItemMonth) = 1

THEN ISNULL(ShippedJanuary, 0)

WHEN MONTH(CW.ItemMonth) = 2

THEN ISNULL(ShippedFebruary, 0)

WHEN MONTH(CW.ItemMonth) = 3

THEN ISNULL(ShippedMarch, 0)

WHEN MONTH(CW.ItemMonth) = 4

THEN ISNULL(ShippedApril, 0)

WHEN MONTH(CW.ItemMonth) = 5

THEN ISNULL(ShippedMay, 0)

WHEN MONTH(CW.ItemMonth) = 6

THEN ISNULL(ShippedJune, 0)

WHEN MONTH(CW.ItemMonth) = 7

THEN ISNULL(ShippedJuly, 0)

WHEN MONTH(CW.ItemMonth) = 8

THEN ISNULL(ShippedAugust, 0)

WHEN MONTH(CW.ItemMonth) = 9

THEN ISNULL(ShippedSeptember, 0)

WHEN MONTH(CW.ItemMonth) = 10

THEN ISNULL(ShippedOctober, 0)

WHEN MONTH(CW.ItemMonth) = 11

THEN ISNULL(ShippedNovember, 0)

WHEN MONTH(CW.ItemMonth) = 12

THEN ISNULL(ShippedDecember, 0)

END

WHEN (

SELECT SUM(a.CasesDelivered)

FROM (

SELECT TOP 5 bola.CasesDelivered

FROM [NC].[Lad] bola

WHERE bola.Active = 1

AND bola.RFSCode = cw.RFSCode

AND bola.ItemMonth <= CW.ItemMonth

ORDER BY bola.BillOfLadingDate DESC

) a

) > cw.Inven

THEN (

SELECT ROUND(SUM(b.ProcessingCharge) / NULLIF(SUM(b.CasesDelivered), 0), 4) AS WeightedAvereage

FROM (

SELECT TOP 5 bolb.CasesDelivered

,bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge

FROM [NC].[Lad] bolb

WHERE bolb.Active = 1

AND bolb.RFSCode = cw.RFSCode

AND bolb.ItemMonth <= CW.ItemMonth

ORDER BY bolb.BillOfLadingDate DESC

) b

) * CASE

WHEN MONTH(CW.ItemMonth) = 1

THEN ISNULL(ShippedJanuary, 0)

WHEN MONTH(CW.ItemMonth) = 2

THEN ISNULL(ShippedFebruary, 0)

WHEN MONTH(CW.ItemMonth) = 3

THEN ISNULL(ShippedMarch, 0)

WHEN MONTH(CW.ItemMonth) = 4

THEN ISNULL(ShippedApril, 0)

WHEN MONTH(CW.ItemMonth) = 5

THEN ISNULL(ShippedMay, 0)

WHEN MONTH(CW.ItemMonth) = 6

THEN ISNULL(ShippedJune, 0)

WHEN MONTH(CW.ItemMonth) = 7

THEN ISNULL(ShippedJuly, 0)

WHEN MONTH(CW.ItemMonth) = 8

THEN ISNULL(ShippedAugust, 0)

WHEN MONTH(CW.ItemMonth) = 9

THEN ISNULL(ShippedSeptember, 0)

WHEN MONTH(CW.ItemMonth) = 10

THEN ISNULL(ShippedOctober, 0)

WHEN MONTH(CW.ItemMonth) = 11

THEN ISNULL(ShippedNovember, 0)

WHEN MONTH(CW.ItemMonth) = 12

THEN ISNULL(ShippedDecember, 0)

END

WHEN (

SELECT SUM(a.CasesDelivered)

FROM (

SELECT TOP 1 bola.CasesDelivered

FROM [NC].[Lad] bola

WHERE bola.Active = 1

AND bola.RFSCode = cw.RFSCode

AND bola.ItemMonth <= CW.ItemMonth

ORDER BY bola.BillOfLadingDate DESC

) a

) <= cw.Inven

THEN (

SELECT ROUND(SUM(b.ProcessingCharge) / NULLIF(SUM(b.CasesDelivered), 0), 4) AS WeightedAvereage

FROM (

SELECT TOP 1 bolb.CasesDelivered

,bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge

FROM [NC].[Lad] bolb

WHERE bolb.Active = 1

AND bolb.RFSCode = cw.RFSCode

AND bolb.ItemMonth <= CW.ItemMonth

ORDER BY bolb.BillOfLadingDate DESC

) b

) * CASE

WHEN MONTH(CW.ItemMonth) = 1

THEN ISNULL(ShippedJanuary, 0)

WHEN MONTH(CW.ItemMonth) = 2

THEN ISNULL(ShippedFebruary, 0)

WHEN MONTH(CW.ItemMonth) = 3

THEN ISNULL(ShippedMarch, 0)

WHEN MONTH(CW.ItemMonth) = 4

THEN ISNULL(ShippedApril, 0)

WHEN MONTH(CW.ItemMonth) = 5

THEN ISNULL(ShippedMay, 0)

WHEN MONTH(CW.ItemMonth) = 6

THEN ISNULL(ShippedJune, 0)

WHEN MONTH(CW.ItemMonth) = 7

THEN ISNULL(ShippedJuly, 0)

WHEN MONTH(CW.ItemMonth) = 8

THEN ISNULL(ShippedAugust, 0)

WHEN MONTH(CW.ItemMonth) = 9

THEN ISNULL(ShippedSeptember, 0)

WHEN MONTH(CW.ItemMonth) = 10

THEN ISNULL(ShippedOctober, 0)

WHEN MONTH(CW.ItemMonth) = 11

THEN ISNULL(ShippedNovember, 0)

WHEN MONTH(CW.ItemMonth) = 12

THEN ISNULL(ShippedDecember, 0)

END FROM NC.ComsWarehouse CW LEFT JOIN NC.Items I ON CW.RFSCode = I.RFSCode LEFT JOIN NC.AccountNumbers AN ON AN.AccountNumberID = I.AccountNumberID WHERE (

ISNULL(CW.BeginningInventory, 0) <> 0

OR ISNULL(CW.SpreadsheetInventory, 0) <> 0

)

AND ISNULL(CW.Active, 0) = 1

AND ISNULL(I.Active, 0) = 1 )

,Delivered_Items AS (

SELECT DISTINCT RFSCode

,MAX(OldUSDA) AS OldUSDA_DI

,MAX(NewUSDA) AS NewUSDA_DI

FROM NC.DeliveredItems

WHERE Active = 1

GROUP BY RFSCode

)

,Com_House_2 AS (

SELECT CW.ItemMonth

,CW.ItemID

,CW.ProductName

,CW.RFSCode

,CASE

WHEN CW.OldUSDA_CW IS NULL

AND DI.OldUSDA_DI IS NOT NULL

THEN DI.OldUSDA_DI

WHEN CW.OldUSDA_CW IS NOT NULL

AND DI.OldUSDA_DI IS NULL

THEN CW.OldUSDA_CW

ELSE CW.OldUSDA_CW

END AS OldUSDA

,CASE

WHEN CW.NewUSDA_CW IS NULL

AND DI.NewUSDA_DI IS NOT NULL

THEN DI.NewUSDA_DI

WHEN CW.NewUSDA_CW IS NOT NULL

AND DI.NewUSDA_DI IS NULL

THEN CW.NewUSDA_CW

ELSE CW.NewUSDA_CW

END AS NewUSDA

,CW.Processed

,CW.BeginningInventory

,CW.BeginningInventoryAdj

,CW.TotalInventory

,CW.PhysicalInventory

,CW.EndingInventory

,CW.Variance

,ISNULL(CW.AccountNumber, 0) AS AccountNumber

,ISNULL(CW.AccountName, 'Unknown') AS AccountName

,CW.MonthlyReceipts

,CW.MonthlyUsage

,CW.CaseRate

,CW.ProcessingCharge

,CW.USDAStorageCharge

,CW.PerUnitCharge

,CW.EndingInventoryExtension

,CW.ReceiptValue

,CW.UsageProcessing

,CW.UsageStorage

,CW.UsageInventoryExtension

FROM Com_House_1 CW

LEFT JOIN Delivered_Items DI ON CW.RFSCode = DI.RFSCode

)

,Com_House_Final AS (

SELECT ItemMonth

,AccountNumber

,AccountName

,ItemID

,ProductName

,RFSCode

,OldUSDA

,NewUSDA

,ISNULL(NewUSDA, OldUSDA) AS USDA

,Processed

--, BeginningInventory

,BeginningInventoryAdj AS BeginningInventory

,EndingInventory

--, PhysicalInventory

,MonthlyReceipts

,MonthlyUsage

,CaseRate

,ProcessingCharge

,USDAStorageCharge

,PerUnitCharge

,EndingInventoryExtension

,ReceiptValue

,UsageProcessing

,UsageStorage

,UsageInventoryExtension

FROM Com_House_2 CWF

)

,First_Month AS (

SELECT FirstMonth.ItemMonth

,FirstMonth.AccountNumber

,FirstMonth.AccountName

,FirstMonth.ProductName

,FirstMonth.Processed

,FirstMonth.RFSCode

,FirstMonth.USDA

,FirstMonth.BeginningInventory

FROM (

SELECT CWF.ItemMonth

,CWF.AccountNumber

,CWF.AccountName

,CWF.ProductName

,CWF.Processed

,CWF.RFSCode

,CWF.USDA

,CWF.BeginningInventory

,row_number() OVER (

PARTITION BY CWF.RFSCode ORDER BY CWF.ItemMonth ASC

) AS row

FROM Com_House_Final CWF

WHERE CWF.ItemMonth >= @StartDate

AND CWF.ItemMonth <= @EndDate

) AS FirstMonth

WHERE ROW = 1

)

,Last_Month AS (

SELECT LastMonth.ItemMonth

,LastMonth.RFSCode

,LastMonth.EndingInventory

,LastMonth.MonthlyReceipts

,LastMonth.MonthlyUsage

,LastMonth.CaseRate

,LastMonth.ProcessingCharge

,LastMonth.USDAStorageCharge

,LastMonth.PerUnitCharge

,LastMonth.EndingInventoryExtension

,LastMonth.ReceiptValue

,LastMonth.UsageProcessing

,LastMonth.UsageStorage

,LastMonth.UsageInventoryExtension

FROM (

SELECT CWF.ItemMonth

,CWF.RFSCode

,CWF.MonthlyReceipts

,CWF.MonthlyUsage

,CWF.EndingInventory

,CWF.CaseRate

,CWF.ProcessingCharge

,CWF.USDAStorageCharge

,CWF.PerUnitCharge

,CWF.EndingInventoryExtension

,CWF.ReceiptValue

,CWF.UsageProcessing

,CWF.UsageStorage

,CWF.UsageInventoryExtension

,row_number() OVER (

PARTITION BY CWF.RFSCode ORDER BY CWF.ItemMonth DESC

) AS row

FROM Com_House_Final CWF

WHERE CWF.ItemMonth >= @StartDate

AND CWF.ItemMonth <= @EndDate

) AS LastMonth

WHERE ROW = 1

)

,Monthly_Processing AS (

SELECT CWF.AccountNumber

,CWF.AccountName

,CWF.ProductName

,CWF.RFSCode

,CWF.USDA

,SUM(CWF.MonthlyReceipts) AS Receipts

,SUM(CWF.MonthlyUsage) AS Usage

,SUM(CWF.UsageProcessing) AS UsageProcessingSum

FROM Com_House_Final CWF

WHERE CWF.ItemMonth >= @StartDate

AND CWF.ItemMonth <= @EndDate

GROUP BY CWF.AccountNumber

,CWF.AccountName

,CWF.ProductName

,CWF.RFSCode

,CWF.USDA

,CWF.RFSCode

) SELECT CONVERT(INT, FM.AccountNumber) AS AccountNumber

,FM.AccountName

,FM.ProductName

,FM.Processed

,FM.RFSCode

,FM.USDA

,FM.ItemMonth AS Month_Start

,LM.ItemMonth AS Month_End

,FM.BeginningInventory

,MP.Usage

,MP.Receipts

,FM.BeginningInventory + MP.Receipts - MP.Usage AS EndingInventory

--, LM.EndingInventory

,LM.CaseRate

,LM.ProcessingCharge

,LM.USDAStorageCharge

,LM.PerUnitCharge

,(LM.CaseRate + LM.ProcessingCharge) * (FM.BeginningInventory + MP.Receipts - MP.Usage) AS EndingInventoryExtension

--, LM.EndingInventoryExtension

,LM.ReceiptValue

,LM.UsageProcessing

,LM.UsageStorage

,LM.UsageInventoryExtension FROM First_Month FM LEFT JOIN Last_Month LM ON LM.RFSCode = FM.RFSCode LEFT JOIN Monthly_Processing MP ON MP.RFSCode = FM.RFSCode

)

Here is the major challenge. This is almost 600 lines of sql and it won't parse because it appears to have been chopped down or something. We have no idea what this is supposed to do, what the tables are like or what you are expecting for results. In short, you posted a wall of text and think somebody can actually tell you what is wrong with it. As posted there is nothing anybody can do to help you.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

dlsql SSC Enthusiast Points: 186 More actions · Answer 4

If you look at your First_Month set which the final select statement selects from you have

WHERE ROW = 1

Its a little unclear what is actually being returned but is it always one row? If it is that is probably your problem.