Completed

Help to write SQL Stored Procedure

This project was successfully completed by eperfections for $50 USD in a day.

Get free quotes for a project like this
Employer working
Completed by:
Skills Required
Project Budget
$30 - $250 USD
Completed In
1 day
Total Bids
19
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:
[url removed, login to view] = [url removed, login to view]
[url removed, login to view] = [url removed, login to view]
[url removed, login to view] = @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.

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online