|
'''I tried this following Query but it giving me wrong total what i am missing in following query
SELECT Prd.Item_Name, sum(P.Piece_No) AS PStock, sum(D.Itm_Qty) AS SoldStock, sum(R.Itm_Qty) as RefStock, sum(P.Piece_No) - sum(D.Itm_Qty) - sum(R.Itm_Qty) AS Avl_Stock
FROM Product AS Prd, Purchase_Detail AS P, Detail_Description AS D, Refund_Detail as R
WHERE Prd.Item_Name=P.Item
GROUP BY Prd.Item_Name;
|
|
|
|
|
You need to add links in the WHERE clause for D and R to Prd. At the moment you have cross joins to these two tables so you will get incorrect results.
You may want to look at using LEFT OUTER JOINS as it is possible that products may not have been sold or refunded and in that case, the results will not appear for that product if you use INNER JOINS.
|
|
|
|
|
Thanks for reply i.j.russell
if i am using this
WHERE Prd.Item_Name = P.Item and Prd.Item_Name = d.Item and Prd.Item_Name = R.Item_Name
Its just giving me Refunded items with wrong total,
|
|
|
|
|
I Tried this also but not getting right total.
'''''''''''''''''
SELECT Prd.Item_Name, Sum(P.Piece_No) AS PStock, Sum(D.Itm_Qty) AS SoldStock, Sum(R.Itm_Qty) AS RefStock, Sum(P.Piece_No)-Sum(D.Itm_Qty)-Sum(R.Itm_Qty) AS Avl_Stock
FROM Refund_Detail AS R, (Product AS Prd INNER JOIN Purchase_Detail AS P ON Prd.Item_Name = P.Item) INNER JOIN Detail_Description AS D ON Prd.Item_Name = D.Item
WHERE (((Prd.Item_Name)=[P].[Item] And (Prd.Item_Name)=[d].[Item]))
GROUP BY Prd.Item_Name;
|
|
|
|
|
Hey.. Where are you Mr. J4amieC..
If you are capable answer this ...
Else....You know what...
|
|
|
|
|
Is there any need for that? I fully agree with his comments. I only helped you because you tried to resolve it yourself and got part of the way there. If this is your attitude, I will refrain from assisting you any further and hope that others do the same.
|
|
|
|
|
tell me broo why we are here, why peoples are posting his problem here...?
for to hear this " Go to your boss and tell him you are not qualified developer " ??
Before i posted this problem i already tried above query
and thats why i asked question here ?
I am also replying answer from other forums also..
i know how to answer and how to reply.
I am telling anyone who is asking so stupid question like this. But i am replying them.
i think you understand what i mean to say.
|
|
|
|
|
Let me break this down for you. See your original post. It just tells the problem followed by code demand. It shows as if either:
1. You are not capable of doing that and completely clueless, or
2. Lazy enough to do your work.
Although it does not proves you are any of these, but the impression is like that. As you mentioned, you had already tried something, you should have mentioned that. If you would have structured your post as:
1. Your task.
2. What have you tried.
3. Where did you got stuck while solving the problem.
This would give a better impression and better responses from others. 
It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD
|
|
|
|
|
Danish your way to discuss is very good.
you understand the fault of my first post.
and you mentioned it on very nice way,
i understand and thanks to explain me.
But i am sorry Danish i just dont like the way of other helps.
|
|
|
|
|
khan786 wrote: i just dont like the way of other helps
Well, learn from the mistakes and get over it.
It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD
|
|
|
|
|
khan786 wrote: If you are capable answer this ..
Absolutely.
khan786 wrote: Else....You know what...
Er...no... what?
|
|
|
|
|
Just to get rid of another useless, no-brainer of a post... here you go:
// Perfectly workable solution removed, as the OP is an ungrateful gimp.
Gives the exact output you needed, with the test code you provided. No warranties or guarantees. Change the names to your actual table names yourself.
edit: After reading the above comments, this is the very last time you'll get help from me.
edit2: And after reading the below comment, you'll not even get the benefit of this answer.
modified on Wednesday, October 21, 2009 2:21 PM
|
|
|
|
|
see ..!!!
Now u are angry..You have to feel this. You have to think twise to tell some one like this
" If you're incapable, I suggest you go back to your boss, hand him your keycard and tell him you are not qualified to be a developer. " OK.
1 - and by the way this is wrong answer,
what kind of developer you are,
each time how can user punch single product name to Check stock, There is million Productname in database.
2 - I just Give you crystal example from my first post. what is in database and what type of output i want, But you didnt understand (No Brain).
Lol...I know what type of developer u are i dont need your wrong answer anymore just keep it with you.
I will post correct one 2morrow.
''''''''''''
|
|
|
|
|
You do not deserve to be helped. Seriously. A person who wrote code that YOU were supposed to write and instead of being grateful, you are abusing the guy.
It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD
|
|
|
|
|
You, sir, are an anus.
Your data model makes this query neigh on impossible to write any other way than that posted above. It is quite simple to adjust what I have given you to pull out the data for one particular product, but i dont remember you requesting this at all.
I seriously suggest you seek employment in a less brain-intensive industry. Get used to this phrase "Big mac and fries please".
|
|
|
|
|
J4amieC
This is the Correct one Still I am working On it to get some more Correct result, You Brain Less Idiot Look and think your self how u can say you are a developer, and by the way what are you doing here" "U think you can helping people" C ur self in the mirro U Idiot. and learn the manner how to speak with other.
'''''''''''''''''''''''''''''''''''''''''''
Just to save time i asked question here.
''''''''''''''''''''''''''''''''''''''''''
SELECT Item_Name,<br />
<br />
(SELECT SUM(Piece_No) AS Quantity FROM Purchase_Detail WHERE (Product.Item_Name = Item)) AS QuantityB,<br />
<br />
(SELECT SUM(Itm_Qty) AS Quantity FROM Detail_Description WHERE (Product.Item_Name = Item)) AS QuantityC,<br />
<br />
(SELECT SUM(Itm_Qty) AS Quantity FROM Refund_Detail WHERE (Product.Item_Name = Item_Name)) AS QuantityD,<br />
<br />
(SELECT SUM(Piece_No) AS Quantity FROM Purchase_Detail AS tblB_1 WHERE (Product.Item_Name = Item)) -<br />
<br />
(SELECT SUM(Itm_Qty) AS Quantity FROM Detail_Description AS tblC_1 WHERE (Product.Item_Name = Item)) -<br />
<br />
(SELECT SUM(Itm_Qty) AS Quantity FROM Refund_Detail AS tblD_1 WHERE (Product.Item_Name = Item_Name)) AS Total<br />
<br />
FROM Product GROUP BY Item_Name
|
|
|
|
|
Voted & reported as Abuse
|
|
|
|
|
|
You sir, are a first class jackass.
|
|
|
|
|
Try this
declare @tbl1 table(productname varchar(20))
insert into @tbl1 values('abc_123a')
insert into @tbl1 values('cfl_523j')
insert into @tbl1 values('jpm_111k')
declare @tbl2 table(productname varchar(20),purqty int)
insert into @tbl2 values('abc_123a',50)
insert into @tbl2 values('abc_123a',30)
insert into @tbl2 values('cfl_523j',100)
insert into @tbl2 values('cfl_523j',20)
insert into @tbl2 values('cfl_523j',10)
insert into @tbl2 values('jpm_111k',105)
insert into @tbl2 values('jpm_111k',10)
insert into @tbl2 values('jpm_111k',25)
declare @tbl3 table(productname varchar(20),soldqty int)
insert into @tbl3 values('abc_123a',10)
insert into @tbl3 values('abc_123a',05)
insert into @tbl3 values('cfl_523j',20)
insert into @tbl3 values('cfl_523j',05)
insert into @tbl3 values('cfl_523j',10)
insert into @tbl3 values('jpm_111k',15)
insert into @tbl3 values('jpm_111k',05)
insert into @tbl3 values('jpm_111k',10)
declare @tbl4 table(productname varchar(20),refqty int)
insert into @tbl4 values('abc_123a',2)
insert into @tbl4 values('abc_123a',6)
insert into @tbl4 values('cfl_523j',4)
insert into @tbl4 values('cfl_523j',3)
insert into @tbl4 values('cfl_523j',9)
insert into @tbl4 values('jpm_111k',3)
insert into @tbl4 values('jpm_111k',8)
insert into @tbl4 values('jpm_111k',2)
;with cte1 as(
select productname,sum(purqty)as purqty from @tbl2
group by productname)
,cte2 as(
select productname,sum(soldqty)as soldqty from @tbl3
group by productname)
,cte3 as(
select productname,sum(refqty)as refqty from @tbl4
group by productname)
select c1.productname,c1.purqty,c2.soldqty,c3.refqty,c1.purqty- c2.soldqty-c3.refqty as STOCK
from cte1 c1 inner join cte2 c2 on c1.productname = c2.productname
inner join cte3 c3
on c1.productname = c3.productname
Let me know in case of any concern
Niladri Biswas
|
|
|
|
|
Thanks Nildari Biswas for Reply
I appreciate for your answer. Thank you very much.
But here i solved my problem please check.
SELECT Item_Name,<br />
<br />
(SELECT SUM(Piece_No) AS Quantity FROM Purchase_Detail WHERE (Product.Item_Name = Item)) AS QuantityB,<br />
<br />
(SELECT SUM(Itm_Qty) AS Quantity FROM Detail_Description WHERE (Product.Item_Name = Item)) AS QuantityC,<br />
<br />
(SELECT SUM(Itm_Qty) AS Quantity FROM Refund_Detail WHERE (Product.Item_Name = Item_Name)) AS QuantityD,<br />
<br />
(SELECT SUM(Piece_No) AS Quantity FROM Purchase_Detail AS tblB_1 WHERE (Product.Item_Name = Item)) -<br />
<br />
(SELECT SUM(Itm_Qty) AS Quantity FROM Detail_Description AS tblC_1 WHERE (Product.Item_Name = Item)) -<br />
<br />
(SELECT SUM(Itm_Qty) AS Quantity FROM Refund_Detail AS tblD_1 WHERE (Product.Item_Name = Item_Name)) AS Total<br />
<br />
FROM Product GROUP BY Item_Name<br />
<br />
<br />
again Thanks for reply.
|
|
|
|
|
Hi
I have 2 tables table1 and table2
table1 has primary key column a which is joined with table2 column foriegnkey column b. on joining the two tables i get the result as below
table1.a table2.b table2.c
1 1 x
1 1 y
1 1 z
but i want the output as below
table1.a table2.b table2.c
1 1 x,y,z
can anyone help me how i can do this?
want to avoid duplicates
Regards
Usha
Naina
|
|
|
|
|
Although probably possible, there is no easy way to do what you want - and it runs counter to the way database queries should work.
You'd be far better off pulling your data out in the original way you posted and handling any weird aggregation of strings in code.
FWIW here's your test code
create table #myTable (a INT, b INT, c VARCHAR(10))
INSERT INTO #myTable VALUES(1,1,'x')
INSERT INTO #myTable VALUES(1,1,'y')
INSERT INTO #myTable VALUES(1,1,'z')
INSERT INTO #myTable VALUES(2,2,'j')
INSERT INTO #myTable VALUES(2,2,'k')
INSERT INTO #myTable VALUES(2,2,'l')
declare @list VARCHAR(MAX), @a INT, @b INT
SELECT @a=1,@b=1
select @list = coalesce(@list + ',' + c ,c) from #myTable where a=@a AND b=@b
select a,b,@list as c
FROM #myTable
WHERE a=@a AND b=@b
group by a,b
drop table #myTable
|
|
|
|
|
If you are using Sql Server 2005 or 2008 you can use FOR XML PATH to concatenate data.
|
|
|
|
|
i.j.russell wrote: If you are using Sql Server 2005 or 2008 you can use FOR XML PATH to concatenate data
Do you have a link or an article for this or should I chase through BOL
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
