October 8, 2012 at 1:38 pm
Prd_IDProduct_nameProduct_CodeCode_Type Product_Type cost_CP
1 UNLEADEDABD00C C ULOOPPM 5532.00
2 UNLEADED ABD00H H ULOOPPM 545.00
3 UNLEADED ABD00L L ULOOPPM 258.00
4 UNLEADED BNGVUC C BIOUNLEADED 55.00
5 UNLEADED BNGVUH H BIOUNLEADED 8989.00
6 UNLEADED BNGVUL L BIOUNLEADED 86.00
code for above
SELECT
p.Prd_ID,
p.[Product_name]
,[Product_Code]
,[Code_Type]
,[Product_Type],
cp.cost_CP
FROM [fuel_exchange].[dbo].[Product] as p
inner join Cost_Ppid as cp on cp.P_IDcp=p.Prd_ID
where CONVERT(date,cp.datcp)='2012-10-08'
code for below :
SELECT
p.Prd_ID,
p.[Product_name]
,[Product_Code]
,[Code_Type]
,[Product_Type],
cp.cost_CP
FROM [fuel_exchange].[dbo].[Product] as p
inner join Cost_Ppid as cp on cp.P_IDcp=p.Prd_ID
where CONVERT(date,cp.datcp)='2012-10-08' and p.Code_Type='c'
Prd_IDProduct_nameProduct_CodeCode_Type Product_Type cost_CP
1 UNLEADEDABD00C C ULOOPPM 5532.00
4 UNLEADED BNGVUC C BIOUNLEADED 55.00
If you observe top table all the records having code_type c,h,l
is there any alternative to write a query to pick up only c or h or l after that for eg If I pick up code_type 'c' the cost should multiple with with some value
like cost of (ABD00C)*0.95+ cost of (BNGVUC)*0.45
I am tryng for this code past 5hurs can any one help me in that pls.
thanks.
October 8, 2012 at 2:12 pm
From what you have posted nobody can offer much help. The description is pretty vague and there is nothing to work with. Please see the first link in my signature for best practices when posting questions.
_______________________________________________________________
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 8, 2012 at 2:43 pm
alright
I will explain it clearly from that first table unleaded product I want to sum up based on codetype like c,h and l.
output should be in 3 rows instead of 6 rows
by unleaded c 4566
unleadedh 666
unleadedl 2222
i think now you can understood
thanks.
October 8, 2012 at 2:49 pm
vinay.varaala (10/8/2012)
alrightI will explain it clearly from that first table unleaded product I want to sum up based on codetype like c,h and l.
output should be in 3 rows instead of 6 rows
by unleaded c 4566
unleadedh 666
unleadedl 2222
i think now you can understood
thanks.
That really isn't any clearer. Where do those values comes from?
I took your original post and turned into consumable data. Let's start with that and then you can explain what you want for output.
;with SomeData(Prd_ID, Product_name, Product_Code, Code_Type, Product_Type, cost_CP)
as
(
select 1, 'UNLEADED', 'ABD00C', 'C', 'ULOOPPM', 5532.00 union all
select 2, 'UNLEADED', 'ABD00H', 'H', 'ULOOPPM', 545.00 union all
select 3, 'UNLEADED', 'ABD00L', 'L', 'ULOOPPM', 258.00 union all
select 4, 'UNLEADED', 'BNGVUC', 'C', 'BIOUNLEADED', 55.00 union all
select 5, 'UNLEADED', 'BNGVUH', 'H', 'BIOUNLEADED', 8989.00 union all
select 6, 'UNLEADED', 'BNGVUL', 'L', 'BIOUNLEADED', 86.00
)
select * from SomeData
_______________________________________________________________
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 8, 2012 at 2:54 pm
I had one more table called Cost_PID in that one column having Cost I joined this with Product.
SELECT
p.Prd_ID,
p.[Product_name]
,[Product_Code]
,[Code_Type]
,[Product_Type],
cp.cost_CP
FROM [fuel_exchange].[dbo].[Product] as p
inner join Cost_Ppid as cp on cp.P_IDcp=p.Prd_ID
where CONVERT(date,cp.datcp)='2012-10-08' and p.Code_Type='c'
October 8, 2012 at 2:58 pm
vinay.varaala (10/8/2012)
I had one more table called Cost_PID in that one column having Cost I joined this with Product.SELECT
p.Prd_ID,
p.[Product_name]
,[Product_Code]
,[Code_Type]
,[Product_Type],
cp.cost_CP
FROM [fuel_exchange].[dbo].[Product] as p
inner join Cost_Ppid as cp on cp.P_IDcp=p.Prd_ID
where CONVERT(date,cp.datcp)='2012-10-08' and p.Code_Type='c'
I can't see what you see and I have no knowledge of your project. If you want some help you are going to have to post enough details for somebody to help.
Again, please see the first link in my signature for best practices when posting questions.
We need to have ddl (create table scripts), sample data (insert statements) and desired output based on the 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 8, 2012 at 3:04 pm
leave about that
in below query how to pick up only product code 'C'
select 1, 'UNLEADED', 'ABD00C', 'C', 'ULOOPPM', 5532.00 union all
select 2, 'UNLEADED', 'ABD00H', 'H', 'ULOOPPM', 545.00 union all
select 3, 'UNLEADED', 'ABD00L', 'L', 'ULOOPPM', 258.00 union all
select 4, 'UNLEADED', 'BNGVUC', 'C', 'BIOUNLEADED', 55.00 union all
select 5, 'UNLEADED', 'BNGVUH', 'H', 'BIOUNLEADED', 8989.00 union all
select 6, 'UNLEADED', 'BNGVUL', 'L', 'BIOUNLEADED', 86.00
October 8, 2012 at 3:07 pm
vinay.varaala (10/8/2012)
leave about thatin below query how to pick up only product code 'C'
select 1, 'UNLEADED', 'ABD00C', 'C', 'ULOOPPM', 5532.00 union all
select 2, 'UNLEADED', 'ABD00H', 'H', 'ULOOPPM', 545.00 union all
select 3, 'UNLEADED', 'ABD00L', 'L', 'ULOOPPM', 258.00 union all
select 4, 'UNLEADED', 'BNGVUC', 'C', 'BIOUNLEADED', 55.00 union all
select 5, 'UNLEADED', 'BNGVUH', 'H', 'BIOUNLEADED', 8989.00 union all
select 6, 'UNLEADED', 'BNGVUL', 'L', 'BIOUNLEADED', 86.00
where Code_Type = 'C'
_______________________________________________________________
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 8, 2012 at 7:17 pm
vinay.varaala (10/8/2012)
alrightI will explain it clearly from that first table unleaded product I want to sum up based on codetype like c,h and l.
output should be in 3 rows instead of 6 rows
by unleaded c 4566
unleadedh 666
unleadedl 2222
i think now you can understood
thanks.
Could it be as simple as this?
;with SomeData(Prd_ID, Product_name, Product_Code, Code_Type, Product_Type, cost_CP)
as
(
SELECT 1, 'UNLEADED', 'ABD00C', 'C', 'ULOOPPM', 5532.00 union all
SELECT 2, 'UNLEADED', 'ABD00H', 'H', 'ULOOPPM', 545.00 union all
SELECT 3, 'UNLEADED', 'ABD00L', 'L', 'ULOOPPM', 258.00 union all
SELECT 4, 'UNLEADED', 'BNGVUC', 'C', 'BIOUNLEADED', 55.00 union all
SELECT 5, 'UNLEADED', 'BNGVUH', 'H', 'BIOUNLEADED', 8989.00 union all
SELECT 6, 'UNLEADED', 'BNGVUL', 'L', 'BIOUNLEADED', 86.00
)
SELECT Product_name, Code_Type, Total=SUM(Cost_CP)
FROM SomeData
GROUP BY Product_name, Code_Type
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply