Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2014-01-22
7
788 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
ID: 39802125
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
ID: 39802249
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
ID: 39803183
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
ID: 39805393
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
ID: 39806712
Actually, please disregard my most recent request.

Thanks, for the good work on this!

TBSupport
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

839 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