March 13, 2013 at 11:29 pm
[font="Courier New"]Hi All,
Below is my written query
DECLARE @FromDate DATETIME
DECLARE @EndDate DATETIME
SET @FromDate = '2013-01-01 00:00:00.000'
SET @EndDate = '2013-02-13 00:00:00.000'
SELECT DISTINCTyear(sd.FKDAT) As YEARWISE_DATA,
so.vkbur As MARKET,
so.bezei As NAME,
sd.kunrg As PARTY,
cm.NAME1 As PARTY_NAME,
--za.FGCODE As ITEM,
--za.FGDESC As ITEM_DESCRIPTION,
--za.EANNUM As USACODE,
im.MATNR As ITEM,
im.MAKTX As ITEM_DESCRIPTION,
im.EAN11 As USACODE,
SUM(sd.FKIMG) As QUANTITY,
SUM(vb.NETWR) As VALUE_IN_FC,
SUM(sd.NTGEW) As WEIGHT
--(SUM(vb.KWMENG) - SUM(sd.FKIMG)) As PENDING_QUANTITY,
--(SUM(vb.NETWR) - SUM(sd.NETWR)) As PENDING_VALUE,
--(SUM(vb.NTGEW) - SUM(sd.NTGEW)) As PENDING_WEIGHT
FROM
sales_office so
LEFT JOIN
SALES_DATA sd
ON
so.VKBUR = sd.VKBUR
INNER JOIN
Item_Mas im
ON
sd.MATNR = im.MATNR
INNER JOIN
Cust_Mas cm
ON
sd.KUNRG = cm.KUNNR
INNER JOIN
VBAP vb
ON
sd.AUBEL = vb.VBELN
AND
sd.AUPOS = vb.POSNR
and
sd.MATNR = vb.MATNR
WHERE
sd.FKDAT >= @FromDate
AND
sd.FKDAT <= @EndDate
AND
im.EAN11 != ' '
GROUP BY
YEAR(sd.FKDAT),
so.vkbur,
so.bezei,
sd.kunrg,
cm.NAME1,
--za.FGCODE As ITEM,
--za.FGDESC As ITEM_DESCRIPTION,
--za.EANNUM As USACODE,
im.MATNR,
im.MAKTX,
im.EAN11
ORDER BY
1,
so.VKBUR,
sd.kunrg,
im.EAN11 ASC
For PENDING_QUANTITY, PENDING_VALUE and PENDING_WEIGHT calculations I have to place two conditions
I will check in sales_data table with condition (sd.FKDAT >= @FromDate AND sd.FKDAT <= @EndDate AND sd.KUNRG = 'HA127', AND sd.MATNR = 'FG151765162081222')
Condition-1 IF I found no entries in result set i.e. 0 Rows Affected Then I want this result set
QUANTITY = 0, VALUE_IN_FC = 0, WEIGHT = 0,
PENDING_QUANTITY = SUM(vb.KWMENG) PENDING_VALUE = SUM(vb.NETWR) and PENDING_WEIGHT = SUM(vb.NTGEW) From VBAP table with one more filter only for Pending Attributes calculation i.e. (vb.AUDAT >= @FromDate AND vb.AUDAT <= @EndDate AND vb.KUNNR = 'HA127', AND vb.MATNR = 'FG151765162081222') alongwith other select query output
Condition-2 IF I found entries in sales_data table based on parameters provided above Then
QUANTITY = SUM(sd.FKIMG), VALUE_IN_FC = SUM(vb.NETWR), WEIGHT = SUM(sd.NTGEW)
PENDING_QUANTITY = 0, PENDING_VALUE = 0, PENDING_WEIGHT = 0
My concern is how to add these conditions in above Select query and in Inner Join of Sales_data & VBAP statements..?[/font]
March 13, 2013 at 11:44 pm
Either use CASE statement with LEFT JOIN
or
EXISTS clause
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 14, 2013 at 4:28 am
Please format your query. Do you feel comfortable looking at it?
Split the query into two parts. Use a temp table. Branch out your query using IF ELSE. Don't worry if you have to repeat the query (in the IF and ELSE part, I mean). IF ELSE usually gives good performance.
https://sqlroadie.com/
March 14, 2013 at 6:10 am
Something like this?
SELECT --DISTINCT -- not needed, covered by GROUP BY
year(sd.FKDAT) As YEARWISE_DATA,
so.vkbur As MARKET,
so.bezei As NAME,
sd.kunrg As PARTY,
cm.NAME1 As PARTY_NAME,
im.MATNR As ITEM,
im.MAKTX As ITEM_DESCRIPTION,
im.EAN11 As USACODE,
QUANTITY= CASE WHEN x.Condition = 1 THEN 0 ELSE SUM(sd.FKIMG) END,
VALUE_IN_FC= CASE WHEN x.Condition = 1 THEN 0 ELSE SUM(vb.NETWR) END, -- should this be vb. or sd.?
[WEIGHT]= CASE WHEN x.Condition = 1 THEN 0 ELSE SUM(sd.NTGEW) END,
PENDING_QUANTITY= CASE WHEN x.Condition = 1 THEN SUM(vb.KWMENG) ELSE 0 END,
PENDING_VALUE= CASE WHEN x.Condition = 1 THEN SUM(vb.NETWR) ELSE 0 END,
PENDING_WEIGHT= CASE WHEN x.Condition = 1 THEN SUM(vb.NTGEW) ELSE 0 END
FROM sales_office so
LEFT JOIN SALES_DATA sd
ON so.VKBUR = sd.VKBUR
INNER JOIN Item_Mas im
ON sd.MATNR = im.MATNR
INNER JOIN Cust_Mas cm
ON sd.KUNRG = cm.KUNNR
INNER JOIN VBAP vb
ON sd.AUBEL = vb.VBELN
AND sd.AUPOS = vb.POSNR
AND sd.MATNR = vb.MATNR
CROSS APPLY (
SELECT Condition = CASE
WHEN
--sd.FKDAT >= @FromDate -- not needed, already in WHERE clause
--AND sd.FKDAT <= @EndDate -- not needed, already in WHERE clause
sd.KUNRG = 'HA127' AND sd.MATNR = 'FG151765162081222'
THEN 2 ELSE 1 END
) x
WHERE sd.FKDAT >= @FromDate
AND sd.FKDAT <= @EndDate
AND im.EAN11 != ' '
GROUP BY
YEAR(sd.FKDAT),
so.vkbur,
so.bezei,
sd.kunrg,
cm.NAME1,
im.MATNR,
im.MAKTX,
im.EAN11
ORDER BY
1,
so.VKBUR,
sd.kunrg,
im.EAN11 ASC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 14, 2013 at 6:15 am
Incidentally, your WHERE clause turns this LEFT JOIN SALES_DATA sd
ON so.VKBUR = sd.VKBUR into an INNER JOIN.
The same happens with table VBAP inner joined to SALES_DATA. If you really want to left-join SALES_DATA to sales_office, then inner-join VBAP to SALES_DATA, you're going to have to change your query.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 14, 2013 at 6:56 am
[font="Courier New"]Hi Chris,
Thanks for reply One thing i wanna ask you Do I need to add same logic for condition 2..?
Because what I am getting is one side results set i.e all quantity = 0, all value_in_fc = 0, all weight = 0 alongwith values for Pending attributes.. [/font]
March 14, 2013 at 7:02 am
bhushan_juare (3/14/2013)
[font="Courier New"]Hi Chris,Thanks for reply One thing i wanna ask you Do I need to add same logic for condition 2..?
Because what I am getting is one side results set i.e all quantity = 0, all value_in_fc = 0, all weight = 0 alongwith values for Pending attributes.. [/font]
Check your data. You should always have a good idea of what results a query should return. I can't tell you the answer as I can't see your data. The query I posted should cover both conditions.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 15, 2013 at 11:19 pm
[font="Courier New"]Hi Chris,
I have done some modifications in my script.. and I am getting correct result set but their are some (im.MATNR As ITEM) entries who are not present in sales_data but are present in vbap table based on this condition
((vb.AUDAT >= @FromDate AND vb.AUDAT <= @EndDate) OR (sd.AUBEL = vb.VBELN AND sd.AUPOS = vb.POSNR))
AND
sd.MATNR = vb.MATNR
AND
sd.KUNRG = vb.KUNNRand Then I will get Quantity = 0, Value_in_FC = 0, Weight = 0 and Pending attributes will be whatever present based on above condition..I want to add this last portion in the query so to get Actual Outputs. PFA Sample data Output and SQL Script..
Thanks & Regards,
Bhushan
[/font]
March 18, 2013 at 2:31 am
bhushan_juare (3/15/2013)
[font="Courier New"]Hi Chris,I have done some modifications in my script.. and I am getting correct result set but their are some (im.MATNR As ITEM) entries who are not present in sales_data but are present in vbap table based on this condition
((vb.AUDAT >= @FromDate AND vb.AUDAT <= @EndDate) OR (sd.AUBEL = vb.VBELN AND sd.AUPOS = vb.POSNR))
AND
sd.MATNR = vb.MATNR
AND
sd.KUNRG = vb.KUNNRand Then I will get Quantity = 0, Value_in_FC = 0, Weight = 0 and Pending attributes will be whatever present based on above condition..I want to add this last portion in the query so to get Actual Outputs. PFA Sample data Output and SQL Script..
Thanks & Regards,
Bhushan
[/font]
This doesn't look quite correct. Firstly, the join conditions here are very different to the ones in your original query. Secondly, you state "entries who are not present in sales_data but are present in vbap table based on this condition". Are you sure you understand the relationships between the two tables?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 19, 2013 at 6:10 am
[font="Courier New"]Hi Chris,
Yes, my concern is absolutely right because sales_data table gave me all order quantities details(i.e MATNR) whose Purchase Order is generated and ready to dispatched else if not found in sales_data(i.e MATNR) will get from VBAP table called as Pending Order and this is the heirarchy which is set in database tables hence I have to check in both tables (sales_data, VBAP) to get all six calculated attributes..PFA excel sheet you will come to know what I am talking about..?[/font]
March 19, 2013 at 6:27 am
bhushan_juare (3/19/2013)
[font="Courier New"]Hi Chris,Yes, my concern is absolutely right because sales_data table gave me all order quantities details(i.e MATNR) whose Purchase Order is generated and ready to dispatched else if not found in sales_data(i.e MATNR) will get from VBAP table called as Pending Order and this is the heirarchy which is set in database tables hence I have to check in both tables (sales_data, VBAP) to get all six calculated attributes..PFA excel sheet you will come to know what I am talking about..?[/font]
Which data is generated first, sales_data or VBAP?
Put in a different way; will one of the two tables contain orders which are not (yet) in the other?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply