Project Description:
I need help with a store procedure
I have 3 tables:
Customer table
-CustomerID varchar(50)
-ProductionDate datetime
-DiscontinuedDate datetime
-StopProductionDate datetime
-Portefolje_deactive int
tmtBB table
-CustID varchar
-SupplierID int
-StartDate datetime
-StopDate datetime
Vendor table
-id int
-vendorGUID uniqueidentifier
Relations:
Customer.CustomerID = tmtBB.CustID
Vendor.id = tmtBB.SupplierID
Vendor.vendorGUID = @InputGUID
Requirements
(ProductionDate <= @InputDate and ProductionDate IS NOT NULL)
(StopProductionDate IS NULL or StopProductionDate >= @InputDate)
(DiscontinuedDate >= @InputDate or DiscontinuedDate IS NULL)
(Portefolje_deactive = 0)
The purpose of the stored procedure is to return the customers who is attached to a given vendor at a given time. You provide the stored procedure with and input date and inputguid (refers to a specific vendor through the vendor table) and it shall return the customer "belonging" to the giving vendor at that input date.
Some of the records has no StopDate because the StopDate is not known at the insert time.
There is 2 input parameters:
@inputGUID
@inputDate
Testdata:
customer table
CustomerID ProductionDate DiscontinuedDate StopProductionDate Portefolje_deactive
570714700000006500 2008-10-01 NULL NULL 0
tmtB table
CustID SupplierID StartDate StopDate
570714700000006500 1 1998-11-18
570714700000006500 1 2008-10-01 2009-03-01
570714700000006500 4 2010-08-01
570714700000006500 4 2010-12-01 2011-03-31
vendor table:
id vendorGUID
1 116A148C-8CF7-11E0-9FF8-FA8F4824019B
4 17429488-8CF7-11E0-9518-0A904824019B
@inputGUID = 116A148C-8CF7-11E0-9FF8-FA8F4824019B
@inputDate = 1998-11-17
Required result: Not return customer 570714700000006500 cause he is first attached to vendor 1 1998-11-18.
@inputGUID = 116A148C-8CF7-11E0-9FF8-FA8F4824019B
@inputDate = 1998-12-20
Required result: Return 570714700000006500 cause he is now attached to vendor 1 and will be until the next record with different vendoid.
@inputGUID = 116A148C-8CF7-11E0-9FF8-FA8F4824019B
@inputDate = 2010-10-10
Required result: Return 570714700000006500 due to record number 2.
@inputGUID = 116A148C-8CF7-11E0-9FF8-FA8F4824019B
@inputDate = 2010-08-01
Required result: Not return 570714700000006500 cause he is now attached to vendor 4.
@inputGUID = 17429488-8CF7-11E0-9518-0A904824019B
@inputDate = 2012-01-01
Required result: Return 570714700000006500 cause he is now attached to vendor 4. And inputGUID = vendor4's GUID.
@inputGUID = 116A148C-8CF7-11E0-9FF8-FA8F4824019B
@inputDate = 2012-01-01
Required result: Not return 570714700000006500 cause he is now attached to vendor 4. And inputGUID = vendor4's GUID.
For the simplicity I have only provided a single customer, but in the testing phase I will send more customers and tmtB records.
