April 23, 2008 at 7:59 am
here is my query
select km_ndc,
case when (extended_amount_corrected)=0.00 and
qty_corrected = 0 and
cost_corrected = 0 and
contract_price_corrected = 0 then sum(extended_amount_submitted )
else sum(extended_amount_corrected)
end,
case when (extended_amount_corrected)=0.00 and
qty_corrected = 0 and
cost_corrected = 0 and
contract_price_corrected = 0 then sum(qty_submitted)
else sum(qty_corrected)
end
from dbo.chargeback_detail
group by km_ndc,extended_amount_corrected,qty_corrected,cost_corrected,contract_price_corrected
so here i have corrected and submitted type of columns in my table so i just want to know is is there any other way to write this query more simpler ..the result i want it if any of the 4 columns is non zero then i should use corrected values if all of them are zeros then i should use submitted values...so could anyone gelp me out thkz in advance....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
April 23, 2008 at 8:05 am
here is my query... small change in the query
select km_ndc,
case when (extended_amount_corrected)=0.00 and
qty_corrected = 0 and
cost_corrected = 0 and
contract_price_corrected = 0 then sum(extended_amount_submitted )
else sum(extended_amount_corrected)
end
from dbo.chargeback_detail
group by km_ndc,extended_amount_corrected,qty_corrected,cost_corrected,contract_price_corrected
srry abt the previous post this is my query plz help me out
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
April 23, 2008 at 8:14 am
Not really a better way to write that, but you could replace the AND's with + and test to see the performance difference.
SELECT
km_ndc
,CASE WHEN (extended_amount_corrected
+ qty_corrected
+ cost_corrected
+ contract_price_corrected) = 0
THEN SUM(extended_amount_submitted)
ELSE SUM(extended_amount_corrected)
END
FROM
dbo.chargeback_detail
GROUP BY
km_ndc
,extended_amount_corrected
,qty_corrected
,cost_corrected
,contract_price_corrected
Also, are you sure you should be grouping on the last four columns or should you sum them before you test for the 0 value? Just a question.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 23, 2008 at 8:29 am
Thkz Jason ... I need not group the last 4 Columns actually but if i dont do that it throws me an error so i have jus added it ....what exactly i want is the total(amt) which is either sum(extended_amt_(corrected or submitted)) but is there any other way which removes the last 4 columns in the group by ....i jus want the total amt but need to check out which is included in the sum whether corrected or submitted...plz is there any other way ...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
April 23, 2008 at 8:34 am
SELECT
km_ndc
,CASE WHEN SUM(extended_amount_corrected)
+ SUM(qty_corrected)
+ SUM(cost_corrected)
+ SUM(contract_price_corrected) = 0
THEN SUM(extended_amount_submitted)
ELSE SUM(extended_amount_corrected)
END
FROM
dbo.chargeback_detail
GROUP BY
km_ndc
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 23, 2008 at 8:40 am
SELECT
km_ndc
,CASE WHEN SUM(extended_amount_corrected)
+ SUM(qty_corrected)
+ SUM(cost_corrected)
+ SUM(contract_price_corrected) = 0
THEN SUM(extended_amount_submitted)
ELSE SUM(extended_amount_corrected)
END
FROM
dbo.chargeback_detail
GROUP BY
km_ndc
i am a bit weak in sql coding so let me know if it satisfies my condition which is if all the 4 columns are zero then we use submitted else corrected....bcoz as we are agregating here for 4 columns ...so here in the query does the sum(qty_corrected) ---- does it apply to a single row by row or all rows at once or by km_ndc....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
April 23, 2008 at 8:48 am
chinna (4/23/2008)
i am a bit weak in sql coding so let me know if it satisfies my condition which is if all the 4 columns are zero then we use submitted else corrected....bcoz as we are agregating here for 4 columns ...so here in the query does the sum(qty_corrected) ---- does it apply to a single row by row or all rows at once or by km_ndc....
All rows for each km_ndc. If you want it for each individual row, then remove ALL of the SUM()'s and remove the grouping.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 23, 2008 at 9:05 am
Jason but the problem here is if i have two rows such asa
col0 col1 col2 col3 col4 col5
1 0 0 0 0 3------so this taks submitted values
2 0 0 0 0 4---takes submitted values
1 0 0 0 9 8---this takes corrected values
col5 -submitted extended amt
col4----corrected extended amt
so now when we sum it we get
case when sum(col1+col2+col3+col4)=0 then sum(extended_amt_submitted) else sum(extended_amt_corrected) end
group by col0
now we get sum as 9 but i just want to clarify with you whether is it the correct way or should i do it row by row ....but when i use the group by col0 do i get the same answer or diff one...so here in this case i am applying for a group of rows directly if i have 12 rows in a group and if 11 rows have 4 columns with zero and one row with non zeros it takes corrected values but the value of the sum i expect differs so i need it row by row i believe ...let me talk to my manager in between
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
April 23, 2008 at 9:08 am
All rows for each km_ndc. If you want it for each individual row, then remove ALL of the SUM()'s and remove the grouping.
but if i do this way it throws me error as
Msg 8120, Level 16, State 1, Line 1
Column 'dbo.chargeback_detail.Extended_amount_corrected' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'dbo.chargeback_detail.Qty_corrected' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'dbo.chargeback_detail.Cost_corrected' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'dbo.chargeback_detail.Contract_Price_corrected' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
April 23, 2008 at 9:17 am
SELECT
km_ndc
,CASE WHEN (extended_amount_corrected)
+ (qty_corrected)
+ (cost_corrected)
+ (contract_price_corrected) = 0
THEN SUM(extended_amount_submitted)
ELSE SUM(extended_amount_corrected)
END
FROM
dbo.chargeback_detail
GROUP BY
km_ndc,extended_amount_corrected,qty_corrected,cost_corrected,contract_price_corrected
------------------------------------
SELECT
km_ndc
,CASE WHEN sum(extended_amount_corrected)
+ sum(qty_corrected)
+ sum(cost_corrected)
+ sum(contract_price_corrected) = 0
THEN SUM(extended_amount_submitted)
ELSE SUM(extended_amount_corrected)
END
FROM
dbo.chargeback_detail
GROUP BY
km_ndc
are these both queries the same i mean does they get the same output....
here in my first query i am using group by qty,cost,conract but we dont have that in our select list so does it really matters ...so i go with first query what do u suggest...i jus want to a conclusion and then talk to my manager
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply