CODEWITHSUNDEEP
sqlsql-server-2005
Jessica
Jessica

Reputation: 239

SQL Query data issues

I have the following data:

ID       Date                 interval  interval_date            tot_activity  non-activity
22190   2011-09-27 00:00:00     1000    2011-09-27 10:00:00.000     265           15

I have another table with this data:

Date                        ID             Start                END               sched_non_activity     non_activity
10/3/2011 12:00:00 AM   HBLV-22267  10/3/2011 2:02:00 PM    10/3/2011 2:11:00 PM    540

Now, in the second table's non_activity field, I would like this to be the value from the first table. However, I need to capture the tot_activity - non_activity where the intervals(in 15 min increments) from the first table, fall in the same time frame as the start and end of the second table.

I have the following so far:

SELECT     1.ID, 1.Date, 1.interval, 1.interval_date, 1.tot_activity, 1.non_activity,
            1.tot_activity - 1.non_activity AS non_activity
FROM         table1 AS 1 INNER JOIN
                      LIST AS L ON 1.ID = L.ID INNER JOIN
                      table2 AS 2 ON 1.Date = 2.Date AND L.ID = Right(2.ID,5) 
Where 1.interval_date >= 2.Start AND 1.interval_date < 2.End

ORDER BY 1.ID, 1.interval_date

With this, I can already see I will be unable to capture if a start from table 2 is at 15:50, which means that I need to capture interval 15:45.

is there any way of doing this through queries, or should I be using variables, and doing the check per interval. Any help at all would be greatly appreciated.

Upvotes: 0

Views: 116

Answers (2)

James Johnson
James Johnson

Reputation: 46047

I'm having a tough time understanding your issue, but you might have better luck with the DATEDIFF function:

DATEDIFF(SECOND, 1.interval_date, 2.Start) >= 0 AND DATEDIFF(SECOND, 1.interval_date, 2.End) <= 0

I apologize if I'm not catching your drift. If I'm missing something, could you try to clarify a little bit?

Upvotes: 1

LoudNPossiblyWrong
LoudNPossiblyWrong

Reputation: 3893

I think you are asking too much from a query here.

What i would do is treat the two tables as lists ordered by time stamps and solve the problem programatically (ie not with a single query)

For example, create a function that traverses the first table in 15min increments and find the best match in the second table (i am guessing this is what you are trying to do). Implement your function to return the same results set as your query above or store it in a temporary table. Select from the result set. T-SQL is your friend :)

Upvotes: 1

Related Questions