February 23, 2012 at 1:07 am
Hi i have Two Tables
1 st tbl_Purchase
CREATE TABLE [dbo].[tbl_Purchase](
[Purchase_id] [bigint] IDENTITY(1,1) NOT NULL,
[Location_Id] [bigint] NULL,
[PU_Ref] [nvarchar](50) NULL,
[InvoiceDate] [datetime] NULL,
[Original_Amt] [decimal](18, 2) NULL,
[Supplier_ID] [bigint] NULL,
[Status] [int] NULL,
[UploadFile] [text] NULL,
[till] [int] NULL,
[Empid] [varchar](100) NULL,
[NACID] [int] NULL,
[TAXID] [int] NULL,
CONSTRAINT [PK_tbl_Purchase] PRIMARY KEY CLUSTERED
(
[Purchase_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
2nd
tbl_pur_Child
CREATE TABLE [dbo].[tbl_pur_Child](
[s_N] [bigint] IDENTITY(1,1) NOT NULL,
[Purchase_ID] [bigint] NULL,
[Paid_amt] [decimal](18, 2) NULL,
[Location_Id] [bigint] NULL,
[InvoiceDate] [datetime] NULL,
CONSTRAINT [PK_tbl_pur_Child] PRIMARY KEY CLUSTERED
(
[s_N] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
To getting the orignal amount for any supplier i am writing a query
select Supplier_ID,sum(Original_Amt)as Amount from dbo.tbl_Purchase group by Supplier_ID
This Will Give Me a orignal amount group by Supplier...
In 2 nd Table I am storing a data when i am paying for any supplier...
So i want sum of paid amount also group by supplier....
How can i get this...
My Second Table not Have Supplier id...
it has purchase id...
Means it will work somethink like this..
Firsts its take purchase ids supllier wise...
Than sum of all the paid amount according to that purchase ids .
Plese Give ME a Way How i can Achieve this....
February 23, 2012 at 1:20 am
Hello
I am Able To do..
By Using That Query
select pu.Supplier_ID,pa.Purchase_ID,pa.Paid_amt
into #temp1
from tbl_Purchase pu
inner join
tbl_pur_Child pa
on
pu.Purchase_id =pa.Purchase_ID
select Supplier_ID,sum(paid_amt) as Paid from #temp1 group by Supplier_ID
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply