Site Patient Visit
Number Number Number
1 1 1
1 1 2
1 2 1
1 3 1
2 1 1
2 2 1
3 1 1
3 1 2
Site Number 1 has 2 visit 1 and 1 both (visit 1 and visit 2)
Site number 2 has 2 visit 1
site number 3 has 1 both (visit 1 and visit 2)
I am really confused as to how to accomplish this in one query. I can query it sep, but not together. Ultimately, I am...
AtCor
Last Activity: Jul 6 '07, 04:17 PM
Joined: Dec 12 '06
Location:
-
Complicated count w/ subquery
-
Non integer counting
1
2
3
* (unscheduled visit) (should be 3.01)
* (unscheduled visit) (should be 3.02)
Basically when there is an unscheduled visit, it should take the previous visit number and add .01
I am not sure how to count using non integers
Thanks -
I think I figured it out, there is an error in my raw data. I need to go back and recompile that. Your codes works excellent. Thanks for all of your help. -
Patient ID Date Time Operator Index
55100003 09NOV2006 09:17 91
55100003 09NOV2006 09:18 100
55100003 14NOV2006 10:43 97 (should be discarded)
55100003 14NOV2006 10:44 94 (should be discarded)Leave a comment:
-
I thought that I had corrected the problem. The only issue that I am having with the current code is that if there are more than 2 values in the 90's, it is keeping all of them. I am close though. Thanks for all of your help.Leave a comment:
-
filtering data correctly?
SELECT [Patient Identifier], Date, [Operator Index], Time
FROM (SELECT ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AS [Patient Identifier], ISNULL(t9.Date, t8.Date) AS Date, ISNULL(t9.Rows, t8.Rows)
AS Rows, c.[Operator Index], c.Time, ROW_NUMBER() OVER (PARTITION BY ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]),
ISNULL(t9.Date, t8.Date)
ORDER BY c.Time) AS RowNum
FROM (SELECT [Patient Identifier],... -
Here is the code I wrote and it is not correct although it appears to be correct at first. I was validating my data and discovered on several instances a value of 80 (something) is there instead of 90 (something).
SELECT [Patient Identifier], Date, [Operator Index], Time
FROM (SELECT ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AS [Patient Identifier], ISNULL(t9.Date, t8.Date) AS Date, ISNULL(t9.Rows,...Leave a comment:
-
I figured it out! Thanks!Leave a comment:
-
Patient Identifier Patient Initials Date Time Operator Index
0517_00003 GHV 18-Oct-06 11:48 91
0517_00003 GHV 18-Oct-06 11:50 100
0517_00004 JMH 17-Oct-06 11:41 89
0517_00004 JMH 17-Oct-06 11:50 93
0517_00004 JMH 17-Oct-06 11:52 91
0517_00004 JMH 17-Oct-06 12:00 93
0534_00003 JS 21-Nov-06 12:35 100
0534_00003 JS 21-Nov-06 12:46 100
0534_00004 ChM 20-Nov-06 10:49 100
0534_00004...Leave a comment:
-
filter data by row number
SELECT *
FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] <= 89) AS a
WHERE RowNum <= 4
UNION
SELECT *
FROM (SELECT [Patient Identifier], [Operator Index],... -
Yes, that is trueLeave a comment:
-
The patient test must be placed according to patient, and sorted by date (and time performed). My ultimate goal is to accept the first two tests above 90 or the first 4 tests above 80. If there are two 80's and then 2 90's, I want the 2 90's. Am I best off putting a row number in from of the 90's, a row number in front of the 80's and then accepting 2 90's or 4 80's? It is not working with a count function. I have a running row number, but that...Leave a comment:
-
using count to filter data
I am trying to filter data using count. For a given day and patient, I would like to return all the valid tests. When I use a count this way, it only returns the patients with at least 4 tests. I want it to display those patients that have 1, 2, 3, or 4 valid tests, but not if 5, 6 or .... we taken on a given day. I only need the first four valid tests. Any suggesions?
SELECT Sheet1$.[Patient ID]
FROM M_PWA... -
Yes, no problem with thatLeave a comment:
-
using a count to filter excess data
SELECT Sheet1$.[Trial ID] AS [Trail ID], CASE Sheet1$.[Trial ID] WHEN 'CSPP100A2344' THEN '0' END AS Extension, SUBSTRING(Sheet 1$.[Patient ID], 1, 4)
AS [Center Number], SUBSTRING(Sheet 1$.[Patient ID], 5, 8) AS [Patient Number], SUBSTRING(Sheet 1$.[Patient ID], 1, 4)
+ '_' + SUBSTRING(Sheet 1$.[Patient ID], 5, 8) AS [Patient Identifier], Sheet1$.[Trial ID] + '_' + SUBSTRING(Sheet 1$.[Patient...
No activity results to display
Leave a comment: