Subquery Assistance Needed
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
ADVERTISEMENT
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
Oct 12, 2004
I've got a website with dynamic content, each page (subject) got an ID. On every page there can be a number of links. These are either links to internal other pages on that website or external links.
For the internal links the only thing I need is the ID and Title of that page. Those can be found in the Tbl_subjects. As for external links I need ID, Title and URL which can be found in the Tbl_ext_links.
I've got a table named Tbl_linkboxes with:
- a Subject ID which means that this link belongs on this subject page.
- Link ID which is either an ID from Tbl_subjects or Tbl_ext_links
- External a boolean column to indicate if the Link ID refers to the Subject table or the External links table
There's basically 2 questions:
1) How to make this work? I've got a query below as feeble attempt
2) Should I really really really consider to use 2 columns for IDs and removing the External boolean. And simply setting one of those fields in the columns to >0 while the other is 0.
Okay, here's my attempt
PHP Code:
SELECT s.Sub_id, s.Link_id
(l.external IS FALSE, (SELECT Title FROM Tbl_subjects), (SELECT Title,URL FROM Tbl_ext_links)
FROM Tbl_subjects s
WHERE s.Sub_id = <some id>
Not sure if I should work with IIF here to make it work or something else. I'm almost tempted to kick the boolean column overboard and introduce a JOINT on both columns then, one for external link ids and other for internal page ids.
Amazing how long one can stare at a query and not being able to get it right
View 14 Replies
View Related
Jul 20, 2005
Objective:The primary table I loaded into MySql has 2.5 MM records: ID, Ticker,Date, Price; and all works well. My need is to write a QUERY to exportoutfile?) multiple text files. For example, I have 6 years worth ofdata, and need to generate 1 file per day that contains all the Tickersand Prices for that day. Furthermore, I need the text file name to bethe name of the date (e.g. April 4, 1998 with 1000 Tickers & Priceswould result in a file that was named "040498.txt" (either csv or tabdelimited).Is this possible? If so, can someone please help me in this effort...thealternative is not pretty.Thanks in advance![color=blue]>> Trevor[/color]*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Dec 20, 2007
I have the following SQL Statement and I am trying to pass in a wildcard for the "Branch Parameter". The Branch Parameter is a numeric field.
I need (Branch = %) wildcard to select all the records. Can someone tell me the proper syntax for this?
SELECT EmployeeID, CountryCode, FName, LName, Branch, Title, Status, Required, Total, PercentageFROM dbo.vw_Compliance_Percentage_EmployeeWHERE (Branch = @branch)ORDER BY Branch
I receive the message failed to convert parameter value from string to an int32
View 7 Replies
View Related
Jun 23, 2000
Hello everyone. I've attached a copy of my recently created stored procedure but now I want to expound on it by creating synatx within it that will allow me to create a 'weighting' type of scenario based on pre-determined weight/ranking classifications (example: a selection of skill '1' would grant the user 2 points toward their ranking, a selection of skill '2' might grant the user 4 pts., etc.) In the end, the users would be tallied and sorted based on the highest ranking (in pts) to the lowest.
The business I'm in is that we develop a web site interface for recruiters and potential job seekers to post resumes, develop a career plan and rank their current work status against the open market.
In short, does anyone out there know how I can implement a "ranking" type system into the syntax provided below?
I've considered the CASE statement but was not clear on how it would work.
Any suggestions would be great.
Claude
cjohnson@staffmentor.net
CREATE PROCEDURE spListMatch
@job_id uniqueidentifier
AS
declare @jobcity varchar(50)
declare @jobposition uniqueidentifier
declare @jobrelocate bit
declare @jobtravel uniqueidentifier
declare @jobyears int
declare @jobIndustry uniqueidentifier
declare @Jobstate varchar(2)
declare @candcity varchar(50)
declare @candposition uniqueidentifier
declare @candrelocate bit
declare @candtravel uniqueidentifier
declare @candstate varchar(2)
declare @candindustry uniqueidentifier
declare @candyears int
declare @holdid uniqueidentifier
declare @candidateid uniqueidentifier
declare @displayid int
declare @ks1 varchar(50)
declare @ks2 varchar(50)
declare @ks3 varchar(50)
declare @ks4 varchar(50)
declare @ks5 varchar(50)
declare @match int
declare @key_skill_desc varchar(50)
declare @strongest int
declare @candIndustrydesc varchar(50)
declare @candPositiondesc varchar(50)
declare @candTraveldesc varchar(50)
declare @prefcity varchar(50)
declare @prefstate varchar(2)
declare @citymatch int
declare @icount numeric
declare @totcount numeric
declare @debug int
select @debug = 1
set nocount on
select @jobcity = city, @jobposition = position_id, @jobrelocate = relocate_assist, @jobtravel = travel_id, @jobstate = state, @jobyears = position_yrs from t_job_order where job_id = @job_id
select @totcount = count(*) from t_job_vstat where job_id = @job_id
select @totcount = @totcount + 3
DECLARE Cand_Cursor CURSOR FOR
select candidate_id, key_skill_desc, strongest from t_cand_vstat, t_key_skill where t_cand_vstat.key_skill_id in (select key_skill_id from t_job_vstat where job_id = @job_id) and
t_cand_vstat.key_skill_id = t_key_skill.key_skill_id
order by candidate_id
CREATE TABLE #ReturnTemp (
candidateid uniqueidentifier NOT NULL,
displayid int,
city varchar(50),
state varchar(2),
Industry varchar(50),
travel varchar(50),
position varchar(50),
hitcount smallint,
tpercent numeric,
ks1 varchar(50),
ks2 varchar(50),
ks3 varchar(50),
ks4 varchar(50),
ks5 varchar(50)
)
OPEN Cand_Cursor
declare @candidate_id uniqueidentifier
FETCH NEXT FROM Cand_Cursor into @candidate_id, @key_skill_desc, @strongest
select @holdid = @candidate_id
WHILE @@FETCH_STATUS = 0
BEGIN
if @candidate_id <> @holdid
begin
select @icount = @icount + 1
if @match = 1
update #ReturnTemp set hitcount = @icount, tpercent = (@icount/@totcount * 100), ks1 = @ks1, ks2 = @ks2, ks3 = @ks3, ks4 = @ks4, ks5 = @ks5 where candidateid = @holdid
select @match = 1
select @ks1 = ""
select @ks2 = ""
select @ks3 = ""
select @ks4 = ""
select @ks5 = ""
select @holdid = @candidate_id
select @icount = 1
select @candrelocate = relocate, @candtravel = travel_id from t_cand_pref where candidate_id = @candidate_id
select @candcity = city, @candstate = state, @displayid = display_id from t_candidate1 where candidate_id = @candidate_id
select @candposition = position_id, @candyears = position_yrs, @candindustry = cat_sub_cat_id from t_cand_seek where candidate_id = @candidate_id
if @candposition = @jobposition select @icount = @icount + 10
if @candyears = @jobyears select @icount = @icount + 8
if @candtravel = @jobtravel
begin
select @icount = @icount + 2
end
else if @jobtravel <> '91C858C8-4A46-4FD8-9B73-87FEE00F799E'
begin
if @candtravel = '91C858C8-4A46-4FD8-9B73-87FEE00F799E'
begin
select @match = 0
end
else
begin
select @icount = @icount + 1
end
end
DECLARE City_Cursor CURSOR FOR
select distinct city, state from t_cand_pref_city_state C, t_city_state S where
c.city_state = s.city_state and C.candidate_id = @candidate_id
OPEN City_Cursor
FETCH NEXT FROM City_Cursor into @prefcity, @prefstate
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM City_cursor
into @prefcity, @prefstate
select @citymatch = 0
if ((@prefcity = @jobcity) and (@prefstate = @jobstate))
begin
--do nothing
select @citymatch = 1
select @icount = @icount + 1
end
END
CLOSE City_Cursor
DEALLOCATE City_Cursor
if @citymatch = 0
select @match = 0
if @candindustry <> @jobindustry
select @match = 0
if @strongest = 1
begin
if @ks1 = ""
select @ks1 = @key_skill_desc
else if @ks2 = ""
select @ks2 = @key_skill_desc
else if @ks3 = ""
select @ks3 = @key_skill_desc
else if @ks4 = ""
select @ks4 = @key_skill_desc
else if @ks5 = ""
select @ks5 = @key_skill_desc
end
if @match = 1
begin
select @candIndustrydesc = cat_sub_desc from t_cat_sub_cat where cat_sub_cat_id = @candIndustry
select @candPositiondesc = position_desc from t_position where position_id = @candPosition
select @candTraveldesc = travel_desc from t_travel where travel_id = @candtravel
INSERT INTO #ReturnTemp(Candidateid,
displayid,
city,
state,
Industry,
travel,
position,
hitcount)
values (@candidate_id,
@displayid,
@candcity,
@candstate,
@candIndustrydesc,
@candtraveldesc,
@candpositiondesc,
@icount)
end
end
else
begin
if @strongest = 1
begin
if @ks1 = ""
select @ks1 = @key_skill_desc
else if @ks2 = ""
select @ks2 = @key_skill_desc
else if @ks3 = ""
select @ks3 = @key_skill_desc
else if @ks4 = ""
select @ks4 = @key_skill_desc
else if @ks5 = ""
select @ks5 = @key_skill_desc
end
select @icount = @icount + 1
end
--look at other stuff
FETCH NEXT FROM Cand_cursor
into @candidate_id, @key_skill_desc, @strongest
END
CLOSE Cand_Cursor
DEALLOCATE Cand_Cursor
select * from #ReturnTemp
View 2 Replies
View Related
Oct 26, 2004
Code:
select *
from linkboxes l
left outer
join subjects s
on l.sub_link_id
= s.sub_id
left outer
join external_links e
on l.ext_link_id
= e.ext_id
where l.sub_id = subid
View 4 Replies
View Related
May 22, 2008
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
View 3 Replies
View Related
Nov 13, 2007
I am getting the following syntax error to my query below. This query is similar to many I currently use and I have not been able to track down the error. Can anyone spot the error?
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 43
Incorrect syntax near 'data'.
Select
e.account_number,
epi.Provider_Role = [1],
epi.Provider_Role = [2],
epi.Provider_Role = [3],
epi.Provider_Role = [4],
epi.Provider_Role = [5],
epi.Provider_Role = ,
epi.Provider_Role = [7],
epi.Provider_Role = ,
epi.Provider_Role = [9],
epi.Provider_Role = [10],
epi.Provider_Role = [11]
from (SELECT e.account_number,
row_number() over (partition by epi.PROVIDER_ROLE order by e.account_number asc) as rownum,
e.medrec_no,
e.account_number,
Isnull(ltrim(rtrim(pt.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(pt.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(pt.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(pt.patient_sname)), '')
AS SRM_PatientName,
CONVERT(int,pm.PatientAge),
left(e.admission_date,11) as Admit_Date,
left(e.episode_date,11) as Disch_Date,
(CASE WHEN DATEDIFF(DAY, e.admission_date,e.episode_date) = 0 Then 1
ELSE DATEDIFF(DAY, e.admission_date,e.episode_date) END) AS LOS,
pm.PrinOpPhys,
epi.PROVIDER_CODE,
epi.PROVIDER_ROLE,
pe.PERSON_NAME as physician_name
From srm.episodes e inner join
dbo.PtMstr pm on pm.accountnumber=e.account_number inner join
srm.ITEM_HEADER ih ON ih.ITEM_KEY = e.EPISODE_KEY INNER JOIN
srm.PATIENTS pt ON pt.PATIENT_KEY = ih.LOGICAL_PARENT_KEY inner join
srm.CDMAB_PROV_EPI epi on epi.episode_key=e.episode_key inner join
srm.providers p on p.provider_key = epi.provider_key inner join
srm.person_element pe on pe.item_key = p.provider_key
Where e.episode_date is not null and pm.AnyProc like '%4495%'
) data
PIVOT
(max(epi.Provider_Role) for rownumber
in ( [1], [2], [3], [4], [5], , [7], , [9], [10], [11] )) pvt
order by e.account_number
Thanks!
View 10 Replies
View Related
Nov 7, 2007
Hello, I have the following stored procedure and the following aspx page. I am trying to connect this aspx page to the stored procedure using the SqlDataSource. When the user enters a branch number in textbox1, the autonumber generated by the database is returned in textbox2. I am not quite sure what to do to get this to execute. Can someone provide me assistance? Will I need to use some vb.net code behind?
Stored ProcedureCREATE PROCEDURE InsertNearMiss @Branch Int, @Identity int OUT ASINSERT INTO NearMiss (Branch)VALUES (@Branch)
SET @Identity = SCOPE_IDENTITY()
GO
ASPX Page
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NearMissConnectionString %>" InsertCommand="InsertRecord" InsertCommandType="StoredProcedure" SelectCommand="InsertRecord" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="TextBox1" Name="Branch" PropertyName="Text" Type="Int32" /> <asp:ControlParameter ControlID="TextBox2" Direction="InputOutput" Name="Identity" PropertyName="Text" Type="Int32" /> </SelectParameters> <InsertParameters> <asp:Parameter Name="Branch" Type="Int32" /> <asp:Parameter Direction="InputOutput" Name="Identity" Type="Int32" /> </InsertParameters> </asp:SqlDataSource> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
View 2 Replies
View Related
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 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
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
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 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
Oct 23, 2007
Hi Folks,
I have a slight problem designing two drop down lists accessing my database as some clients are listed twice because they are located in multiple citys
I have one table called Clients with the following columns
Client
City
F_name
L_name
ID
I am using SELECT DISTINCT Client FROM Clients for my first Dropdownlist with a postback etc.
e.g.
Brown
Black
The Second Dropdownlist must select the different Distinct City Values where they are located on selecting Dropdownlist one
e.g.
Brown > London, New York
or
Black > Singapore and Boston
What SQL Code can i place so the second dropdownlist will select the correct values on selecting the first Dropdownlist.
Thanks,
Sully
View 4 Replies
View Related
Sep 16, 1999
I need some explanantion on the following query that I'm trying to run:
if exists (select * from sysobjects
where id = object_id ('slice_trace')
and sysstat & 0xf = 3)
delete from slice_trace
where control_seq_number = 130
and claim_number = 7912450
When I run this query within a database where the slice_trace table does NOT exist, it still seems to execute the delete statement and hence fails because the object is not there.
Essesntially I need this statement to execute only in databases where the table exists.
View 3 Replies
View Related
Apr 9, 2008
I new it was a mistake changing from mysql to mssql! I'm having difficulty with the following and would really appreciate any help you could offer.
I have two tables.
The first [users] has personal details:
[id]
[business_name]
[title]
[surname]
[firstname]
The second [orders] has their orders:
[order_id]
[user_id]
[order_date]
[order_price]
I need a query that returns the latest record from [orders] for each member, with member details, but only where the latest order was within (now)-425.
Many thanks.
View 1 Replies
View Related
Mar 19, 2008
Dear friends!
I'd like to ask your assistance in writing some T-SQL.
I have a table
MYTABLE (THEDATE datetime, TEMPERATURE numeric(4))
3.02 15
4.02 16
5.02 16
8.02 13
10.02 15
11.02 15
12.02 19
I need a T-SQL without using analitic(range) functions like RANK or DENSE-RANK to provide the following output:
3.02 15
4.02 16
8.02 13
10.02 15
12.02 19
In other words I need to obtain only first occurance of the "neibough" adjacent rows where the temperature is equal.
Please advice.
Thanks in advance.
View 4 Replies
View Related
Jul 20, 2005
I have received a table of data that has a field containing dateinformation. Unfortunately it was derived from a MainFrame dump andoriginated as a txt file and was then ported into an Access MDB filebefore it became an SQL table. The date format is vchar(50) andactually is comprised of 6 charecters ie: 010104 for Jan 1 2004. Ineed to run a select statement for a range of dates such as 010104thru 030104. Unfortunately being a charecter field this returnsincorrect results under a majority of cases. Back in my dBase daysthere was a VAL() that could be used in this case but I have beenunable to find anything comperable in SQL. Can anyone help me please?Thanks in advanceSteve
View 2 Replies
View Related
Jul 27, 2007
Error: "A connection was successfully established with the server, but an error occurred during the pre-login handshake. (provider: SSL Provider, error:0 - The certificate chain was issues by an authority that is not trusted.) (Microsoft SQL Server)
I am running SQL Server 2005 Developer ed. Windows XP SP2
Trying to connect over the internet to a SQL Server 2005 Workgroup ed. SP1 on Windows Small Business Server 2003 SP1
I have had success doing this before.
I can terminal sevice in to the box and confirm my credentials work
"Force Enycrption" has not been enabled on either the server or the client
The Certificate tab is clear under "Protocols for MSSQLServer", but the server does have certs I can see them in the "Certificate" dropdown.
Any help would be great.
John
View 11 Replies
View Related
Feb 28, 2008
Hey all,
I have been working for some time on this T-SQL statement and getting it to work the way I would like it to work. I have been reading through books and everything, but am still having trouble. I am pretty new to T-SQL so I am probably making some beginners mistakes.
Basically I am working on a SQL 2005 server and I have a single database with two tables. Below are the tables and the fields that I am using from each. The table name is in bold and the fields are plain.
Users
username (PK, varchar(7), not null)
full_name (varchar(50), not null)
call_history
queue (varchar(6), not null)
update_date (datetime, null)
status (char(1), not null)
Ok, now that you have the gist of the tables and fields. What I am trying to do is the impossible I think. Basically I work in a call center that supports software. The queue field is the identifier of what specialist is handling any call within the table. It can be linked to the "username" on the Users table. One thing I just noticed is that the varchar has a difference. Will that affect my below select statement?
SELECT u.full_name AS 'Specialist Name', COUNT (*)
FROM dbo.call_history c
LEFT OUTER JOIN dbo.users u ON c.queue = u.username
WHERE (c.status = 'P') AND (DATEDIFF(dayofyear, update_date, getdate()) >= 6) AND (DATEDIFF(dayofyear, update_date, getdate()) <= 9)
AND queue IN('alatif', 'AWILLI', 'AYOUNG', 'BPRING', 'CSKINN', 'DALDEN', 'DBACCH', 'DGIZZI', 'DKUSSA', 'DMCCUE',
'EKEPFE', 'GBACKH', 'GJONES', 'HESTAL', 'JBANKS', 'JCRICH', 'JDELGA', 'JFOLCH', 'JGRAVE', 'JHARRI',
'JLI', 'JMYERS', 'JPOPPE', 'JRICHA', 'JRIMME', 'JTHOMP', 'JWELLS', 'KDUKHI', 'KSTANL', 'LCHAMP', 'LGABOR',
'LHARVE', 'LMONTG', 'LSHORT', 'LTOM', 'MBECK', 'MJONES', 'MVANDE', 'NBROWN','NTOMPK', 'PELLIS',
'RATTAR', 'RDODGE', 'TANDRO', 'TBROWN', 'TDAVIS', 'TNDREX', 'TNORRI', 'YSOSA', 'YWILLI')
GROUP BY full_name
ORDER BY full_name
So here is my dilemma:
I am trying to emulate a spreadsheet that was given to me by my boss. This is pretty much going to determine whether or not I can get a job as the Web Developer. So the spread sheet displays the number of calls that have not been updated in X amount of days for all of our reps. I have been trying so many different varieties from nested SELECT statements now to joins. To put it simply enough, do I need to do a query that populates column by column?
Here is what it should look like:
http://i135.photobucket.com/albums/q129/tico1177/CropperCapture1.jpg
As you can see, I have to include everyone even if they have zero. That is where the problem comes in. When I use the above statement, the list shows up but takes away people from the list instead of keeping them and placing a null for the count. I have tried placing a HAVING statement at the bottom of the query to compensate for COUNT(*) = 0, but I get an error. I think because I have a WHERE statement.
I am trying to see if this whole thing can be done with a SQL statement to avoid having loop though in code. I basically want to populate a datagridview with the information that I gather.
Is this possible?
All help is greatly appreciated!
View 26 Replies
View Related
Jan 14, 2008
I am trying to build a related article display. I have a SQLDataSource that I want to be able to select information (Category) from the table (Articles) based on a querystring (ID). SELECT [Category] FROM [Articles] WHERE ([ArticleID] = @ID) which for an example, lets say ID = 1, and it results as Category = Health.That is easy enough, but how would I then select all columns from the table WHERE Category = Result of first SELECT? SELECT * FROM [Articles] WHERE ([Category] = ??? Result of prior select) Can this be done in 1 select command, or would a store procedure need to be written? I am a little lost, sincr I am using a SQLDataSource, and it will be displayed with a Repeater. Thanks!
View 2 Replies
View Related
Feb 5, 2008
I'm using SQL 2000 and would like to send a generated email using this stored procedure:
select Libraryrequest.LoanRequestID, Titles.Title, requestors.fname+ ' ' + requestors.lname as [Name], libraryrequest.requestdate,libraryrequest.shipdate,libraryrequest.duedatefrom libraryrequestjoin requestors on requestors.requestorid=libraryrequest.requestoridjoin Titles on Titles.Titleid = Libraryrequest.titleidwhere duedate < DATEADD(day, DATEDIFF(day, '20010101', CURRENT_TIMESTAMP), '20010101')
I know I need to go to Management, SQL Server Agent, Jobs, New Job. Do I put the stored procedure in the descriptions part? After that I'm lost what do I do.
Thanks!
View 10 Replies
View Related
May 23, 2008
Good Morning,
I need to write a query to provide MaxDate of each Exam and still show the ID of that Exam. My table is
ID Exam Date
1 FMS 1/1/2006
2 FMS 1/1/2007
3 FMS 1/1/2008*
4 ECS 1/1/2006
5 ECS 1/1/2007
6 ECS 1/1/2008* My attempted query isSELECT ID, Exam, Max(Date) AS MaxOfDateFROM Table1
GROUP BY ID, Exam;
My query actual results
ID Exam Date
1 FMS 1/1/2006
2 FMS 1/1/2007
3 FMS 1/1/2008*
4 ECS 1/1/2006
5 ECS 1/1/2007
6 ECS 1/1/2008* My desired results
ID Exam Date
3 ECS 1/1/2007
6 ECS 1/1/2007
I would appreciate any help that could be provided.
Thanks!
View 17 Replies
View Related
Jul 28, 2005
First off, here is my query:SELECT DeviationDist.DEVDN, DeviationDist.DEVDD, DEVDA, DEVCT, DEVST, DEVBG, DEVDV, DEVPS, DEVAT, DEVCN, DEVCF, DEVCP, DEVCEFROM DeviationDistINNER JOIN DeviationContact ON DeviationContact.DEVDN = DeviationDist.DEVDNWHERE DeviationDist.DEVDN = '200270'ORDER BY Deviationdist.DEVDN DESCI'm joining the deviationcontact table to the deviation dist table by DEVDN. This query works fine except when the DeviationContact table doesnt contain any records for the DEVDN that the DeviationDist table does contain. In my app, Deviationdist will always have an record for each DEVDN, but DeviationContact may not. I would like to still get the results back for what records exist in the DeviationDist table, even if DeviationContact has no associated records, but still show them if it does...
View 1 Replies
View Related
Jul 24, 2001
-- Joins are not yet my friend.
-- I want all sites even if they are not in the syslog table.
-- select count(gssites.sname) from gssites
-- where gssites.rectype = 'S' = 67
-- this query returns 13 rows, I want all 67
declare @start int, @end int
set @start = 1
set @end = 0
selectgssites.sname'SyncSite'
,syslog.resultcode'RES'
,rtrim(convert(varchar(2),datepart(month,SYSLOG.ON DATE))
+'-'+convert(varchar(2),datepart(day,SYSLOG.ONDATE ))
+'-'+convert(varchar(4),datepart(year,SYSLOG.ONDATE)) )'SyncDate'
from Syslog
left outer join gssites
on gssites.sname = substring(syslog.siteid,9,8)
wheregssites.rectype = 'S'
AND (SYSLOG.RECTYPE='G')
AND (SYSLOG.ONDATE
Between (GetDate()-@start)
And GetDate()-@end)
AND (SYSLOG.SITEID<>'')
AND (SYSLOG.RESULTCODE='SUC')
ORDER BY SyncSite, syslog.ondate
--TIA
jEfFp...
View 2 Replies
View Related
Nov 30, 2005
Hi,
I have a need to renumber or resequence the line numbers for each unique claim number. For background, one claim number many contain many line numbers. For each claim number, I need the sequence number to begin at 1 and then increment, until a new claim number is reached, at which point the sequence number goes back to 1. Here's an example of what I want the results to look like:
ClaimNumber LineNumber SequenceNumber
abc123 1 1
abc123 2 2
abc123 3 3
def321 5 1
def321 6 2
ghi456 2 1
jkl789 3 1
jkl789 4 2
So...
SELECT ClaimNumber, LineNumber, <Some Logic> AS SequenceNumber FROM MyTable
Is there any way to do this?
Thanks,
Dennis
View 4 Replies
View Related
Mar 2, 2004
Hi,
Hopefully this will be painless for you guys/gals - however due to my lack of skills/knowledge I need some clarification.
I have table_X which I have a trigger on INSERT setup.
This trigger updates Field_2 = '1' and inserts some rows in another table.
Is there some way that I can restrict this trigger to only run when Field_1 = "BLAH"
So essentially I am trying to find out how I can pull information/data from the record that fired the trigger and use this in the trigger? (ie to check if Field_1 = "BLAH" and to use Field_3 to further restrict the underlying triggers' updates and inserts)
Hopefully I have given enough information on this one - if not please let me know any points that I should need to clarify.
Thanks in advance for your help!!!
Cheers
View 6 Replies
View Related
Mar 5, 2007
I realize this query is inherently incorrect, but my issue is mainly syntax. The line, "WHEN a.order_id <> b.order_id THEN" is wrong. I want to ensure that a.order_id is not in the settlement table. So I was thinking something along the lines of "WHEN a.order_id not in (select order_id from settlement)" which I know will cause a slower response time, but I'm willing to deal with it. In any case, that syntax doesn't appear to work.
sum(
CASE
WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'E' and (
CASE
WHEN a.order_id <> b.order_id THEN
a.transaction_date
ELSE
b.delivery_date
END) used_date datediff(d,used_date, ".$cutOffDate.") < 30) THEN
a.amount
END) earn_amount_rtp_curr,
Any help here would be hotness!
Thanks!
View 10 Replies
View Related
