May 28, 2009 at 9:53 pm
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[/b]
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
May 28, 2009 at 10:21 pm
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
May 29, 2009 at 12:37 am
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
May 29, 2009 at 12:44 am
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
May 29, 2009 at 1:43 am
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
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy