1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44
|
USE [INVOICE_TRACKING]
GO
/****** Object: StoredProcedure [dbo].[InvoicesBySupplierAndDate] Script Date: 03/30/2011 15:00:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[InvoicesBySupplierAndDate]
-- Add the parameters for the stored procedure here
@DateInf as date,
@DateSup as date,
@Supplier as char(15)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT TOP (100) PERCENT dbo.TINVOICE_HEAD.Invoice_Ref, dbo.TINVOICE_HEAD.Invoice_ID, dbo.TINVOICE_HEAD.Invoice_date,
dbo.TINVOICE_HEAD.Received_dte, dbo.TINVOICE_HEAD.Curr, dbo.TINVOICE_HEAD.Invoice_Value, dbo.TINVOICE_HEAD.Code_supp,
dbo.SSRFADD.ADDRESS_1, dbo.SALFLDGSAG.ANAL_T7,dbo.SALFLDGSAG.JRNAL_NO, dbo.SALFLDGSAG.POSTING_DATE
FROM dbo.TINVOICE_HEAD INNER JOIN
dbo.SSRFADD ON dbo.TINVOICE_HEAD.Code_supp = dbo.SSRFADD.ADD_CODE LEFT OUTER JOIN
dbo.SALFLDGSAG ON dbo.TINVOICE_HEAD.Invoice_ID = dbo.SALFLDGSAG.ANAL_T7
WHERE (dbo.TINVOICE_HEAD.Received_dte >= CONVERT(date,@DateInf,101)) AND
(dbo.TINVOICE_HEAD.Received_dte <= CONVERT(date,@DateSup,101))
AND (dbo.TINVOICE_HEAD.Code_supp = @Supplier)
GROUP BY dbo.TINVOICE_HEAD.Invoice_Ref, dbo.TINVOICE_HEAD.Invoice_ID, dbo.TINVOICE_HEAD.Invoice_date, dbo.TINVOICE_HEAD.Received_dte,
dbo.TINVOICE_HEAD.Curr, dbo.TINVOICE_HEAD.Invoice_Value, dbo.TINVOICE_HEAD.Code_supp, dbo.SSRFADD.ADDRESS_1,
dbo.SALFLDGSAG.ANAL_T7, dbo.SALFLDGSAG.JRNAL_NO, dbo.SALFLDGSAG.POSTING_DATE
ORDER BY dbo.TINVOICE_HEAD.Received_dte DESC
END |
Partager