October 19, 2012 at 10:26 am
Posted - 10/19/2012 : 12:24:44
--------------------------------------------------------------------------------
Hi guys
I have a situation here. I'm working on pricing report for a customer. I could get the report from our company software but it took more than 20 minutes to have all the pricing. My manager think it would be faster if I use SQL to retrive the report. However, I researched online to find a query for customer pricing report, the query only gives me a result for only one item ( based on inv_mast_uid)
The query looks like this:
execute dbo.p21_price_engine @customer_id = 6418,
@company_id = 'TSD', @inv_mast_uid = '3826',
@supplier_id = NULL, @disc_group_id = NULL,
@prod_group_id = NULL, @mfr_class_id = NULL,
@customer_part_no = '', @tran_date = {ts '2012-07-05 00:00:00.000'},
@oe_sales_unit_size = '1.0000', @oe_qty_ordered = '1',
@source_location_id = 1, @oe_pricing_unit_size = '1.0000',
@sales_cost = 3.5, @debug_mode = 0, @summary_price = 1,
@order_type = 706, @rollup_component_price = 'N',
@calculator_type = 'B', @udl_list = NULL, @configuration_id = 2703,
@oe_source_location_id = 1, @limit_by_location_id = 'N',
@forced_price_value = NULL,
@check_inventory = 'N',
@use_web_based_pricing = 'N',
@sales_location_id = 1,
@ship_to_id = 6418,
@base_price_library_uid = NULL,
@selected_price_library_uid = NULL,
@customer_sensitivity_value = NULL,
@customer_category_uid = NULL,
@data_service_level = NULL,
@data_services_exp_date_is_valid = NULL,
@audit = 'N'
If you looks on the @inv_mast_uid. I tried to apply more range to get more results by using BETWEEN or other operators such as ( >, >= 0,...). But it only works with = operator, do you know how I can extend this inv_mast_uid to get multiple items instead of giving only one item .
October 19, 2012 at 10:37 am
Pass in a delimited concateonated list as a string. Then you can either use a bit of dynamic SQL so you don't have to convert it or you can parse the list and convert it.
October 19, 2012 at 10:52 am
Depends on what the sp is doing with the parameters. You'll need to post the script for the sp.
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
October 19, 2012 at 11:04 am
You might look into table parameters too. This seems like a good candidate for one.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 19, 2012 at 11:06 am
I have limited knowledge about SQL. Could you guys more specific or give me like a sample to apply?
October 19, 2012 at 11:12 am
tantcu (10/19/2012)
I have limited knowledge about SQL. Could you guys more specific or give me like a sample to apply?
BOL is the best place for this kind of stuff. http://msdn.microsoft.com/en-us/library/bb510489.aspx
If you want specific help coding you need to provide ddl and sample data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 19, 2012 at 11:54 am
Sean Lange (10/19/2012)
tantcu (10/19/2012)
I have limited knowledge about SQL. Could you guys more specific or give me like a sample to apply?BOL is the best place for this kind of stuff. http://msdn.microsoft.com/en-us/library/bb510489.aspx
If you want specific help coding you need to provide ddl and sample data.
DDL means Data Definition Language. It's the CREATE TABLE, CREATE PROCEDURE, and INSERT INTO scripts (including indexes, constraints, foreign keys, and primary keys) that enable us to recreate your schema in our databases so we can help track down the best solutions. Without that information, we're just guessing and we can't help you very well.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply