June 16, 2010 at 11:16 am
I should get data from Time column of temptime table where the time which does not come inside any range
i.e time data not in between '11:50' and '13:30' OR not between '04:20' and '05:50',but the catch is
if the active is 0 then the time data should display number greater than previous active 1 to before next active 1.
For example for range '04:20','06:00'
04:2041
04:5040
05:2040
05:5041
The output should be
04:30
05:00
05:30
Since for Ins_No 4 the active 1 is before 04:30 from data from temptime and 05:30 is before the 05:50 of Ins_No 4
for active 1.
June 16, 2010 at 11:36 am
Would you please clarify your requirement?
Where did you get the 30min steps from?
Please provide ready to use sample data as described in the the first link of my signature.
Also, please describe the scenarios you need to cover including sample data and expected result.
June 16, 2010 at 11:49 am
My final output from temptime should be
00:30 to 04:30 because there is no entry for any time between this range
06:30 to 09:00
11:30
14:00 to 17:30
21:30 to 23:30 all these are in the given range in FlowTimesheet table.
Other than this if there is a active 0 entry for a given Ins_no between two active 1 then that range also should come.
No need to think about Usp_InsertFlowtimesheet procedure I have already created only the retrival part is what I require.
June 16, 2010 at 11:57 am
Once upon a time there was a purple crocodile in a forest, he went to the office when the summer just started to open doors to the bus full of memories about the old books. Whenever he tried to drink on the road to the significant space program, we have done our best to get train to stop over the boarder of Mexico...
Sorry if my answer is not very clear...:-D
Seriously, your second post didn't help much in understanding of your problem. I've got you setup scripts (which you've attached in the first post), however I have failed to understand what you expect to get as result from it.
Sorry again...
June 16, 2010 at 1:37 pm
Given your test data, if I understand you correctly, there are only two contiguous time intervals in the 24-hour period where the Active flag was set (=1) between consecutive FlowTimesheet records (ordered by Time1) with the same values for Flow_ID and Ins_No.
1) Flow_Id = 1, Ins_No = 2, Time: 18:00 to 21:20
TempTime records to be omitted from result set:
18:00
18:30
19:00
19:30
20:00
20:30
21:00
2) Flow_Id = 1, Ins_No = 3, Time: 10:00 to 10:30
TempTime records to be omitted from result set:
10:00
10:30
So I think the result set should be:
Time
-----
00:00
00:30
01:00
01:30
02:00
02:30
03:00
03:30
04:00
04:30
05:00
05:30
06:00
06:30
07:00
07:30
08:00
08:30
09:00
09:30
11:00
11:30
12:00
12:30
13:00
13:30
14:00
14:30
15:00
15:30
16:00
16:30
17:00
17:30
21:30
22:00
22:30
23:00
23:30
(39 row(s) affected)
If we can safely assume that the Usp_InsertFlowtimesheet stored procedure inserts records in strict time order such that the ID identity column forms a contiguous sequence of integers with no gaps, then the following query will produce the above results:
SELECT [Time] FROM TempTime TT
WHERE NOT EXISTS (
SELECT * FROM FlowTimesheet F1
INNER JOIN FlowTimesheet F2
ON (F1.Flow_ID = F2.Flow_ID AND F1.Ins_No = F2.Ins_No AND F1.ID = F2.ID - 1)
WHERE (F1.Active = 1 AND F2.Active = 1)
AND (TT.[Time] BETWEEN F1.Time1 AND F2.Time1)
)
If the above assumption about the ID column is not safe, the query can be easily modified using ROW_NUMBER() to generate a guaranteed contiguous sequence of integers with which to perform the self join on the FlowTimesheet table.
June 17, 2010 at 12:48 am
The output which you have shown is correct except for these
09:30 & 11:00 should not come because for Ins_No 3 the active is 1
13:30 should not come because for Ins_No 1 the active is 1
June 18, 2010 at 4:22 am
In which case this amendment should fulfill your requirements:
SELECT TT.[Time] FROM TempTime TT
WHERE NOT EXISTS (
SELECT * FROM FlowTimesheet F1
INNER JOIN FlowTimesheet F2
ON (F1.Flow_ID = F2.Flow_ID AND F1.Ins_No = F2.Ins_No AND F1.ID = F2.ID - 1)
WHERE (F1.Active = 1 AND F2.Active = 1)
AND (TT.[Time] BETWEEN F1.Time1 AND F2.Time1)
)
AND NOT EXISTS (
SELECT * FROM FlowTimesheet
WHERE (Active = 1)
AND (TT.[Time] = Time1)
)
It would have been easier if you had given your expected output for the supplied test data in your original posts.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply