SELECT DISTINCT
        ResponseArchive.MobileNumber AS MobileNo,
        ResponseText AS Message
        FROM
        Client.ResponseArchive
                INNER JOIN Client.MessageArchive
                ON ResponseArchive.MobileNumber = MessageArchive.MobileNumber
        WHERE ResponseDate >= '2013-03-01'
        AND DateArchived >= '2013-03-01'
        AND DateArchived <= '2013-03-31'      
        AND ResponseArchive.MobileNumber Like '%'

        WHERE ResponseDate >= '2013-03-01'
        AND DateArchived BETWEEN '2013-03-01' AND '2013-03-31'      

        WHERE 
        DateArchived BETWEEN '2013-03-01' AND '2013-03-31'      
        AND 
        ResponseDate >= '2013-03-01'

MobileNumber IS NOT NULL - ?

MobileNumber LIKE '3538%' - ?

SELECT DISTINCT TOP 5000 * FROM %NOTOPOPT( 
 SELECT 
 ResponseArchive.ResponseID, ResponseArchive.MobileNumber AS MobileNo, 
 ResponseText AS Message 
 ,ResponseDate 
 FROM Client.ResponseArchive 
 INNER JOIN Client.MessageArchive 
 ON ResponseArchive.MobileNumber = MessageArchive.MobileNumber 
 WHERE ResponseDate >= '2013-03-01' 
 AND MessageArchive.DateArchived BETWEEN '2013-03-01' AND '2013-03-31' 
 ) WHERE MobileNo LIKE '%' 
 order by MobileNo, ResponseDate 

SELECT DISTINCT TOP 5000 
 ResponseArchive.ResponseID, ResponseArchive.MobileNumber AS MobileNo, 
 ResponseArchive.ResponseText AS Message, 
 ResponseArchive.ResponseDate 
 FROM 
 %NOTOPOPT 
 Client.ResponseArchive 
 INNER JOIN Client.MessageArchive 
 ON ResponseArchive.MobileNumber = MessageArchive.MobileNumber 
 WHERE ResponseArchive.ResponseDate >= '2013-03-01' 
 AND MessageArchive.DateArchived BETWEEN '2013-03-01' AND '2013-03-31' 
 AND ResponseArchive.MobileNumber IS NOT NULL 
 order by ResponseArchive.MobileNumber, ResponseArchive.ResponseDate desc 

