|
|
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
|
|
|
|
|
|
Thanks i.j
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try this
declare @pk_table table (id int)
insert into @pk_table select 1 union all select 2 union all select 3
union all select 4
declare @fk_table table (id int, string varchar(2))
insert into @fk_table
select 1,'x' union all select 1,'y' union all select 1,'z' union all
select 2,'a' union all select 2,'b' union all select 2,'c' union all
select 3,'d' union all select 3,'e' union all select 3,'f' union all
select 4,'g' union all select 4,'h' union all select 4,'i'
select cast(id as varchar(20)) + space(2) + cast(id as varchar(20)) + space(2) +
(select string + ','
from @fk_table fkt
where fkt.id = pkt.id
FOR xml path('')) as OutputCol
from @pk_table pkt
group by pkt.id
Niladri Biswas
|
|
|
|
|
Hi thanks for all the replies. I finally approached by calling a function from select query.
Select col1, col2, func() as col3,col4 from table1 a inner join table2 b
on a.col1 = b.col2
Regards
Naina
Naina
|
|
|
|
|
Hello,
I suggestion.. if possible try to avoid using function as it causes performance bottleneck.
Niladri Biswas
|
|
|
|
|
But one that has me baffled. Yes, I've Googled, I've checked the online help, but I can't find an answer to this stupid question. Be kind - I'm old...
I have SQL Server Express 2008 installed on my server and, for various reasons, I want to develop a db app on a WinXP client machine using the server instance. How do I connect to it? The local copy of the SQL Server Management tool only "sees" the local instance of SQL Server Ex 2005 on the XP client. I know a better solution would be to install the more recent version on the client, but it's a problem to do so. The client has a ton of RAM, but almost no drive space and no way to add more, while the server has little RAM and over a TB of drive space available. I really want to work from the client, but host everything on the server.
Any tips?
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Hi,
I guess the problem is not that you are using an SQL Server 2005 Management Studio. To "see" the server on your client machine you need to start the SQL Server-Browser service on your server machine. This service propagates the name of the local sql server to remote machines.
Let me know if this helps you.
Regards
Sebastian
|
|
|
|
|
Thanks, Sebastion - it worked, at least partially. I can "see" the database engine on the server, but I can't connect. I'll need to configure it to accept remote connections first. Back to the books to find out how...
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
By default, Sql Server Express is local only. You need to allow remote connections through the Surface Area Configuration tool accessed via your Start menu. I can't remeber if it is installed by default or if you need to install Management Studio Express to get access to it.
|
|
|
|
|
Thanks!
The 2008 version doesn't have a Surface Area Configuration tool; it's built in to the Management Studio. All protocols are enabled ( my doing, not the default ), but when I try to connect from the client I receive error 10061 - the target server actively refused the connection. I'm logged in as an Admin on both machines, and the SQL Server Browser service is running on the server. Perhaps I have to install the 2008 version of the management studio on the client... Any thoughts on that?
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
With SQL Server 2005 Express, it seems I had to modify security settings to make it accept remote connections. I'm afraid I'm not very good at this sort of thing; my approach was to mash on the keys until it gave up in disgust and did what I wanted.
|
|
|
|
|
I may try your approach; nothing Microsoft has to suggest works.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
How can i assign primary key to a field having BIT datatype in sql server 2000.
Thanks
Tufail
|
|
|
|
|
A bit data type stores ones or zeros, you intend to have only 2 records in your table? I generally use int but even there I have run into the 32k limit.
|
|
|
|
|
To an existing table, or a new table?
DECLARE @TestTable TABLE(
[col1] bit NOT NULL PRIMARY KEY,
[col2] varchar(10) NULL
)
INSERT @TestTable
SELECT 0, 'item 1'
UNION ALL
SELECT 1, 'item 2'
SELECT *
FROM @TestTable; Results in;
col1 col2
----- ----------
0 item 1
1 item 2
INSERT @TestTable (col1, col2) VALUES (9, 'item 3') Will fail, since "9" is too large a value for type 'BIT'. The error will say that it's duplicate, since it tries to convert the "9" to a BIT, resulting in the value "1":
PRINT CAST(9 AS BIT) Results in "1"
INSERT @TestTable (col1, col2) VALUES (1, 'item 42') Will fail due to the primary key, since there's already an item with a value "1" for col1.
I are Troll 
|
|
|
|
|
Slow day up North is it, besides 42 ALWAYS works, your definition of BIT must be wrong.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Slow day up North is it, besides 42 ALWAYS works, your definition of BIT must be wrong.
I'm slow in thinking today. Item 42 shouldn't work, since there already is an item with the pk-value of "1" inserted into the table.
BIT = { 0 | 1 }, and the first INSERT statement should fill the table to it's maximum capacity.
I are Troll
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
