T-SQL Displaying a Figure Only Once Based on Certain Criteria

Hello:

Below is my T-SQL query and attached is a sample of results.

I want the result of the following clause in my query to display only one time:

SUM(IV30300.TRXQTY) OVER (PARTITION BY IV30300.ITEMNMBR, IV30300.TRXLOCTN) As [SUMQTY]

This clause gives a lump sum of quantity for each item at its respective location.  

But, as you see in the Results screenshot that I attached, it keeps repeating this calculation for each record.  Again, I just want this calculation to display once for each combination of item and location code.  For the rest of the records for that field, I would like for those records to display as blank.

How can this be done?

Thanks!

TBSupport


select DISTINCT '60 Days' as [Date Range],
IV30300.ITEMNMBR As [Item Number], IV00101.ITEMDESC As [Item Description], 
IV30300.DOCNUMBR AS [Doc],
SUM(IV30300.TRXQTY) OVER (PARTITION BY IV30300.ITEMNMBR, IV30300.TRXLOCTN) As [SUMQTY], IV30300.TRXLOCTN As [Location Code],
CASE WHEN IV30300.DOCTYPE = '1' THEN 'Adjustment'
WHEN IV30300.DOCTYPE = '2' THEN 'Variance'
WHEN IV30300.DOCTYPE = '3' THEN 'Transfer'
WHEN IV30300.DOCTYPE = '4' THEN 'Purchasing Receipt'
WHEN IV30300.DOCTYPE = '5' THEN 'Sales Return'
WHEN IV30300.DOCTYPE = '6' THEN 'Sales'
WHEN IV30300.DOCTYPE = '7' THEN 'Assembly'
END
AS [Document Type],
IV30300.DOCDATE As [Document Date], 
IV30300.TRXQTY As [Qty], IV30300.UOFM As [U Of M], IV30300.UNITCOST As [Unit Cost],
IV30300.EXTDCOST As [Extended Cost], IV00101.USCATVLS_1 as [Use]
 from DTZ..IV30300
 INNER JOIN DTZ..IV00101 
 on 
DTZ..IV30300.ITEMNMBR = DTZ..IV00101.ITEMNMBR
 INNER JOIN DTZ..IV00102 
 on
DTZ..IV30300.ITEMNMBR = DTZ..IV00102.ITEMNMBR and
DTZ..IV30300.TRXLOCTN = DTZ..IV00102.LOCNCODE
 WHERE 
IV30300.DOCTYPE IN (1, 6)
and IV00102.QTYONHND > 0 and
IV00102.QTYONHND - IV00102.ATYALLOC > 0
AND IV00102.RCRDTYPE <> 1
AND IV00101.USCATVLS_1 IN ('FG', 'RAW')
and IV30300.TRXQTY < 0
and IV30300.DOCDATE < cast(getdate()-60 as datetime)

Open in new window

Results.docx
LVL 1
TBSupportAsked:
Who is Participating?
 
smiliefaceConnect With a Mentor Commented:
Try using a CTE and adding a row number, and then using that row number to control the display

Something like
WITH CTE_1 AS (
select DISTINCT '60 Days' as [Date Range],
IV30300.ITEMNMBR As [Item Number], IV00101.ITEMDESC As [Item Description], 
IV30300.DOCNUMBR AS [Doc],
ROW_NUMBER() OVER PARTITION BY (IV30300.ITEMNMBR, IV30300.TRXLOCTN) AS RowNumber,
SUM(IV30300.TRXQTY) OVER (PARTITION BY IV30300.ITEMNMBR, IV30300.TRXLOCTN) As [SUMQTY], IV30300.TRXLOCTN As [Location Code],
CASE WHEN IV30300.DOCTYPE = '1' THEN 'Adjustment'
WHEN IV30300.DOCTYPE = '2' THEN 'Variance'
WHEN IV30300.DOCTYPE = '3' THEN 'Transfer'
WHEN IV30300.DOCTYPE = '4' THEN 'Purchasing Receipt'
WHEN IV30300.DOCTYPE = '5' THEN 'Sales Return'
WHEN IV30300.DOCTYPE = '6' THEN 'Sales'
WHEN IV30300.DOCTYPE = '7' THEN 'Assembly'
END
AS [Document Type],
IV30300.DOCDATE As [Document Date], 
IV30300.TRXQTY As [Qty], IV30300.UOFM As [U Of M], IV30300.UNITCOST As [Unit Cost],
IV30300.EXTDCOST As [Extended Cost], IV00101.USCATVLS_1 as [Use]
 from DTZ..IV30300
 INNER JOIN DTZ..IV00101 
 on 
DTZ..IV30300.ITEMNMBR = DTZ..IV00101.ITEMNMBR
 INNER JOIN DTZ..IV00102 
 on
DTZ..IV30300.ITEMNMBR = DTZ..IV00102.ITEMNMBR and
DTZ..IV30300.TRXLOCTN = DTZ..IV00102.LOCNCODE
 WHERE 
IV30300.DOCTYPE IN (1, 6)
and IV00102.QTYONHND > 0 and
IV00102.QTYONHND - IV00102.ATYALLOC > 0
AND IV00102.RCRDTYPE <> 1
AND IV00101.USCATVLS_1 IN ('FG', 'RAW')
and IV30300.TRXQTY < 0
and IV30300.DOCDATE < cast(getdate()-60 as datetime))
SELECT 
   [Date Range], [Item Number], [Item Description], [Doc]
   , CASE WHEN RowNumber = 1 THEN CAST([SUMQTY] AS VARCHAR(20)) ELSE '' END AS SUMQTY
   , [Location Code], [Document Type], [Document Date], [Qty], [U Of M]
   , [Unit Cost], [Extended Cost], [Use]
   FROM CTE_1

