February 19, 2010 at 12:17 pm
I am trying to create a view that contains virtual data. For instance:
An employee can create X number of jobs per day. They do not have all of their available jobs for a day filled. The jobs are filled by slots in a day.
A basic select statement for this would look like(but it doesn't show me the empty slots):
SELECT dbo.installers.id, dbo.installersSchedule.slot, dbo.installersSchedule.customerID, dbo.installersSchedule.installDate
FROM dbo.installers INNER JOIN
dbo.installersSchedule ON dbo.installers.id = dbo.installersSchedule.installerID
The tables involved are:
CREATE TABLE [dbo].[installers] (
[id] [smallint] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[maxInstalls] [tinyint] NOT NULL ,
[employeeID] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[installersSchedule] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[installerID] [smallint] NOT NULL ,
[slot] [tinyint] NOT NULL ,
[customerID] [int] NULL ,
[installDate] [datetime] NOT NULL
) ON [PRIMARY]
Any help would be greatly appreciated.
February 19, 2010 at 1:30 pm
Can you show us some sample data for those 2 tables and, more importantly, an example of the result set you want to see returned?
February 19, 2010 at 1:55 pm
Installers Table
id name maxInstalls employeeID
------ ------------------------------ ----------- -----------
1 John Doe 4 45
2 Jane Doe 4 46
3 Wang Chung 6 47
4 Pedro Santiago 3 48
5 Alex Tegret 5 49
InstallersSchedule Table
id installerID slot customerID installDate
----------- ----------- ---- ----------- ------------------------------
1 1 2 5689 2010-01-01 00:00:00.000
2 4 3 5742 2010-01-01 00:00:00.000
3 2 4 6559 2010-01-01 00:00:00.000
4 1 4 5863 2010-01-01 00:00:00.000
5 3 3 4498 2010-01-01 00:00:00.000
View Sample Results(If created like I would like):
installerID slot customerID installDate
----------- ---- ----------- ------------------------------
1 1 2010-01-01 00:00:00.000
1 2 5689 2010-01-01 00:00:00.000
1 3 2010-01-01 00:00:00.000
1 4 5863 2010-01-01 00:00:00.000
2 1 2010-01-01 00:00:00.000
2 2 2010-01-01 00:00:00.000
2 3 2010-01-01 00:00:00.000
2 4 6559 2010-01-01 00:00:00.000
3 1 2010-01-01 00:00:00.000
3 2 2010-01-01 00:00:00.000
3 3 4498 2010-01-01 00:00:00.000
3 4 2010-01-01 00:00:00.000
3 5 2010-01-01 00:00:00.000
3 6 2010-01-01 00:00:00.000
4 1 2010-01-01 00:00:00.000
4 2 2010-01-01 00:00:00.000
4 3 5742 2010-01-01 00:00:00.000
5 1 2010-01-01 00:00:00.000
5 2 2010-01-01 00:00:00.000
5 3 2010-01-01 00:00:00.000
5 4 2010-01-01 00:00:00.000
5 5 2010-01-01 00:00:00.000
1 1 2010-01-02 00:00:00.000
1 2 2010-01-02 00:00:00.000
1 3 2010-01-02 00:00:00.000
1 4 2010-01-02 00:00:00.000
2 1 2010-01-02 00:00:00.000
2 2 2010-01-02 00:00:00.000
2 3 2010-01-02 00:00:00.000
2 4 2010-01-02 00:00:00.000
3 1 2010-01-02 00:00:00.000
3 2 2010-01-02 00:00:00.000
3 3 2010-01-02 00:00:00.000
3 4 2010-01-02 00:00:00.000
3 5 2010-01-02 00:00:00.000
3 6 2010-01-02 00:00:00.000
4 1 2010-01-02 00:00:00.000
4 2 2010-01-02 00:00:00.000
4 3 2010-01-02 00:00:00.000
5 1 2010-01-02 00:00:00.000
5 2 2010-01-02 00:00:00.000
5 3 2010-01-02 00:00:00.000
5 4 2010-01-02 00:00:00.000
5 5 2010-01-02 00:00:00.000
February 19, 2010 at 2:13 pm
Here's a solution where you can use a Tally, or numbers, table to explode out the MaxInstalls value to get the total available slots for an installer and then join that back with the schedules to see the available slots versus the filled slots.
You'll need a Tally table to make this work. You can find out more about them and how to build on here[/url].
One thing that my code does not account for is the install dates for slots that have not been filled. Your example shows a value for the date, but unless you tell me what logic to use to find out the default value, my code will simply display NULL dates.
Here's the code.
DECLARE @installers TABLE (
id smallint NOT NULL ,
name varchar (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
maxInstalls tinyint NOT NULL ,
employeeID int NOT NULL
)
INSERT INTO @installers
SELECT 1, 'John Doe', 4, 45 UNION ALL
SELECT 2, 'Jane Doe', 4, 46 UNION ALL
SELECT 3, 'Wang Chung', 6, 47 UNION ALL
SELECT 4, 'Pedro Santiago', 3, 48 UNION ALL
SELECT 5, 'Alex Tegret', 5, 49
DECLARE @installersSchedule TABLE (
id int NOT NULL ,
installerID smallint NOT NULL ,
slot tinyint NOT NULL ,
customerID int NULL ,
installDate datetime NOT NULL
)
INSERT INTO @installersSchedule
SELECT 1, 1, 2, 5689, '2010-01-01 00:00:00.000' UNION ALL
SELECT 2, 4, 3, 5742, '2010-01-01 00:00:00.000' UNION ALL
SELECT 3, 2, 4, 6559, '2010-01-01 00:00:00.000' UNION ALL
SELECT 4, 1, 4, 5863, '2010-01-01 00:00:00.000' UNION ALL
SELECT 5, 3, 3, 4498, '2010-01-01 00:00:00.000'
--Get Installers and available Slots (1-maxInstalls), then join into schedule
SELECT i.Name,
i.Slot,
s.CustomerID,
s.installDate
FROM (
SELECT id,
Name,
t.N as Slot
FROM @Installers i
INNER JOIN dbo.Tally t ON t.N <= i.maxInstalls
) i
LEFT JOIN @installersSchedule s ON s.installerID = i.ID AND s.Slot = i.Slot
February 19, 2010 at 3:34 pm
This is really close but it does not generate the dates for future jobs. I generated a dateTally table similar to what you did for the next 30 years. Would this be of any help?
CREATE TABLE [dbo].[dateTally] (
[aDate] [datetime] NOT NULL
) ON [PRIMARY]
February 22, 2010 at 8:06 am
Thanks, you helped me come up with this:
CREATE VIEW dbo.fullSchedule
AS
SELECT i.name, i.Slot, s.customerID, i.aDate
FROM (SELECT TOP 100 PERCENT i.id, i.name, t . N AS Slot, dbo.dateTally.aDate
FROM dbo.installers i INNER JOIN
dbo.Tally t ON t . N <= i.maxInstalls CROSS JOIN
dbo.dateTally
ORDER BY i.id, dbo.dateTally.aDate, t . N) i LEFT OUTER JOIN
dbo.installersSchedule s ON i.aDate = s.installDate AND s.installerID = i.id AND s.slot = i.Slot
I am not sure if this is the most efficient method but it seems to work.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply