Showing posts with label based. Show all posts
Showing posts with label based. Show all posts

Thursday, March 29, 2012

help with complex SQL query

I need some help from prog. gurus with sql query.

I have a commodity table and I would like to pull information from it based on the conditions. I can write code to pull straight forward information from the table but I need help in writing query such that the later columns are based on the former columns.

To clear my question, I am writing the sample code below.
The column 4, 5 and 6 are based on column 1, 2, 3.
Column6 is column3/column2 (which is simple)
But column4 & 5 are too complex for me to write the code for. Column4 is the % of Quantity (cty_code)/(all countries) for that particular commodity. cty_code correspends to a country.

Table information:

[dbo].[2005exp](
[dom_or_for] [char](1) NOT NULL,
[commodity] [char](10) NOT NULL,
[cty_code] [char](4) NOT NULL,
[district] [char](2) NOT NULL,
[stat_month] [char](2) NOT NULL,
[cards_mo] [decimal](8, 0) NOT NULL,
[all_qy1_mo] [decimal](12, 0) NOT NULL,
[all_val_mo] [decimal](12, 0) NOT NULL
)


Table data snapshot:


Query1:
select top 10 commodity, sum(all_qy1_mo) as Quantity, sum(all_val_mo) as Price from [2005exp]
where cty_code=5310 group by Commodity order by price desc


Output:

Commodity

Quantity

Price

Column4Column5Price/Quantity

8517305000

0

46307629

8517905000

0

11990255

3003100000

2268

2687905

0.35%29.92%1185.1437

8524990000

148

1815000

8471300000

2591

1673570

9802400000

0

1560247

9880004000

0

1197407

8802300080

1

1100000

3819000000

601192

899417

9802200000

0

811996

Query2: (based on the results of query 1 i.e., commodity)
select Sum(all_qy1_mo) as Quantity, sum(all_val_mo) as Price from [2005exp]
where commodity=3003100000

Output:

Quantity

Price

645261

8982928

2268 / 645261 = 0.35% Column 4

2687905 / 8982928 = 29.92% Column 5

Quantity/Price = Average (2687906/2268 = 1185.1437) i.e. column3/column2=Column6

The final output should like: (There are additional 2 columns in the table below but they are not required)

select a.commodity, sum(a.all_qy1_mo) as Quantity, sum(a.all_val_mo) as Price,

CASE WHEN (select Sum(b.all_qy1_mo)from [2005exp] b

where a.commodity=b.commodity)<>0 THEN (sum(a.all_qy1_mo)/(select Sum(b.all_qy1_mo) from [2005exp] b

where a.commodity=b.commodity))*100.00 ELSE NULL END as col4, CASE WHEN (select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity)<>0 THEN (sum(a.all_val_mo)/(select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity))*100.00 ELSE NULL END as col5,

CASE WHEN sum(a.all_qy1_mo)<> 0 THEN sum(a.all_val_mo)/sum(a.all_qy1_mo) ELSE NULL END as col6

from [2005exp] a

WHERE a.cty_code=5310

GROUP BY a.commodity

|||

Thank you so much. The code works perfectly. I really appreciate all your help.

I wanted to add another column 7 which is column5/column4. How can I save the column5 and Column4 values in 2 different variables and then perform the division ?

I tried to use just the alias names i.e col5/col4, but its not working.

I tried using the actual code for both columns and its working but its taking 4 minutes to execute the query. Its repeating the same steps twice. How is it done right way ?

Also, I was trying to delcare a variable for the table name but without any success. Who is it done ?

Use test
Go

Declare @.Country Int
Set @.Country=5310

select top 10 a.commodity as Commodity, c.descrip_1 as Description, c.quantity_1 as Unit,

sum(a.all_qy1_mo) as Quantity, sum(a.all_val_mo) as [Value],

CASE WHEN (select Sum(b.all_qy1_mo)from [2005exp] b where a.commodity=b.commodity)<>0 THEN (sum(a.all_qy1_mo)/(select Sum(b.all_qy1_mo) from [2005exp] b where a.commodity=b.commodity))*100.00 ELSE NULL END as [U.S. Share(Quantity) %],

CASE WHEN (select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity)<>0 THEN (sum(a.all_val_mo)/(select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity))*100.00 ELSE NULL END as [U.S. Share(Value) %],

CASE WHEN sum(a.all_qy1_mo)<> 0 THEN sum(a.all_val_mo)/sum(a.all_qy1_mo) ELSE NULL END as [Average Price],

CASE WHEN (select Sum(b.all_qy1_mo)from [2005exp] b where a.commodity=b.commodity)<>0 THEN
(((sum(a.all_val_mo)/(select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity)))/((sum(a.all_qy1_mo)/(select Sum(b.all_qy1_mo) from [2005exp] b where a.commodity=b.commodity)))) ELSE NULL END as [Price Ratio]

from [2005exp] a inner join concord c on a.commodity=c.commodity

WHERE a.cty_code=@.Country
GROUP BY a.commodity, c.descrip_1, c.quantity_1 order by [Value] desc

Friday, March 23, 2012

HELP WITH A VIEW - calculated column

I need a view that contains a select statement that reads through all rows in a table, and based on the value in one of the columns, returns an additional column containing either "Manager" or "employee" depending on the values of that column. I'm not sure whenter to use a loop statement , a local variable, etc - -- but the end result must be a datagrid holding all all rows in the table plus the additional "Manager column" Can someone help me?

SELECT CASE col1 WHEN 'M' THEN 'Manager' ELSE 'Employee' END as EmpType
FROM tablename

You can use a CASE statement. Books online has a very good reference for using CASE.|||Thanks - works great!

Monday, March 19, 2012

Help with a confusing and advanced query - If Exsists?

Ok,

I need to select a list of products based on a complex criteria. First off, here is a list of the tables I am using and their function.

Dbo.Products – This table holds product names and information.

Dbo.Products_Attributes – This table holds a list of attributes for each product. It has a many to 1 relationship with Products. This table holds only 2 fields – ProductID and AttributeID

Dbo.Customers – This table holds basic customer information and some of the needed criteria for the product search.

Dbo.Customers_Attributes – This table contains a list of attributes that the customer needs in his/her products. This table has a many to 1 relationship with Customers and only has 2 fields, CustomerNum and AttributeID

Dbo.Attributes – This table contains all of the different attributes possible for our products to have. Each can be used by a customer when choosing criteria.

Background: Each attribute is a True / False. Either they want that attribute, or they do not care if they get it or not. This is where the hard part comes in. If a particular attribute is listed as needed in dbo.Customers_Attributes, then I do not want any records pulled from Products that DOES NOT have this attribute. On the other hand, if the attribute is NO listed in the customers_attributes list, it is assumed that the customer wants it. In other words, if there were NO attributes in the customers_attributes list, ALL products would be returned. Attributes listed in the customers_attributes table are "required" (no product will be returned that does not have what is in the list).

I hope I was clear enough… I had to change the table names a functions slightly because of some stupid policy about giving out too much proprietary information here.

If any of you know how to do the above with a subquery of some kind, please let me know.

Dave LarsonI'm not sure that I catch your task properly, but try something like that:

SELECT ProductId, ProductName
FROM dbo.Products
WHERE (NOT (ProductId IN
(SELECT dbo.Products.ProductId
FROM dbo.Products INNER JOIN
dbo.Product_Attributes ON dbo.Products.ProductId = dbo.Product_Attributes.Product
WHERE (dbo.Product_Attributes.Attribute IN
(SELECT dbo.Castomer_Attributes.Attribute
FROM dbo.Castomer_Attributes INNER JOIN
dbo.Customers ON dbo.Castomer_Attributes.Customer = dbo.Customers.CustomerId
WHERE (dbo.Customers.CustomerId = @.CustomerId))))))

where @.CustomerId is a parameter.

Sunday, February 19, 2012

Help replacing where not exists in a query

I am trying to clean up an ugly query that's based on trying to find items that exist in one table but not the other.

My tables are like this:

ITEMS
itemID,
itemName,
itemDescription
etc...

ORDERITEMS
OrderLineID,
OrderID,
itemID

ORDERS
OrderID,
OrderCompleted

Currently my query looks something like this:

Select Items.* from ITEMS where not exists (select 1 from ORDERITEMS inner join ORDERS on ORDERITEMS.OrderID = ORDERS.OrderID where ORDERITEMS.itemID=ITEMS.itemID and (ORDERS.OrderCompleted=1))

So this query is looking for ITEMS what don't have a corresponding entry in the ORDERITEMS table. As I understand it this is pretty inefficient as it is going to be executing the sub query in the Not Exists statement for each entry in the ITEMS table. Is the preferred method to do something along the lines of somehow making the sub query into a derived table and doing a left or right join?

Thanks for reading!

Ryan

Does this help?

Find Orphans Example:

An orphan record exists when there is a Contact2 record but NOT a Contact1 record.

SELECT * FROM Contact2 WHERE AccountNoNOT IN (SELECT AccountNo from Contact1)

|||

Would the NOT IN select clause be executed on every loop though? Which is less efficient *as I understand it*, than a left join?

|||

Using NOT EXISTS is the way I've always done it and the way I've always seen it done. So long as your indexes are built right this will perform well -- doing it once per ITEM is no problem as long as there's an index ORDERITEMS.itemId. And, btw, you can simply put a Select * in the subquery, you don't need to say Select top 1.

Re Steve's suggestion, I don't know for sure, but I'm not convinced it will work well, I'd like to see a showplan with your data. Translating his suggestion into your data, I think it turns into:

select *
from ITEMS
where itemId not in (select itemId -- select distinct itemId??
from ORDERITEMS inner join ORDERS on ORDERITEMS.OrderID = ORDERS.OrderID
where ORDERS.OrderCompleted=1)

The subselect will be executed only once, not once for each row of ITEMS.

However, I am not so sure this will perform well -- and it may be dependant on sql server 2000 vs 20005 whether it works well. Among other things, is there a limit to the number of items in an IN list? Like I say, I'd like to see the query plans

|||

If the subquery referenced by the NOT IN clause doesn't reference any fields of the outer query, then it'll be executed only once. In fact, it should have the same query plan as a left join.

select *
from ITEMS
where itemId not in (select itemId -- select distinct itemId??
from ORDERITEMS inner join ORDERS on ORDERITEMS.OrderID = ORDERS.OrderID
where ORDERS.OrderCompleted=1)

should produce a similiar query plan to:

SELECT *

FROM items i

LEFT JOIN (SELECT itemId FROM orderitems oi JOIN orders o ON oi.OrderID=o.OrderID WHERE o.OrderCompleted=1) t1 ON i.ItemID=t1.ItemID

WHERE t1.ItemID IS NULL

|||

FYI, if OrderItems.ItemId is nullable there will be a potential for getting different results in each method, seehttp://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

Motley:

In fact, it should have the same query plan as a left join.

Not trying to argue, but do you know for a fact that SQL Server resolves a nested NOT IN list subquery exactly thes same way as a NOT EXISTS? Do you have a citation on this. I really don't know myself -- for a fact -- one way or the other.

|||

I didn't say that NOT IN and NOT EXISTS produce the same query plans. As you pointed out, NOT EXISTS clauses almost always uses a WHERE/JOIN clause that references a field in the outer query, which would radically change the query plan because the results for the subquery would vary in each iteration of the outer query. In any case, I was referring to NOT IN and LEFT JOIN.

With that said, you are correct. If one or more of the fields are nullable (even if no records actually contain a null value), then it can change the query plan, sometimes significantly. As I wrote that, you'll notice I even backed off from saying that it will produce the exact same query plan (Just similiar) as I wrote the two query examples. Three-valued-logic discussions are often beyond the scope of the simple questions that people usually ask here on these forums and I prefer to keep my answers simple when possible.

The article you linked brings up some very good points regarding the use of IN, EXISTS, and JOIN when dealing with one or more columns that can possibly contain NULL values. I'd have to argue a few points the author makes regarding the "expected" results (Specificially, I suspect that most beginner SQL writers that don't understand three-valued logic would indeed expect the results that the IN gave for the last results -- No records since both the parent and child had records with an ID of NULL. However, there are other test cases that would fail, the author just happened to have test data that gave that result). Still, it's a good article that explores the differences when dealing with nullable fields.

As for a citation -- (overlooking that isn't what was meant to be conveyed) I don't trust what is said, even by experts. Only the truth is important, and even "experts" are too often wrong in what they say. It is however, an excellent starting point until I learn better.

--

After all that, the worst performing queries will be ones that use subqueries that depend on value from each row in an outer query. It is very difficult for the query planner to come up with an efficient query plan to such a query. Optimizing such a query into one that has no such dependancy whether using IN, EXISTS, or JOIN will usually produce much better results, especially as the size of the data grows.

|||

Motley:

I didn't say that NOT IN and NOT EXISTS produce the same query plans

You're right, I misquoted you. My apologies.

Motley:

As for a citation -- (overlooking that isn't what was meant to be conveyed) I don't trust what is said, even by experts. Only the truth is important, and even "experts" are too often wrong in what they say. It is however, an excellent starting point until I learn better

Huh? I'd accept a citation from eg MSDN citation or SQL Server books online, wouldn't you? Could be wrong, but they did design the product.. Simply looking at query plans from individual queries is not enough support a blanket statement. I would rephrase my question to ask whether (how) you know that this NOT IN and LEFT JOIN resolve to the same thing. I don't keep up with every advance of the optimizer, maybe you do, but you made a pretty strong claim and I'm asking on what that is based ("In fact, it should have the same query plan as a left join").

Interestingly, I just did find a 2005 refrerence in which MS says that EXISTS and IN subqueries, as we have been talking about them here, are "semantically equivilent"http://msdn2.microsoft.com/en-us/library/ms188336.aspx

I also just found this 2005 reference,http://msdn2.microsoft.com/en-us/library/ms172984.aspx, for 2005 Compact Edition (didn't know there was one) which says, "The SQL Server Compact Edition query processor always rewrites the IN subquery to use JOIN"

Motley:

After all that, the worst performing queries will be ones that use subqueries that depend on value from each row in an outer query. It is very difficult for the query planner to come up with an efficient query plan to such a query. Optimizing such a query into one that has no such dependancy whether using IN, EXISTS, or JOIN will usually produce much better results, especially as the size of the data grows.

To accept that I would definately want to see a MS reference.