SELECT DISTINCT TOP 5000 
 ResponseArchive.ResponseID, ResponseArchive.MobileNumber AS MobileNo, 
 ResponseArchive.ResponseText AS Message, 
 ResponseArchive.ResponseDate 
 FROM 
 %NOTOPOPT 
 Client.ResponseArchive 
 INNER JOIN Client.MessageArchive 
 ON ResponseArchive.MobileNumber = MessageArchive.MobileNumber 
 WHERE ResponseArchive.ResponseDate >= '2013-03-01' 
 AND MessageArchive.DateArchived BETWEEN '2013-03-01' AND '2013-03-31' 
 AND ResponseArchive.MobileNumber [ '353' 
 order by ResponseArchive.MobileNumber, ResponseArchive.ResponseDate desc 

Class Client.ResponseArchive Extends (%Library.Persistent, %Library.Populate) [ ClassType = persistent, Not ProcedureBlock, StorageStrategy = "" ]
{

/// Response ID
Property ResponseID As %String(MAXLEN = 10, MINLEN = 0) [ Required ];

Index ResponseID On ResponseID;

/// Mobile Number
Property MobileNumber As %String(COLLATION = "EXACT", MAXLEN = 20, MINLEN = 0);

Index MobileNumber On MobileNumber;

/// Response Date
Property ResponseDate As %Date [ Required ];

Index ResponseDate On ResponseDate;

/// Response Time
Property ResponseTime As %Time [ Required ];

/// Response Text
Property ResponseText As %String(MAXLEN = 250, MINLEN = 0) [ Required ];

/// Response Comment
Property ResponseComment As %String(MAXLEN = 250, MINLEN = 0);

/// Mobile Number Operator ID
Property MNOID As %String(MAXLEN = 7, MINLEN = 0) [ Required ];

/// Response Actioned Flag
Property ResponseActionedFlag As %Boolean;

/// Response Review
Property ReviewedDate As %Date;

Property ReviewedTime As %Time;

Property ReviewedBy As %String;

Index Primary On (MobileNumber, ResponseID) [ IdKey, PrimaryKey ];

...
}

Class Client.MessageArchive Extends (%Library.Persistent, %Library.Populate) [ ClassType = persistent, Not ProcedureBlock, StorageStrategy = "" ]
{

/// Message ID
Property MessageID As %Integer(MAXVAL = 2000000000, MINVAL = 0) [ Required ];

Index RequestResponseID On RequestResponseID;

/// Message Text
Property MessageText As %String(MAXLEN = "") [ Required ];

/// Message Type
Property MessageType As %String(MAXLEN = 10, MINLEN = 0) [ Required ];

/// Mobile Number
Property MobileNumber As %String(COLLATION = "EXACT", MAXLEN = 20, MINLEN = 0);

Index MobileNumber On MobileNumber;

/// Date Message Sent
Property DateSent As %Date;

Index DateSent On DateSent;

/// Time Message Sent
Property TimeSent As %Time;

/// Template ID used
Property TemplateIDUsed As %String(MAXLEN = 20, MINLEN = 0) [ Required ];

Index TemplateIDUsed On TemplateIDUsed;

/// Date Message Queued
Property DateQueued As %Date [ Required ];

Index DateQueued On DateQueued;

/// Time Message Queued
Property TimeQueued As %Time [ Required ];

/// Queue ID
Property QueueID As %String(MAXLEN = 10, MINLEN = 0);

/// Issued By
Property IssuedBy As %String(COLLATION = "Exact", MAXLEN = 50, MINLEN = 0) [ Required ];

Index IssuedBy On IssuedBy;

/// Cancelled By
Property CancelledBy As %String(MAXLEN = 50, MINLEN = 0);

Index CancelledBy On CancelledBy;

/// Email Address
Property EmailAddress As %String(MAXLEN = 100, MINLEN = 0);

/// Request Response ID
Property RequestResponseID As %String(MAXLEN = 10, MINLEN = 0) [ Required ];

/// Request Status Code
Property RequestStatusCode As %String(MAXLEN = 5, MINLEN = 0) [ Required ];

Index RequestStatusCode On RequestStatusCode;

/// Response Error Text
Property RequestErrorText As %String(MAXLEN = 250, MINLEN = 0);

Index RequestErrorText On RequestErrorText;

/// Interface ID
Property InterfaceID As %String(MAXLEN = 30, MINLEN = 0);

Index InterfaceID On InterfaceID;

/// Customer Reference
Property CustomerReference As %String(MAXLEN = 100, MINLEN = 0);

/// Batch Reference
Property BatchReference As %String(COLLATION = "Exact", MAXLEN = 200, MINLEN = 0);

Index BatchReference On BatchReference;

/// Supplied Mobile Number
Property SuppliedMobileNumber As %String(MAXLEN = 30, MINLEN = 0);

/// Date Message was archived
Property DateArchived As %Date;

Index DateArchived On DateArchived;

/// Time Message was archived
Property TimeArchived As %Time;

/// Send message at date
Property SendOnDate As %Date;

/// Send message at time
Property SendAtTime As %Time;

/// Message Expiry Date
Property ExpiryDate As %Date;

/// Message Expiry Time
Property ExpiryTime As %Time;

/// Index DateArchived On DateArchived;
Index Primary On MessageID [ IdKey, PrimaryKey ];

/// Comments are to handle the PIN (Patient Internal Number)
Property Comments As %String(MAXLEN = 100, MINLEN = 0);

/// SMS Provider record which SMS Provider was used to send the message
Property SMSProvider As %String(MAXLEN = 30, MINLEN = 0);

...

}

Query rdSPResponses(StartDate As %Date, EndDate As %Date, MobileNumber As %String, BatchReference As %String, Department As %String, UserID As %String, Template As %String, MaxRec As %Integer) As %SQLQuery [ SqlProc ]
{
	
SELECT distinct top :MaxRec  
ResponseID, ResponseArchive.MobileNumber as MobileNumber,
ResponseDate,ResponseTime,
ResponseText,ResponseActionedFlag   
,{fn concat ( {fn concat (ContactSurname,', ')}, ContactName)} as FullName
,ReviewedBy
FROM %NOTOPOPT ResponseArchive inner join MessageArchive
on ResponseArchive.MobileNumber = MessageArchive.MobileNumber
 left outer join ContactDetails on ResponseArchive.MobileNumber = ContactDetails.ContactMobileNumber
 left outer join Users on MessageArchive.IssuedBy = Users.UserID and ResponseArchive.MobileNumber = MessageArchive.MobileNumber
  Where (DateArchived >= :StartDate) and 
  (DateArchived <= :EndDate) and 
  (ResponseDate >= :StartDate) and 
  (ResponseArchive.MobileNumber Like :MobileNumber) 
  and BatchReference Like :BatchReference
and Department Like :Department
  and IssuedBy Like :UserID
  and  RequestStatusCode =0
  and TemplateIDUsed like :Template
  order by ResponseArchive.MobileNumber, ResponseDate desc, ResponseTime desc
}

SELECT DISTINCT TOP 5000 
 ResponseArchive.MobileNumber AS MobileNo, 
 ResponseText AS Message, 
 ResponseDate 
 FROM 
 %NOTOPOPT 
 Client.ResponseArchive 
 INNER JOIN Client.MessageArchive 
 ON ResponseArchive.MobileNumber = MessageArchive.MobileNumber 
 WHERE ResponseDate >= '2013-03-01' 
 AND DateArchived >= '2013-03-01' 
 AND DateArchived <= '2013-03-31' 
 and (('%' is not null and ResponseArchive.MobileNumber Like '%') or ('%' is null and ResponseArchive.MobileNumber is not null)) 
 order by ResponseDate 

SELECT DISTINCT TOP 5000 
 ResponseArchive.MobileNumber AS MobileNo, 
 ResponseText AS Message, 
 ResponseDate 
 FROM 
 Client.ResponseArchive 
 INNER JOIN Client.MessageArchive 
 ON ResponseArchive.MobileNumber = MessageArchive.MobileNumber 
 WHERE ResponseDate >= '2013-03-01' 
 AND DateArchived >= '2013-03-01' 
 AND DateArchived <= '2013-03-31' 
 and (('%' is not null and ResponseArchive.MobileNumber Like '%') or ('%' is null and ResponseArchive.MobileNumber is not null)) 
 order by ResponseDate 

SELECT DISTINCT TOP 5000 
 ResponseArchive.MobileNumber AS MobileNo, 
 ResponseText AS Message, 
 ResponseDate 
 FROM 
 %NOTOPOPT 
 Client.ResponseArchive 
 INNER JOIN Client.MessageArchive 
 ON ResponseArchive.MobileNumber = MessageArchive.MobileNumber 
 WHERE ResponseDate >= '2013-03-01' 
 AND DateArchived >= '2013-03-01' 
 AND DateArchived <= '2013-03-31' 
 and ((NULL is not null and ResponseArchive.MobileNumber Like NULL) or (null is null and ResponseArchive.MobileNumber is not null)) 
 order by ResponseDate 

Index ResponseDate On ResponseDate [ Data = (MobileNumber, ResponseID) ];

Index ResponseDate On ResponseDate ;

Index ResponseDate On (MobileNumber, ResponseID);

Department Like '%'

((NULL is not null and Department = NULL) or (NULL is null and Department is not null))

Department Like 'DepartmentName%'

(('DepartmentName' is not null and Department = 'DepartmentName') or ('DepartmentName' is null and Department is not null))

SELECT Count(MessageArchive.MessageID)
FROM  Client.MessageArchive
WHERE (RequestStatusCode = 0)
AND (IssuedBy LIKE 'RADIOLOGY%')
AND (TemplateIDUsed LIKE '%')
AND (DateArchived BETWEEN '2013-03-25'AND '2013-03-26')

SELECT Count(MessageArchive.MessageID)
FROM Client.MessageArchive
WHERE (RequestStatusCode = 0)
AND (IssuedBy LIKE 'RADIOLOGY%')
AND (DateArchived BETWEEN '2013-03-25'AND '2013-03-26')