select / join query?
-
Not sure which keywords to look up in BOL.
I have two tables. One has a record entered ONLY when there's a change in the juice being being made (two columns: timestamp, flavor). The second has records entered every minute (two columns: timestamp, speed of converyor belt).
How do I join the two tables to show speed and flavor for every minute of the day? Ex: At 10:37AM we made Orange juice at a rate of 37 cans per minute (three columns: timestamp, flavor, speed)?
Thanks!
-
can you post some sample data and the expected results (gonna be much easier that way)?.
-
No problem!
Table 1
7:27 Orange
10:53 Apple
13:47 Prune
Table2
7:35 37
7:36 39
7:37 38
...
11:56 42
11:57 42
...
14:12 23
14:13 26
Result Query
7:35 37 Orange
7:36 39 Orange
7:37 38 Orange
...
11:56 42 Apple
11:57 42 Apple
...
14:12 23 Prune
14:13 26 Prune
-
Where is the date kept?? That query becomes almost impossible without that.
-
Sorry, left the date out, but it's part of the timestamp column.
Table 1
08/24/05 7:27 Orange
08/24/05 10:53 Apple
08/24/05 13:47 Prune
Table2
08/24/05 7:35 37
08/24/05 7:36 39
08/24/05 7:37 38
...
08/24/05 11:56 42
08/24/05 11:57 42
...
08/24/05 14:12 23
08/24/05 14:13 26
Result Query
08/24/05 7:35 37 Orange
08/24/05 7:36 39 Orange
08/24/05 7:37 38 Orange
...
08/24/05 11:56 42 Apple
08/24/05 11:57 42 Apple
...
08/24/05 14:12 23 Prune
08/24/05 14:13 26 Prune
-
Are you trying to join two tables which do not actually have foreign keys by joining them based upon the timestamp being within, (not sure about this) one hour?
In other words Orange matches 08/24/2005 between the hours of 7 - 8 (am/pm?) and Apple matches on the same date between the hours of 11 - 12?
I wasn't born stupid - I had to study.
-
--use the datetime here.. assuming that you don't always change at the minute exactly
Declare @Types table (DateStart datetime not null, name varchar(25) not null, primary key clustered (DateStart, name))
Declare @Production table (DateProduction datetime not null, Qty tinyint not null, primary key clustered (DateProduction, Qty))
Insert into @Types (DateStart, name) values ('08/24/2005 7:27', 'Orange')
Insert into @Types (DateStart, name) values ('08/24/2005 10:53', 'Apple')
Insert into @Types (DateStart, name) values ('08/24/2005 13:47', 'Prune')
--Select * from @Types
Insert into @Production (DateProduction, Qty) values ('08/24/2005 7:35', 37)
Insert into @Production (DateProduction, Qty) values ('08/24/2005 7:36', 39)
Insert into @Production (DateProduction, Qty) values ('08/24/2005 7:37', 38)
Insert into @Production (DateProduction, Qty) values ('08/24/2005 11:56', 42)
Insert into @Production (DateProduction, Qty) values ('08/24/2005 11:57', 42)
Insert into @Production (DateProduction, Qty) values ('08/24/2005 14:12', 23)
Insert into @Production (DateProduction, Qty) values ('08/24/2005 14:13', 26)
--Select * from @Production
Select P.DateProduction, P.Qty, dtDateRanges.Name from @Production P inner join
(
Select T1.DateStart, ISNULL(MIN(T2.DateStart), GetDate() + 1) as DateEnd, T1.Name from @Types T1 left outer JOIN @Types T2 on T2.DateStart > T1.DateStart group by T1.DateStart, T1.Name
) dtDateRanges
on P.DateProduction >= dtDateRanges.DateStart and P.DateProduction < dtDateRanges.DateEnd
REMOVE THE +1 in GetDate() + 1. I added it so that the Prune data would be returned, but it's earlier than your data here so I wasn't getting anything.
-
Very impressive! This will take me a bit to figure out...
The LEFT OUTER JOIN is throwing me....
I wasn't born stupid - I had to study.
-
Because there's always one CURRENT production ('08/24/2005 13:47', Prunes) in this case. The self inner join would always miss the current production setting as there's nothing > than itself (assuming there's no data insered in advanced in that table). So I do a left join then isnull to GetDate() to get all the last rows.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply