Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SELECT TOP 100 PERCENT Expand / Collapse
Author
Message
Posted Friday, May 29, 2009 8:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 02, 2009 2:36 AM
Points: 1, Visits: 7
hi all expert..,


i have one problem...

I have tried several times but can not
how to use union this my Query:

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SELECT TOP 100 PERCENT ...

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SELECT TOP 100 PERCENT ItemCode,ItemName, OnHand,

(SELECT SUM(T1.BaseQty * T2.NumInSale) AS Qty_Sales
FROM dbo.INV1 T1 LEFT OUTER JOIN dbo.OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE (MONTH(ActDelDate) = MONTH(GETDATE())) AND (T1.ItemCode = T0.ItemCode)) AS Qty_Sales,

(SELECT SUM(T1.BaseQty * T2.NumInSale) AS Qty_Sales
FROM dbo.INV1 T1 LEFT OUTER JOIN dbo.OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE (MONTH(ActDelDate) = MONTH(GETDATE()) - 1) AND (T1.ItemCode = T0.ItemCode)) AS Qty_Sales_Month_1,

DAY(GETDATE()) AS Days,
CASE WHEN Month(GetDate()) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN Month(GetDate()) IN (2, 4, 6, 9, 11) THEN 30 ELSE 28 END AS 'Total days',

(OnHand / (SELECT SUM(T1.BaseQty * T2.NumInSale) AS Qty_Sales
FROM dbo.INV1 T1 LEFT OUTER JOIN dbo.OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE (MONTH(ActDelDate) = MONTH(GETDATE())) AND (T1.ItemCode = T0.ItemCode)) / CASE WHEN Month(GetDate()) IN (1, 3, 5, 7, 8, 10, 12) THEN 31 WHEN Month(GetDate()) IN (2, 4, 6, 9, 11) THEN 30 ELSE 28 END) *DAY(GetDate()) AS TURNOVER_IN_MONTH,

(OnHand / (SELECT SUM(T1.BaseQty * T2.NumInSale) AS Qty_Sales FROM dbo.INV1 T1 LEFT OUTER JOIN dbo.OITM T2 ON T1.ItemCode = T2.ItemCode WHERE (MONTH(ActDelDate) = MONTH(GETDATE())) AND (T1.ItemCode = T0.ItemCode)) / CASE WHEN Month(GetDate()) IN (1, 3, 5, 7, 8, 10, 12) THEN 31 WHEN Month(GetDate()) IN (2, 4, 6, 9, 11) THEN 30 ELSE 28 END)*DAY(GetDate()) * 30 AS TURNOVER_DAYS
FROM dbo.OITM T0

WHERE (SELECT SUM(T1.BaseQty * T2.NumInSale) AS Qty_Sales FROM dbo.INV1 T1 LEFT OUTER JOIN dbo.OITM T2 ON T1.ItemCode = T2.ItemCode WHERE (MONTH(ActDelDate) = MONTH(GETDATE())) AND (T1.ItemCode = T0.ItemCode)) > 0 OR T0.ONHAND > 0 ORDER BY 1



i want to union for Turn over & sales
eq:
1. You can put "No Sale" on the Turn Over Coloum if a "zero sales" but has in the hands of hareholders. AND
2. You can put "Sales" in the Turn Over Coloum if a "sale" has a stock that has shares in the hands.


I may be the expert can solve the problem or the solution munkin there where I will ask for this my problem ...
thanks a lot ...
Post #726154
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse