SQL Report Help




 Hi I'm dave and I'm new to the forum, SQL and SSRS. Thanks in advance for your assistance and patience.

I have a recall report that produces letters for every patient who needs to return to the office on a certain date based on their recall plan (a plan that determines when they should return based on their condition).

I have another table that stores all appointments for each patients (past and present). Appointments in the recall\_plans table are auto-generated whereas appointments in the appointments table where created manually. The recall plan report is not checked if a person calls in to make an appointment so often the same appointment is represented in both tables resulting in duplicate reminder letters being sent out.

I need to do two things: (I know my approach is not necessarily resolving the business problem but this is what I am tasked with)
1. I need to produce a list showing the next appointment for each patient but only if it is in the future.
2. I need to add a column to the first report showing each patient’s next appointment so someone can manually identify that duplicate letters would go out for specific patients and intervene accordingly.


Recall Report Query:

```
SELECT description as [Plan Name], per.first\_name + ' ' + per.last\_name as [Patient], substring (plan\_start\_date, 5,2) + '-' + substring (plan\_start\_date, 7,2) + '-' + substring (plan\_start\_date, 1,4) as [Plan Start Date], substring (nr.expected\_return\_date, 5,2) + '-' + substring (nr.expected\_return\_date, 7,2) + '-' + substring (nr.expected\_return\_date, 1,4) as [Expected Return Date] FROM recall\_plan\_mstr rp, patient\_recall\_plans nr, patient pt, person per WHERE rp.practice\_id = nr.practice\_id and rp.recall\_plan\_id = nr.recall\_plan\_id and nr.practice\_id = pt.practice\_id and nr.person\_id = pt.person\_id and per.person\_id = pt.person\_id and (active\_plan\_ind = 'Y') and rp.practice\_id = '0025
```

Recall Report Results:

```
PLAN NAME PATIENT START RETURN

OFFICE VISIT W/ DR Charles Span 04-18-2011 12-15-2011
```

Appointments Query:

```
select person\_id, appt\_date from appointments where person\_id is not null group by person\_id, appt\_date order by person\_id, appt\_date desc
```

Appointments Results:

```
073C8F83-CE15-4192-8E12-00006CB5A433 20091228 073C8F83-CE15-4192-8E12-00006CB5A433 20090510 073C8F83-CE15-4192-8E12-00006CB5A433 20090301 073C8F83-CE15-4192-8E12-00006CB5A433 20081006 378A281C-FAE7-43DF-BC03-00006E386680 20110509 378A281C-FAE7-43DF-BC03-00006E386680 20110217 378A281C-FAE7-43DF-BC03-00006E386680 20110124 378A281C-FAE7-43DF-BC03-00006E386680 20110111 378A281C-FAE7-43DF-BC03-00006E386680 20101207 816D4D31-3C99-4762-878D-000097883B73 20110316 816D4D31-3C99-4762-878D-000097883B73 20101216
```

Questions:
1. How can I produce a list from the appointments table that results with one patient per row with only the latest appointment that is in the future? Do I need to write a cursor for that?
2. How can I comingle this list into my recall report so it has a column to the right of return column that displays the patient’s next appointment date (future only)?


Both tables have a person number GUID.

I hope I have adequately explained and provided enough information. If any additional information is needed please don’t hesitate to ask.

Thanks, dave
Hi, Really hard to work this out from your query but I suspect the answer would be a correlated sub query to find the latest appointment date.



https://www.geeksforgeeks.org/sql-correlated-subqueries/
1) form controls use a list box for the names of the conference rooms;use datetimepicker for scheduling the reservation...set the format property to custom;set the customformat property to mm dd yyyy hh mm ssof course, will also need ok button2) dataset up the data file like this:room date time1st floor, 6/8/21, 14:00:002nd floor, 6/9/21, 10:00:003rd floor, 6/8/21, 13:30:00reads these records from the file to determine if there is a conflict.if the room is available at the requested time, writes the reservation tothe file (in append mode) and displays a message box saying the reservationis complete. otherwise display a message box saying the room is unavailableat that time. hi paul,

i believe the financial reporting covers in quite a lot of detail exactly what you are looking for.

from what i can tell you aren’t using templates in your tables…

[[image]](https://forum.enterprisedna.co/uploads/db7134/original/2x/5/559a333f85c159c8d9bec9235ca5c45997d08858.png "image")

if you want to get a certain order and custom table then you really need to use the template approach covered in t… let table = base.gettable("table 1")

let record = await input.recordasync("record", table)

await table.updaterecordasync(record.id, {

"single select": null

}) You need to iterate over the resource...

 
```
while ($row = mysql_fetch_object($tariff_query)) {
    echo $row->somefield;
    echo $row->someotherfield;
}

```
 So you now get all the records not just the first one.

 BTW, if you want to use an object, why not use PDO or mysqli? mysql is dead - or as good as. First you need to clear the call in the database. `$mysqli->query`
  




 (adsbygoogle = window.adsbygoogle || []).push({});
 
  

sent request before parameter binding, etc.


Replace as follows:



```
$stmt = $mysqli->prepare('SELECT reply\_id FROM replies WHERE reply\_topic = ? ORDER BY reply\_date ASC');
$stmt->bind_param('i',$param)
$stmt->execute();

        

        
        
        
 (adsbygoogle = window.adsbygoogle || []).push({});
 
        

    
```

 `$mysqli->prepare`
  




 (adsbygoogle = window.adsbygoogle || []).push({});
 
  

returns a new object for which you want to bind values ​​and execute them. It avoids [SQL injection](https://translate.googleusercontent.com/translate_c?depth=1&pto=aue&rurl=translate.google.com&sl=ru&sp=nmt4&tl=en&u=) !


None of this is discounted. `mysqli_`
  




 (adsbygoogle = window.adsbygoogle || []).push({});
 
  

is exactly what you should be using.



  




 (adsbygoogle = window.adsbygoogle || []).push({});
 
  

Since all you need is an array of results, you can use the following code to do this - no custom functions are required.



```
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
        $set[] = $row['reply\_id'];
}

        

        
        
        
 (adsbygoogle = window.adsbygoogle || []).push({});
 
        

    
```

For each row, `while`
  




 (adsbygoogle = window.adsbygoogle || []).push({});
 
  

each field is stored in an array with the column name as the key. Thus, `$row['reply_id']`
  




 (adsbygoogle = window.adsbygoogle || []).push({});
 
  

contains the value of a column `reply_id`
  




 (adsbygoogle = window.adsbygoogle || []).push({});
 
  

in the database. Above, I have stored this value in a named array `$set`
  




 (adsbygoogle = window.adsbygoogle || []).push({});
 
  

and when it iterates over the next row of the result, it also stores the next value for that array.


You can manipulate the result before or after saving it to a new array as you see fit.


Hope it helps. Lets say you want to send a table with a single column of GUIDs.


First we need to create a structure using [SqlMetaData](https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlmetadata(v=vs.110).aspx) which represents the schema of the table (columns).



```
var tableSchema = new List<SqlMetaData>(1)
{
  new SqlMetaData("Id", SqlDbType.UniqueIdentifier)
}.ToArray();

```

Next you create a list of records that match the schema using [SqlDataRecord](https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqldatarecord(v=vs.110).aspx).



```
var tableRow = new SqlDataRecord(tableSchema);
tableRow.SetGuid(0, Guid.NewGuid());
var table = new List<SqlDataRecord>(1)
{
  tableRow
};

```

Then create the [SqlParameter](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter(v=vs.110).aspx):



```
var parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.Structured;
parameter.ParameterName = "@UserIds";
parameter.Value = table;

var parameters = new SqlParameter[1]
{
  parameter
};

```

Then simply call the stored procedure by using the [Database.SqlQuery](https://msdn.microsoft.com/en-us/library/gg696545(v=vs.113).aspx).



```
IEnumerable<ReturnType> result;
using (var myContext = new DbContext())
{
  result = myContext.Database.SqlQuery<User>("GetUsers", parameters)
    .ToList();         // calls the stored procedure
    // ToListAsync();  // Async
{

```

In SQL Server, create your User-Defined Table Type (I suffix them with TTV, Table Typed Value):



```
CREATE TYPE [dbo].[UniqueidentifiersTTV] AS TABLE(
  [Id] [uniqueidentifier] NOT NULL
)
GO

```

Then specify the type as a parameter (don't forget, Table Type Values have to be readonly!):



```
CREATE PROCEDURE [dbo].[GetUsers] (
  @UserIds [UniqueidentifiersTTV] READONLY
) AS
BEGIN
  SET NOCOUNT ON

  SELECT u.* -- Just an example :P
  FROM [dbo].[Users] u
  INNER JOIN @UserIds ids On u.Id = ids.Id
END

``` you would need something like this then pull a list of people in that database after the ammount of time 
 /**
 * @event discordmessagereaction
 */
 $.bind('discordmessagereaction', function (event) {
 var reactionevent = event.getevent(),
 reactionuser = reactioneve… you might be able to use synchronize. if you have more than 2 tables, you will need to use it multiple times. i do not think there is a direct way to convert a baq to a ssrs report. however, it is not very difficult to use the baq to generate the sql (to use as the dataset) and then create the report from there. in epicor (in v10 at least, but i think 9 is similar), just open the baq and it should have the "query phrase" on the first tab. copy and paste this into a new dataset you will use in your report. if you have any parameters in epicor, these should be handled by ssrs when you create the dataset. if you are using a baq report in epicor or as part of a dashboard and using a separate filter you can add a where clause and use parameters.if you have not been using ssrs already there are some good resources for setting it up with epicor that can be found online.if you have epicor support you can use the education tab in epicweb (https://epicweb.epicor.com/), it has some courses for setting this up. i used this one recently when setting one up.http://blog.datixinc.com/blog/epicor-10-ssrs-report-setup-and-overview Get your attorney's office manager and your doctor's office manager on a conference call. Get this straightened out. 


This shouldnot be a problem of the patient/client! Lien letters (LOP) should be arranged between office staff. Come on now.


Perhaps a replacement .....