Solved

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

Posted on 2014-01-22
7
758 Views
Last Modified: 2014-01-24
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
0
Comment
Question by:TBSupport
  • 2
  • 2
7 Comments
 
LVL 3

Accepted Solution

by:
smilieface earned 500 total points
Comment Utility
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
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
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
 
LVL 1

Author Comment

by:TBSupport
Comment Utility
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
 
LVL 3

Expert Comment

by:smilieface
Comment Utility
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
 
LVL 1

Author Comment

by:TBSupport
Comment Utility
Actually, please disregard my most recent request.

Thanks, for the good work on this!

TBSupport
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now