December 7, 2009 at 7:03 am
create table #x
(phno int,
Description varchar(20),
qty int,
amt money)
insert into #x values(123,'abc',1,60)
insert into #x values(123,'abc',-1,60)
insert into #x values(123,'abc',1,60)
insert into #x values(345,'def',1,30)
insert into #x values(345,'def',1,40)
insert into #x values(345,'def',-1,40)
insert into #x values(345,'def',-1,30)
insert into #x values(345,'def',-1,30)
insert into #x values(345,'def',1,30)
insert into #x values(345,'ghi',1,35)
insert into #x values(345,'ghi',-1,35)
insert into #x values(345,'ghi',1,50)
insert into #x values(567,'jkl',1,89)
insert into #x values(567,'jkl',-1,89)
insert into #x values(678,'abc',1,24)
insert into #x values(678,'abc',-1,24)
insert into #x values(678,'abc',1,24)
insert into #x values(678,'abc',-1,24)
insert into #x values(789,'mno',1,54)
insert into #x values(789,'mno',-1,54)
insert into #x values(789,'mno',1,65)
insert into #x values(789,'abc',-1,54)
insert into #x values(789,'abc',1,54)
insert into #x values(890,'abc',1,34)
insert into #x values(890,'abc',1,34)
select * from #x
alter table #x
add flag varchar(10)
here in this table i have phno,description and qty and amount
qty 1 is accepted,-1 is rejected
for each phno i have more than 1 row for each description
Scenario1:
for Phno "123" with description "abc" we have 3 records with qty 1,-1,1 so i would like to mutually cancel the 1,-1
it so i want to flag the +1,-1 as N
so it would be like
insert into #x values(123,'abc',1,60)N
insert into #x values(123,'abc',-1,60)N
insert into #x values(123,'abc',1,60)NULL
Scenario2:
for phno "567" with description "JKL" we have 2 records with qty 1 and -1 so mutual cancellation
so i want both to be flagged N
insert into #x values(567,'jkl',1,89)N
insert into #x values(567,'jkl',-1,89)N
Scenario3:
I have same ph no but with different description here
for "789" i have description "mno" and "abc" so for each phno and each description i would like to mutually cancel if it has qty 1 and -1.
Here it has same phno and desc but different amt so it should flag the +1 and -1 with same amt and leave the other amt as below
so i would like it to be flagged
insert into #x values(789,'mno',1,54) N
insert into #x values(789,'mno',-1,54)N
insert into #x values(789,'mno',1,65)NULL
insert into #x values(789,'abc',-1,54)N
insert into #x values(789,'abc',1,54)N
Scenario4:
Here phno "890" has qty 1 twice with same description and same amt so would like to flag any one...like
insert into #x values(890,'abc',1,34)N
insert into #x values(890,'abc',1,34)NULL
----summary
if it has same phno and description with same amt with +1 and -1 then flag it
we should mutually cancel only if the amount is also same ...if any problem understanding my issue please let me know
Note: it is not sure that it will be alternatively like 1,-1,1 it wont be in sucha way sometime it could be like 1,-1,-1,1,1
so need to flag the first 4 if the phno,dec,amt is same for all 4
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
December 7, 2009 at 9:26 am
If it is the journey that is important, perhaps you ought to try to do this yourself. Make an effort and try to solve the problems.
We are happy to help, but we do not just do the work for you. Try and when you don't understand something, ask a question. Don't post your work here. If we just do it for you, we've gotten the journey, you just end up at the destination.
December 7, 2009 at 10:27 am
Cmon Steve do you think i havent tried i have tried all weekend i shall post even my queries its not like i tried ....but i am unable to figure out all the scenarios so asked you thats it...
so you are making fun of my quote....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
December 7, 2009 at 10:29 am
Please post your attempts. If you post what you have tried, we can better help you. Along with queries, it will be helpful to post table structures. By knowing what you have tried, we can either spot the error, or at the very least know what not to try.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 7, 2009 at 10:53 am
If you multiply the Qty by the Amount, then do a sum function on this column, you should have a good start.
Your qty will not likely be 1 all the time. What if someone orders 10 and 5 are cancelled?
And since you have different amounts, I'd assume there might be an item number involved?
If there is, then you probably want to cancel out the +1 -1 lines even if they were for the same qty and amount.....
Like Steve says - looks like homework.
When you get stuck, show us your sql, and you will likely get more helpful hints.
Just giving you some answers won't help you learn very well.
Greg E
December 7, 2009 at 11:18 am
Try this. This works for the data provided. You can do additional changes for any other data.
create table #x
(phno int,
Description varchar(20),
qty int,
amt money)
insert into #x values(123,'abc',1,60)
insert into #x values(123,'abc',-1,60)
insert into #x values(123,'abc',1,60)
insert into #x values(345,'def',1,30)
insert into #x values(345,'def',1,40)
insert into #x values(345,'def',-1,40)
insert into #x values(345,'def',-1,30)
insert into #x values(345,'def',-1,30)
insert into #x values(345,'def',1,30)
insert into #x values(345,'ghi',1,35)
insert into #x values(345,'ghi',-1,35)
insert into #x values(345,'ghi',1,50)
insert into #x values(567,'jkl',1,89)
insert into #x values(567,'jkl',-1,89)
insert into #x values(678,'abc',1,24)
insert into #x values(678,'abc',-1,24)
insert into #x values(678,'abc',1,24)
insert into #x values(678,'abc',-1,24)
insert into #x values(789,'mno',1,54)
insert into #x values(789,'mno',-1,54)
insert into #x values(789,'mno',1,65)
insert into #x values(789,'abc',-1,54)
insert into #x values(789,'abc',1,54)
insert into #x values(890,'abc',1,34)
insert into #x values(890,'abc',1,34)
alter table #x
add flag varchar(10)
; WITH cteX AS
(
SELECTROW_NUMBER() OVER ( PARTITION BY phno, description, amt ORDER BY Qty desc ) Row, *
FROM#x
)
UPDATEP
SETFlag = CASE WHEN Row = 1 AND S.phno IS NULL THEN NULL ELSE 'N' END
FROMcteX P
LEFT OUTER JOIN(
SELECTphno, description, amt
FROM#x
GROUP BY phno, description, amt
HAVING SUM(Qty) = 0
) S ON P.phno = S.phno AND P.description = S.description AND P.amt = S.amt
SELECT * FROM #x
drop table #x
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 7, 2009 at 11:25 am
It looks to me like what you need is a running total calculation. Do a search for that, and it should give you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 8, 2009 at 7:32 am
Thanks King ...u r the king it helped me out...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply