Immediate help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Immediate help

Author

Topic

tempali
Starting Member

3 Posts

Posted - 2011-06-19 : 08:15:58

Hi;
Sorry for my bad English language.
I want to write a query to the inventory value of each customer of a particular commodity at a table to store and display.
I have three tables:
1 - customer information
2 - Data entry of input goods (receipt)
3 - transit information (draft)
The value of the goods account balance for each customer and put on the table.

get sum of receipt and input of customer number 10
select sum(receipt.qty)- sum(input.qty) as Balance from [input],[receipt] where cutomrtID="10" and goodsName="TV"

the customerid and goodsname is my Variables.

=========================================================================
godsname | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | Total |
-------------------------------------------------------------------------
tv | 100 | 50 | 200 | 160 | 489 | 560 | 56 | 8 | 70 | 1693 |
-------------------------------------------------------------------------
radio | 5 | 89 | 75 | 0 | 0 | 0 | 568 | 796 | 152 | 1685 |
-------------------------------------------------------------------------

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-19 : 14:16:03

It is difficult for someone who is not familiar with your database and tables to provide useful suggestions. So can you post the details of the tables along with some sample data? Brett's blog here will help you get the table DDL and provide guidance on how to post the sample data: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

With the information you have provided, I would guess that your query needs a join condition - something like this:


select sum(receipt.qty)- sum(input.qty) as Balance from 
[input] inner join [receipt] on input.customerID = receipt.CustomerID 
where cutomrtID="10" and goodsName="TV"

But, I am only guessing!

tempali
Starting Member

3 Posts

Posted - 2011-06-20 : 01:00:43

my database tables is :


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_customer_]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_customer_]
GO

CREATE TABLE [dbo].[_customer_] (
	[customer id] [int] NOT NULL ,
	[customer name] [varchar] (100) COLLATE Arabic_CI_AS NOT NULL ,
	[customer address] [varchar] (255) COLLATE Arabic_CI_AS NULL ,
	[customer phone] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
	[customer cell] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
	[customer fax] [varchar] (50) COLLATE Arabic_CI_AS NULL 
) ON [PRIMARY]
GO
data entry examples:

	1	Gold2011				
	2	??????? ?????				
	3	??? ??????				
	4	????? ????? ????? ???				
	5	????? ????? ???? ?????				
	6	????? ????				
	7	????? ????? ?????				
	8	????? ???? ???? ????				
	9	????? ????????				
	10	????? ????? ????				
==========================================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_IN_]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_IN_]
GO

CREATE TABLE [dbo].[_IN_] (
	[receipt no] [int] NULL ,
	[receipt date] [char] (10) COLLATE Arabic_CI_AS NULL ,
	[customer id] [int] NULL ,
	[part no] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
	[part qty] [int] NULL ,
	[part situation] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
	[part description] [varchar] (100) COLLATE Arabic_CI_AS NULL ,
	[continer no] [bigint] NULL ,
	[driver name] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
	[car no] [char] (10) COLLATE Arabic_CI_AS NULL ,
	[bill of loading] [bigint] NULL ,
	[unit] [char] (20) COLLATE Arabic_CI_AS NULL ,
	[sup id] [int] NULL ,
	[rec id] [int] IDENTITY (1, 1) NOT NULL ,
	[type Receipt] [nvarchar] (50) COLLATE Arabic_CI_AS NULL 
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_out_]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_out_]
GO
data entry example:

	310	89/12/04  	1	W196BF	176	-	786545	4353034	?????	126?26    	0	??????              	12	50665	
	385	89/12/14  	3	W196BC	176	-	878515	4882151	????	815?39    	0	??????              	12	50732	
	22	89/10/19  	1	W196BC	176	-	798902	4807918	??????	529?25    	0	??????              	12	50798	
	133	89/11/07  	1	IN LBUC488RTAO1	-	?????? ???? ????	?????		23279 	??????              	19	50863	
	170	89/11/09  	5	W196BF	176	-	322244	5098859	???????	151?18    	0	??????              	12	50931	
	208	89/11/09  	1	W196BF	176	-	322238	4830862	?????	351?45    	0	??????              	12	50999	
	1	89/10/11  	1	IN C186RQ	330	-	?????? ??? ????			0	??????              	13	51066	
	1	89/10/11  	9	OD LTC1260BA	74	-	?????? ??? ????			0	??????              	21	51131	
	1	89/10/11  	1	IN TNNC0608SAO	70	-	?????? ??? ????			0	??????              	20	51195	
	1	89/10/11  	1	TAY33502201	50	-	?????? ??? ????			0	??????              	15	51259	
															
==========================================================================
CREATE TABLE [dbo].[_out_] (
	[order No] [bigint] NOT NULL ,
	[order Date] [varchar] (10) COLLATE Arabic_CI_AS NOT NULL ,
	[customer ID] [int] NOT NULL ,
	[part no] [varchar] (100) COLLATE Arabic_CI_AS NOT NULL ,
	[part description] [varchar] (255) COLLATE Arabic_CI_AS NULL ,
	[part qty] [int] NOT NULL ,
	[driver name] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
	[car no] [varchar] (10) COLLATE Arabic_CI_AS NULL ,
	[buyer name] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
	[buyer address] [varchar] (255) COLLATE Arabic_CI_AS NULL ,
	[unit] [char] (10) COLLATE Arabic_CI_AS NULL ,
	[sup id] [int] NULL ,
	[rec id] [int] IDENTITY (1, 1) NOT NULL 
) ON [PRIMARY]
GO

Data entry example:

	4	89/10/04	1	IN S126TQ	Indoor,Cool/Hot Titan Split 12000	7	?????-????	179?47	?????? ??????	-	??????    	11	219786
	4	89/10/04	3	OD S126TQ	Outdoor,Cool/Hot Titan Split 12000	7	?????-????	179?47	?????? ??????	-	??????    	11	219787
	5	89/10/04	1	IN TPNH808FLAO	Indoor,Floor Standing 80000		1	???????	326?25	???? ?????	-	??????    	14	219788
	5	89/10/04	3	OD TPUH808FLAO	Outdoor,Floor Standing 80000		1	???????	326?25	???? ?????	-	??????    	14	219789
	6	89/10/06	1	IN S246TQ	Indoor,Cool/Hot Titan Split 24000	2	????	834?93	????- ????	-	??????    	11	219790
	6	89/10/06	1	OD S246TQ	Outdoor,Cool/Hot Titan Split 24000	2	????	834?93	????- ????	-	??????    	11	219791
	7	89/10/06	5	IN S126TQ	Indoor,Cool/Hot Titan Split 12000	2	????	466?56	???? ??? ???????	-	??????    	11	219792
	7	89/10/06	1	OD S126TQ	Outdoor,Cool/Hot Titan Split 12000	2	????	466?56	???? ??? ???????	-	??????    	11	219793
	8	89/10/07	9	IN S246TQ	Indoor,Cool/Hot Titan Split 24000	48	????	775?18	??? ??? ?????? ???? -	??????    	11	219794
	8	89/10/07	1	OD S246TQ	Outdoor,Cool/Hot Titan Split 24000	48	????	775?18	??? ??? ?????? ???? 	-	??????    	11	219795
==========================================================================

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources