Difficult Query
-
Hi All,
Just wondering if this were possible I have a table which contains Information on Electrical Components and when it was assembled similar to the following.
ComponentCode, ComponentType, AssembledDate etc
First of all I needed to find the number of components assembled within a particular time frame so I
used a Select ComponenType, Count(ComponentCode) From Comp Where Assembeld date between XXX and XXX.
This seems to work fine however now I am supposed to try and cross correlate with a table that has information on shiftTeams which has a structre like
Date, DayShiftTeam, NightShiftTeam
So for Each Record above there will be a single date with a dayshift team (which work fom 8 to 5) and a NightShift (who work 5 to 2)
Is there any way with a query to find out how many components each team made in a prticular shift, assembled over a given time period.
Results like
Team Shift Component (Count of Components)
A D CompA 20
A N CompA 23
B D CompC
-
That should do it the query you need is the last query of the batch... all the rest is setup tables and feed sample data :
USE SSCGO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'Shifts' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
DROP TABLE dbo.Shifts
GO
CREATE TABLE dbo.Shifts
(
StartShiftDate SMALLDATETIME NOT NULL
, EndShiftDate SMALLDATETIME NOT NULL
, ShiftType CHAR(1) NOT NULL
CONSTRAINT CK_Shifts_ShiftType CHECK ([ShiftType] IN ('D', 'N'))
, CONSTRAINT PK_Shifts PRIMARY KEY CLUSTERED
(
StartShiftDate
, EndShiftDate
) ON [PRIMARY]
)
GO
--Populate the table
DECLARE @StartDate AS SMALLDATETIME
DECLARE @EndDate AS SMALLDATETIME
--set the startdate to whenever you started recording this data, or whenever you need to start reporting from.
--I'd go for the first one if at all possible
SET @StartDate = '1995/01/01 08:00:00'
SET @EndDate = '1995/01/01 17:00:00'
--populate a working table
IF OBJECT_ID('Tempdb..#Work') > 0
DROP TABLE #Work
SELECT
TOP 16437 --45 years worth of days
IDENTITY(INT, 0, 1) AS n
INTO #Work
FROM master.dbo.SysColumns C1
CROSS JOIN master.dbo.SysColumns C2
INSERT INTO dbo.Shifts (StartShiftDate, EndShiftDate, ShiftType)
SELECT
DATEADD(DD, w.n, dtShifts.StartShiftDate) AS StartShiftDate
, DATEADD(DD, w.n, dtShifts.EndShiftDate) AS EndShiftDate
, dtShifts.ShiftType
FROM
(
SELECT @StartDate AS StartShiftDate, @EndDate AS EndShiftDate, 'D' AS ShiftType
UNION ALL
SELECT @EndDate AS StartShiftDate, DATEADD(HH, 9, @EndDate) AS EndShiftDate, 'N' AS ShiftType
) dtShifts
CROSS JOIN #Work w
--cleanup
IF OBJECT_ID('Tempdb..#Work') > 0
DROP TABLE #Work
--You run the previous setup code only once. Then this is how you use it :
IF OBJECT_ID('Tempdb..#Demo') > 0
DROP TABLE #Demo
CREATE TABLE #Demo
(
Team VARCHAR(10) NOT NULL
, ComponentCode VARCHAR(10) NOT NULL
, AssemblyDate DATETIME NOT NULL
)
GO
CREATE CLUSTERED INDEX IX_Demo ON #Demo (AssemblyDate)
GO
INSERT INTO #Demo (Team, ComponentCode, AssemblyDate)
SELECT 'A', 'Comp1', '2007/02/02 08:00:00'
UNION ALL
SELECT 'A', 'Comp1', '2007/02/02 09:00:00'
UNION ALL
SELECT 'B', 'Comp1', '2007/02/02 10:00:00'
UNION ALL
SELECT 'A', 'Comp1', '2007/02/02 11:00:00'
UNION ALL
SELECT 'B', 'Comp1', '2007/02/02 12:00:00'
UNION ALL
SELECT 'A', 'Comp1', '2007/02/02 13:00:00'
UNION ALL
SELECT 'A', 'Comp1', '2007/02/02 14:00:00'
UNION ALL
SELECT 'A', 'Comp1', '2007/02/02 15:00:00'
UNION ALL
SELECT 'A', 'Comp1', '2007/02/02 16:00:00'
UNION ALL
SELECT 'A', 'Comp1', '2007/02/02 16:00:00'
UNION ALL
SELECT 'B', 'Comp1', '2007/02/02 17:00:00'
UNION ALL
SELECT 'A', 'Comp1', '2007/02/02 18:00:00'
UNION ALL
SELECT 'A', 'Comp1', '2007/02/02 19:00:00'
UNION ALL
SELECT 'A', 'Comp1', '2007/02/02 20:00:00'
UNION ALL
SELECT 'A', 'Comp1', '2007/02/02 21:00:00'
UNION ALL
SELECT 'A', 'Comp1', '2007/02/02 22:00:00'
UNION ALL
SELECT 'A', 'Comp1', '2007/02/02 23:00:00'
UNION ALL
SELECT 'A', 'Comp1', '2007/02/02 00:00:00'
UNION ALL
SELECT 'A', 'Comp1', '2007/02/03 01:59:00'
UNION ALL
SELECT 'A', 'Comp22', '2007/02/02 15:00:00'
UNION ALL
SELECT 'A', 'Comp22', '2007/02/02 16:00:00'
UNION ALL
SELECT 'A', 'Comp22', '2007/02/02 16:00:00'
UNION ALL
SELECT 'B', 'Comp22', '2007/02/02 17:00:00'
UNION ALL
SELECT 'A', 'Comp22', '2007/02/02 18:00:00'
UNION ALL
SELECT 'A', 'Comp22', '2007/02/02 19:00:00'
UNION ALL
SELECT 'A', 'Comp22', '2007/02/02 20:00:00'
UNION ALL
SELECT 'A', 'Comp22', '2007/02/02 21:00:00'
INSERT INTO #Demo (Team, ComponentCode, AssemblyDate)
SELECT TOP 75 PERCENT Team, ComponentCode, DATEADD(D, 1, AssemblyDate) FROM #Demo ORDER BY NEWID()
INSERT INTO #Demo (Team, ComponentCode, AssemblyDate)
SELECT TOP 75 PERCENT Team, ComponentCode, DATEADD(D, 2, AssemblyDate) FROM #Demo ORDER BY NEWID()
INSERT INTO #Demo (Team, ComponentCode, AssemblyDate)
SELECT TOP 75 PERCENT Team, ComponentCode, DATEADD(D, 3, AssemblyDate) FROM #Demo ORDER BY NEWID()
INSERT INTO #Demo (Team, ComponentCode, AssemblyDate)
SELECT TOP 75 PERCENT Team, ComponentCode, DATEADD(D, 4, AssemblyDate) FROM #Demo ORDER BY NEWID()
INSERT INTO #Demo (Team, ComponentCode, AssemblyDate)
SELECT TOP 75 PERCENT Team, ComponentCode, DATEADD(D, 5, AssemblyDate) FROM #Demo ORDER BY NEWID()
INSERT INTO #Demo (Team, ComponentCode, AssemblyDate)
SELECT TOP 75 PERCENT Team, ComponentCode, DATEADD(D, 6, AssemblyDate) FROM #Demo ORDER BY NEWID()
INSERT INTO #Demo (Team, ComponentCode, AssemblyDate)
SELECT TOP 75 PERCENT Team, ComponentCode, DATEADD(D, 7, AssemblyDate) FROM #Demo ORDER BY NEWID()
SELECT
D.Team
, D.ComponentCode
, S.ShiftType
, COUNT(*) AS Total
FROM #Demo D
INNER JOIN dbo.Shifts S
ON D.AssemblyDate >= S.StartShiftDate
AND D.AssemblyDate < EndShiftDate
GROUP BY
D.Team
, D.ComponentCode
, S.ShiftType
ORDER BY
D.ComponentCode
, S.ShiftType
, D.Team
IF OBJECT_ID('Tempdb..#Demo') > 0
DROP TABLE #Demo
-
Thanks Guys muchly apreciated
The Tables that I have are as such
Shift Roster Table
Day Shift Day Shift Night Shift Night Shift
Date Team Man Hrs Team Man Hrs
26/01/06 A 24 C 36
27/01/06 A 36 C 36
28/01/06 B 36 C 36
29/01/06 B 36 C 36
30/01/06 B 24 A 48
Component Table
Assemplby Component Assembled
Job code
JB1234 00091234 ComponentA 10/01/2007 04:24
JB1234 00091235 ComponentA 10/01/2007 16:37
JB1235 00091236 ComponentA 10/01/2007 16:24
JB1235 00091237 ComponentB 10/01/2007 18:24
JB1235 00091238 ComponentC 10/01/2007 16:24
Which dont link really at all (the component table was an existing table that cannot be modified) the roster table is a new table. The request came to correlate given the assembled date try to determine the Team and number of components they put together knowing.
-
Sorry forgot to mentio the desired output would be as such
Sorry Forgot to Mention the output should look as such
Team Shift Component #Assembled A N ComponentA 34 A N ComponentB 13 A N ComponentC 23 A N TIO2 12 A D XRF 34 A D XRFPRP 13 A D AAS1 23 A D TIO2 12 B N XRF 34 B N XRFPRP 13 B N AAS1 23 B N TIO2 12 B D XRF 34 B D XRFPRP 13 B D AAS1 23 B D TIO2 12 C N XRF 34 C N XRFPRP 13 C N AAS1 23 C N TIO2 12 -
So try adapting my last query to accomodate your actual tables. Tell us if you need further help after you tried that.
-
Cheers Ninja's RGR'us did that after I posted this
Works
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply