Select Query in sql server [Resolved]

I have modified the query to consider even if we have more than 2 rows for an item..

create table itemtest
(item int, quantity int, oldquantity int,canceled bit)

INSERT INTO itemtest VALUES (207,12,6,0)
INSERT INTO itemtest VALUES (208,5,2,0)
INSERT INTO itemtest VALUES (207,4,0,1)
INSERT INTO itemtest VALUES (207,2,0,0)

SELECT * FROM itemtest

;with cte as
(select item,
Sum(quantity) as quantity,
Sum(oldquantity) as oldquantity,
canceled from itemtest group BY item,canceled )
--the above cte will group items based on itemid and canceled. Also we sum both the quantity and old quantity

select i1.item, case WHEN i2.canceled=1 THEN (i1.quantity-i1.oldquantity-i2.quantity)
ELSE i1.quantity-i1.oldquantity end as effect from cte i1
INNER JOIN cte i2 on i1.item=i2.item
WHERE i1.canceled=0 AND i2.canceled=1
UNION
SELECT item,(quantity-oldquantity) as effect FROM cte where item not IN (SELECT item from cte where canceled=1)

--From the Cte, we do an equijoin and considering only canceled items, we add quantity+old quantity of not canceled item and from that subtract quantity of the item which is canceled)
--Added an union because the first one will return you only the item has a canceled row, for other items for which all rows are active, are obtained from second query
The output will be
item effect
207 4
208 3

Sarithapraveen, if this helps please login to Mark As Answer. | Alert Moderator

Hi Saritha ,

Try below query .

select product,SUM(price) from salcmp where Year='2010' group by product 

It may be help You.

Regards,

Arul R
arul44.ece@gmail.com

Sarithapraveen, if this helps please login to Mark As Answer. | Alert Moderator

Hi Arul,


My situation is not like this. i will explain it.

i have a table with columns

Item Qty OldQty Cancelled
207 12 6 false // here difference is 6
208 5 2 false
207 4 0 true //this record is cancelled. so total 2 added in affect.

My need is like this

Item Qty
207 2
208 3

Sarithapraveen, if this helps please login to Mark As Answer. | Alert Moderator

@saritha praveen, please explain some more about your scenario, What is old quantity means? its the remaining pieces? And what should happen if cancelled =true.

Sarithapraveen, if this helps please login to Mark As Answer. | Alert Moderator

Hi San,

when i edit a transaction, the edited quantity is stored in quantity and quantity before editing is placed in old quantity. When cancelled = true means it is deleting. that is that records quantity has to be deducted

Sarithapraveen, if this helps please login to Mark As Answer. | Alert Moderator

@Saritha am sorry.still i didnt understand the issue. that how you are checking qty of cancelled and oldqty of previous item. I believe there might be chances of multiple records for same item..how to handle that.

for eg

what output you are expecting

207 12 6 false
207 6 2 false
207 4 0 true
207 6 2 false
207 4 1 true

Sarithapraveen, if this helps please login to Mark As Answer. | Alert Moderator

San,


First i have an item and its quantity is 6. Then i added same item with quantity 4 while editing the record. The next time i changed the quantity from 6 to 12 and i deleted the item with quantity 4. So net effect is 2 added.(12-6 - 4). this is my scenario

Sarithapraveen, if this helps please login to Mark As Answer. | Alert Moderator

Check this table and my query.
create table itemtest
(item int, quantity int, oldquantity int,canceled bit)

INSERT INTO itemtest VALUES (207,12,6,0)
INSERT INTO itemtest VALUES (208,5,2,0)
INSERT INTO itemtest VALUES (207,4,0,1)

SELECT * FROM itemtest


with cte as
(select item,quantity,oldquantity,canceled,ROW_NUMBER() OVER (partition by item order BY item) as row from itemtest)

select i1.item, case WHEN i2.canceled=1 THEN (i1.quantity-i1.oldquantity-i2.quantity)
ELSE i1.quantity-i1.oldquantity end as effect from cte i1
INNER JOIN cte i2 on i1.item=i2.item
WHERE i1.row=1 AND i2.row=2
UNION
SELECT item,(quantity-oldquantity) as effect FROM cte where item not IN (SELECT item from cte where ROW=2)



It will give output as

item effect
207 2
208 3

Whatever records you have given, i have developed my query for that. It might not work if you have more than 2 records for the same item in your table. We have to work for that. This is a just a rough idea that how can we develop it.let me know if you have different scenario

Sarithapraveen, if this helps please login to Mark As Answer. | Alert Moderator

San,


Can u explain this query pls.... also i have more than 2 records for the same item in table....

Sarithapraveen, if this helps please login to Mark As Answer. | Alert Moderator

Thanks San.... This satisfies my condition

Sarithapraveen, if this helps please login to Mark As Answer. | Alert Moderator

You are welcome.

-Santhosh

Sarithapraveen, if this helps please login to Mark As Answer. | Alert Moderator

San,

Even though this satisfies my condition, i cant understand the logic behind this query. if u get time can u just explain it to me

Sarithapraveen, if this helps please login to Mark As Answer. | Alert Moderator

Updated my previous solution with comments. logic is not difficult there, just split the queries and try to understand
-Santhosh

Sarithapraveen, if this helps please login to Mark As Answer. | Alert Moderator

@SarithaPraveen If it solves your problem, please mark it as answer

Sarithapraveen, if this helps please login to Mark As Answer. | Alert Moderator