Click here to Skip to main content
15,920,596 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
select 
min(h.[Spcl Order]),
(select  (case when sum(pro.[Quantity]) is null then 0 else sum(pro.[Quantity]) END))
+
(select (case when sum(A.[Quantity]) is null then 0 else sum(A.[Quantity]) END),
 min(b.[Posting Date]),min(c.[Order No]),min(d.[Prod_Order No]),min(e.[No]),
 min(f.[Item Category Code]),min(g.[Location Code]),min(i.[Spcl Order_Purch])
 from [tablename2] A 
inner join [sample].[dbo].[Posting Date_PRL] as b on a.[Posting Date]=b.[Posting Date]
inner join [sample].[dbo].[Order No_PRL] as c on a.[Order No_]=c.[Order No]
inner join [sample].[dbo].[Prod_ OrderNo_PRL] as  d on a.[Prod_ Order No_]=d.[Prod_OrderNo] 
inner join [sample].[dbo].[No_PRL] as e on a.[No_]=e.[No]
inner join [sample].[dbo].[Item Category Code_PRL] as f on a.[Item Category Code]=f.[Item Category Code]
inner join [sample].[dbo].[Location Code_PRL] as g  on a.[Location Code]=g.[Location Code]
inner join [sample].[dbo].[Spcl Order_PRL] as i on a.[Spcl Order]=i.[Spcl Order_Purch]
where ((A.[Item Category Code] = 'STYLES') and (A.[Prod_ Order No_]='') and ( A.[Buy-from Vendor No_] in ('S02052')) AND (A.[Quantity]>0) 
AND ( A.[Posting Date] = pro.[Finished Date])))
 
from [tablename1] pro 
inner join [sample].[dbo].[Spcl Order_PO] as h on  Pro.[Spcl Order]=h.[Spcl Order]
where ((pro.[Status]=4) and (pro.[Finished Date] ='2013-10-10' ))  group by pro.[Finished Date]
Posted
Updated 27-Feb-14 0:21am
v2

1 solution

I think this is what you are after:
SQL
SELECT * FROM
(
	select 
	pro.[Finished Date] finished_date,
	min(h.[Spcl Order]) Spcl_Order,
	case when sum(pro.[Quantity]) is null then 0 else sum(pro.[Quantity]) END pro_qty
	from [tablename1] pro 
	inner join [sample].[dbo].[Spcl Order_PO] as h on  Pro.[Spcl Order]=h.[Spcl Order]
	where ((pro.[Status]=4) and (pro.[Finished Date] ='2013-10-10' ))  
	group by pro.[Finished Date]
) as pro1
INNER JOIN
(
	select 
	 A.[Posting Date] posting_date
	 case when sum(A.[Quantity]) is null then 0 else sum(A.[Quantity]) END other_qty,
	 min(b.[Posting Date]) min_posting_date,
	 min(c.[Order No]) min_order,
	 min(d.[Prod_Order No]) min_prod_order,
	 min(e.[No]) min_no,
	 min(f.[Item Category Code]) min_cat_code,
	 min(g.[Location Code]) min_loc_code,
	 min(i.[Spcl Order_Purch]) min_order_purch
	from [tablename2] A 
	inner join [sample].[dbo].[Posting Date_PRL] as b on a.[Posting Date]=b.[Posting Date]
	inner join [sample].[dbo].[Order No_PRL] as c on a.[Order No_]=c.[Order No]
	inner join [sample].[dbo].[Prod_ OrderNo_PRL] as  d on a.[Prod_ Order No_]=d.[Prod_OrderNo] 
	inner join [sample].[dbo].[No_PRL] as e on a.[No_]=e.[No]
	inner join [sample].[dbo].[Item Category Code_PRL] as f on a.[Item Category Code]=f.[Item Category Code]
	inner join [sample].[dbo].[Location Code_PRL] as g  on a.[Location Code]=g.[Location Code]
	inner join [sample].[dbo].[Spcl Order_PRL] as i on a.[Spcl Order]=i.[Spcl Order_Purch]
	where (A.[Item Category Code] = 'STYLES') and (A.[Prod_ Order No_]='') and ( A.[Buy-from Vendor No_] in ('S02052')) AND (A.[Quantity]>0) 
	GROUP BY A.[Posting Date])
)  as post
ON pro1.finished_date = post.posting_date

BTW, you still could use your query if you leave only select (case when sum(A.[Quantity]) is null then 0 else sum(A.[Quantity]) END) in your sub-query, and remove all other aggregates (min(b.[Posting Date]),min(c.[Order No]),min(d.[Prod_Order No]),min(e.[No]),
min(f.[Item Category Code]),min(g.[Location Code]),min(i.[Spcl Order_Purch]))
, i.e., this query will also work:
SQL
select 
min(h.[Spcl Order]),
case when sum(pro.[Quantity]) is null then 0 else sum(pro.[Quantity]) END
+
(select (case when sum(A.[Quantity]) is null then 0 else sum(A.[Quantity]) END)
 from [tablename2] A 
inner join [sample].[dbo].[Posting Date_PRL] as b on a.[Posting Date]=b.[Posting Date]
inner join [sample].[dbo].[Order No_PRL] as c on a.[Order No_]=c.[Order No]
inner join [sample].[dbo].[Prod_ OrderNo_PRL] as  d on a.[Prod_ Order No_]=d.[Prod_OrderNo] 
inner join [sample].[dbo].[No_PRL] as e on a.[No_]=e.[No]
inner join [sample].[dbo].[Item Category Code_PRL] as f on a.[Item Category Code]=f.[Item Category Code]
inner join [sample].[dbo].[Location Code_PRL] as g  on a.[Location Code]=g.[Location Code]
inner join [sample].[dbo].[Spcl Order_PRL] as i on a.[Spcl Order]=i.[Spcl Order_Purch]
where ((A.[Item Category Code] = 'STYLES') and (A.[Prod_ Order No_]='') and ( A.[Buy-from Vendor No_] in ('S02052')) AND (A.[Quantity]>0) 
AND ( A.[Posting Date] = pro.[Finished Date])))
 
from [tablename1] pro 
inner join [sample].[dbo].[Spcl Order_PO] as h on  Pro.[Spcl Order]=h.[Spcl Order]
where ((pro.[Status]=4) and (pro.[Finished Date] ='2013-10-10' ))  group by pro.[Finished Date]
 
Share this answer
 
Comments
Maciej Los 27-Feb-14 16:45pm    
Sounds reasonable! +5!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900