Open in new window

0
 
ValentinoVBI ConsultantCommented:
Good suggestion by smilieface, one small remark though regarding this line:

, CASE WHEN RowNumber = 1 THEN CAST([SUMQTY] AS VARCHAR(20)) ELSE '' END AS SUMQTY

I wouldn't cast the number to string, conversions like that should be handled on the client (reporting) side.  But I do understand why it was added, probably related to this part of the question: "I would like for those records to display as blank"

In any case, I would always handle this on the report side.  Null displays as blank too, so that line could become:

, CASE WHEN RowNumber = 1 THEN [SUMQTY] ELSE null END AS SUMQTY
0
 
TBSupportAuthor Commented:
That worked!  Thanks!  I did have to correct a couple of syntax errors where the "ROW_NUMBER" clause is and SQL forced me to put in an "Order By" within that clause.  

But, again, it worked and below is the completed CTE.

Now, is it possible to get the value returned by the CTE for "SUMQTY" to be the last row, for each occurrence of Item Number and Location Code?

Currently, SUMQTY is appearing in any row.  It would look better, if it were in the last row looking like a true "summed" quantity.

TBSupport

WITH CTE_1 AS (
select DISTINCT '60 Days' as [Date Range],
IV30300.ITEMNMBR As [Item Number], IV00101.ITEMDESC As [Item Description], 
IV30300.DOCNUMBR AS [Doc],
ROW_NUMBER() OVER (PARTITION BY IV30300.ITEMNMBR, IV30300.TRXLOCTN ORDER BY IV30300.ITEMNMBR) AS RowNumber,
SUM(IV30300.TRXQTY) OVER (PARTITION BY IV30300.ITEMNMBR, IV30300.TRXLOCTN) As [SUMQTY], IV30300.TRXLOCTN As [Location Code],
CASE WHEN IV30300.DOCTYPE = '1' THEN 'Adjustment'
WHEN IV30300.DOCTYPE = '2' THEN 'Variance'
WHEN IV30300.DOCTYPE = '3' THEN 'Transfer'
WHEN IV30300.DOCTYPE = '4' THEN 'Purchasing Receipt'
WHEN IV30300.DOCTYPE = '5' THEN 'Sales Return'
WHEN IV30300.DOCTYPE = '6' THEN 'Sales'
WHEN IV30300.DOCTYPE = '7' THEN 'Assembly'
END
AS [Document Type],
IV30300.DOCDATE As [Document Date], 
IV30300.TRXQTY As [Qty], IV30300.UOFM As [U Of M], IV30300.UNITCOST As [Unit Cost],
IV30300.EXTDCOST As [Extended Cost], IV00101.USCATVLS_1 as [Use]
 from DTZ..IV30300
 INNER JOIN DTZ..IV00101 
 on 
DTZ..IV30300.ITEMNMBR = DTZ..IV00101.ITEMNMBR
 INNER JOIN DTZ..IV00102 
 on
DTZ..IV30300.ITEMNMBR = DTZ..IV00102.ITEMNMBR and
DTZ..IV30300.TRXLOCTN = DTZ..IV00102.LOCNCODE
 WHERE 
IV30300.DOCTYPE IN (1, 6)
and IV00102.QTYONHND > 0 and
IV00102.QTYONHND - IV00102.ATYALLOC > 0
AND IV00102.RCRDTYPE <> 1
AND IV00101.USCATVLS_1 IN ('FG', 'RAW')
and IV30300.TRXQTY < 0
and IV30300.DOCDATE < cast(getdate()-60 as datetime))
SELECT 
   [Date Range], [Item Number], [Item Description], [Doc]
   , CASE WHEN RowNumber = 1 THEN CAST([SUMQTY] AS VARCHAR(20)) ELSE '' END AS SUMQTY
   , [Location Code], [Document Type], [Document Date], [Qty], [U Of M]
   , [Unit Cost], [Extended Cost], [Use]
   FROM CTE_1

Open in new window

0
 
smiliefaceCommented:
Ouch - That's nasty.

The only way I can think of is to sort the ROW_NUMBER in descending order and then reverse the order in the outer select.

That way the row with ROW_NUMBER() = 1 is last.

Oh - And I put the convert in to match the request that the column be blank for lines other than the one with the value. I don't consider NULL to be blank!

Edit:
So lets assume you want it in DOCNUMBR order, change the ORDER BY in ROW_NUMBER() to be ORDER BY IV30300.DOCNUMBR DESC, and then change the outer select to order by IV30300.ITEMNMBR, IV30300.TRXLOCTN, IV30300.DOCNUMBR
0
 
TBSupportAuthor Commented:
Actually, please disregard my most recent request.

Thanks, for the good work on this!

TBSupport
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.