Help with this query please - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with this query please

Author

Topic

ido1957
Starting Member

10 Posts

Posted - 2011-08-31 : 16:44:22

I've got to build this query and I'm lost as to how....
I can do this with code that includes SQL's and loops but I need
to do it within one SQL query.

I've got to find all cancelled sold product instances with no charges in the charges file.

Charges File has
- Service_Number + Charge_Type_Code
- Customer_Number + Charge_Type_Code

Product (Sales) File has
- Product_Sale_Number
- Product_Type_Code
- Customer_Number
- Product_Status

Product Type / Charge_Type File
- Product_Type_Code
- Charge_Type_Code

Service File
Service_Number
Product_Sale_Number

Any idea how this can be done?

ido1957
Starting Member

10 Posts

Posted - 2011-08-31 : 16:49:12

...plus I need to check combinations of Customer/Charges and Service/Charges in the charge file

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-31 : 17:50:53

Can you provide data in a consumable format and expected output? Here is a link that can help you prepare that information:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 00:31:21

are they are tables or are they files storing the data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 00:33:35

if they're tables use like


SELECT <required columns here>
FROM Product p
LEFT JOIN [Product Type / Charge_Type] pc
ON pc.Product_Type_Code = p.Product_Type_Code
WHERE pc.Product_Type_Code IS NULL
AND p.Product_Status ='Cancelled'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ido1957
Starting Member

10 Posts

Posted - 2011-09-01 : 12:36:29

Charges table c
has two different key/combinations that I'm looking for:
- Service_id + tariff_id
- customer_node_id + tariff_id
----------------------------------------------------
service_id | customer_node_id |tariff_id | charge
------------|--------------------------------------
200241 | null | 1000341 | 10
200241 | null | 1000360 | 15
200241 | 100301 | 1000380 | 20
----------------------------------------------------

product_instance_history table pih
- product_instance_id => joins to st.base_product_instance_id
- base_product_id => joins to pt.product_id
- customer_node_id => part of the 2 part key to find records in the charge file.
- product status
---------------------------------------------------------------------
product_instance_id | product_id | customer_node_id | product status
--------------------|------------|------------------|----------------
100241 | 1000320 | 100301 | Cancelled
---------------------------------------------------------------------

product_tariff table pt
- product_id => joins to pih.product_id
- tariff_id => part of the 2 part key to find records in the charge file.
------------------------
product_id | tariff_id
------------|-----------
100320 | 1000341
100320 | 1000360
100320 | 1000380
------------------------

service table st
- service_id => part of the 2 part key to find records in the charge file.
- base_product_instance_id => joins to pih.product_instance_id
----------------------------------------------------
service_id | base_product_instance_id
------------|--------------------------------------
200241 | 100241
----------------------------------------------------

SELECT pih.product_instance_id,
pih.customer_node_id,
pt.tariff_id,
sh.service_id,
pd.product_id
FROM product_instance_history pih,
product_tariff pt,
product pd,
service_history sh
WHERE pih.product_id = pd.product_id
AND pd.product_id = pt.product_id
AND pih.product_instance_status_code = "cancelled"
AND sh.base_product_instance_id = pih.product_instance_id

Results
-----------------------------------------------------------------------------
product_instance_id | customer_node_id |tariff_id | service_id | product_id
------------|----------------------------------------------------------------
100241 |100301 |1000341 |200241 |1000320
100241 |100301 |1000360 |200241 |1000320
-----------------------------------------------------------------------------

Then I loop through each row and query for count(*) in charge file
looking for match on
service_id + tariff or
customer_node_id + tariff
If the count is zero for both combinations then the product qualifies (for further processing).

So I'm using 2 SQL queries and a loop whereas I want to just use on SQL query.

Hope this helps clarify what I'm trying to do....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 13:30:25

ok. and whats the output you're expecting?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ido1957
Starting Member

10 Posts

Posted - 2011-09-01 : 15:14:06

I need a list of product_instance_id's that have a status of 'Cancelled' and have no charges within the charges file. The sample Charge File shows entries but I'm looking for where there are no entries...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 23:51:55

[code]SELECT pih.<required columns here>
FROM product_instance_history pih
LEFT JOIN product_tariff pt
ON pt.product_id = pih.product_id
LEFT JOIN Charges c
ON c.tariff_id = pt.tariff_id
WHERE c.tariff_id IS NULL
AND pih.product status ='Cancelled'

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ido1957
Starting Member

10 Posts

Posted - 2011-09-20 : 16:37:32

Many thanks - that worked!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 22:03:52

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ido1957
Starting Member

10 Posts

Posted - 2011-09-26 : 13:01:47

Well, I tested a couple of scenarios and I need to ask for more help...:-)

Basically, I want to get only Product B details in both these scenarios :
Product A - Active + charges exist
Product B - Cancelled + no charges exist

Product A - Active + no charges exist
Product B - Cancelled + no charges exist

I find that when I have charges for the tariff from any Product (A or B) that it doesn't pick anything up.
i.e. it thinks that it needs to exclude if any charges exist for that tariff, whereas I need to filter for the tariff + service id and / or tariff + customer id (which are the keys in the charge file) for cancelled product B. Unfortunately there is no direct link between the Product ID and the charge file.

I made a minor change to the SQL as it was not recognizing my cancelled (Status 9) product instances correctly:

Select product_instance_id from product_instance_history where product_instance_status_code = 9 AND product_instance_id in (SELECT unique pih.product_instance_id FROM product_instance_history pih LEFT JOIN product_tariff pt ON pt.product_id = pih.product_id LEFT JOIN Charge c ON c.tariff_id = pt.tariff_id WHERE c.tariff_id IS NULL )

Can you help me?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 13:06:28

didnt get your requirement.please explain in below format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ido1957
Starting Member

10 Posts

Posted - 2011-09-26 : 13:59:35

product_instance_history joins to service_history
Pih Product Instance ID = sh.Base Product Id

product instance joins to customer node history
pih.customer_node_id = cnh.customer_node_id

service_history joins to charge
sh.service_id = ch.service_id

customer_node_history joins to charge
cnh.customer_node_id = ch.customer_node_id

product_instance_history is keyed by product_instance_id
service history is keyed by service_id
customer_node_history is keyed by customer_node_id

There are two "keys" to the charge file that I need to find:
A charge may have a customer_node id value only
A charge may have a service_id value only
A charge may have both a service_id value and a customer_node id value. But I need to find when either match the value associated with the product_instance_history record.

So I want to select product_instance_id's where the product_instance is "cancelled" and there are no charges in the charge file for the associated service_id or associated customer_node_id

The code doesn't really need the tariff I don't think as any charge creted by an tariff means it should not be picked up.

This is my current SQL and it picks only when the tariff is not in the charge file for any customer. I think I need to stop using tariff as a criteria all the products use the same tariffs. I need to get to the service id and customer id keys in the charge file.

select product_instance_id FROM product_instance_history where product_instance_status_code = 9 AND product_instance_id in (SELECT unique pih.product_instance_id FROM product_instance_history pih LEFT JOIN product_tariff pt ON pt.product_id = pih.product_id LEFT JOIN Charge c ON c.tariff_id = pt.tariff_id WHERE c.tariff_id IS NULL )

Can you help? And let me know if this is enough detail?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 14:04:18

please go through link posted and give details in required format unless you want someone to keep on guessing and giving inaccurate solutions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ido1957
Starting Member

10 Posts

Posted - 2011-09-27 : 11:36:41

I don't have enterprise manager and cannot install it. Can I just post a text version of the tables ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 11:47:19

sounds like this.


SELECT pih.*
FROM Product_instance_History pih
LEFT JOIN Service_History sh
ON sh.service_id = pih.service_Id
LEFT JOIN Customer_node_History cnh
ON cnh.customer_node_id
LEFT JOIN Charge ch
ON (cnh.customer_node_id = ch.customer_node_id
OR sh.service_id =ch.service_id)
WHERE ch.charge IS NULL
AND pih.product_status='cancelled'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ido1957
Starting Member

10 Posts

Posted - 2011-09-30 : 12:24:40

visakh16 - Thank You very Much again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 12:26:42

quote:
Originally posted by ido1957

visakh16 - Thank You very Much again!

did it work finally?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ido1957
Starting Member

10 Posts

Posted - 2011-09-30 : 13:20:54

Yes - I tried a variety of scenarios and it seems to cover all that I can think of.
Thanks Again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 13:29:09

ok welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources