How to add below complex conditions in SQL script..

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » How to add below complex conditions in SQL...

11 posts, Page 1 of 2

1 2 »»

How to add below complex conditions in SQL script..

Rate Topic

Display Mode

Topic Options

Author

Message

bhushan_juare

Posted Wednesday, March 13, 2013 11:29 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:14 AM
Points: 62, Visits: 358

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 DISTINCT year(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..?

Post #1430762

Bhuvnesh

Posted Wednesday, March 13, 2013 11:44 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

Either use CASE statement with LEFT JOIN
or
EXISTS clause

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1430765

Arjun Sivadasan

Posted Thursday, March 14, 2013 4:28 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 09, 2013 3:29 AM
Points: 365, Visits: 697

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.

Post #1430864

ChrisM@Work

Posted Thursday, March 14, 2013 6:10 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1430906

ChrisM@Work

Posted Thursday, March 14, 2013 6:15 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #1430907

bhushan_juare

Posted Thursday, March 14, 2013 6:56 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:14 AM
Points: 62, Visits: 358

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..

Post #1430933

ChrisM@Work

Posted Thursday, March 14, 2013 7:02 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

bhushan_juare (3/14/2013)

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #1430937

bhushan_juare

Posted Friday, March 15, 2013 11:19 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:14 AM
Points: 62, Visits: 358

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

Post Attachments

Sample Data & Output.xlsx (2 views, 11.92 KB)

SQL Query.txt (2 views, 1.80 KB)

Post #1431862

ChrisM@Work

Posted Monday, March 18, 2013 2:31 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

bhushan_juare (3/15/2013)

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?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #1432103

bhushan_juare

Posted Tuesday, March 19, 2013 6:10 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:14 AM
Points: 62, Visits: 358

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..?

Post #1432592

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions