help with a query - 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

help with a query

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-09-29 : 13:52:21

THIS IS THE SAMPLE DATE
I want to see supplierID, sum of amount shipped and sum of amount not shipped
and group them by suppier, total no of orders accpeted by the vendor, total no of orders declined

CREATE TABLE #OrdersShipped
(OrderID int, [Shipped Date] datetime)
INSERT #OrdersShipped
SELECT 1, '1/2/05' union all
SELECT 2, '2/3/05'

CREATE TABLE #OrderStatus
(supplierIDint, OrderID int, status varchar(10), amount decimal(9,2))
INSERT #OrderStatus
SELECT 111, 1 , 'accepted', 1000.00 UNION ALL
SELECT 111, 2, 'accepted', 2500.00 UNION ALL
SELECT 222, 3, 'pending', 2000 UNION ALL
SELECT 222, 4, 'accepted', 3000.00 UNION ALL
Select 333, 5, 'declined', 200 UNION ALL
Select 333, 6, 'accepted', 500

Ashley Rhodes

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-29 : 13:57:49

Is this an RFW?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-29 : 14:08:10

quote:
Is this an RFW?

Yeah, I was wondering that too!

Ashley, here's the query you need - in future though, you need to start posting what you have tried and we'll help you get it right, but if you just continually ask us to do your work for you - you'll start finding no-one will reply.

SELECT #OrderStatus.supplierID
	, sum(CASE WHEN [Shipped Date] IS NOT NULL
	THEN amount
	ELSE 0
	END) AS amtshipped
	, sum(CASE WHEN [Shipped Date] IS NULL
	THEN amount
	ELSE 0
	END) AS amtnotshipped
	, sum(CASE WHEN status ='accepted'
	THEN 1
	ELSE 0
	END) AS accepted
	, sum(CASE WHEN status ='declined'
	THEN 1
	ELSE 0
	END) AS declined
FROM #OrderStatus
LEFT OUTER JOIN #OrdersShipped ON #OrderStatus.OrderID = #OrdersShipped.OrderID
GROUP BY supplierID

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-29 : 15:15:57

It is also a duplicate post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72743

CODO ERGO SUM

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-10-02 : 16:02:26

Its not that I did not tried

THIS IS WHAT I HAD. I had a problem with grouping

select
supplierID,
sum(case when o.status = 'accepted' then 1 else 0 end) as [ORDERS ACCEPTED],
sum(case when o.status = 'declined' then 1 else 0 end) as [ORDERS DECLINED],
sum(case when o.status = 'accepted' and o.orderid in (select os1.orderid from #ordersshipped os1 where os1.orderid = o.orderid)
then 1 else 0 end ) as [ORDERS SHIPPED] ,

sum(case when o.status = 'accepted' and o.orderid not in (select os1.orderid from #ordersshipped os1 where os1.orderid = o.orderid)
then 1 else 0 end ) as [ORDERS PENDING SHIPPING] ,

sum(case when o.status = 'accepted' and o.orderid in (select os1.orderid from #ordersshipped os1 where os1.orderid = o.orderid)
then o.amount else 0 end ) as [AMOUNT OF ORDERS SHIPPED] ,

sum(case when o.status = 'accepted' and o.orderid not in (select os1.orderid from #ordersshipped os1 where os1.orderid = o.orderid)
then o.amount else 0 end ) as [AMOUNT OF PENDING SHIPPING] ,

sum(case when o.status = 'declined' then o.amount else 0 end ) as [AMOUNT OF ORDERS DECLINED]

into #t1

From #ORDERS O

GROUP BY o.supplierid, O.status, O.OrderID, o.amount
GO

select * from #t1 group by supplierID, [ORDERS ACCEPTED], [ORDERS DECLINED],[ORDERS SHIPPED],
[ORDERS PENDING SHIPPING], [AMOUNT OF ORDERS SHIPPED], [AMOUNT OF PENDING SHIPPING], [AMOUNT OF ORDERS DECLINED]

go

drop table #OrdersShipped , #orders , #t1

Ashley Rhodes

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-10-02 : 16:02:53

WHAT IS RFW??????????????

Ashley Rhodes

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-02 : 22:38:19

Request for work.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-03 : 09:22:02

RTDH - Request to do homework

CODO ERGO SUM