Criptic Query

[Leighton21]

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
B

 

[AlexCuse]

I think you can do this, as long as there is something to join the two tables on. Can you post some sample data from each table and your desired result? It will make things much easier, and eliminate a lot of back and forth over minor things.

Ignorance of certain subjects is a great part of wisdom

 

[SQLSister]

The key piece of information you need is which hours of the day belong to which shift.
Then you can do a union query. first select the compents form the day shift using the time component of the date to identify which components were done during the shift. Then union it ito a simliar query for the night shift.

To get the order you can then order the result set by team, shift, component

Questions about posting. See faq183-874

 

[Leighton21]

Thanks Guys muchly apreciated



The Tables that I have are as such



Shift Roster Table
Date Day Day Night Night
Team Man Hours Team Man Hours
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.

The output would be

Team Shift Component #Assembled
A N ComponentA 34
A N ComponentB 13
A N ComponentC 23
A D ComponentA 34
A D ComponentB 13
A D ComponentC 23
B N ComponentA 34
B N ComponentB 13

 

[AlexCuse]

Ok, this is made on the assumption that your Day Shift is 8-4, and nigh shift is 4-12. You can of course tweak the times all you want (but might need to use dateadd if it goes into next day. I don't know your data). I also am assuming you want this to be grouped by date, and that the teams are working on all components. So I'm not sure this will even work for you based on the information you've given.

Now that that's out of the way:

select a.[Date], a.[DayTeam] as Team, 'D' as Shift, 
b.[Component], b.[Assembled]
from [Shift_Roster] a
inner join
(select dateadd(day, datediff(day, 0, Assembled),0) as [Date], 
Component, Count(Component)
from Component
where datepart(hour, Assembled) between 8 and 16
group by dateadd(day, datediff(day, 0, Assembled),0), Component) b
on a.[Date] = b.[Date]

union select a.[Date], a.[DayTeam] as Team, 'N' as Shift, 
b.[Component], b.[Assembled]
from [Shift_Roster] a
inner join
(select dateadd(day, datediff(day, 0, Assembled),0) as [Date], 
Component, Count(Component)
from Component
where datepart(hour, Assembled) between 16 and 24
group by dateadd(day, datediff(day, 0, Assembled),0), Component) b
on a.[Date] = b.[Date]

order by Team, Component

(typed, not tested)

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Leighton21]

Cheers Alex