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
Below is my table structure:
CREATE TABLE [reports].[MDC_DRG](
[InsDrgMdc] [nvarchar](50) NULL,
[InsDrgMdcDesc] [nvarchar](50) NULL,
[MDC] [nvarchar](100) NULL,
[Drg] [nvarchar](50) NULL,
[DrgDesc] [nvarchar](255) NULL,
[AnyDx] [nvarchar](100) NULL,
[Cases] [decimal](18, 0) NULL,
[IntOOS] [int] NOT NULL,
[ChronicOther] [nvarchar](50) NULL
) ON [PRIMARY]
Here is a sample select from the AnyDx field:
AnyDx
,85225,85225,5304 ,5181 ,80501,E8190,3485,
,1983 ,1983 ,49121,1625 ,34550,79381,78079,30001,V1,
,2396 ,2396 ,78039,311,
,33390,33390,31401,49390,
,3410,3410,2768,3051,72293,V146,V4579,V1041,
,43491,43491,25000,4019 ,V1581,
,43491,43491,25000,4919 ,3569 ,4019 ,72252,78650,71,
,43491,43491,2768,30590,34290,4019,V146,7845,2724,2,
,43491,43491,34290,40290,25000,7843,7872,41401,4278,
,43491,43491,412 ,25000,41401,4019 ,2724 ,29680,V1,
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.....
Hi.. I need help with this SQL statement. I don't know if this can be done with a single SQL statement or not!
I have two tables:
tblProducts: ProductID, ProductName
tblOrders: OrderID, ProductID
I need to "SELECT tblProducts.ProductName FROM tblProducts WHERE (tblProducts.ProductID is not in tblOrders Table)".
Thanks in advance...
Hi,
Table A
member_no - character 5course1 - character 1course2 - character 1
Table Bcourse_no - character 1course_name - character 20I'd like a resultant view that contains A.member_no, A.course1, B.coursename as COURSE1NAME, A.course2, B.course_name as COURSE2NAMEI know how to make the join and pick up the name for course1 but I DON'T know how to pick up both names of course 1 and course 2 at the same time.I'm thinking I have to make a join on one column and then join the result of that a second time to pick up the other column's value?
Thanks in advance for any assistance,Stewart
Hi,
I am trying to insert the follows rows to my production database... and this the sample data
Row Plan PART_ID FUND_ID TOT_ACT1 TOT_ACT2
Number Num
1170925 129602759 19765P471 BB4928.47 CT0.00 DV26.30 GL153.75 TF0.00 WD0.00 OT0.00 EB5108.52 205.0110 24.04 206.0720 24.79
2170925 129602759 35472P406 BB2663.64 CT325.00 DV87.46 GL26.42 TF530.92 WD0.00 OT0.00 EB3633.44 189.0450 14.09 254.6210 14.27
3170925 129602759 LOAN BB1506.88 CT0.00 DV25.48 GL0.00 TF-530.92 WD0.00 OT0.00 EB1001.44 1506.88 1.00 1001.44 1.00
4170925 148603737 19765L587 BB25.14 CT0.00 DV0.46 GL-0.45 TF0.00 WD0.00 OT0.00 EB25.15 5.3830 4.67 5.4790 4.59
5170925 148603737 19765P471 BB7.48 CT0.00 DV0.05 GL0.23 TF0.00 WD0.00 OT0.00 EB7.76 0.3110 24.04 0.3130 24.79
6170925 148603737 35472P208 BB12.53 CT0.00 DV0.28 GL0.09 TF0.00 WD0.00 OT0.00 EB12.90 0.9360 13.39 0.9570 13.48
7170925 148603737 35472P604 BB7.48 CT0.00 DV0.24 GL0.15 TF0.00 WD0.00 OT0.00 EB7.87 0.4720 15.85 0.4870 16.16
8170925 148603737 315805549 BB29.72 CT0.00 DV0.00 GL2.15 TF0.00 WD0.00 OT0.00 EB31.87 1.5320 19.40 1.5320 20.80
9170925 148603737 197199102 BB5.00 CT0.00 DV0.06 GL0.27 TF0.00 WD0.00 OT0.00 EB5.33 0.1650 30.32 0.1670 31.94
So the number of rows in this table is 1007 right now my insert query inserts all the data but excepts LOAN and i want Loans inserted in a seperate column in my production dataabse but thats not happening so can some one pls take a look at this query and see whats wrong... My query is as follows1 INSERT INTO Statements..ParticipantPlanFundBalances1
2 (
3 PlanId,
4 ParticipantId,
5 PeriodId,
6 FundId,
7 Loans,
8 --PortfolioId,
9 Act1,
10 TotAct1,
11 Act2,
12 TotAct2,
13 Act3,
14 TotAct3,
15 Act4,
16 TotAct4,
17 Act5,
18 TotAct5,
19 Act6,
20 TotAct6,
21 Act7,
22 TotAct7,
23 Act8,
24 TotAct8,
25 Act9,
26 TotAct9,
27 Act10,
28 TotAct10,
29 Act11,
30 TotAct11,
31 Act12,
32 TotAct12,
33 Act13,
34 TotAct13,
35 Act14,
36 TotAct14,
37 Act15,
38 TotAct15,
39 Act16,
40 TotAct16,
41 Act17,
42 TotAct17,
43 Act18,
44 TotAct18,
45 Act19,
46 TotAct19,
47 Act20,
48 TotAct20,
49 OpeningUnits,
50 OPricePerUnit,
51 ClosingUnits,
52 CPricePerUnit,
53 AllocationPercent
54 )
55 SELECT
56 cp.PlanId,
57 p.ParticipantId,
58 @PeriodId,
59 Case When a.FUND_ID <> 'LOAN' Then f.FundId ELSE 0 END,
60 CASE When a.FUND_ID = 'LOAN' Then 'LOAN' END as Loanfunds,
61 --planinfo.PortfolioId,
62 Case WHEN a.ACT_ID1 = 'BB' Then 1 END,
63 a.TOT_ACT1,
64 Case WHEN a.ACT_ID2 = 'CT'Then 2 END,
65 a.TOT_ACT2,
66 CASE When a.ACT_ID3 = 'DV' then 3 END,
67 a.TOT_ACT3,
68 CASE When a.ACT_ID4 = 'GL' Then 4 End,
69 a.TOT_ACT4,
70 CAse When a.ACT_ID5 = 'TF' THEN 5 END,
71 a.TOT_ACT5,
72 CASE When a.ACT_ID6 = 'WD' THEN 6 END,
73 a.TOT_ACT6,
74 CASE WHEN a.ACT_ID7 = 'OT' THEN 7 END,
75 a.TOT_ACT7,
76 CASE When a.ACT_ID8 = 'EB' THEN 8 END,
77 a.TOT_ACT8,
78 a.ACT_ID9,
79 a.TOT_ACT9,
80 a.ACT_ID10,
81 a.TOT_ACT10,
82 a.ACT_ID11,
83 a.TOT_ACT11,
84 a.ACT_ID12,
85 a.TOT_ACT12,
86 a.ACT_ID13,
87 a.TOT_ACT13,
88 a.ACT_ID14,
89 a.TOT_ACT14,
90 a.ACT_ID15,
91 a.TOT_ACT15,
92 a.ACT_ID16,
93 a.TOT_ACT16,
94 a.ACT_ID17,
95 a.TOT_ACT17,
96 a.ACT_ID18,
97 a.TOT_ACT18,
98 a.ACT_ID19,
99 a.TOT_ACT19,
100 a.ACT_ID20,
101 a.TOT_ACT20,
102 a.UNIT_OP,
103 a.PRICE_OP,
104 a.UNIT_CL,
105 a.PRICE_CL,
106 IsNull(i.ALLOC_PER1,'0.00')
107 FROM
108 ASDBF a
109
110 --Derive the unique Plan Id
111 INNER JOIN Statements..ClientPlan cp
112 ONa.PLAN_NUM = cp.ClientPlanId
113 AND
114 cp.ClientId = @ClientId
115 --Derive the unique ParticipantId from the Participant table
116 INNER JOIN Statements..Participant p
117 ONa.PART_ID = p.PartId
118 -- Derive the unique fund id from the Fund Table
119 INNER JOIN Statements..Fund f
120 ONa.FUND_ID = f.Cusip
121 OR
122 a.FUND_ID = f.Ticker
123 OR
124 a.FUND_ID = f.ClientFundId
125 Left Outer JOIN INVSRC i
126 ONa.FUND_ID = i.INV_ID
127 AND
128 a.PLAN_NUM = i.Plan_Number
129 AND
130 a.PART_ID = i.PART_ID
131 --Get the unique portfolio name ffor the PArticipant Funds..
132 WHERE
133 --Ignore rows that failed the scrub.
134 a.Import = 1
135 AND
136 --Import only those that are not already in the ParticipantPlanFundBalances table
137 NOT EXISTS (
138 SELECT *
139 FROM
140 Statements..ParticipantPlanFundBalances1 pfb
141 WHERE
142 pfb.PlanId = cp.PlanId
143 AND
144 pfb.ParticipantId = p.ParticipantId
145 AND
146 pfb.PeriodId = @PeriodId
147 AND
148 pfb.FundId = f.FundId
149 )
any help is appreciated.
Regards
Karen
Hi,
In my database i have a column Called EntryDates which has 'Jan1' etc.. stored in it. and i am getting an Xml object and in the object i am getting @EntryDAtesMatch and @EntryDatesPS and they have different months stored in it.
So i want to insert into Entrydates or set the Entrydates as to whats been passed in following scenarios
If @Entrydates is Not Null then i want @EntryDates to be set as @EntryDates.
Suppose @EntryDates is NULL But @EntryDatesMatch OR @entryDAtesPS are not null then i want @EntryDates to be = @EntryDatesMatch..
Suppose is @EntryDates @EntryDatesMatch and null i want @EntryDates to be set as @EntryDatesPS...
This is what ihave tried doing.. but doesnt work as the way i want it..
SET @EntryDates =
CASE --When @EntryDatesMatch = NULL Then @EntryDates
When @EntryDates = NULL and @EntryDatesMatch = NULL Then @EntryDatesPS
When @EntryDates = NULL and @EntryDatesPS = NULL then @EntryDatesMatch
End
Any help will be appreciated..
Regards,
Karen
Hi,
I am trying to do a simple select statement which returns data from the table. The criteria is only if the count of the records is more than 10. Say, if a vendor called test exists 10 times in the table, I would like to display that vendor only, if less than 10, then that vendor won't be displayed at all.
Here is what I have started.
Select vendor_id, vendor_name, Invoice_Date, Invoice_Number from Vendors where Invoice_Date Between '01/01/2007' And '03/03/2008' Group By vendor_id, vendor_name, Invoice_Date, Invoice_Number Having Count(Invoice_Number) > 10
This doesn't seem to work. What am I doing wrong here?
I have worked on developing a project that I am pleased to say is comming along nicely considering I have only been doing programming for a few weeks but I am now stuck and may be going down the wrong path.
I have two tables one that is used for logging via username and password, it also holds a third coloumn realting to the suers account that they do not enter. Once they have logged in I am recalling the users identity and running a query to establish what account the user belongs to. Later on in the script I am trying to run a query on a table called orders that list all orders belonging to that account that was established in the earlier query labelled as 'accountrequesting'.
The script I have below returns all orders for all accounts even though I have tried a wide variety of endings on the Sql search on the orders table.
The code may look a little untidy and to advanced programmers may look rough but I am still learning.....please can anyone help.
sub Page_Load(sender as Object, e as EventArgs)
'collecting the user identity
Dim user as string
user=request("User.Identity.Name")
'running a query to establish what account they have access to all from users table
Dim accountrequesting as string
accountrequesting = "select account_id from users where username = " & user & "'"
'opening the data source and connection
Dim ConnectionString As String = "Data Source=xxxx;Database=xxxx;User ID=xxxxxxx;Password=xxxxxxx"
Dim Myconn as New SQLConnection(connectionstring)
'this is the line I am stuck on, the account id in this orders table needs to match against the value gained in accountrequesting above tried a lot of different endings
Dim MySQL as string ="select prodshipped, account_id from orders"
'produces output
Dim ds as Dataset=New Dataset()
Dim Cmd as new SQLDataAdapter (MySQL, Myconn)
Cmd.Fill (ds,"orders")
MyDataGrid.datasource=ds.tables("orders").defaultview
MyDataGrid.Databind()
End Sub
I have a stored procedure that populates a table. I want to query the table using column names as variables, however the data is in decimal format so I am getting errors converting varchar to numeric. My table consists of columns labeled D1, D2, etc. for every possible day of any month, DOW1, DOW2, etc. for every day of the week. I also have values labeled midDpct and midDOWpct. My query is as follows:
select *
from Table
where D10 > midDaypct * 2 and DOW6 > 0
union
select *
from Table
where DOW6 >midDOWpct * 2 and D10 > 0
We are targeting a specific day of the month and a specific day of week, day 10 and day of week 6 in this example. I want to make these variables so we can easily change out the target day and dow.
Hi,
here is what i am trying to do, when @call=2, then use table1.call
else use table2.call
and get syntax error no matter what I try,
case @services=2 then table1.call else table2.call end
I have a table consisting of two fields, OStype and OSversion, withentries like:OStypeOSversionsolaris 2.5solaris 2.6redhat 6.2redhat 6.2solaris 8redhat AS4solaris 10solaris 10redhat AS2.1redhat AS3redhat AS4I want to create a select statement that returns for each OS type, thetotal number of entries and for each version the total number ofentries.In the example the result would be:OStype OStype Count OSversion OSversion Countsolaris 52.5 12.6 18 110 2redhat 66.2 2AS2.1 1AS3 1AS4 2Thanks in advance for your help.Ian
View 3 Replies View RelatedI have a table with two columns. empnum and empname. there are some data with same empnum but different empname. I just want to get the first empname for the same empnum. Is there a way to write a sql stetment to do this?
Thanks
I want to Add in a Case Statement to my Stored Procedure to Identify 5 classifications:
A: Birth to 6 yrs old
B: Female 7 yrs to 18 yrs
C: Male 7 yrs to 18 yrs
D: Female over 18 yrs old
E: Male over 18 yrs old
So Im looking for something like this:
CASE
WHEN [Patient Age] >= 6 THEN 'A'
WHEN [Patient Age] between .....
Any help is appreciated!
Declare @ApptDate datetime
Select @ApptDate = a.ApptStart
FROM Appointments a
WHERE a.AppointmentsId = @AppointmentsId
SELECT '290.PatientName'=IsNull(pp.First,'') + ' ' + isnull(pp.Middle,'') + ' ' + isnull(pp.Last,'')+ ' ' + isnull(pp.Suffix,''),
'291.PatLast'=IsNull(pp.Last,''),
'292.PatFirst'=IsNull(pp.First,''),
'293.PatMiddle'=IsNull(pp.Middle,''),
'294.PatientAddr1'=IsNull(pp.Address1,''),
'295.PatientAddr2'=IsNull(pp.Address2,''),
'296.PatientCity'=IsNull(pp.City,''),
'297.PatientState'=IsNull(pp.State,''),
'298.PatientZip'=IsNull(pp.Zip,''),
'299.PatientCountry' = ISNULL(pp.Country,''),
'300.PatientBirthdate' = pp.Birthdate,
'301.PatientSex'=IsNull(pp.Sex,''),
'302.PatientPhone1'=IsNull(pp.Phone1,''),
'303.PatientSSN'=IsNull(pp.SSN,''),
'304.PatOccupation'=IsNull(pp.EmpOccup,''),
'305.PatSchool'=IsNull(pp.MedicalRecordNumber,''),
'306.PatBudget'=IsNull(g.Budget,0),
'307.PatientSameAsGuarantor'=IsNull(pp.PatientSameAsGuarantor,0),
'308.PatSuffix'=IsNull(pp.Suffix,''),
'400.PatientAge' = CASE WHEN datediff(day, pp.birthdate, @ApptDate) <= 6 THEN cast(datediff(day, pp.birthdate, @ApptDate) as varchar(10)) + ' dys'
WHEN datediff(day, pp.birthdate, @ApptDate) <= 112 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 7) as varchar(10)) + ' wks'
WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) <= day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) as varchar(10)) + ' mos'
WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) > day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) - 1 as varchar(10)) + ' mos'
WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) >= 2 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) as varchar(10)) + ' yrs'
ELSE '' END
FROM PatientProfile pp
LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
WHERE pp.PatientProfileID = @PatientProfileId
I'm trying to get my feet wet with creating a Sql Server project within VS 2005 and I have the using statements below present but when I compile I get errors stating that SqlContext, SQLPipe, and SQLCommand do not exist in the current context. What am I missing? Under the references node I have 3 references present (System, System.Data, and System.XML). What am I missing?
TIA
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
I have a table called emp
1eno enamedesgsal
1aaamanager20000
2bbbexecutive15000
3ccchr25000
4dddceo45000
using single update statement , how can i update the salary based on the desg .i have to give hike if its manager sal =sal+(sal*0.10) .like this
Thanks & Regards
Zakeer
Hello,
I am looking to modify this Case Statement. Where it says ELSE '' I need it to display the actual contents of the cell. 1 = Yes , 0 = No, (any other integer) = actual value.
Right now if the value is anything other than 1 or 0, it will leave the cell blank.
CASE dbo.Training.TrainingStatus WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END AS TrainingStatus
Thank You.
Hi,
I am trying to insert the Source name and clientId to a table called clientSource...
The User will send in some Dbf File.... So in a particular file called PlanDbf.. I have the following fields
PlanNumber, Name, SRC1Name, SRC2Name, SRC3Name.... SRC20Name
170234 Constructions Employee Deferral Employer Discretionary Employer Matching....
And in another table called SourceDBF i have the following fields with data
PlanNumber PARTID SOURCE_NUM etc...
170234 123456789 1
170234 123456789 3
170234 451231334 1
So how do i match the Source_NUM with SRCnames when i insert it into the table..
INSERT INTO Statement..ClientSource(@ClientId, SourceName)SELECT s.SOURCE_NUM FROM SourceDBF ..
but i am stuck..
any help will be appreciated.
Regards
Karen
Hi,I have a need to create a table detailing the ID of all contacts and thelast time they were contacted. This information is stored in 2 tables,'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'in the 'activity' table).I guess I need some sort if iteration to go through each contact and findfind the last activity that took place against each of them (there many bemore than 1 activity against each contact) and then place the output valuesinto the new table.Can anyone show me how to go about this?Thanks!
View 2 Replies View RelatedHi,
Any help with this would be great. I have SQL tables all in the following format:
ID_PRODUCT,
ID_MARKET,
ID_BUCKET,
ID_COLLECTION,
ID_MEASURE,
MEASURE_VALUE
Now each table holds different measures e.g. 326, 229 & 278. I would like to subtract the measure_value of 229 from 278 and then add the measure_value from 278 from this. e.g
(326-229) + 278 = new measure_value.
This is for each id-product where the following 3 conditions meet:
the ID_PRODUCT, ID_MARKET AND ID_BUCKET match. Im lost, any help would be great.
thanks
Jay
p.s this what i have started with at the moment as a test however it dont work.
SELECT ID_PRODUCT,
ID_MARKET,
ID_BUCKET,
ID_COLLECTION,
ID_MEASURE,
MEASURE_VALUE
FROM (SELECT TOP 100 PERCENT dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT,
dbo.DPOUT_EXCEPTIONS_326.ID_MARKET,
dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET,
dbo.DPOUT_EXCEPTIONS_326.ID_COLLECTION,
dbo.DPOUT_EXCEPTIONS_326.ID_MEASURE,
dbo.DPOUT_EXCEPTIONS_326.MEASURE_VALUE + DBO.DPOUT_LSBP_229.MEASURE_VALUE as 'MEASURE_VALUE'
FROM dbo.DPOUT_EXCEPTIONS_326 INNER JOIN dbo.dpout_lsbp_229 on
dbo.dpout_exceptions_326.id_product = dbo.dpout_lsbp_229.id_product
where dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT = dbo.dpout_lsbp_229.id_product
and dbo.DPOUT_EXCEPTIONS_326.ID_MARKET = DBO.DPOUT_LSBP_229.ID_MARKET
and dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET = DBO.DPOUT_LSBP_229.ID_BUCKET)
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
WHERE etc ......
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies View RelatedHello, 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.
Anyone?
thanks in advance.
hi,
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 .
Pls reply asap....
-Sweety
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
Hello experts!
I have a case statement that provides filtering of hours during certain days of the week. An example is the data I want to show on Sunday is different from the rest of the week. I am using....
Code Snippet
WHERE ((CASE WHEN Datepart(dw, TestDateTime) = 1 AND datepart(hh, TestDateTime) BETWEEN 8 AND 22 THEN 1 WHEN Datepart(dw, TestDateTime)
>= 2 AND datepart(hh, TestDateTime) BETWEEN 6 AND 23 OR
datepart(hh, TestDateTime) BETWEEN 0 AND 2 THEN 1 ELSE 0 END) >= @ShowCore)
Esentially it gives a parameter (@showcore) to where it shows the filtered hours when 1 is selected, and all hours if 0 is selected.
Basically, Sunday I want to show transaction from between 8am and 10pm, All other days would be 12am - 2am and 6am to 11:59:59 when selecting 1 as the parameter.
Any help is appreciated.
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
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
Example of table data:
emr_temp_labtest_config
labtestcode, zsegcode
003277, ZBL
emr_zseg_code_descriptions
zsegcode, valuecode
ZBL, PATRAC
ZBL, HERITG
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
View 1 Replies View RelatedPer 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
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END
What is the best way to script this
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
View 1 Replies View RelatedIt appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.
View 5 Replies View Related