problem solved, thanks - 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

problem solved, thanks

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-09-04 : 14:16:31

I'm pretty sure there's a way to write this query without using temp tables. I've been trying to use sub selects, specifically this type:

S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 14:20:30

Can you explain what you're trying to do in words please? also give some sample data and output to illustrate it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 15:08:07

Can you try like this

select		s.storeName,
		org.organizationName,
		o.orderID,
		o.orderStateID,
		o.deliveryDate,
		orderState,
		sum (case when o.orderStateID = '3' then (OrderItemType.dollarValue*OrderItem.quantity) else 0 end) as total,
		s.peakLimit,
		sum ((OrderItemType.dollarValue*OrderItem.quantity)) AS overPeakTotal

from		Store s
		join [Order] o
		   on s.storeID = o.storeID 
		join Organization org
		   on s.organizationID = Org.organizationID
		left join OrderItem         
		   on o.orderID = OrderItem.orderID 
	        left join OrderItemType 
		   on OrderItem.orderItemTypeID = OrderItemType.orderItemTypeID 
		join OrderState 
		   on o.orderStateID = OrderState.orderStateID 
		left join #temp t
		   on s.storeID = t.storeID

where		o.orderStateID IN ('1', '3')
		and o.deliveryDate > GetDate()+2

group by	s.storeName,
			org.organizationName
			o.orderID,
			o.orderStateID,
			orderState,
			o.deliveryDate,
			s.peakLimit