Per my client, when it creates the qualifier for 'HCPS-DEN"' it needs to create the qualifier as 'AD' not 'HC'. Currently, with the present update script its inserting 'HC' on all. I need it to say when the 'HCPS-DEN' is used, insert the 'AD' not 'HC'. Everything else should be the HC.
I tried using a CASE statement:
CASE WHEN Description = 'HCPC - DEN' THEN 'AD' ELSE 'HC' END
however I get this back:
Msg 207, Level 16, State 1, Line 10
Invalid column name 'Description'.
I was trying to plug this part in where HC is being entered. I think its missing an obvious Join or maybe there is a better way to write this. Description is being used after the first BEGIN. I know it needs to also go into my insert somehow, but I'm not sure how or if there is an easier way to do this. Any help is greatly appreciated!!
declare @qualid int
if 1=1
BEGIN
if not exists (select * from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN')
begin
declare @listorder int
select @listorder=max(listorder)+1 from medlists where tablename='ProcedureCodeQualifier'
insert into medlists
select 'ProcedureCodeQualifier',NULL,'HC','HCPC - DEN',@listorder,NULL,NULL,NULL,NULL,NULL,NULL,NULL,@listorder,getdate(),'CYSScript',getdate(),dbo.GetLogonId()
update medlists set dotid=medlistsid where medlistsid=scope_identity()
end
select @qualid=medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN'
update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (8634)
END
ELSE
BEGIN
select @qualid =(select top 1 medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description like 'Health Care Financing Administration Common Procedural Coding System (HCPCS) Codes%')
update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (8634)
END
Per my client, when it creates the qualifier for 'HCPS-DEN"' it needs to create the qualifier as 'AD' not 'HC' . Currently, with the present update script its inserting 'HC' on all. I need it to say when the 'HCPS-DEN' is used, insert the 'AD' not 'HC'. Everything else should be the HC.
I tried using a CASE statement: CASE WHEN Description = 'HCPC - DEN' THEN 'AD' ELSE 'HC' END
however I get this back:
Msg 207, Level 16, State 1, Line 10 Invalid column name 'Description'.
I was trying to plug this part in where HC is being entered. I think its missing an obvious Join or maybe there is a better way to write this. Any help is greatly appreciated!!
declare @qualid int if 1=1 BEGIN if not exists (select * from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN') begin declare @listorder int select @listorder=max(listorder)+1 from medlists where tablename='ProcedureCodeQualifier'
insert into medlists select 'ProcedureCodeQualifier',NULL,'HC','HCPC - DEN',@listorder,NULL,NULL,NULL,NULL,NULL,NULL,NULL,@listorder,getdate(),'CYSScript',getdate(),dbo.GetLogonId()
update medlists set dotid=medlistsid where medlistsid=scope_identity() end
select @qualid=medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN'
update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (8634) END ELSE BEGIN select @qualid =(select top 1 medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description like 'Health Care Financing Administration Common Procedural Coding System (HCPCS) Codes%') update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (8634) END
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005
1 -- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id
2 -- PK and Index select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id
This sql is extracting some sort of the information about the structure of the sql server database[2005] I need a sql whihc will return the same result for sql server 2000
You are troubleshooting a T-SQL stored procedure with a developer using SQL Server 2005. There are several update transactions and calls to other stored procedures within the stored procedure, and you suspect one of them is failing. You don€™t know which one. You don€™t want to heavily modify the stored procedure with error detection. What can you do to easily trap the error?
A stored procedure is called from an outwardly facing web application. The stored procedure uses dynamic SQL to retrieve data from a table. Is this a €œsafe€? thing to do? Why or why not?
A developer writes a T-SQL view that joins data from two tables. The code is as follows€¦
SELECT C.contactName, C.contactTelNo, F.fundManagerName, F.fundManagerTelNo FROM Contacts C, FundManagers F WHERE C.fundManagerId *= F.fundManagerId
When the view is executed, the developer notices differing result sets on a daily basis. Why?
I have successfully installed SQL Server 2005 SP1 but when I go to the "Microsoft Update" site it is still shown as required. I have tried going back to an old image of the PC and installing SQL Server 2005 and SP1 again but still the "Microsoft Update" page is not picking up the fact that SP1 has installed successfully. Anyone had the same problem or know of a fix?
I need to update a column(X) in database A.tableA with a column(Y) from database B.tableB where the value in another column(Z) in A.tableA = the value in a column(W) in B.tableB
I am having a heck of a time getting an UPDATE statement to work. Can anyone point out what it is I'm doing wrong? Here is my statement...... strSQL = "UPDATE tbl-Pnumber_list SET Project_Title = 'success' WHERE ID = @IDParam" Thanks! Eugh
Hello, I'm new to the site, I've been trying to find help and i hope this is the right place.
In my MySQL database i have a table 'A' with a colum called 'Description'. Inside 'Description' there is some text and a link to a page. I have over 2500 Description fields and all have the same link inside but i need to change that link to a new one and i dont know how to update the description column with the new link. I know how to change just everything in it but its like this:
text text text text link
so how do i just change the link in the description column.
I need to issue an update statement to the ChronicOther field. If the range below is in the AnyDx field, then I want the ChronicOther field to say "Chronic", if not then I want it to say "Other".
where AnyDx between '25000' and '25091'
or AnyDx between '41400' and '41406'
or AnyDx between '42800' and '42890'
or AnyDx between '40100' and '40291'
or AnyDx between '34200' and '34292'
or AnyDx between '14000' and '19910'
or AnyDx between '49600' and '49600'
or AnyDx between '27240' and '27240')
My problem is what is the best way to look for this range (below) in the AnyDx field without multiple like statements?
example: Update reports.MDC_DRG SET ChronicOther = CASE When AnyDx like ('%,25000,%') Then 'Chronic' Else 'Other' etc., etc.....
I have my database stored in the sqlserver 2005. Using the table name i am retrieving the table and it is displayed to the user in the form of datagridview.I am allowing the user to modify the contents of the table, including the headers. Is it possible for me to update the table straightway rather than giving a sql update command for each and every row of the table .
Hello, I am trying to update a table based on values that I have imported into a temporary table. Basically I have a list of lab codes (EMR_temp_labtest_configupdate) and each lab has a zseg code tied to it. The definitions for zseg code are in a separate table called (EMR_zseg_code_descriptions)
I need to update the lab_test_add_conf to add in each lab code that does not have any configuration information (not exists in lab_test_add_conf) based on the zsegcode.
For example a zsegcode (defined in the emr_zseg_code_descriptions table) is ZBL and the lab code 003277 fits into the zseg category according to the temp table. For each lab code that first into the ZBL category a row needs to be inserted for
I want to look at the data in the temp table and determine which category it is in and then insert into the lab_test_add_conf table a row for each lab test each zseg table value code that exists.
My Final Goal: lab_test_add_conf:
lab test code, valuecode 003277, PATRAC 003277, HERITG
I know I need to do an update statement but I am not sure how to set up the SET statement or if there is anythign that I need to take into consideration. Here is my code so far.....any thoughts?
select a.labtestcode from EMR_temp_labtest_configupdate a Where Not Exists (Select * From lab_test_add_conf b where a.labtestcode = b.labtest_key)
update table lab_test_add_conf
select a.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units, b.tablename,b.fieldname from EMR_temp_labtest_configupdate a join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
Hi AllI am updating a local table based on inner join between local tableand remote table.Update LocalTableSET Field1 = B.Field1FROM LinkedServer.dbname.dbo.RemoteTable BINNER JOIN LocalTable AON B.Field2 = A.Field2AND B.Field3 = A.Field3This query takes 18 minutes to run.I am hoping to speed up the process by writing in OPENQUERY syntax.ThanksRS
I am new to SQL server, but I need to develop a "big" application with it. Here's my application requirements:
1)Create a table, say TableA, in SQL server 7 or 2000 database,say DB1, based on another Oracle database,say DB2, on a remote server. Update TableA based on DB2 every day in some specific time. 2) If new records are inserted into the TableA,some other tables needed to update in DB1. At the same time, an e-mail will send to three receipts.
I have no idea of which function or method I can use to fulfill the requirements. Can any experts here give me some advice, detail explanation would be grateful.
I have a situation where my Visual C# application presents a number of fields. In order to update a student object, I wish to call a stored proc. 1 or more fields can be updated... And If one is left null, then I don't want to update it, but instead I want to keep the old value.
I am really wondering if I am approaching this the right way. The following stored proc does what I want it to do, however I'm thinking there may be a faster way...
Here it is:
-- Update a student, by ID.
DROP PROCEDURE p_UpdateStudent
CREATE PROCEDURE p_UpdateStudent
@ID INT,
@NewFName VARCHAR(25),
@NewOName VARCHAR(25),
@NewLName VARCHAR(25),
@NewDOB DATETIME,
@NewENumber VARCHAR(10),
@NewContactAID INT,
@NewContactBID INT
AS
BEGIN
SET NOCOUNT ON;
-- DECLARE THE OLD VALUES
DECLARE @FName AS VARCHAR(25)
DECLARE @OName AS VARCHAR(25)
DECLARE @LName AS VARCHAR(25)
DECLARE @DOB AS DATETIME
DECLARE @ENumber AS VARCHAR(10)
DECLARE @ContactAID AS INT
DECLARE @ContactBID AS INT
-- Get all of the old values
SELECT @FName = FName FROM TBL_Student WHERE ID = 10000
SELECT @OName = OName FROM TBL_Student WHERE ID = 10000
SELECT @LName = LName FROM TBL_Student WHERE ID = 10000
SELECT @DOB = DOB FROM TBL_Student WHERE ID = 10000
SELECT @ENumber = ENumber FROM TBL_Student WHERE ID = 10000
SELECT @ContactAID = ContactAID FROM TBL_Student WHERE ID = 10000
SELECT @ContactBID = ContactBID FROM TBL_Student WHERE ID = 10000
-- USE ISNULL to set all of the new parameters to the provided values only if they are not null
-- Keep the old ones otherwise.
SET @NewFName = ISNULL(@NewFName, @FName)
SET @NewOName = ISNULL(@NewOName, @OName)
SET @NewLName = ISNULL(@NewLName, @LName)
SET @NewDOB = ISNULL(@NewDOB, @DOB)
SET @NewENumber = ISNULL(@NewENumber, @ENumber)
SET @NewContactAID = ISNULL(@NewContactAID, @ContactAID)
SET @NewContactBID = ISNULL(@NewContactBID, @ContactBID)
-- Do the update
UPDATE TBL_Student
SET FName = @NewFName,
OName = @NewOName,
LName = @NewLName,
DOB = @NewDOB,
ENumber = @NewENumber,
ContactAID = @NewContactAID,
ContactBID = @NewContactBID
WHERE
ID = @ID
END
GO
So yeah it works. But As you can see I wish to keep an old copy of the values to perform checks pre update....
Is there any faster way, or am I on the right track? I need a pro's advice :) (before i write all of my procs!!)
I just want to display all the data of a Single table into a Data Grid, I know that we can drag and drop the table on to a form and datagrid is generated, but here I want to retrive those values through my code, how should i do that
I am getting following errors while running the program Error 1) Error No. 28037, MS SQL Server 2005 Evrywhere Edition Error: A request to send data to the computer running IIS has failed. For more information see HRESULT Error 2) Error No. 0, SQL Server 2005 Evrywhere Edition ADO.Net Data Provider Error: The specified table does not exist [ JobLists ].
Can anybody please tell me, where I went wrong ??? In this code anywhere else????
Note: While adding a Data Source of SQL Server 2005 Mobile Edition, I have added that .sdf file into my project, thats why I have written the Data Source as : .DbFile.sdf
@"Data Source = .DbDotNetCF.sdf";
The code is as follows:
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.SqlServerCe;
namespace DeviceApplication1 { public partial class Form1 : Form { string filename = @".DbDotNetCF.sdf";
private DataSet dsJobLists;
public Form1() { InitializeComponent(); }
private void DeleteDB() { if (System.IO.File.Exists(filename)) { System.IO.File.Delete(filename); } }
private void Sync() { SqlCeReplication repl = new SqlCeReplication();
if (DbDotNetCFDataSetUtil.DesignerUtil.IsRunTime()) { // TODO: Delete this line of code to remove the default AutoFill for 'dbDotNetCFDataSet.JobLists'. this.jobListsTableAdapter.Fill(this.dbDotNetCFDataSet.JobLists); } } } }
I have created a merge replication correctlly( I suppose, there were no errros) Please help
Help - I need to import data into an existing table. Most import rows were unique, so I had no problem using DTS and appending. However, some import rows match existing rows except for one column/field that contains updated/new data, and I have to either replace the entire row with the imported row, or replace the individual field with the new data. How do I do that when there are many rows to import? It would take forever typing in all the data using UPDATE. Thanks in advance for your help!
I have been building a database in SQL Server Express for some months now using the Windows level authentication login that has given me full access to everything in the database.
The time has now come where I need to create user accounts and grant permissions to specific stored procedures and I'm having trouble doing this.
Can anyone give me a brief rundown of the required T-SQL commands I need to set up a user account that can do nothing but run stored procs (not the system procs which apparently are being discontinued: http://msdn2.microsoft.com/en-us/library/ms182795.aspx).
So far I have:-
CREATE LOGIN db_test_user WITH PASSWORD = 'eXaMpL3Pwd USE db_new CREATE USER db_test_user
The above code executes successfully but when I try and connect (using Management Studio) I get error message 'The user is not associated with a trusted SQL Server connection (error 18452)' which means little to me.
I also tried creating a 'WITHOUT LOGIN' user for the database but could not figure out how to give it a password.
I don't know (/understand) roles/schemas and don't know if I really need them as I only need user access to specific stored procs. I don't ever want them to see the tables for example so they only need the most restricted access.
We have just updated our sql 2005 server with sql SP 1. Should we apply this service pack to the client boxes that access this server or is that unnecessary?
System.Data.SqlClient.SqlException was unhandled Class=20 ErrorCode=-2146232060 LineNumber=0 Message="An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)" Number=2 Server="" Source=".Net SqlClient Data Provider" State=0
Hi guys, I have just set up a database in SQL 2005 Express Edition. However, I have a problem accessing it with the application program created in VB 2005 EE. Before using SQL 2005, the application worked perfectly in SQL 2000. Here is my connection string used in SQL 2000:
Dim sqlConn As New SqlConnection("Data Source=(local); Database='Inventory List'; Integrated Security=yes")
Is this valid in SQL 2005 Express Edition? Or should I change the connection string instead? Thank you very much.
I have two tables t_DTM_DATA_STAGING around 2 million records t_DTM_DATA around 251 million records
The below SQL statement looks for records in the t_DTM_DATA_STAGING table that are not in the t_DTM_DATA table and adds them to a 3rd table. (t_DTM_DATA_STAGING2)
This statement has been running fine for weeks, but now it seems to get hung every day. I ran sp_Who2 and it says the status is runnable. I let it run for around 5 or 6 hours the other day to see if it will finish but it didn't. This SQL job is step 3 in a 6 step SQLAgent job that usually finishes in 30 to 45 minutes.
I'm not sure how to troubleshoot this problem. No other jobs are running at the time this job runs.
Could this SQL statement be written a better way?
Thanks for any help anyone can provide.
Jerid
SET QUOTED_IDENTIFIER ON
INSERT INTO [DTM].[dbo].[t_DTM_DATA_STAGING2] ([CP],,[MAJ],[MINR],[LOCN],[DPT],[YEAR],[PD],[WK],[TRDT],[SYSTEM],[AMOUNT],[DESCRIPTION],[GROUP],[VENDOR] ,[INVOICE],[IDAT],[PO_NUMBER],[DDAT],[RCV#],[RDAT],[RSP],[EXPLANATION],[UPLOAD_DATE],[UPLOAD_USER],[UPLOAD_NAME] ,[RELEASE_DATE],[RELEASE_USER],[RELEASE_NAME],[TRTM]) SELECT t_DTM_DATA_STAGING.CP, t_DTM_DATA_STAGING.CO, t_DTM_DATA_STAGING.MAJ, t_DTM_DATA_STAGING.MINR, t_DTM_DATA_STAGING.LOCN, t_DTM_DATA_STAGING.DPT, t_DTM_DATA_STAGING.YEAR, t_DTM_DATA_STAGING.PD, t_DTM_DATA_STAGING.WK, t_DTM_DATA_STAGING.TRDT, t_DTM_DATA_STAGING.SYSTEM, t_DTM_DATA_STAGING.AMOUNT, t_DTM_DATA_STAGING.DESCRIPTION, t_DTM_DATA_STAGING.[GROUP], t_DTM_DATA_STAGING.VENDOR, t_DTM_DATA_STAGING.INVOICE, t_DTM_DATA_STAGING.IDAT, t_DTM_DATA_STAGING.PO_NUMBER, t_DTM_DATA_STAGING.DDAT, t_DTM_DATA_STAGING.RCV#, t_DTM_DATA_STAGING.RDAT, t_DTM_DATA_STAGING.RSP, t_DTM_DATA_STAGING.EXPLANATION, t_DTM_DATA_STAGING.UPLOAD_DATE, t_DTM_DATA_STAGING.UPLOAD_USER, t_DTM_DATA_STAGING.UPLOAD_NAME, t_DTM_DATA_STAGING.RELEASE_DATE, t_DTM_DATA_STAGING.RELEASE_USER, t_DTM_DATA_STAGING.RELEASE_NAME, t_DTM_DATA_STAGING.TRTM FROM t_DTM_DATA_STAGING LEFT OUTER JOIN t_DTM_DATA AS t_DTM_DATA_1 ON t_DTM_DATA_STAGING.TRTM = t_DTM_DATA_1.TRTM AND t_DTM_DATA_STAGING.TRDT = t_DTM_DATA_1.TRDT AND t_DTM_DATA_STAGING.PD = t_DTM_DATA_1.PD AND t_DTM_DATA_STAGING.YEAR = t_DTM_DATA_1.YEAR AND t_DTM_DATA_STAGING.DPT = t_DTM_DATA_1.DPT AND t_DTM_DATA_STAGING.LOCN = t_DTM_DATA_1.LOCN AND t_DTM_DATA_STAGING.MINR = t_DTM_DATA_1.MINR AND t_DTM_DATA_STAGING.MAJ = t_DTM_DATA_1.MAJ AND t_DTM_DATA_STAGING.CO = t_DTM_DATA_1.CO AND t_DTM_DATA_STAGING.CP = t_DTM_DATA_1.CP WHERE (t_DTM_DATA_1.CP IS NULL)
I ma using sql server 2005.I have a bunch of statements of sql and i have created a stored procedure for those. When i execute i found that there is lot's of difference between execution time of stored procedure and direct sql in query windows.
can anyone help me to optimize the execution time for stored prcedure even stored prcedure is very simple. I have used sql server 2000 and i am new in sql server 2005.
I am getting the following error in SQL2005. I need some assistance with adding in a Subquery.
Msg 207, Level 16, State 1, Line 33 Invalid column name 'LastVisitDate'. Msg 207, Level 16, State 1, Line 34 Invalid column name 'LastVisitDate'.
/* Patient List*/ SET NOCOUNT ON
DECLARE @Zip varchar(40) SELECT @Zip = LTRIM(RTRIM('NULL')) + '%'; WITH cteMedlitsPatientStatus AS ( SELECT * FROM Medlists WHERE TableName = 'PatientProfileStatus' )
SELECT PatientID, RespSameAsPatient=isnull(PatientSameAsGuarantor,0), PatientName=CASE WHEN RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) <> '' THEN RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) + ', ' + ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) ELSE RTRIM(ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) END, PatientAddr1=pp.Address1, PatientAddr2=pp.Address2, PatientCity=pp.City, PatientState=pp.State, PatientZip=pp.Zip, PatientRespName=CASE WHEN RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) <> '' THEN RTRIM(RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) + ', ' + ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,'')) ELSE RTRIM(ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,'')) END, PatientRespAddr1=pr.Address1, PatientRespAddr2=pr.Address2, PatientRespCity=pr.City, PatientRespState=pr.State, PatientRespZip=pr.Zip, FinancialClass=isnull(ml.Description,'none'), Doctor=df.ListName,Facility=df1.OrgName,Balance=isnull(ppa.PatBalance,0)+isnull(ppa.InsBalance,0), pp.DeathDate, Status = ml1.Description, pp.BirthDate, (select top 1 visit from patientvisit pv where LastVisitDate >= ISNULL(NULL,'1/1/1900') and LastVisitDate < dateadd(d, 1,ISNULL(NULL,'1/1/3000')) AND pp.patientprofileid = pv.PatientProfileID and datediff(day, getDate(), visit) < 0 order by visit desc) as LastVisitDate
FROM PatientProfile pp LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileID = ppa.PatientProfileID LEFT JOIN Guarantor pr ON pp.GuarantorID = pr.GuarantorID LEFT JOIN MedLists ml ON pp.FinancialClassMID = ml.MedListsID LEFT JOIN DoctorFacility df ON pp.DoctorID = df.DoctorFacilityID LEFT JOIN DoctorFacility df1 ON pp.FacilityId = df1.DoctorFacilityID LEFT JOIN cteMedlitsPatientStatus ml1 ON pp.PatientStatusMId = ml1.MedlistsId
As posted previously, thank you in advance... --------------------------------------------
We have a reverse proxy for rs 2000 -> a client requests a report, it (the proxy) then goes out to the rs box, gets the report, encrypts any return urls and feeds the modified html to the requesting client. I understand this isn't necessary anymore with rs 2005 due to the architecture. Question is, when I use the ReportExecutionService.Render method it is still returning the parameters for the report, and not the ReportSession, ControlID, Controller, etc. parameters which hides the actual return values on the href links of the report.
Documentation is plentiful for rs 2005, but examples are not. Can someone please explain to me if using the new features in rs 2005 to hide the parameter values from the users is possible via web request? Making the parameter values completely and entirely (even via sniffer) is absolutely a must (which is why we are currently encrypting return URL's).
try { string extension; string encoding; string mimeType; Warning[] warnings = null; string[] streamIDs = null; byte[] result = rs.Render (sFormat, sDeviceSettings, out extension, out mimeType, out encoding, out warnings, out streamIDs); string d = System.Text.Encoding.ASCII.GetString(result, 0, result.Length); HttpContext.Current.Response.Write (d); } catch { // do stuff }
My code is returning: http://henneseyjm1/ReportServer$sql2005?%2fJH.RSReporting%2fBAG&cy_start_date=1%2f1%2f2006&cy_end_date=3%2f1%2f2006&region=RG20&entity_num=nothing&proc_ctr=nothing&office_num=nothing&render_format=htm&view_name=standard&group_id=0&server_name=http%3a%2f%2flocalhost%2fJHnet%2f&user_is_office=False&rs%3aParameterLanguage=&rc%3aParameters=Collapsed&rc%3aToolbar=False
Where I would like it to return: http://localhost/Reports$sql2005/Reserved.ReportViewerWebControl.axd?ReportSession=iyvsxg45vhzwd2acii3jj4q4&ControlID=de367546-919a-4f67-be4d-cd2747166dca&Culture=1033&UICulture=9&ReportStack=1&OpType=ReportArea&Controller=ClientControllerctl161&PageNumber=1&ZoomMode=Percent&ZoomPct=100&ReloadDocMap=true&EnableFindNext=False&LinkTarget=_top
In my Database, I do not have a [Last Visit Date]. I have had to pull it by doing the following:
(select top 1 visit from patientvisit pv where visit >= ISNULL(NULL,'1/1/1900') and visit < dateadd(d, 1,ISNULL(NULL,'1/1/3000')) AND pp.patientprofileid = pv.PatientProfileID and datediff(day, getDate(), visit) < 0 order by visit desc) as [Last Visit Date]
My client would like to have a listing of patients with a visit within the past 2 years and without a visit in the past 2 years. What I would like to do is have a case statement that evaluates like:
Case When dateadd(y,[LastVisitDate],getdate())<2 then 'Less Than 2' When dateadd(y,[LastVisitDate],getdate())>=2 then '2 or more' else 'No detected visit' END
So basically, either your in 2 yrs or your not.
My Current Query:
/* Patient List*/ SET NOCOUNT ON
DECLARE @Zip varchar(40) SELECT @Zip = LTRIM(RTRIM('NULL')) + '%'; WITH cteMedlitsPatientStatus AS ( SELECT * FROM Medlists WHERE TableName = 'PatientProfileStatus' )
SELECT PatientID, RespSameAsPatient=isnull(PatientSameAsGuarantor,0), PatientName=CASE WHEN RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) <> '' THEN RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) + ', ' + ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) ELSE RTRIM(ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) END, PatientAddr1=pp.Address1, PatientAddr2=pp.Address2, PatientCity=pp.City, PatientState=pp.State, PatientZip=pp.Zip, PatientRespName=CASE WHEN RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) <> '' THEN RTRIM(RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) + ', ' + ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,'')) ELSE RTRIM(ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,'')) END, PatientRespAddr1=pr.Address1, PatientRespAddr2=pr.Address2, PatientRespCity=pr.City, PatientRespState=pr.State, PatientRespZip=pr.Zip, FinancialClass=isnull(ml.Description,'none'), Doctor=df.ListName,Facility=df1.OrgName,Balance=isnull(ppa.PatBalance,0)+isnull(ppa.InsBalance,0), pp.DeathDate, Status = ml1.Description, pp.BirthDate, (select top 1 visit from patientvisit pv where visit >= ISNULL(NULL,'1/1/1900') and visit < dateadd(d, 1,ISNULL(NULL,'1/1/3000')) AND pp.patientprofileid = pv.PatientProfileID and datediff(day, getDate(), visit) < 0 order by visit desc) as [Last Visit Date]
FROM PatientProfile pp LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileID = ppa.PatientProfileID LEFT JOIN Guarantor pr ON pp.GuarantorID = pr.GuarantorID LEFT JOIN MedLists ml ON pp.FinancialClassMID = ml.MedListsID LEFT JOIN DoctorFacility df ON pp.DoctorID = df.DoctorFacilityID LEFT JOIN DoctorFacility df1 ON pp.FacilityId = df1.DoctorFacilityID LEFT JOIN cteMedlitsPatientStatus ml1 ON pp.PatientStatusMId = ml1.MedlistsId
The Folowing code is not working anymore. (500 error)
Set objRS = strSQL1.Execute strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID) objRS.Open strSQL1, objConn , 2 , 3 , adCmdText If not (objRS.BOF and objRS.EOF) Then objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1 objRS.update End If objRS.Close
The .execute Method works fine
strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID) objConn.Execute strSQL1
