SQL query calculation

[pratimapaudel]

Can anyone help me?

I have a table which has item id, moduletype, unit id, price field.
I would like to do calculation like this.
For the same item price is different according to moduletype.
I would like to select price as 'cost price' only when moduletype='0'
and price as 'purchase price' if moduletype='1'
I tried to use case statement but it shows me null if condition doesnot satisfy.
I want to show the cost price and purchase price of one item in one row not in two row . SO, case statement doesn't work at this time (i think).

And also if unit id of the cost price doesn't match with the unit price of purchase prise than i need to convert the unit id of purchse price to unit id of costprice. How can i do that?

Thanks in advance
P

[Delerna]

is this what you need
[code=sql]
SELECT [item id], [unit id],
sum(case when moduletype=0 then price else 0 end) as CostPrice,
sum(case when moduletype=1 then price else 0 end) as PurchasePrice
FROM YourTable
GROUP BY [item id], [unit id]
[/code]

[deric]

For your first issue, use a Pivot to achieve that. I believe you can make it on your own. If you will encounter problems with your created code using pivot, let us know.

For the second issue, please rephrase your question. I'm confused whether the unit IDs will not match or the unit prices.
I wrote a code from the way I understand your statement. It is not tested because I dont have a query analyzer here. But, you might get an idea out from it..

update table t1, table t2
set t1.unitid = t2.unitid
where t2.moduletype = '0' --cost
and t1.moduletype = '1' --purchase
and t1.price <> t2.price

[pratimapaudel]

Delerna
Thanks for Reply.

[pratimapaudel]

Deric
Thanks for you help.
Your post is helpful but i did this in another way. I found another way that we can select data from same table with two condition. We don't need to do case statement .
Anyway it was good information for me regarding to PIVOT.
Thanks
I will be keep on posting the questions in future.

For your first issue, use a Pivot to achieve that. I believe you can make it on your own. If you will encounter problems with your created code using pivot, let us know.

For the second issue, please rephrase your question. I'm confused whether the unit IDs will not match or the unit prices.
I wrote a code from the way I understand your statement. It is not tested because I dont have a query analyzer here. But, you might get an idea out from it..

update table t1, table t2
set t1.unitid = t2.unitid
where t2.moduletype = '0' --cost
and t1.moduletype = '1' --purchase
and t1.price <> t2.price