SELECT
#dp_all.RegistrationNumber AS 'номер устройства',
#dp_all.DeviceLocation AS 'Номер ТС',
MAX(#card_transactions.timestamp) AS 'дата валидации',
#dp_all.LastVersionReadTime AS 'дата связи'
FROM
#dp_all
INNER JOIN #card_transactions ON
#dp_all.RegistrationNumber like '%' + #card_transactions.device_reg_no
WHERE
#dp_all.DeviceLocation is not null
GROUP BY
#dp_all.RegistrationNumber,
#dp_all.DeviceLocation,
#dp_all.LastVersionReadTime
ORDER BY
#dp_all.DeviceLocation
Где:
- RegistrationNumber -bigint,
- DeviceLocation — nvarchar,
- LastVersionReadTime — datetime,
- derice_reg_no — bigint.
Anton Shchyrov
33k2 золотых знака29 серебряных знаков59 бронзовых знаков
задан 11 сен 2018 в 13:13
7
Есть такая штука Data type precedence. Именно из-за приоритетов вы получаете ошибку при сравнении, уже неявно приобразованной строки с bigint значением.
Второй момент, сравнивать эти поля стоит тогда когда они оба будут одного типа. Преведите первый в varchar(n)/nvarchar(n) и второй с помощью CAST или CONVERT и сравнивайте вашим подходом наздоровье.
Пример условия:
ON convert(nvarchar,#dp_all.RegistrationNumber) like '%' + convert(nvarchar,#card_transactions.device_reg_no)
ответ дан 11 сен 2018 в 13:41
Nick ProskuryakovNick Proskuryakov
3,7222 золотых знака14 серебряных знаков39 бронзовых знаков
2
- Remove From My Forums
-
Question
-
Hi,
I am using one query in my Stored procedure when i am passing the values i am getting the error like Error converting data type varchar to bigint.
select @BasicPrice = convert(float, rate_dc) from RENAULT.TMFSCBaseValue
where PKConditionType_vc = ‘ZPRS’ and PKSalesDocType_vc = ‘YP’ and
PKDistributionChannel_vc = ’01’ and PKDivision_vc = ’91’
and PKServiceNo_vc = @ServiceNo and PKModelCode_vc = @ModelCode
and convert(bigint,@InvoiceDate) between convert(bigint,PKValidityStartDate_dt)
and convert(bigint,PKValidityEndDate_dt)Input values
@DealerCode :: LN010391
@VehSrNo :: 9ZJ16246
@ModelCode :: LP11PB15RO11WD
@ServiceNo :: 1
@InvoiceDate :: 09/10/20
@ServiceDate :: 24/06/20in table TMFSCBaseValue PKValidityStartDate_dt data type id varchar
Please help someone on this
Ramana
Answers
-
Change the format of @ServiceDate :: 24/06/20
Currently, it is being passed in the format dd/mm/yy. Instead, make it as
mm/dd/yy and you are done.Hope, this helps !
-Vinay Pugalia
If a post answers your question, please click «Mark As Answer» on that post or
«Vote as Helpful».
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia-
Marked as answer by
Tuesday, June 28, 2011 11:35 AM
-
Marked as answer by
- Remove From My Forums
-
Вопрос
-
Hi,
I am a newbie user of MS SQL. I’m creating this procedure and been searching for the right solution to this problem for so long. I really need help this time! When I compile the procedure, it was fine but when I try to execute it like:
EXEC TA_COPY_TKT_DB 201166573491, 201166573491, ‘MSSQLSERVERDEV’, ‘MSSQLSERVERDEV’, ‘Demo84’, ‘Demo841’
I get this error:
Msg 8114, Level 16, State 5, Procedure TA_COPY_TKT_DB, Line 24
Error converting data type varchar to bigint.
Here’s the whole procedure I created:
ALTER PROCEDURE [dbo].[TA_COPY_TKT_DB]
@FromDocID
T_DOC_ID,@ToDocID
T_DOC_ID,@FromServerName
VARCHAR(50),@ToServerName
VARCHAR(50),@FromDatabaseName
VARCHAR(50),@ToDatabaseName
VARCHAR(50)as
begin
Declare
@SqlStmt
VARCHAR(150)Set Nocount On
/* Check PS_DOC_HDR if exists */
Begin Tran
EXEC USP_DEL_TKT @FromDocID
print @ToDocID
print @FromDocID
Set @SqlStmt = ‘INSERT INTO ‘ + @ToServerName + ‘.’ + @ToDatabaseName + ‘.dbo.PS_DOC_HDR ‘ +
‘SELECT * FROM ‘ + @FromServerName + ‘.’ + @FromDatabaseName + ‘.dbo.PS_DOC_HDR WHERE DOC_ID = ‘ + convert(bigint,@FromDocID)
Commit Tran
end
Ответы
-
Your last statement is of the form
Set @SqlStmt = <some concatened strings> + convert(bigint,@FromDocID)
But the result of convert(bigint, @FromDocID) is (of course) a bigint. So you have a string type and a bigint type. Bigint has a higher priority then varchar, so SQL will try to convert the concatened strings to a bigint, which it can’t do so you
get the error.So you must make the convert(bigint, @FromDocID) into a varchar (or char) type. Since you declared it as a T_DOC_ID datatype and didn’t tell us what T_DOC_ID really is, it’s hard to know exactly what you want. Some possibilities
If T_DOC_ID is an integer type (tiny int, smallint, int, bigint) then
Set @SqlStmt = <some concatened strings> + convert(varchar(20),@FromDocID)
If T_DOC_ID is something else then
Set @SqlStmt = <some concatened strings> + convert(varchar(20),convert(bigint, @FromDocID))
Tom
-
Помечено в качестве ответа
2 января 2012 г. 4:30
-
Помечено в качестве ответа
February 25, 2016 at 12:03 pm
#308419
I am working on a project where I need to move values from one table (tableOriginal) into another table (tableNew). The original table has two columns EstCards / EstAccts that are stored as varchar but in the new table, we want these values stored as integer (technically bigint because some values in the original table for some reason are in the billions).
When I do the following SQL Statement (see pasted below), I am getting an error saying (this will be added to the SELECT INTO statement but for now just need to convert the varchar values to bigint and if they cannot be converted, enter zero in the new table):
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
I am not sure what record this is breaking on to know what I am missing. Do you know of a way to check if a varchar can be converted to a number and if it cannot, I want to insert a zero or even know how to find the record that is failing the test? I really only think I need only one “WHEN” statement, but I am trying anything I can think of.
SELECT
PID
, IDate
, BegDate
, EndDate
, CASE
WHEN EstCards IS NULL THEN 0
WHEN LEN(EstCards) = 0 THEN 0
WHEN ISNUMERIC(EstCards) = 0 THEN 0
WHEN ISNUMERIC(EstCards) = 1 THEN CAST(EstCards AS BIGINT)
ELSE 0
END
, CASE
WHEN EstAccounts IS NULL THEN 0
WHEN LEN(EstAccounts) = 0 THEN 0
WHEN ISNUMERIC(EstAccounts) = 0 THEN 0
WHEN ISNUMERIC(EstAccounts) = 1 THEN CAST(EstAccounts AS BIGINT)
ELSE 0
END
, 0
, 0
, ‘Data Migration’
, ‘svc’
, GETDATE()
FROM
tableOriginal
Any help is appreciated
dogramone
Hall of Fame
Points: 3216
kocheese
SSCommitted
Points: 1517
Appreciate the help, but I do not have access to create database objects. We «own the data, but not the objects / database», so if possible, I need to be able to do this with built in T-SQL.
Luis Cazares
SSC Guru
Points: 183695
No need for a performance problem caused by a scalar udf. There’s a simple formula explained in the following article that can help you simplify your code.
http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/
SELECT
PID
, IDate
, BegDate
, EndDate
, CASE
WHEN EstCards NOT LIKE '%[^0-9]%' THEN CAST(EstCards AS BIGINT)
ELSE 0
END
, CASE
WHEN EstAccounts NOT LIKE '%[^0-9]%' THEN CAST(EstAccounts AS BIGINT)
ELSE 0
END
, 0
, 0
, 'Data Migration'
, 'svc'
, GETDATE()
FROM
tableOriginal;
Note: This won’t handle negative values. You’ll need to modify the condition to handle negative values correctly.
Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?
How to post data/code on a forum to get the best help: Option 1 / Option 2
SQLBill
SSC Guru
Points: 51440
You should also be able to use ISNUMERIC to see what data is not numeric and therefore can’t be converted.
-SQLBill
Luis Cazares
SSC Guru
Points: 183695
If by any chance you’re working with SQL Server 2012 or later, you can use TRY_CAST or TRY_CONVERT.
That wasn’t my first suggestion because this is the 2008 forum, so it might not apply to you.
Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?
How to post data/code on a forum to get the best help: Option 1 / Option 2
Jeff Moden
SSC Guru
Points: 1002845
Luis Cazares (2/25/2016)
If by any chance you’re working with SQL Server 2012 or later, you can use TRY_CAST or TRY_CONVERT.
Have you compared it for performance?
Luis Cazares
SSC Guru
Points: 183695
Jeff Moden (2/25/2016)
Luis Cazares (2/25/2016)
If by any chance you’re working with SQL Server 2012 or later, you can use TRY_CAST or TRY_CONVERT.
Have you compared it for performance?
I hadn’t test it for performance myself, but I just did. The use of TRY_CAST or TRY_CONVERT uses minimal resources and is almost as fast as not converting the data at all. Using the CASE statement is about 5 times slower than using just functions.
Here’s the quick test that I made. I changed the amount of invalid values but didn’t impact the performance.
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4 a, E2 b
)
SELECT CAST( n AS varchar(7))
+ CASE WHEN n < 100000 THEN 'a' ELSE '' END --adding some invalid conversions
AS nString
INTO #Test
FROM cteTally;
DECLARE @nString varchar(7),
@nBigInt bigint;
SET STATISTICS TIME ON;
PRINT 'Dry run';
SELECT @nString = nString
FROM #Test;
PRINT 'Using TRY_CONVERT';
SELECT @nBigInt = ISNULL( TRY_CONVERT( bigint, nString), 0)
FROM #Test;
PRINT 'Using CASE';
SELECT @nBigInt = CASE WHEN nString NOT LIKE '%[^0-9]%' THEN CAST(nString AS BIGINT)
ELSE 0
END
FROM #Test;
SET STATISTICS TIME OFF;
GO
DROP TABLE #Test;
I remember that you mentioned a similar issue when formatting dates using CASE instead of native functions.
Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?
How to post data/code on a forum to get the best help: Option 1 / Option 2
Jeff Moden
SSC Guru
Points: 1002845
Jeff Moden
SSC Guru
Points: 1002845
SQLBill (2/25/2016)
You should also be able to use ISNUMERIC to see what data is not numeric and therefore can’t be converted.
-SQLBill
Careful now. ISNUMERIC should never be confused with ISALLDIGITS. It will allow a great many things that, for example, will translate to a MONEY datatype but not to an INT. Please see the following article for 1 of the alternatives…
http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/
Jeff Moden
SSC Guru
Points: 1002845
Luis Cazares (2/27/2016)
Jeff Moden (2/25/2016)
Luis Cazares (2/25/2016)
If by any chance you’re working with SQL Server 2012 or later, you can use TRY_CAST or TRY_CONVERT.
Have you compared it for performance?
I hadn’t test it for performance myself, but I just did. The use of TRY_CAST or TRY_CONVERT uses minimal resources and is almost as fast as not converting the data at all. Using the CASE statement is about 5 times slower than using just functions.
Here’s the quick test that I made. I changed the amount of invalid values but didn’t impact the performance.
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4 a, E2 b
)
SELECT CAST( n AS varchar(7))
+ CASE WHEN n < 100000 THEN 'a' ELSE '' END --adding some invalid conversions
AS nString
INTO #Test
FROM cteTally;
DECLARE @nString varchar(7),
@nBigInt bigint;
SET STATISTICS TIME ON;
PRINT 'Dry run';
SELECT @nString = nString
FROM #Test;
PRINT 'Using TRY_CONVERT';
SELECT @nBigInt = ISNULL( TRY_CONVERT( bigint, nString), 0)
FROM #Test;
PRINT 'Using CASE';
SELECT @nBigInt = CASE WHEN nString NOT LIKE '%[^0-9]%' THEN CAST(nString AS BIGINT)
ELSE 0
END
FROM #Test;
SET STATISTICS TIME OFF;
GO
DROP TABLE #Test;I remember that you mentioned a similar issue when formatting dates using CASE instead of native functions.
Very cool. I’ll try this Monday when I get to work. Thank you, Sir.
Considering the miserable performance of the new FORMAT function, I’m suspicious of anything new anymore. I really appreciate you putting this together. Thanks.
Hugo Kornelis
SSC Guru
Points: 64770
I’m a bit late to the party, but depending on what version of SQL Server you are on, have you looked at using TRY_CAST insterad of trying to build it yourself with that clunky CASE expression?
EDIT: Oops, never mind. Now I see that someone else already posted that suggestion. My bad.
Ed Wagner
SSC Guru
Points: 287024
Well, until this thread, I’d never considered racing my NumbersOnly ITVF against the new TRY_CONVERT function. We’re just migrating one instance to SQL 2012 and it had never occurred to me. Now, however, I have something new to try. They don’t have identical functionality, but are similar enough that the curiosity is killing me.
I honestly hope TRY_CONVERT performs well. Thank you, Luis, for you post causing me me to think of it.
Last question on this pesky report I am working on!!
Please note, the SQL is handled dynamically by the SQL Server, please disregard the Coding in the WHERE Clause. It will look really odd to you, but trust me its not an issue. I am getting the below mentioned error.
Msg 8114, Level 16, State 5, Line 215
Error converting data type varchar to bigint.
SET NOCOUNT ON
CREATE TABLE #Details
(
TicketNumber VARCHAR(20),
FacilityId VARCHAR(20),
PatientControlNumber VARCHAR(20),
PatientMRN VARCHAR(20),
SSN VARCHAR(9),
Zip VARCHAR(10),
DOB VARCHAR(15) ,
Sex VARCHAR(1),
AdmissionDate VARCHAR(15),
[Source of Admission] VARCHAR(10),
PatientDischargeStatus VARCHAR(10),
DischargeDate VARCHAR(15),
PrimaryDiag VARCHAR(10),
SecondDiag VARCHAR(10),
ThirdDiag VARCHAR(10),
FourthDiag VARCHAR(10),
FifthDiag VARCHAR(10),
SixthDiag VARCHAR(10),
SeventhDiag VARCHAR(10),
EighthDiag VARCHAR(10),
NinthDiag VARCHAR(10),
PrincipleCode VARCHAR(15),
SecondCode VARCHAR(15),
ThirdCode VARCHAR(15),
FourthCode VARCHAR(15),
FifthCode VARCHAR(15),
SixthCode VARCHAR(15),
[Primary CPT Date] VARCHAR(15) ,
[Coding Method Used] VARCHAR(10),
[Statement Covers Period] VARCHAR(20) ,
[Total Charges] bigint ,
[Primary Payer] VARCHAR(50),
[Secondary Payer] VARCHAR(50),
[Third Payer] VARCHAR(50),
[Performing Physician ID] VARCHAR(15),
[Other Physician ID] VARCHAR(15),
[Type of Bill] VARCHAR(5),
Primary_Diag VARCHAR(10),
Second_Diag VARCHAR(10),
Third_Diag VARCHAR(10),
Fourth_Diag VARCHAR(10),
Fifth_Diag VARCHAR(10),
Sixth_Diag VARCHAR(10)
)
INSERT INTO #Details
SELECT
pv.TicketNumber,
RIGHT(SPACE(13) + ISNULL(f.FederalTaxId,''), 13)AS FacilityId ,
RIGHT(SPACE(17) + ISNULL(pp.PatientId,''), 17) AS PatientControlNumber ,
RIGHT(SPACE(17) + ISNULL(pp.MedicalRecordNumber, ''), 17)AS PatientMRN ,
CASE
WHEN pp.SSN IS NULL THEN RIGHT(SPACE(9) + ISNULL(pp.SSN, '300'),9)
ELSE LEFT(pp.SSN , 9)
END AS SSN ,
LEFT(ISNULL(pp.Zip,'') + SPACE(5) , 5) AS Zip ,
REPLACE(CONVERT(VARCHAR(10) , pp.birthdate , 101) , '/' , '') AS DOB ,
pp.Sex ,
CASE
WHEN pvf.HospitalizationFrom IS NULL THEN REPLACE(CONVERT(VARCHAR(10) , pvp.DateOfServiceFrom , 1) , '/' , '')
ELSE REPLACE(CONVERT(VARCHAR(10) , pvf.HospitalizationFrom , 1) , '/' , '')
END AS AdmissionDate ,
ISNULL(ml.Code,'9') AS [Source of Admission] ,
ISNULL(mll.Code,'09') AS PatientDischargeStatus ,
CASE
WHEN pvf.HospitalizationTo IS NULL THEN REPLACE(CONVERT(VARCHAR(10) , pvp.DateOfServiceTo , 1) , '/' , '')
ELSE REPLACE(CONVERT(VARCHAR(10) , pvf.HospitalizationTo , 1) , '/' , '')
END AS DischargeDate ,
RIGHT(SPACE(5) + ISNULL(REPLACE(pvd1.ICD9Code, '.', ''),''),5) AS Primary_Diag ,
RIGHT(SPACE(5) + ISNULL(REPLACE(pvd2.ICD9Code, '.', ''),''),5) AS SecondDiag ,
RIGHT(SPACE(5) + ISNULL(REPLACE(pvd3.ICD9Code, '.', ''),''),5) AS ThirdDiag ,
RIGHT(SPACE(5) + ISNULL(REPLACE(pvd4.ICD9Code, '.', ''),''),5) AS FourthDiag ,
RIGHT(SPACE(5) + ISNULL(REPLACE(pvd5.ICD9Code, '.', ''),''),5) AS FifthDiag ,
RIGHT(SPACE(5) + ISNULL(REPLACE(pvd6.ICD9Code, '.', ''),''),5) AS SixthDiag ,
RIGHT(SPACE(5) + ISNULL(REPLACE(pvd7.ICD9Code, '.', ''),''),5) AS SeventhDiag ,
RIGHT(SPACE(5) + ISNULL(REPLACE(pvd8.ICD9Code, '.', ''),''),5) AS EighthDiag ,
RIGHT(SPACE(5) + ISNULL(REPLACE(pvd9.ICD9Code, '.', ''),''),5) AS NinthDiag ,
RIGHT(SPACE(9) + ISNULL(LEFT(pvp.CPTCode, 5) + ISNULL(CONVERT(VARCHAR(4), ml4.Code), ' ') + ISNULL(CONVERT(VARCHAR(4), ml5.Code), ' '),''), 9) AS PrincipleCode ,
RIGHT(SPACE(9) + ISNULL(LEFT(pvp2.CPTCode, 5) + ISNULL(CONVERT(VARCHAR(4), ml6.Code), ' ') + ISNULL(CONVERT(VARCHAR(4), ml7.Code), ' '),''), 9) AS SecondCode ,
RIGHT(SPACE(9) + ISNULL(LEFT(pvp3.CPTCode, 5) + ISNULL(CONVERT(VARCHAR(4), ml8.Code), ' ') + ISNULL(CONVERT(VARCHAR(4), ml9.Code), ' '),''), 9) AS ThirdCode ,
RIGHT(SPACE(9) + ISNULL(LEFT(pvp4.CPTCode, 5) + ISNULL(CONVERT(VARCHAR(4), ml10.Code), ' ') + ISNULL(CONVERT(VARCHAR(4), ml11.Code), ' '),''), 9) AS FourthCode ,
RIGHT(SPACE(9) + ISNULL(LEFT(pvp5.CPTCode, 5) + ISNULL(CONVERT(VARCHAR(4), ml12.Code), ' ') + ISNULL(CONVERT(VARCHAR(4), ml13.Code), ' '),''), 9) AS FifthCode ,
RIGHT(SPACE(9) + ISNULL(LEFT(pvp6.CPTCode, 5) + ISNULL(CONVERT(VARCHAR(4), ml14.Code), ' ') + ISNULL(CONVERT(VARCHAR(4), ml15.Code), ' '),''), 9) AS SixthCode ,
REPLACE(CONVERT(VARCHAR(10) , pvp.DateOfServiceFrom , 1) , '/' , '') AS [Primary CPT Date] ,
'4' as [Coding Method Used] ,
REPLACE(CONVERT(VARCHAR(10) , pvp.DateOfServiceFrom , 1) , '/' , '') + REPLACE(CONVERT(VARCHAR(10) , pvp.DateOfServiceTo , 1) , '/' , '') AS [Statement Covers Period] ,
CAST((pva.OrigInsAllocation + pva.OrigPatAllocation ) * 100 as Bigint ) AS [Total Charges] ,
LEFT(ISNULL(ic.ListName,'') + SPACE(25),25) AS [Primary Payer] ,
LEFT(ISNULL(ic2.ListName,'') + SPACE(25),25) AS [Secondary Payer] ,
LEFT(ISNULL(ic3.ListName,'') + SPACE(25),25) AS [Third Payer] ,
LEFT(ISNULL(od.StateLicenseNo,'') + SPACE(12),12) AS [Performing Physician ID] ,
LEFT(ISNULL(oth.StateLicenseNo,'') + SPACE(12),12) AS [Other Physician ID] ,
'999' AS [Type of Bill] ,
RIGHT(SPACE(4) + ISNULL(REPLACE(pvd1.ICD9Code, '.', ''),''),4) AS Primary_Diag ,
RIGHT(SPACE(4) + ISNULL(REPLACE(pvd2.ICD9Code, '.', ''),''),4) AS Second_Diag ,
RIGHT(SPACE(4) + ISNULL(REPLACE(pvd3.ICD9Code, '.', ''),''),4) AS Third_Diag ,
RIGHT(SPACE(4) + ISNULL(REPLACE(pvd4.ICD9Code, '.', ''),''),4) AS Fourth_Diag ,
RIGHT(SPACE(4) + ISNULL(REPLACE(pvd5.ICD9Code, '.', ''),''),4) AS Fifth_Diag ,
RIGHT(SPACE(4) + ISNULL(REPLACE(pvd6.ICD9Code, '.', ''),''),4) AS Sixth_Diag
FROM
PatientVisit pv
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
LEFT OUTER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
LEFT JOIN PatientVisitFiling pvf ON pv.PatientVisitId = pvf.PatientVisitId
LEFT JOIN MedLists ml ON pvf.AdmissionSourceMId = ml.MedListsId
LEFT JOIN Medlists mll ON pvf.PatientStatusMId = mll.MedListsId
LEFT JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
LEFT JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
LEFT JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId
LEFT JOIN DoctorFacility od ON pv.OperatingDoctorId = od.DoctorFacilityId
LEFT JOIN DoctorFacility oth ON pv.OtherDoctorId = oth.DoctorFacilityId
LEFT JOIN PatientVisitInsurance pvi ON pv.PatientVisitID = pvi.PatientVisitID AND pvi.OrderForClaims = 1
LEFT JOIN PatientInsurance pi ON pvi.PatientInsuranceID = pi.PatientInsuranceID
LEFT JOIN InsuranceCarriers ic ON pi.InsuranceCarriersID = ic.InsuranceCarriersID
LEFT JOIN PatientVisitInsurance pvi2 ON pv.PatientVisitID = pvi2.PatientVisitID AND pvi2.OrderForClaims = 2
LEFT JOIN PatientInsurance pi2 ON pvi2.PatientInsuranceID = pi2.PatientInsuranceID
LEFT JOIN InsuranceCarriers ic2 ON pi2.InsuranceCarriersID = ic2.InsuranceCarriersID
LEFT JOIN PatientVisitInsurance pvi3 ON pv.PatientVisitID = pvi3.PatientVisitID AND pvi3.OrderForClaims = 3
LEFT JOIN PatientInsurance pi3 ON pvi3.PatientInsuranceID = pi3.PatientInsuranceID
LEFT JOIN InsuranceCarriers ic3 ON pi3.InsuranceCarriersID = ic3.InsuranceCarriersID
LEFT JOIN PatientVisitDiags pvd1 ON pv.PatientVisitId = pvd1.PatientVisitId and pvd1.listorder = 1
LEFT JOIN PatientVisitDiags pvd2 ON pv.PatientVisitId = pvd2.PatientVisitId and pvd2.listorder = 2
LEFT JOIN PatientVisitDiags pvd3 ON pv.PatientVisitId = pvd3.PatientVisitId and pvd3.listorder = 3
LEFT JOIN PatientVisitDiags pvd4 ON pv.PatientVisitId = pvd4.PatientVisitId and pvd4.listorder = 4
LEFT JOIN PatientVisitDiags pvd5 ON pv.PatientVisitId = pvd5.PatientVisitId and pvd5.listorder = 5
LEFT JOIN PatientVisitDiags pvd6 ON pv.PatientVisitId = pvd6.PatientVisitId and pvd6.listorder = 6
LEFT JOIN PatientVisitDiags pvd7 ON pv.PatientVisitId = pvd7.PatientVisitId and pvd7.listorder = 7
LEFT JOIN PatientVisitDiags pvd8 ON pv.PatientVisitId = pvd8.PatientVisitId and pvd8.listorder = 8
LEFT JOIN PatientVisitDiags pvd9 ON pv.PatientVisitId = pvd9.PatientVisitId and pvd9.listorder = 9
LEFT JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId AND pvp.ListOrder = 1
LEFT JOIN PatientVisitProcs pvp2 ON pv.PatientVisitId = pvp2.PatientVisitId AND pvp2.Listorder = 2
LEFT JOIN PatientVisitProcs pvp3 ON pv.PatientVisitId = pvp3.PatientVisitId AND pvp3.Listorder = 3
LEFT JOIN PatientVisitProcs pvp4 ON pv.PatientVisitId = pvp4.PatientVisitId AND pvp4.ListOrder = 4
LEFT JOIN PatientVisitProcs pvp5 ON pv.PatientVisitId = pvp5.PatientVisitId AND pvp5.ListOrder = 5
LEFT JOIN PatientVisitProcs pvp6 ON pv.PatientVisitId = pvp6.PatientVisitId AND pvp6.ListOrder = 6
LEFT OUTER JOIN MedLists ml4 ON pvp.Modifier1MId = ml4.MedListsId
LEFT OUTER JOIN MedLists ml5 ON pvp.Modifier2MId = ml5.MedListsId
LEFT OUTER JOIN MedLists ml6 ON pvp2.Modifier1MId = ml6.MedListsId
LEFT OUTER JOIN MedLists ml7 ON pvp2.Modifier2MId = ml7.MedListsId
LEFT OUTER JOIN MedLists ml8 ON pvp3.Modifier1MId = ml8.MedListsId
LEFT OUTER JOIN MedLists ml9 ON pvp3.Modifier2MId = ml9.MedListsId
LEFT OUTER JOIN MedLists ml10 ON pvp4.Modifier1MId = ml10.MedListsId
LEFT OUTER JOIN MedLists ml11 ON pvp4.Modifier2MId = ml11.MedListsId
LEFT OUTER JOIN MedLists ml12 ON pvp5.Modifier1MId = ml12.MedListsId
LEFT OUTER JOIN MedLists ml13 ON pvp5.Modifier2MId = ml13.MedListsId
LEFT OUTER JOIN MedLists ml14 ON pvp6.Modifier1MId = ml14.MedListsId
LEFT OUTER JOIN MedLists ml15 ON pvp6.Modifier2MId = ml15.MedListsId
WHERE
--Filter on Date
(
(1 = 1 AND (pv.Entered >= ISNULL(NULL, '1/1/1900') AND pv.Entered < DATEADD(d,1,ISNULL(NULL,'1/1/3000')))) OR
(1 = 2 AND (pvp.DateOfServiceFrom>= ISNULL(NULL, '1/1/1900') AND pvp.DateOfServiceFrom < 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 Operating Doctor
(
(NULL IS NOT NULL AND pv.OperatingDoctorId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Company
(
(NULL IS NOT NULL AND pv.CompanyID 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 CPT Code
(
(NULL IS NOT NULL AND pvp.ProceduresId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Diagnosis
(
(NULL IS NOT NULL AND pvd1.DiagnosisId IN (NULL)) OR
(NULL IS NOT NULL AND pvd2.DiagnosisId IN (NULL)) OR
(NULL IS NOT NULL AND pvd3.DiagnosisId IN (NULL)) OR
(NULL IS NOT NULL AND pvd4.DiagnosisId IN (NULL)) OR
(NULL IS NOT NULL AND pvd5.DiagnosisId IN (NULL)) OR
(NULL IS NOT NULL AND pvd6.DiagnosisId IN (NULL)) OR
(NULL IS NOT NULL AND pvd7.DiagnosisId IN (NULL)) OR
(NULL IS NOT NULL AND pvd8.DiagnosisId IN (NULL)) OR
(NULL IS NOT NULL AND pvd9.DiagnosisId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Patient
(
(NULL IS NOT NULL AND pp.PatientProfileID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Patient Sex
(
(Null IS NOT NULL AND pp.sex in (Null)) OR
(Null IS NULL)
)
AND --Filter on Insurance Carrier
(
(NULL IS NOT NULL AND pv.PrimaryInsuranceCarriersId IN (NULL)) OR
(NULL IS NULL)
)
SELECT
#Details.TicketNumber,
#Details.FacilityId
+ #Details.PatientControlNumber
+ #Details.PatientMRN
+ #Details.SSN
+ #Details.Zip
+ #Details.DOB
+ #Details.Sex
+ #Details.AdmissionDate
+ #Details.[Source of Admission]
+ #Details.PatientDischargeStatus
+ #Details.DischargeDate
+ #Details.PrimaryDiag
+ #Details.SecondDiag
+ #Details.ThirdDiag
+ #Details.FourthDiag
+ #Details.FifthDiag
+ #Details.SixthDiag
+ #Details.SeventhDiag
+ #Details.EighthDiag
+ #Details.NinthDiag
+ #Details.PrincipleCode
+ #Details.SecondCode
+ #Details.ThirdCode
+ #Details.FourthCode
+ #Details.FifthCode
+ #Details.SixthCode
+ #Details.[Primary CPT Date]
+ #Details.[Coding Method Used]
+ #Details.[Statement Covers Period]
+ #Details.[Total Charges]
+ #Details.[Primary Payer]
+ #Details.[Secondary Payer]
+ #Details.[Third Payer]
+ #Details.[Performing Physician ID]
+ #Details.[Other Physician ID]
+ #Details.[Type of Bill]
+ #Details.Primary_Diag
+ #Details.Second_Diag
+ #Details.Third_Diag
+ #Details.Fourth_Diag
+ #Details.Fifth_Diag
+ #Details.Sixth_Diag AS ExportFile
FROM
#Details
ORDER BY #Details.TicketNumber
DROP TABLE #Details
Open in new window