November 30, 2011 at 3:40 am
hi...
i have 3 table
1 is
USE [TigerERP]
GO
/****** Object: Table [dbo].[Tbl_Supplier] Script Date: 11/30/2011 15:55:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tbl_Supplier](
[Sup_ID] [bigint] NOT NULL,
[Supplier_Code] [nvarchar](50) NULL,
[SupplierName] [nvarchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Email] [nvarchar](50) NULL,
[MobileNumber] [nvarchar](20) NULL,
[TelNumber] [nvarchar](20) NULL,
[Address] [text] NULL,
[Country] [nvarchar](50) NULL,
[Zipcode] [nvarchar](10) NULL,
[VatNumber] [nvarchar](50) NULL,
[VateRate] [int] NULL,
[Default_AC] [bigint] NULL,
[Coment] [text] NULL,
[Control_Account] [int] NULL,
[CM_Email] [nvarchar](50) NULL,
[CM_Mob] [nvarchar](20) NULL,
[CM_ph] [nvarchar](20) NULL,
[CM_Fax] [nvarchar](20) NULL,
[CM_Url] [nvarchar](50) NULL,
[Mod_Pay] [nvarchar](20) NULL,
[AC_Name] [nvarchar](50) NULL,
[AC_Num] [nvarchar](50) NULL,
[Sort_Code] [nvarchar](50) NULL,
[IBN_Num] [nvarchar](50) NULL,
[Payment_Ref] [nvarchar](50) NULL,
[Crtedit_Limit] [money] NULL,
[UploadFiles] [text] NULL,
[showinpaidout] [bit] NULL,
[Credit_Day] [int] NULL,
CONSTRAINT [PK_Tbl_Supplier] PRIMARY KEY CLUSTERED
(
[Sup_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]
2 nd is
CREATE TABLE [dbo].[Tbl_InvoicePay_Master](
[Payment_Id] [bigint] NOT NULL,
[SuppID] [bigint] NULL,
[Mode] [varchar](50) NULL,
[amount] [decimal](18, 2) NULL,
[ref1] [nvarchar](50) NULL,
[ref2] [nvarchar](50) NULL,
[date] [datetime] NULL,
[status] [int] NULL,
[descr] [text] NULL,
[ModuelFlag] [varchar](50) NULL,
CONSTRAINT [PK_Tbl_InvoicePay_Master] PRIMARY KEY CLUSTERED
(
[Payment_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]
3rd is
CREATE TABLE [dbo].[Tbl_Invoice_Master](
[Invid] [bigint] NOT NULL,
[SuppID] [bigint] NULL,
[InvoiceDate] [datetime] NULL,
[EntryDate] [datetime] NULL,
[DueDate] [datetime] NULL,
[RefNo] [varchar](50) NULL,
[Total_Amount] [decimal](18, 2) NULL,
[Tax_Amt] [decimal](18, 2) NULL,
[Descr] [text] NULL,
[AmountType] [varchar](50) NULL,
[InvoicePath] [text] NULL,
[Status] [int] NULL,
[OUT_StandingAmt] [decimal](18, 2) NULL,
[Module_Flag] [varchar](50) NULL,
CONSTRAINT [PK_Tbl_Invoice_Master] PRIMARY KEY CLUSTERED
(
[Invid] 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]
Sample Data is .......
INSERT INTO [Tbl_Supplier] VALUES(2,'a1','Airtel','A','B','dilip@gmail.com','21','','Newyork','United States','12334','212',25,9,' ',0,'dilip.aim11@gmail.com','121','','323','google.com','Cash','','','','','',4343.0000,' ',1,4)
INSERT INTO [Tbl_Supplier] VALUES(3,'a2','aircel','o','o','ds@gmail.com','988','','india','India','82','12',26,11,' ',0,'dilip.aim11@gmail.com','323','','23123','www.goole.com','Cash','','','','','',8888.0000,' ',1,5)
INSERT INTO [Tbl_Supplier] VALUES(4,'a3','Ab','A','B','dilip@gmail.com','21','','Newyork','United States','12334','212',25,9,' ',0,'dilip.aim11@gmail.com','121','','323','google.com','Cash','','','','','',4343.0000,' ',1,8)
INSERT INTO [Tbl_Supplier] VALUES(5,'a4','Relience','o','o','ds@gmail.com','988','','india','India','82','12',26,11,' ',0,'dilip.aim11@gmail.com','323','','23123','www.goole.com','Cash','','','','','',8888.0000,' ',1,6)
for table 2
INSERT INTO [Tbl_InvoicePay_Master] VALUES(1,2,'Cash',3232.00,'33','','Nov 29 2011 5:55:10:577PM',0,' ','PUPAYADVANCE')
INSERT INTO [Tbl_InvoicePay_Master] VALUES(2,2,'Cash',994.00,'4eed','','Nov 29 2011 6:00:10:027PM',0,' ','ADJUSTMENT')
INSERT INTO [Tbl_InvoicePay_Master] VALUES(3,2,'Cash',5232.00,'dds','','Nov 29 2011 6:53:04:967PM',0,' ','ADJUSTMENT')
INSERT INTO [Tbl_InvoicePay_Master] VALUES(4,2,'Cash',70.00,'88','','Nov 29 2011 8:23:19:983PM',0,' ','PUPAY')
INSERT INTO [Tbl_InvoicePay_Master] VALUES(5,2,'Cash',323.00,'ew','','Nov 30 2011 12:44:15:667PM',0,' ','PURCP')
for table 3
INSERT INTO [Tbl_Invoice_Master] VALUES(1,2,'Nov 29 2011 12:00:00:000AM','Nov 29 2011 5:54:38:760PM','Dec 3 2011 12:00:00:000AM','434',3334.00,0.00,' ','Credit',NULL,0,58.00,'INVPUR')
INSERT INTO [Tbl_Invoice_Master] VALUES(2,2,'Nov 29 2011 5:55:10:597PM','Nov 29 2011 5:55:10:597PM','Nov 29 2011 5:55:10:597PM','33',3232.00,0.00,' ','ADVANCE',NULL,0,682.00,'INVPURADVANCE')
INSERT INTO [Tbl_Invoice_Master] VALUES(3,2,'Nov 29 2011 12:00:00:000AM','Nov 29 2011 5:55:43:263PM','Dec 3 2011 12:00:00:000AM','4344',4444.00,0.00,' ','Invoice',NULL,0,742.00,'INVPUR')
INSERT INTO [Tbl_Invoice_Master] VALUES(4,2,'Nov 29 2011 12:00:00:000AM','Nov 29 2011 5:56:21:387PM','Dec 3 2011 12:00:00:000AM','refcre',2000.00,0.00,' ','Credit',NULL,0,1600.00,'INVPUR')
INSERT INTO [Tbl_Invoice_Master] VALUES(5,2,'Nov 29 2011 12:00:00:000AM','Nov 29 2011 5:58:17:067PM','Dec 3 2011 12:00:00:000AM','55t',5555.00,0.00,' ','Invoice',NULL,0,3005.00,'INVPUR')
INSERT INTO [Tbl_Invoice_Master] VALUES(6,5,'Nov 30 2011 12:00:00:000AM','Nov 30 2011 3:10:33:893PM','Dec 6 2011 12:00:00:000AM','re4',3434.00,0.00,' ','Invoice',NULL,0,3434.00,'INVPUR')
I Written a store procedure for it....
alter procedure SP_AgedCreditor
as
SELECT s.Sup_ID,s.SupplierName,
SUM(CASE WHEN I.AmountType = 'INVOICE' THEN I.Total_Amount ELSE 0 END) as Invoice,
SUM(CASE WHEN I.AmountType = 'Credit' THEN I.Total_Amount ELSE 0 END) as credit,
SUM(CASE WHEN I.AmountType = 'Advance' THEN I.Total_Amount ELSE 0 END) as advance
from Tbl_Supplier as s inner join
Tbl_Invoice_Master as I
on
I.SuppID=s.Sup_ID
group by s.SupplierName,s.Sup_ID
SELECT s.Sup_ID,s.SupplierName,
SUM(CASE WHEN P.ModuelFlag = 'PUPAY' THEN P.amount ELSE 0 END) as Paymnet,
SUM(CASE WHEN P.ModuelFlag = 'PURCP' THEN P.amount ELSE 0 END) as Receipt ,
SUM(CASE WHEN P.ModuelFlag = 'ADJUSTMENT' THEN P.amount ELSE 0 END) as Adjustment
from Tbl_Supplier as s inner join
Tbl_InvoicePay_Master as P
on
P.SuppID=s.Sup_ID
group by s.SupplierName,s.Sup_ID
this store procedure is producing a right result.....
But its result is 2 different query....
.........................................................................................................
My second procedure to produce result by single query is...
SELECT s.Sup_ID,s.SupplierName,
SUM(CASE WHEN I.AmountType = 'INVOICE' THEN I.Total_Amount ELSE 0 END) as Invoice,
SUM(CASE WHEN I.AmountType = 'Credit' THEN I.Total_Amount ELSE 0 END) as credit,
SUM(CASE WHEN I.AmountType = 'Advance' THEN I.Total_Amount ELSE 0 END) as advance,
SUM(CASE WHEN P.ModuelFlag = 'PUPAY' THEN P.amount ELSE 0 END) as Paymnet,
SUM(CASE WHEN P.ModuelFlag = 'PURCP' THEN P.amount ELSE 0 END) as Receipt ,
SUM(CASE WHEN P.ModuelFlag = 'ADJUSTMENT' THEN P.amount ELSE 0 END) as Adjustment
from
Tbl_Supplier as s inner join
Tbl_Invoice_Master as I
on
I.SuppID=s.Sup_ID
inner join
Tbl_InvoicePay_Master as P
on
P.SuppID=s.Sup_ID
group by s.SupplierName,s.Sup_ID
its not giving accurate result...
Plse Help Me...
Thnx....
November 30, 2011 at 3:46 am
Try changing the INNER JOIN's in your final query to LEFT OUTER JOIN's
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 30, 2011 at 3:56 am
i tried with all outer join ...
but its not working...
November 30, 2011 at 5:14 am
Since you are joining the base table Tbl_Supplier to both Tbl_InvoicePay_Master and Tbl_Invoice_Master, the rows are getting multiplied during the JOIN. Hence, the amount is also getting multiplied.
Check the result after removing the GROUP BY and SUM function in your final query.
You will have to use Derived Tables or CTE's as I have used below
;with cte_imas as
(
SELECT s.Sup_ID,s.SupplierName,
SUM(CASE WHEN I.AmountType = 'INVOICE' THEN I.Total_Amount ELSE 0 END) as Invoice,
SUM(CASE WHEN I.AmountType = 'Credit' THEN I.Total_Amount ELSE 0 END) as credit,
SUM(CASE WHEN I.AmountType = 'Advance' THEN I.Total_Amount ELSE 0 END) as advance
from @Tbl_Supplier as s inner join
@Tbl_Invoice_Master as I
on
I.SuppID=s.Sup_ID
group by s.SupplierName,s.Sup_ID
), cte_ipmas as
(
SELECT s.Sup_ID,s.SupplierName,
SUM(CASE WHEN P.ModuelFlag = 'PUPAY' THEN P.amount ELSE 0 END) as Paymnet,
SUM(CASE WHEN P.ModuelFlag = 'PURCP' THEN P.amount ELSE 0 END) as Receipt ,
SUM(CASE WHEN P.ModuelFlag = 'ADJUSTMENT' THEN P.amount ELSE 0 END) as Adjustment
from @Tbl_Supplier as s inner join
@Tbl_InvoicePay_Master as P
on
P.SuppID=s.Sup_ID
group by s.SupplierName,s.Sup_ID
)
select*
fromcte_imas im
full outer join cte_ipmas ip on im.Sup_ID = ip.Sup_ID
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 30, 2011 at 5:45 am
Thanks ... its HelpFul to me....
November 30, 2011 at 5:58 am
Glad I could help you out 🙂
Even you deserve a bit of credit for posting some good test data along with the DDL
Had you not posted the test data, I could have never helped you out
Thats why they say Help us help you
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply