SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to create Query for union


how to create Query for union

Author
Message
oracle_net
oracle_net
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
arun.sas
arun.sas
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3021 Visits: 3493
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
oracle_net
oracle_net
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
:-)
arun.sas
arun.sas
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3021 Visits: 3493
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
oracle_net
oracle_net
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search