SQL View - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL View

Author

Topic

btamulis
Yak Posting Veteran

64 Posts

Posted - 2007-02-11 : 19:45:42

I have tried Books online and have queried this forum for assistance in creating a SQL view and have spent much time trying to solve on my own.

I am working with only one table:

Sales: SOP10200
SOPTYPE ITEMNMBR ITEMDESC QUANTITY
3 Widget special 4
3 Widget special 7
2 Gadget special 1
2 Widget special 12
3 Gadget special 5

I want to create a view that summarizes the data based upon SOPTYPE and ITEMNMBR as follows:

ITEM_NUMBER ITEM_DESC QTY_INVOICED QTY_ORDERED
Widget special 11 12
Gadget special 5 1

I am struggling whether to use a select statement or a CASE statement.

I am able to summarize one SOPTYPE as follows:
CREATE VIEW [dbo].[Tec_SOP10200_INVOICE_Qty]
AS
SELECT ITEMNMBR as ITEM_NUMBER, ITEMDESC as ITEM_DESC, SUM(QUANTITY) AS QTY_INVOICED
FROM SOP10200
WHERE SOPTYPE = 3
GROUP BY ITEMNMBR, ITEMDESC

But I need more than one soptype in same view

I tried this CASE statement -
Create tec_sales_view
as
SELECT SOP10200.ITEMNMBR as ITEM_NUMBER
, SOP10200.ITEMDESC as ITEM_DESC
, SOP10200.QTY1 'QTY_INVOICED'
, SOP10200.QTY2 'QTY_ORDERED'
, (CASE
WHEN SOP10200.SOPTYPE = 3 THEN SUM(QUANTITY) QTY1 ELSE 0)
(CASE
WHEN SOP10200.SOPTYPE = 2 THEN SUM(QUANTITY) QTY2 ELSE 0)
END as QTY_Type
FROM SOP10200
GROUP BY ITEMNMBR, ITEMDESC, QTY1, QTY2

But I get error message doesn't recognize QTY1 (invalid column name)
Thank you in advance for any assistance.......

btamulis
Yak Posting Veteran

64 Posts

Posted - 2007-02-11 : 20:23:56

Incredibly, I was able to solve this by searching other postings in this forum.

I ended up with something very simple -

(slightly modified to include more soptypes and a totalqty column)

ALTER VIEW tec_sales_sop10200_view
as
select ITEMNMBR, ITEMDESC,
sum(case when SOPTYPE = '2' then QUANTITY else 0 end) as ORDER_QTY,
sum(case when SOPTYPE = '3' then QUANTITY else 0 end) as INVOICE_QTY,
sum(case when SOPTYPE = '2' then QUANTITY else 0 end) as RETURN_QTY,
sum(case when SOPTYPE = '5' then QUANTITY else 0 end) as BACKORDER_QTY,
sum(QUANTITY) as TOTALQTY
from SOP10200
group by ITEMNMBR, ITEMDESC

This works great for my purpose.

Thanks to all who read......I would still be interested if there are alternative scripts - maybe temp table, etc...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-11 : 20:34:27

I would still be interested if there are alternative scripts - maybe temp table, etc...
If you need a view of it, you can't use temp table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources