how to create Query for union

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Francis Rodrigues » how to create Query for union

how to create Query for union

Rate Topic

Display Mode

Topic Options

Author

Message

oracle_net

Posted Thursday, May 28, 2009 9:53 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, May 31, 2009 7:11 PM
Points: 3, Visits: 10

hi.. expert..,

I have a problem
combine with the ... I ask anyone to help me solve the problem for me

I have a query like this,

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[font="Courier New"][/font]

I want to display as in the following Qty in combine sales and Turnover
Item Code / Stock / Qty Sales (accumulate this month) / Qty Sales (accumulate month-1) / Days (how many days till this month) / Total days in this month / Turn Over month (sales or no sales) / Turn Over Days

eg . mei 28

542514 || 200 || 570 || 370 || 28 || 31 || 0,32 || 9,51
542517 || 1 || 0 || 0 || 28 || 31 || "no sales" || 0

Turn Over month = stock / qty sales month
Turn Over Days = 30 * Turn Over Month
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.

thanks... for you all

Post #725589

arun.sas

Posted Thursday, May 28, 2009 10:21 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231, Visits: 3,483

Hi,

First of all you here after post the requirement in the Forums --> 2005 or 2000 ---> development or in the TSQL as required

OK, Try this

select ItemCode,
ItemName,
OnHand,
Qty_Sales,
Qty_Sales_Month_1,
Days,
[Total days],
TURNOVER_IN_MONTH,
TURNOVER_DAYS from (
SELECT TOP 100 PERCENT ItemCode,
ItemName,
OnHand,
/*YOUR STATEMENT*/
OR T0.ONHAND > 0 ORDER BY 1
) AS X

from this you may draft your requirement like

select ItemCode [Item Code],
OnHand [Stock],
Qty_Sales [Qty Sales],
Qty_Sales_Month_1 [Qty Sales_Last],
([Total days]- Days) Days,
[Total days],
(case when TURNOVER_IN_MONTH > 0 then 'Sales' else 'No Sales'end0 [TURNOVER_IN_MONTH ],
TURNOVER_DAYS
from (
SELECT TOP 100 PERCENT ItemCode,
ItemName,
OnHand,
/*YOUR STATEMENT*/
OR T0.ONHAND > 0 ORDER BY 1
) AS X

ARUN SAS

Post #725594

oracle_net

Posted Friday, May 29, 2009 12:37 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, May 31, 2009 7:11 PM
Points: 3, Visits: 10

Hi Old Hand expert,

I mean I like that.

sorry.. if you can please give me directly in the Query / change directly in my Query.

because I still do not understand that you mean

thank you for helping me
Smile

Post #725614

arun.sas

Posted Friday, May 29, 2009 12:44 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231, Visits: 3,483

Hi,

first try this ,

select ItemCode [Item Code],
OnHand [Stock],
Qty_Sales [Qty Sales],
Qty_Sales_Month_1 [Qty Sales_Last],
([Total days]- Days) Days,
[Total days],
(case when TURNOVER_IN_MONTH > 0 then 'Sales' else 'No Sales'end) [TURNOVER_IN_MONTH ],
TURNOVER_DAYS
from (
SELECT TOP 100 PERCENT ItemCode,
ItemName,
OnHand,
/*YOUR STATEMENT*/
OR T0.ONHAND > 0 ORDER BY 1
) AS X

ARUN SAS

Post #725617

oracle_net

Posted Friday, May 29, 2009 1:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, May 31, 2009 7:11 PM
Points: 3, Visits: 10

Hai expert

such as whether you mean to me change ..?

I have been trying to change and try it out is not working ..

maybe you can change the Query me with copy and paste and edit

select
ItemCode [Item Code],
OnHand [Stock],
Qty_Sales [Qty Sales],
Qty_Sales_Month_1 [Qty Sales_Last],
([Total days]- Days) Days,
[Total days],

(case when TURNOVER_IN_MONTH > 0 then 'Sales' else 'No Sales'end) [TURNOVER_IN_MONTH ],
TURNOVER_DAYS
from (
SELECT TOP 100 PERCENT ItemCode,
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
) AS X

thanks
Smile

Post #725640

« Prev Topic | Next Topic »

Permissions