asked on
How can I get a row to show up in one column if the invoice type = RD (Invoice) or if its RC show up in another row?
DECLARE @date DATETIME;
SET @date = GETDATE();
SELECT DISTINCT
CAST(SUM(invitem.qty * invitem.item_ntprice) AS DECIMAL(18, 2)) AS 'SALES', --WHEN invhead.invc_type = 'RD' ELSE 0
qty AS 'SALE_QTY', --WHEN invhead.invc_type = 'RD' ELSE 0
CAST(SUM(invitem.qty * invitem.item_ntprice) AS DECIMAL(18, 2)) AS 'RETURN', --WHEN invhead.invc_type = 'RC' ELSE 0
qty AS 'RETURN_QTY', --WHEN invhead.invc_type = 'RC' ELSE 0
partmstr.part_code,
item_price,
invhead.invoice_numb,
orderfrom.bus_name,
orderfrom.address1,
orderfrom.city,
orderfrom.state,
orderfrom.zip,
orderfrom.phone,
partmstr.upc_code,
invhead.inv_prt_date,
DATEADD(MONTH, +DATEDIFF(MONTH, 1, @date), -1) AS LASTMONTH,
partmstr.upc_code,
invhead.billto_code,
invhead.shipto_code,
invhead.cost_ctr,
invhead.invc_type,
invitem.item_sales_amt,
partmstr.part_desc
FROM invhead
INNER JOIN invitem
ON invhead.invoice_numb = invitem.invoice_numb
AND invhead.rel_numb = invitem.rel_numb
INNER JOIN partmstr
ON invitem.part_code = partmstr.part_code
INNER JOIN orderfrom
ON invhead.cust_code = orderfrom.cust_code
WHERE (
partmstr.price_cat1 = '765'
AND invhead.inv_prt_date >= DATEADD(DAY, 10, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0))
AND invhead.inv_prt_date < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
)
AND
(
invc_type = 'RC'
OR invc_type = 'RD'
)
GROUP BY invhead.invoice_numb,
invhead.cust_code,
orderfrom.bus_name,
orderfrom.address1,
orderfrom.city,
orderfrom.state,
orderfrom.zip,
invhead.inv_prt_date,
partmstr.part_code,
invitem.qty,
item_price,
partmstr.upc_code,
invhead.inv_prt_date,
invhead.billto_code,
invhead.shipto_code,
invhead.cost_ctr,
invhead.invc_type,
invitem.item_sales_amt,
orderfrom.phone,
partmstr.part_desc
ORDER BY invhead.invoice_numb;
Basically what I am looking for is if the invoice type =RD put get a row for sales$ and sales qty. If the invoice type = RC then have that in the return$ and return qty.
| Sales $ | Sale Qty | Return $ | Return Qty |
| $50.00 | 4 | $0.00 | 0 |
| $0.00 | 0 | $25.00 | 2 |
SQLMicrosoft SQL Server
Last Comment
gpsdh
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you both for your help! The simplicity of the first answer was what did the trick.
SELECT CA1.*
FROM invhead
INNER JOIN invitem
ON invhead.invoice_numb = invitem.invoice_numb
AND invhead.rel_numb = invitem.rel_numb
INNER JOIN partmstr
ON invitem.part_code = partmstr.part_code
INNER JOIN orderfrom
ON invhead.cust_code = orderfrom.cust_code
CROSS APPLY (
SELECT DISTINCT
CAST(SUM(invitem.qty * invitem.item_ntprice) AS DECIMAL(18, 2)) AS 'SALES', --WHEN invhead.invc_type = 'RD' ELSE 0
qty AS 'SALE_QTY', --WHEN invhead.invc_type = 'RD' ELSE 0
0 AS 'RETURN',
0 AS 'RETURN_QTY',
partmstr.part_code,
item_price,
invhead.invoice_numb,
orderfrom.bus_name,
orderfrom.address1,
orderfrom.city,
orderfrom.state,
orderfrom.zip,
orderfrom.phone,
partmstr.upc_code,
invhead.inv_prt_date,
DATEADD(MONTH, +DATEDIFF(MONTH, 1, @date), -1) AS LASTMONTH,
partmstr.upc_code,
invhead.billto_code,
invhead.shipto_code,
invhead.cost_ctr,
invhead.invc_type,
invitem.item_sales_amt,
partmstr.part_desc
WHERE invhead.inc_type = 'RD'
UNION ALL
SELECT DISTINCT
0 AS 'SALES',
0 AS 'SALE_QTY',
CAST(SUM(invitem.qty * invitem.item_ntprice) AS DECIMAL(18, 2)) AS 'RETURN',
qty AS 'RETURN_QTY',
partmstr.part_code,
item_price,
invhead.invoice_numb,
orderfrom.bus_name,
orderfrom.address1,
orderfrom.city,
orderfrom.state,
orderfrom.zip,
orderfrom.phone,
partmstr.upc_code,
invhead.inv_prt_date,
DATEADD(MONTH, +DATEDIFF(MONTH, 1, @date), -1) AS LASTMONTH,
partmstr.upc_code,
invhead.billto_code,
invhead.shipto_code,
invhead.cost_ctr,
invhead.invc_type,
invitem.item_sales_amt,
partmstr.part_desc
WHERE invhead.inc_type = 'RC'
) AS CA1
WHERE (
partmstr.price_cat1 = '765'
AND invhead.inv_prt_date >= DATEADD(DAY, 10, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0))
AND invhead.inv_prt_date < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
)
AND
(
invc_type = 'RC'
OR invc_type = 'RD'
)
GROUP BY invhead.invoice_numb,
invhead.cust_code,
orderfrom.bus_name,
orderfrom.address1,
orderfrom.city,
orderfrom.state,
orderfrom.zip,
invhead.inv_prt_date,
partmstr.part_code,
invitem.qty,
item_price,
partmstr.upc_code,
invhead.inv_prt_date,
invhead.billto_code,
invhead.shipto_code,
invhead.cost_ctr,
invhead.invc_type,
invitem.item_sales_amt,
orderfrom.phone,
partmstr.part_desc
ORDER BY invhead.invoice_numb;