In a sql server 2012 database, I have the following table that I need to obtain data from by doing a lot of self joins.
CREATE TABLE [dbo].[CustomCustomer](
[customID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[personID] [int] NOT NULL,
[attributeID] [int] NOT NULL,
[value] varchar NULL,
[date] [smalldatetime] NULL,
[customGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[districtID] [int] NULL,
LTER TABLE [dbo].[CustomCustomer] WITH NOCHECK ADD CONSTRAINT [FK_CustomerData_Person] FOREIGN KEY([personID])
REFERENCES [dbo].[Person] ([personID])
ALTER TABLE [dbo].[CustomCustomer] CHECK CONSTRAINT [FK_CustomerData_Person]
ALTER TABLE [dbo].[CustomCustomer] WITH NOCHECK ADD CONSTRAINT [FK_CustomerData_CustomerAttribute] FOREIGN KEY([attributeID])
REFERENCES [dbo].[CustomerAttribute] ([attributeID])
ON DELETE CASCADE
ALTER TABLE [dbo].[CustomCustomer] CHECK CONSTRAINT [FK_CustomerData_CustomerAttribute]
I am posting this t-sql question since I would like to know the best way to obtain the required data I need from this table. Basically
data is obtained from this table by looking for the unique values in the attributeID column for each customer that is identified by personID.
The values are unique for a 'group' of atrribute values by looking at the date column of this table.
Customers can have unique categories like location, business type, inventory, and quantity on hand. When data is entered into the group of attribute categories the date
field is used to uniquely identify the group of values that were entered.
Basically I need to obtain data for each customer by the groupings of location (attribute values between 100 and 150) ,inventory (attribute values between 200 and 250) and quantity on
hand (attribute values between 900 and 915). I need to obtain the values from Oct 1. 2015 to Dec. 31 2015.
Thus can you show me the t-sql on how to accomplish this goal by using self-joins and/or whatever you suggest to be used?