Subquery Help Needed. SQL 2005
Jan 20, 2008
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
WHERE ...... etc, etc, etc
View 7 Replies
ADVERTISEMENT
Aug 3, 2007
Using the sql script below in SQL Query Analyzer to determine the member’s primary care physician, the physician who provided the service and the medical group (IPA) of the primary care physician.
Select
t.MEMBER_NBR
, pr.PROV_ID as SvcProviderID
, pr.PROV_RELATION_PROV_ID as PCPProvID
,pti.PROVIDER_TAX_ID -- Is this the tax id of the PCP?
,case when PTI.PROVIDER_TAX_ID = '3006562' THEN 'CV_PHYS'
ELSE 'NOT_CV_PHYS' END AS PhysCVPHYS
, g.PROV_FULL_NM – Should be name of the medical group
From CLAIM a (NOLOCK)
inner join MEMBER_SOURCE t (NOLOCK) on a.MEMBER_ID = t.MEMBER_ID
and t.LOGICAL_DELETE_IND <> 'Y'
inner join PROVIDER g (NOLOCK) on a.CLM_LINE_SVC_PROV_ID = g.PROVIDER_ID
and g.LOGICAL_DELETE_IND <> 'Y'
inner join PROVIDER_RESET pr on g.PROVIDER_ID = pr.PROVIDER_ID
and pr.LOGICAL_DELETE_IND <> 'Y'
inner join PROVIDER_TAX_ID pti on g.PROVIDER_ID = PTI.PROVIDER_ID
AND pti.LOGICAL_DELETE_IND <> 'Y'
where
t.MEMBER_NBR in ('2563896','3628974')
AND pti.PROVIDER_TAX_ID = (select pti.PROVIDER_TAX_ID WHERE pr.PROVIDER_ID = pti.PROVIDER_ID)
group by
t.MEMBER_NBR
, pr.PROVIDER_ID
, pr.PROV_RELATION_PROV_ID
, pti.PROVIDER_TAX_ID
, pr.PROV_RELATION_PROV_ID
, g.PROV_FULL_NM
Upon reviewing the query result set, as displayed below, it appears that I am extracting the name of the physician that provided the service not the medical group (IPA) of the primary care physician in the field titled "ProvFullNm." The contents of this field should be the name of the medical group (IPA)- something like "CV_Physician."
SrcSysMemNbr--SvcProvID--PCPProvID---PhysCVPhys--ProvFullNm
2563896-------5015-------48956-------NOT_CV_PHYS-John Doe
2563896-------5015-------49055-------NOT_CV_PHYS-John Doe
2563896-------5283-------46523-------NOT_CV_PHYS-Sally Fa
2563896-------5246-------46526-------NOT_CV_PHYS-Bill Sne
Additional Comments
"pr.PROV_RELATION_PROV_ID" is the provider_id for the primary care physician
Note, "pr.PROV_ID" is the provider_id for the physician who provided the service. Further, the provider_id for the physician who provided the service and the provider_id for the primary care physician is stored in the same table, "PROVIDER_RESET."
Of course, sometimes the physician who provide service is also the primary care physician.
If the PCP has a tax id of ‘3006562’, then the PCP has a CV_Physician medical group.
Questions
How do I definitely “pull” the pti.PROVIDER_TAX_ID for the primary care physician not the physician who provided the service?
Then, I can determine if the primary care physician’s medical group is “CV_Physician using the CASE statement.
My initial try at this was to include a subquery in the WHERE clause such as
“AND pti.PROVIDER_TAX_ID = (select pti.PROVIDER_TAX_ID WHERE pr.PROVIDER_ID = pti.PROVIDER_ID)”
Thanks in advance.
View 1 Replies
View Related
Jan 25, 2008
I am getting the following syntax error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
From my query: (Please note the Where clause values will look odd to you, however they are not broken or wrong (its how the system reads it (odd I know)). The only issue I have is the subquery.
/*Schedule Summary*/
SET NOCOUNT ON
--Patient Appointments
DECLARE @Today DATETIME
DECLARE @Tomorrow DATETIME
SET @Today = '10/29/2007'
SET @Tomorrow = dateadd(d, 1, '10/29/2007')
SELECT Date=convert(datetime,convert(char(12),Start,1)),
convert(datetime,aps.Start) AS ResourceStart,
convert(datetime,aps.Stop) AS ApptStop,
ApptTime = datediff(minute, aps.Start, aps.Stop),
df.Listname AS Resource,
Facility= f.ListName,
d.Listname AS DoctorName,
--'Available / No Appt' AS Type,
(SELECT dbo.sfnGetAllocsForSlot(aps.ApptSlotId)
FROM ApptSlot aps
JOIN Schedule s ON aps.ScheduleId = s.ScheduleId)AS TYPE,
'Available' AS 'Patient Name',
1 as ApptKind
FROM ApptSlot aps
JOIN Schedule s ON aps.ScheduleId = s.ScheduleId
JOIN DoctorFacility df ON s.DoctorResourceId = df.DoctorFacilityId
JOIN DoctorFacility f ON aps.FacilityId = f.DoctorFacilityId
JOIN DoctorFacility d ON s.DoctorResourceId = d.DoctorFacilityId
LEFT JOIN Appointments a ON aps.apptid = a.appointmentsID
WHERE --Filter on resource
(
('7' IS NOT NULL AND df.DoctorFacilityId IN (7)) OR
('7' IS NULL)
)
AND
(
(NULL IS NOT NULL AND aps.FacilityId IN (NULL)) OR
(NULL IS NULL)
)
AND (Start >= @Today OR @Today IS NULL)
AND (Start < @Tomorrow OR @Tomorrow IS NULL)
AND ApptId IS NULL
AND APS.ListOrder <> -1
ORDER BY [Resource], [ResourceStart]
View 3 Replies
View Related
Sep 4, 2007
My client came back and wanted me to add in a filter for Transaction Date from my Query (see full query below):
WHERE pm.Source = 1 AND t.Amount <> 0
AND --Filter on date range
(
(pm.DateOfEntry >= ISNULL('06/01/2005', '1/1/1900')
AND pm.DateOfEntry < DATEADD(d,1,ISNULL('09/04/2007','1/1/3000')))
)
The issue is how the following subquerys are calculating their values. I think I need to add in something now on if the pm.DateOfEntry is between Date1 and Date2. Would this be right and if so, can someone assist me on the syntax or at least give me a start on it ...?? It appears as if the subqueries are not respecting any date logic, only the id being passed into them.
Section of Code I need assistance with:
PatBalance = (SELECT SUM(pva.PatBalance) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId),
InsBalance = (SELECT SUM(pva.InsBalance) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId),
Charges = (SELECT SUM(pva.OrigInsAllocation + pva.OrigPatAllocation) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId),
Payments = (SELECT SUM(pva.InsPayment + pva.PatPayment) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId),
Adjustments = (SELECT SUM(pva.InsAdjustment + pva.PatAdjustment) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId)
Main Query:
SET NOCOUNT ON
CREATE TABLE #Visit
(
PatientVisitId int,
PatientProfileId int
)
CREATE TABLE #Ledger
(
PatientProfileId int,
Type smallint,
PatientId varchar(15) NULL,
Birthdate datetime NULL,
PatientName varchar(110) NULL,
PatientAddress1 varchar(50) NULL,
PatientAddress2 varchar(50) NULL,
PatientCity varchar(30) NULL,
PatientState varchar(3) NULL,
PatientZip varchar(10) NULL,
PatientPhone1 varchar(15) NULL,
PatientPhone1Type varchar(25) NULL,
PatientPhone2 varchar(15) NULL,
PatientPhone2Type varchar(25) NULL,
PatientVisitId int NULL,
VisitDateOfService datetime NULL,
VisitDateOfEntry datetime NULL,
DoctorId int NULL,
DoctorName varchar(110) NULL,
FacilityId int NULL,
FacilityName varchar(60) NULL,
CompanyId int NULL,
CompanyName varchar(60) NULL,
TicketNumber varchar(20) NULL,
PatientVisitProcsId int NULL,
TransactionDateOfServiceFrom datetime NULL,
TransactionDateOfServiceTo datetime NULL,
TransactionDateOfEntry datetime NULL,
InternalCode varchar(10) NULL,
ExternalCode varchar(10) NULL,
Description varchar(255) NULL,
Fee money NULL,
Units float NULL,
PatAmount money NULL,
InsAmount money NULL,
Action varchar(1) NULL,
Payer varchar(255) NULL,
Notes text NULL,
PatBalance money NULL,
InsBalance money NULL,
Charges money NULL,
Payments money NULL,
Adjustments money NULL
)
/* Get the subset of visits for this report */
INSERT #Visit
SELECT pv.PatientVisitId, pv.PatientProfileId
FROM PatientVisit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitID = pva.PatientVisitID
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
WHERE pp.PatientProfileId = 462 AND pva.PatPayment <> 0
AND --Filter on date type and range
(
('1' = '1' AND pv.Visit >= ISNULL(NULL,'1/1/1900') AND pv.Visit < dateadd(d, 1, ISNULL(NULL,'1/1/3000'))) OR
('1' = '2' AND pv.Entered >= ISNULL(NULL,'1/1/1900') AND pv.Entered < dateadd(d,1,ISNULL(NULL,'1/1/3000')))
)
AND --Filter on doctor
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on company
(
(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
(NULL IS NULL)
)
/* Get demographics for the patient */
INSERT #Ledger
(
PatientProfileId,
Type,
PatientId,
Birthdate,
PatientName,
PatientAddress1,
PatientAddress2,
PatientCity,
PatientState,
PatientZip,
PatientPhone1,
PatientPhone1Type,
PatientPhone2,
PatientPhone2Type,
PatBalance,
InsBalance,
Charges,
Payments,
Adjustments
)
SELECT DISTINCT
pp.PatientProfileId, 1, pp.PatientId, pp.Birthdate,
RTRIM(RTRIM(RTRIM(ISNULL(pp.First, '') + ' ' + ISNULL(pp.Middle, '')) + ' ' + pp.Last) + ' ' + ISNULL(pp.Suffix, '')) AS PatientName,
pp.Address1, pp.Address2, pp.City, pp.State, pp.Zip,
pp.Phone1, pp.Phone1Type, pp.Phone2, pp.Phone2Type,
PatBalance = (SELECT SUM(pva.PatBalance)
FROM #Visit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
WHERE pv.PatientProfileId = pp.PatientProfileId),
InsBalance = (SELECT SUM(pva.InsBalance)
FROM #Visit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
WHERE pv.PatientProfileId = pp.PatientProfileId),
Charges = (SELECT SUM(pva.OrigInsAllocation + pva.OrigPatAllocation)
FROM #Visit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
WHERE pv.PatientProfileId = pp.PatientProfileId),
Payments = (SELECT SUM(pva.InsPayment + pva.PatPayment)
FROM #Visit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
WHERE pv.PatientProfileId = pp.PatientProfileId),
Adjustments = (SELECT SUM(pva.InsAdjustment + pva.PatAdjustment)
FROM #Visit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
WHERE pv.PatientProfileId = pp.PatientProfileId)
FROM PatientProfile pp
INNER JOIN #Visit tv ON pp.PatientProfileId = tv.PatientProfileId
/* Get visit information for the patient */
INSERT #Ledger
(PatientProfileId,
Type,
PatientVisitId,
VisitDateOfService,
VisitDateOfEntry,
DoctorId,
DoctorName,
FacilityId,
FacilityName,
CompanyId,
CompanyName,
TicketNumber
)
SELECT pv.PatientProfileId, 2, pv.PatientVisitId, pv.Visit, pv.Entered,
pv.DoctorId, d.ListName AS DoctorName,
pv.FacilityId, f.ListName AS FacilityName,
pv.CompanyId, c.ListName AS CompanyName,
pv.TicketNumber
FROM #Visit tv
INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
INNER JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
INNER JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId
/* Get diagnosis information for the patient's visits */
INSERT #Ledger
(
PatientProfileId,
Type,
PatientVisitId,
InternalCode,
ExternalCode,
Description
)
SELECT pv.PatientProfileId, 3, pv.PatientVisitId,
pvd.Code, pvd.ICD9Code,
pvd.Description
FROM #Visit tv
INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
INNER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId
/* Get transaction information for the patient's visits */
INSERT #Ledger
(
PatientProfileId,
Type,
PatientVisitId,
PatientVisitProcsId,
TransactionDateOfServiceFrom,
TransactionDateOfEntry,
Description,
Payer,
PatAmount,
InsAmount,
Action,
Notes
)
SELECT pv.PatientProfileId, 5, pv.PatientVisitId, NULL,
ISNULL(pm.CheckDate, b.Entry), b.Entry,
at.Description + CASE WHEN pm.CheckCardNumber IS NULL THEN ''
ELSE ' - Check # ' + pm.CheckCardNumber + ' ' + CASE WHEN pm.CheckDate IS NULL THEN '' ELSE CONVERT(varchar(12), pm.CheckDate, 101) END END,
pm.PayerName,
CASE WHEN pm.Source = 1 THEN t.Amount END,
CASE WHEN pm.Source = 2 THEN t.Amount END,
t.Action,
CASE WHEN ISNULL(t.ShowOnStatement, 0) <> 0 THEN t.Note ELSE NULL END
FROM #Visit tv
INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
INNER JOIN VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitId
INNER JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId AND t.Action = 'P'
INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId AND ISNULL(pm.InsuranceTransfer, 0) = 0
INNER JOIN Batch b ON pm.BatchId = b.BatchId
LEFT JOIN MedLists at ON t.ActionTypeMId = at.MedListsId
WHERE pm.Source = 1 AND t.Amount <> 0
AND --Filter on date range
(
(pm.DateOfEntry >= ISNULL('06/01/2005', '1/1/1900')
AND pm.DateOfEntry < DATEADD(d,1,ISNULL('06/30/2005','1/1/3000')))
)
SELECT tl.*
FROM #Ledger tl
ORDER BY tl.PatientProfileId, tl.PatientVisitId, tl.PatientVisitProcsId, tl.Type
View 1 Replies
View Related
Apr 26, 2008
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
View 1 Replies
View Related
Jul 20, 2005
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
View 3 Replies
View Related
Mar 6, 2008
I am getting an error as
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while running the following query.
SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,
LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,
LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,
ManagerName=(SELECT E.FirstName+' '+E.LastName
FROM EmployeeDetails E
INNER JOIN LUP_EmpProject
ON E.Empid=LUP_EmpProject.Empid
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid
WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)
FROM EmployeeDetails
INNER JOIN LUP_EmpDepartment
ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid
INNER JOIN LUP_FIX_DeptDetails
ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid
AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date
FROM LUP_EmpDepartment
WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid
ORDER BY LUP_EmpDepartment.Date DESC)
INNER JOIN LUP_EmpDesignation
ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid
INNER JOIN LUP_FIX_DesigDetails
ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid
AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date
FROM LUP_EmpDesignation
WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid
ORDER BY LUP_EmpDesignation.Date DESC)
INNER JOIN LUP_EmpProject
ON EmployeeDetails.Empid=LUP_EmpProject.Empid
AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate
FROM LUP_EmpProject
WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid
ORDER BY LUP_EmpProject.StartDate DESC)
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid
WHERE EmployeeDetails.Empid=1
PLEASE HELP.................
View 1 Replies
View Related
May 14, 2008
Hi,
I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Any ideas as to why??
SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar,
RDR1.U_Variety,
(SELECT OITM.U_Variety
FROM OWOR INNER JOIN
WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN
OITM INNER JOIN
OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode
WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity,
OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet,
OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode
FROM OWOR AS t0 INNER JOIN
ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN
RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN
OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode
WHERE (t0.Status <> 'L')
Thanks
Jacquues
View 4 Replies
View Related
May 5, 2008
I needed to pull the top/most recent record only for these fields below. So I created multiple subquerys with the TOP 1 and Order By DESC in them (please see full query below). I unfortunately still get my records back as if the subquery's never got created. I am stumped. Any ideas?
/* Sliding Fee*/
SFEffectiveDate = cpsfh.SFEffectiveDte,
FamilySize = cpsfh.FamilySize,
MonthlyIncome = Convert(Money,ISNULL(cpsfh.MonthlyIncome,0)),
AnnualIncome = Convert(Money,IsNull(cpsfh.MonthlyIncome*12,0)),
NoIncome = CASE WHEN cpsfh.NoIncome = 0 THEN 'No' ELSE 'Yes' END,
SlidingFeeClass = ISNULL(ccml.description,'None'),
SlidingFeeCarrier = ic2.Listname,
FormOfDeclaration = ISNULL(ccml2.description,'(None)'),
LastModifiedBy = cpsfh.lastmodifiedby,
LastModifiedDate = cpsfh.lastmodified,
/*End Sliding Fee*/
My Query:
/* Patient Insurance List */
SET NOCOUNT ON
SELECT dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS Name,
pp.Address1 AS [Patient Address 1],
pp.Address2 AS [Patient Address 2],
pp.City + ', ' + pp.State + ' ' + pp.Zip AS [Patient Address 3],
g.[Last] + ', ' + g.[First] AS [Guarantor Name],
g.Address1 AS [Guarantor Address 1],
g.Address2 AS [Guarantor Address 2], g.City + ', ' + g.State + ' ' + g.Zip AS [Guarantor Address 3],
pp.PatientSameAsGuarantor,
pi.OrderForClaims AS [Order For Claims],
ISNULL(pi.[Last] + ', ' + pi.[First], '0') AS [Other Name],
pi.Address1 AS [Other Address 1],
pi.Address2 AS [Other Address 2],
pi.City + ', ' + pi.State + ' ' + pi.Zip AS [Other Address 3],
ic.ListName AS [Insurance Carrier],
ISNULL(pi.InsuredId, ' ') AS [Insured ID],
/* Sliding Fee*/
SFEffectiveDate =(SELECT TOP 1 cpsfh.SFEffectiveDte ORDER BY cpsfh.SFEffectiveDte DESC),
FamilySize = (SELECT TOP 1 cpsfh.FamilySize ORDER BY cpsfh.FamilySize DESC),
MonthlyIncome = (SELECT TOP 1 Convert(Money,ISNULL(cpsfh.MonthlyIncome,0))ORDER BY cpsfh.MonthlyIncome DESC),
AnnualIncome = (SELECT TOP 1 Convert(Money,IsNull(cpsfh.MonthlyIncome*12,0))ORDER BY cpsfh.MonthlyIncome*12 DESC),
NoIncome = (SELECT TOP 1 CASE WHEN cpsfh.NoIncome = 0 THEN 'No' ELSE 'Yes' END ORDER BY cpsfh.NoIncome DESC),
SlidingFeeClass = (SELECT TOP 1 ISNULL(ccml.description,'None')ORDER BY ccml.description DESC),
SlidingFeeCarrier = (SELECT TOP 1 ic2.Listname ORDER BY ic2.Listname DESC),
FormOfDeclaration = (SELECT TOP 1 ISNULL(ccml2.description,'(None)')ORDER BY ccml2.description DESC),
LastModifiedBy = (SELECT TOP 1 cpsfh.lastmodifiedby ORDER BY cpsfh.lastmodifiedby DESC),
LastModifiedDate = (SELECT TOP 1 cpsfh.lastmodified ORDER BY cpsfh.lastmodified DESC),
/*End Sliding Fee*/
ISNULL(dbo.formatphone(pp.phone1,1),'') AS Phone,
ISNULL(pp.Phone1Type, ' ') AS [Phone Type],
ig.Name AS InsuranceGroup,
df.ListName AS Doctor,
dff.ListName AS Facility,
ml.Description AS FinancialClass
FROMPatientProfile pp
JOIN PatientInsurance pi ON pp.PatientProfileId = pi.PatientProfileId
JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN InsuranceGroup ig ON ic.InsuranceGroupId = ig.InsuranceGroupId
LEFT JOIN MedLists ml ON pp.FinancialClassMId = ml.MedListsId
LEFT JOIN DoctorFacility dff ON pp.FacilityId = dff.DoctorFacilityId
LEFT JOIN DoctorFacility df ON pp.DoctorId = df.DoctorFacilityId
LEFT JOIN cusPatientSlidingFeeHst cpsfh ON pp.PatientProfileId = cpsfh.PatientProfileID
LEFT JOIN cusCRIMedLists ccml ON cpsfh.slidingfeeclass = ccml.medlistsid
LEFT JOIN InsuranceCarriers ic2 ON cpsfh.SFCarrierID = ic2.InsuranceCarriersId
LEFT JOIN cusCRIMedLists ccml2 ON cpsfh.SFFormOfDeclarationMID = ccml2.MedListsId
WHERE --Filter on doctor
(
(NULL IS NOT NULL AND pp.DoctorId IN (NULL)) OR
(NULL IS NULL)
)
AND
(
(NULL IS NOT NULL AND pp.FacilityId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Insurance Carrier
(
(NULL IS NOT NULL AND pi.InsuranceCarriersId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Insurance Group
(
(NULL IS NOT NULL AND ic.InsuranceGroupId IN (NULL)) OR
(NULL IS NULL)
)
ORDER BY dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix)
View 8 Replies
View Related
Apr 10, 2008
I have written following code in my application
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();
repl.InternetUrl = @"http://localhost/WebsiteDotNetCF/sqlcesa30.dll";
repl.Publisher = @"RAHU";
repl.PublisherDatabase = @"DotNetCF";
repl.PublisherSecurityMode = SecurityType.NTAuthentication;
repl.Publication = @"PubDotNetCF";
repl.Subscriber = @"SubDotNetCF";
repl.SubscriberConnectionString = @"Data Source='" + filename + "';Max Database Size=128;Default Lock Escalation =100;";
try
{
if (!System.IO.File.Exists(filename))
{
repl.AddSubscription(AddOption.CreateDatabase);
}
repl.Synchronize();
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
finally
{
repl.Dispose();
}
// Display Same Data In Another DataGrid : dataGrid1
SqlCeConnection cn = new SqlCeConnection(@"Data Source='" + filename + "'");
SqlCeDataAdapter daJobLists = new SqlCeDataAdapter("SELECT JobListsID, JobID, PersonID FROM JobLists", cn);
if (dsJobLists == null)
{
dsJobLists = new DataSet();
}
try
{
dsJobLists.Clear();
daJobLists.Fill(dsJobLists, "JobLists");
dataGrid1.DataSource = dsJobLists.Tables["JobLists"];
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
}
private void DisplaySQLCEErrors(SqlCeException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
MessageBox.Show("Index #" + i.ToString() + ""
+ ex.Errors.Source + ""
+ "Error: " + ex.Errors.Message,
"Error No. " + ex.Errors.NativeError.ToString());
}
}
private void Form1_Load(object sender, EventArgs e)
{
Sync();
DeleteDB();
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
Your help will be appriciated
View 1 Replies
View Related
Jul 19, 2007
Hi guys,
A have a problem that I need understand.... I have 2 server with the same configuration...
SERVER01:
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
********************************
SERVER02:
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
OK, the problem is that if a run the below query in server01, i get error 512:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
But, if run the same query in the server02, the query work fine -.
I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.
Any idea WHY?
SELECT dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao ds_tipo_transacao_movimento ,
dbo.opf_header_operacao.ds_tipo_transacao ds_tipo_transacao_header,
'SD' ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente,
0 vl_entrada_compra_ctro ,0 vl_entrada_compra_premio,
0 vl_entrada_venda_ctro , 0 vl_entrada_venda_premio,
0 vl_saida_compra_ctro, 0 vl_saida_compra_premio,
0 vl_saida_venda_ctro, 0 vl_saida_venda_premio,
0 vl_lucro , 0 vl_prejuizo, 0 vl_naoexec_contrato,
0 vl_naoexec_premio,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_ganho) vl_aprop_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_perda) vl_aprop_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_ganho) vl_rever_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_perda) vl_rever_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_irrf) vl_irrf
FROM dbo.opf_saldo_ctb_opc_flx,
dbo.opf_header_operacao ,
dbo.opf_movimento_operacao
WHERE dbo.opf_saldo_ctb_opc_flx.dt_saldo = '6-29-2007 0:0:0.000'
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_saldo_ctb_opc_flx.no_contrato )
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_movimento_operacao.no_contrato )
and ( dbo.opf_movimento_operacao.dt_pregao = (select (o.dt_pregao) from dbo.opf_movimento_operacao o
where o.no_contrato = dbo.opf_movimento_operacao.no_contrato and o.dt_pregao <='6-28-2007 0:0:0.000' ) )
and (dbo.opf_saldo_ctb_opc_flx.ic_tipo_saldo = 'S')
group by dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao,
dbo.opf_header_operacao.ds_tipo_transacao ,
ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente
Thanks
Nilton Pinheiro
View 9 Replies
View Related
Jul 7, 2006
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?
TIA,
barkingdog
View 1 Replies
View Related
Jul 6, 2014
I am trying to add the results of both of these queries together:
The purpose of the first query is to find the number of nulls in the TimeZone column.
Query 1:
SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename
The purpose of the second query is to find results in the AAST, AST, etc timezones.
Query 2:
SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')
Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.
What I came up with (from research) is:
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))
I get a msq 102, level 15, state 1 error.
I also tried
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results
but I still get an error. For the exact details see:
[URL]
NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008.
View 6 Replies
View Related
May 2, 2007
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.
View 2 Replies
View Related
Jun 14, 2007
I have some relatively simple SQL that acts differently between SQL Server 2000 and 2005. Although it is easy to fix I'd like to know if this difference is expected (documented) or a bug, and if there is perhaps a setting/switch I can use to avoid a code review of hundreds of stored procs to look for similar scenarios. Executed the following script in SQL Server 2005 –CREATE TABLE #Floats(FloatID INT IDENTITY,FloatNumber FLOAT NOT NULL)DECLARE @sngCounter floatSET @sngCounter = 20WHILE @sngCounter >= 0BEGININSERT INTO #Floats ( FloatNumber ) VALUES( @sngCounter )SET @sngCounter = @sngCounter - 1ENDSELECT * FROM (SELECT TOP 100 PERCENT * FROM #Floats ORDER BY FloatNumber) AS FloatNumbersDROP TABLE #FloatsGOProduces the following resultset –FloatID FloatNumber----------- -----------1 202 193 184 175 166 157 148 139 1210 1111 1012 913 814 715 616 517 418 319 220 121 0In SQL Server 2000 resultset is this –FloatID FloatNumber----------- --------21 0.020 1.019 2.018 3.017 4.016 5.015 6.014 7.013 8.012 9.011 10.010 11.09 12.08 13.07 14.06 15.05 16.04 17.03 18.02 19.01 20.0
View 4 Replies
View Related
Mar 8, 2006
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).
My code:
ReportExecutionService rs = new ReportExecutionService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
ParameterValue[] pvValues = null;
DataSourceCredentials[] credCredentials = null;
ReportParameter[] rptparamRequiredParams = null;
ParameterValue[] pvReportParameters = null;
ExecutionHeader execHeader = null;
ExecutionInfo execInfo = null;
rptparamRequiredParams = null;
string sHistoryID = null;
{fill parameters block}
rs.SetExecutionParameters (pvReportParameters, "en-us");
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
View 1 Replies
View Related
Apr 12, 2007
Hey all
I am in the process of implementing transactional replication.
Site 1 : Sql server with 3 Databases
lets just call them DB1 to 3
Site2 : Sql server with 1database
DB1 replicated from site 1
Site 3: Sql server with 1 database
DB2 replicated from site 1
Site 4: Sqlserver with 1 database
DB 3 replicated from site 1
site 5: Sql server with 1 database
DB1 replicated from Site2
I need to know how to go about this
Site 1 will be Publisher/Distributor
Site 2 will be subscriber from site1, but site 2 needs to replicate the same DB down to site5, can site 2 be subscriber and distributer
Site 3 and 4 will subscribe to site 1
The various sites must also be able to update the DB back up to Site 1
The things i need info on is the security settings
The servers are stand alone servers and dont have active directory
Need info on how to configure the Various service accounts, must they be windows accounts.
Is there any content i can read up on to help me on this
Hope all this info helps
Any help would be Great
Thanx a lot
View 7 Replies
View Related
Apr 5, 2006
How can I get Northwind into SQL Server 2005 Development?
In working through Visual Studio 2005 Pro Walkthroughs the next one needs Northwind. Is it straight forward to add in?
View 2 Replies
View Related
Apr 16, 2008
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
View 2 Replies
View Related
Apr 16, 2008
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
View 1 Replies
View Related
Jan 29, 2008
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 ......
View 1 Replies
View Related
Jun 21, 2006
Hi,I have 2 stored procedure 1st insert the data in parent tables and return the Id. and second insert child table data using that parent table id as paramenter. I have foreign key relationship between these two tables also.my data layer methods somewhat looks likepublic void Save(order value){using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required)) { int orderId = SaveOrderMaster(value); value.OrderId = orderid; int childId = SaveOrderDetails(value); //complete the transaction transactionScope.Complete(); }}here 1. SaveOrderMaster() calls an stored procedure InserOrderData which insert a new record in order table and return the orderId which is identity column in Order table.2. SaveOrderDetails() call another sotored procedure which insert order details in to table "orderdetail" using the foreign key "orderid".My Problem:Some time the above method works correctly but when i call it repeatledly (in a loop) with data, some time it gives me foreign key error which state that orderid is not existsin table Order. This will happen only randomly. I am not able to figureout the reason. does some one face the same problem. if yes, what could be the reason and/or solution.
View 5 Replies
View Related
Jul 8, 2007
Hi,
I want to develop a monthly salary report for our employees. The salary is calculated on daily basis for individiaul employees. Thus the report is supposed to display Employee Name, ID etc + 31 columns (representing maximum days in a month). The report will display the employee basic information along with their salary calculation for each day. So its like 35 columns report. I already have used a cursor in my stored procedure with SQL Server 2005 to get this report (displayed in .NET Page) but the performance is more than worst and takes even 3-4 minutes to compile (which ofcourse is unacceptable). There are around 300 employees and they belong to different departments. When we select a given department, all the respective employees report has to be displayed. If i select All Departments, then the entire 300 employees report has to be displayed. I dont want to cache the data as it will have a big performance issue for the server.
I want some good suggestion on how to develop a monthly report in SQL Server 2005 which can display the data with optimized performance. Kindly help of any guru will be highly appreciated.
Regards...
View 2 Replies
View Related
Aug 13, 2007
When is RTRIM needed in a Select ... where clause. I noticed that if I have a column named TEXT varchar(17) which is varchar and in the where clause I state where
TEXT = 'This is the text'
or I state
TEXT = 'This is the text ' followed by 4 spaces
The equate still works - so when do I need RTRIM?
Do I need RTRIM for a host variable:
...where TEXT = RTRIM(:VAR_001)
if the host variable is the same length as the TEXT column field in the SQL Server 2005 database?
View 6 Replies
View Related
May 7, 2008
Hello,
I hope this is in the right forum, I'm new to MSDN. I'm also not an expert in SQL, so please bear with me as I was unable to find an answer by searching the archives.
We have a single SQL 2005 Standard installation with 30 user CALs on a Server2003R2 box. We have another Server 2003 box in a remote but connected location that we previously used for backup replication. What we'd like to do is use some of the replication features in SQL 2005 to replicate the data over every night to the other box, onto which we would install another copy of SQL 2005. This would not be a server accessed by clients, but simply a "live backup", that in the event of a catastrophic failure, could be manually set up to operate as a SQL server in place of the failed primary server. I don't need any kind of automatic "failover", just the ability to replicate the data over to the other system so I have two SQL Servers that synchronize from one to the other overnight.
I was told that I wouldn't need to buy the user CALs again, that the 30 user cals would translate over if the primary server fails and has to be replaced by the cloned server. But what do I buy for the SQL server itself? A boxed version is a bit pricy and comes with 5 CALs which apparently I wouldn't need. Then I noticed that on softwaremedia.com the open license version, which can be bought a la carte, lists a requirement of buying at least 5 cals or a processor license. What exactly do I need to do this, and how can I get it for the least amount of further spending?
Thanks!
View 3 Replies
View Related
Jun 29, 2006
In DTS 2000 there was situation where I had to connect source and destination through a dialup connection (56k), where the transferring of data took me ages to finish, so it was not successful.
In SSIS 2005 is there is a solution?
Thank you
Cheers,
Pradeep.
View 1 Replies
View Related
Jul 11, 2006
Hi
We want to upgrade the Clustered SQL Server 2000 in our production environment to SQL Server 2005 (Clustered).
Are there any complexities we need to take care of?
Do we need a downtime during the period when the SQL server is being upgraded?
Thanks
Priyanka
View 1 Replies
View Related
Jun 5, 2006
I get the following error when I try to access the database from my web application
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
The application works when I run it on my local machine, but when I move the application to a remote server, I get the above error.
Below is the connection string section in my web.config file.
<connectionStrings> <remove name="LocalSqlServer"/> <add name="LocalSqlServer" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|CiaaASPNETDB.MDF;user instance=true;Integrated Security=True;Initial Catalog=CiaaASPNETDB;Integrated Security=True;user instance=true;Connection Timeout = 0" providerName="System.Data.SqlClient"/> <!--Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|CiaaASPNETDB.MDF;user instance=true;Integrated Security=True;Initial Catalog=CiaaASPNETDB --> </connectionStrings>
Can anyone help me with this.
View 1 Replies
View Related
Feb 12, 2007
Using SQL Server 2000 std. edition, I was bitten by the bug described in KBs 818671 and 289149. Query optimizer using Hash Match Team operators would sometimes fail. I added -T8679 at SQL Server startup.
Now that I'm upgrading to SQL Server 2005, is this trace flag still required?
I see that "this was fixed in SQL 2000, SP1." However, I would like a more precise confirmation that this flag is no longer needed in SQL 2005. Sometimes, no news is not necessarily good news.
The error is intermittent, and at least partially dependent on data conditions not available to me for exhaustive regression testing (or else of course I would do that).
Any info, comments, etc. ... would be welcome.
Thanks ...
Tracy Slack
View 2 Replies
View Related
May 26, 2007
Hello all
I built an application that connect to MS SqlServer2005 using Native driver (sqlncli.msi) I install that file from MS site, I need to deply my application to the end-user, and I would like to know what files do I need to deploy to make sure the application is gona run okay on the client PC's.
I search in the registry for the driver, and I found this "sqlncli.dll", is it enough or I need to include more files !!
Thanks and best regrdas
Waleed
View 1 Replies
View Related
Jul 29, 2006
Hi all,
We are using SQL Server 2005, on Windows server 2003 R2.
We Have Two Database Servers say DBServer1 and DBServer2, Now I wants to do Replication between these to servers, such that
1. The Changes at DBServer1 should be reflected at DBServer2
2. The Changes at DBServer2 should be reflected at DBServer1
3. Changes includes Data changes and Schema Changes
4. After every Synchronization Both Databases should be Identical
I tried doing so, what i did was
I cofigured Distribution at DBServer1, also Publisher and Publication at DBServer1
and Made a Subscription at DBServer2.
What I successfully done is
If Publisher means DBServer1 do some changes then it gets updated at DBServer2.
But New Rows added at DBServer2 doesn't gets added at DBServer1
Thanks in Advance,
Vishalgiri Goswami
Kalptaru Infosoft Pvt. Ltd.
View 4 Replies
View Related
Dec 27, 2006
Hi ,
I have a requirement to migrate the data from an existing MS Access database to a newly designed SQL Express 2005 database . Need less to say the table structures in both are totally different.I would like to know how can i handle a scenerio where i want to map table A in access to table B in SQL express (the schema of both different and the number of columns can vary too) , how do i migrate the data from table A in Access to Table B in SQL express using SSMA?
Also i would appreciate if some one can tell me is SSMA the right tool for this , or should i use the upsizing wizard of MS Access.If there is no change in schema between source and destination databases (more of upsizing) then the process is pretty straight forward , The constraint here is that the data needs to be migrated to a new schema where the column names and number of columns can vary between the source table and destination table.. I just need to migrate data only and no other objects.
Need Help!
Thanks
Mahesh
View 1 Replies
View Related
Mar 21, 2008
Hello,
Background: I am a Software Engineer - not a DBA - with limited SQL knowledge (I know the SQL but not the configuration stuff)... I am sure others need this too - I tried but cannot find the answers online... please help me with a few questions:
Task: I need to find an way to install a database via Installshield 2008/command-line (silently), that is accessible locally and remotely regardless of what was already installed, and accessible only to our program - not users, so I am thinking use SA and a strong password since windows authentication may not apply with multiple users accessing this DB and they do not need to login on to the server - only our program does - sound right to use Mixed?).
Questions:
Can SQL 2005 Express be installed when SQL 2005 Full edition is already present?
Can SQL 2005 Express be installed without an instance name?
I think I read an instance name is required in Express, that if I do not provide it defaults to SQLEXPRESS -
So, if the prior question's answer is 'no, they cannot both exist'... prompting the next question:
If the Full version is already installed, should/can I use it - AND - can I add a New Instance silently via command line or ???
Any other ideas on why I can install 2005Express on one system, and not on two others (all 3 are development machines: VS 2005, SQL Express and that's about it)?
Currently I have figured out a command-line call to install - the command I came up with during testing is:
start /wait C: empSQLEXPR32.EXE /qb INSTANCENAME=CINST ADDLOCAL=ALL SECURITYMODE=SQL SAPWD=STR2PWD4SA SQLBROWSERACCOUNT="NT AUTHORITYNETWORK SERVICE" SQLACCOUNT="NT AUTHORITYNETWORK SERVICE" AGTACCOUNT="NT AUTHORITYNETWORK SERVICE" SQLBROWSERAUTOSTART=1 SQLAUTOSTART=1 AGTAUTOSTART=1 DISABLENETWORKPROTOCOLS=0 ERRORREPORTING=1 ADDUSERASADMIN=1
This looks like it might work for SQL Express 2005 - I do not have the full edition installed, yet - thus, this post.
The errors I got, shown in the summary.txt as 'Error 10' on one system with no other info.
My other system reports:
Microsoft SQL Server 2005 9.00.3042.00
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Fri Mar 21 16:45:25 2008
Machine : TONELSON
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.3042.00
Install : Successful
Log File : c:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0007_TONELSON_SQLSupport_1.log
--------------------------------------------------------------------------------
SQL Server Setup failed. For more information, review the Setup log file in %ProgramFiles%Microsoft SQL Server90Setup BootstrapLOGSummary.txt.
...not much to go on as to why it is failing - maybe the hotfix:
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9Express_Hotfix_KB921896_SQLEXPR.EXE
in the directory is blocking the install?
The goal is for my application to use the same connection strings regardless of 2005 full or 2005 express.
Need to figure this out -
Thank you,
Todd
Software Engineer/Developer... learning a lot about SQL
View 6 Replies
View Related
