I dont know what this is called in the technical world. But you know when you do a search on amazon or play.com and the search results contain the first sentance or so of the items describtion rather than the whole thing. How is this done? thanks si!
View 6 RepliesI would like have the default value for a feild to be the result of a call to
System.Web.Security.Membership.GeneratePassword(12,4)
How do I implement this?
In a trigger? Do you have sample code?
Thank you very much
Hi,I want to put passwords in my sql table not in clear text but in password char. Pls help me
View 2 Replies View Relatedwhen i inserted a large amount of text into a text type field through asp.net form but only some of it shows up in SQL server.
can u please help me how to handle this problem? so all the text entered in text feilt can be handled.
Hi there!
There is a feild in my table that contains the city, state and zip, all in the same feild. I was wondering how would be the best way to devide all that info up into 3 feilds? i really don't know how I would go about it since there are cities that can be comosed of 2 or more words. Here is some sample data. I hope someone can help.
SOUTH EL MONTE CA91733617
BOSSIER LA71172
GARDENA CA90249107
MILWAUKEE WI53216
PARIS IL61944
DUQUOIN IL62832
REDWOOD FALLS MN56283
AUBURN ME04210
IRWINDALE CA91706048
PORTLAND OR97202901
PORTLAND OR972028901
ANAHEIM CA928071735
KENT WA98032
CRYSTAL LAKE IL60014611
I have a database with tables with the following schema...
Articles) ArticleID, Version
ArticlesVersions)ArticleID Version Title Body etc...
I use the articles table to simply map to a version. This allows for me to revert the article to a earliar version.
I want to cache all the articles including the versions, but with another feild that needs to be created durring the select to determine wether the article version is the active version. The feild needs to be a bit (boolean).
I was thinking maybe SELECT (IF test = test THEN 1 ELSE 0 END) as IsCurrent.
I've tried doing this but I can't get it to work.
Any help would be appreciated!
SELECT dbo.mw_Articles.ArticleID, dbo.mw_ArticlesVersions.Version, dbo.mw_ArticlesVersions.Body, dbo.mw_ArticlesVersions.Title, dbo.mw_ArticlesVersions.ParentID,
dbo.mw_ArticlesVersions.Approved, dbo.mw_ArticlesVersions.UserName, dbo.mw_ArticlesVersions.UserID, dbo.mw_ArticlesVersions.Importance, dbo.mw_ArticlesVersions.EditNotes, dbo.mw_ArticlesVersions.TIME
FROM dbo.mw_Articles INNER JOIN
dbo.mw_ArticlesVersions ON dbo.mw_Articles.ArticleID = dbo.mw_ArticlesVersions.ArticleID AND
dbo.mw_ArticlesVersions.Approved = 1
Hi,
I have a stored procedure and using various where condition and all have proper index(clustered).
All columns are doing clustered index seek but only the date column doing index scan.
If I forcing(hint) the index and getting better performance but still that doing cluster index scan.
Why I am having problem whith this date field?
and How can able to make it index seek instead of clustered index scan?.
Let's say I have three fields in a table like this...
Name
Tell us about cats
Tell us about dogs
Now I have two records.
Name Billy Bob
Tell us about cats I do not have any Cats
Tell us about dogs "null"
Name Joe Slick
Tell us about cats "null"
Tell us about dogs they sure chase Cats a lot.
I want my report to generate like this:
Name Billy Bob
Tell us about cats I do not have any cats
Name Joe Slick
Tell us about dogs they sure chase cats allot.
How do I omit null fields in a report including the field description? I am pretty flexible and I am willing to explore using ASP.Net, asp, html, VS2005 ? Or please tell me a program to use. I have a lot of reports to create. The data base is MS SQL 2005. The reports need to be published to the web. Can anyone suggest the best method?
In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message.
How can I store over 16000 characters to sql table field (only one field) with language specific characters?
Thanks
Hi everybody,
I would like to know if there is any property in sql2000 database to separate lowercase characters from uppercase characters. I mean not to take the values €˜child€™ and €˜Child€™ as to be the same. We are transferring our ingres database into sqlserver. In ingres we have these values but we consider them as different values. Can we have it in sqlserver too?
Hellen
Good day experts,
I wonder if i got an answer for this.
How can i iliminate a letters from a set of integers and characters using a SQL Statement
for ex:
ABC9800468F
is that possible?
is there a function that i can use to iliminate them?
I’m getting ASCII characters in one column of my table. So I want to replace same column value in NON ASCII characters.
Note – values in column must be same
I'm taking 70-228 this Saturday.Any advice from anyone who has taken it recently?Thanks in advance,Joe in Florida
View 2 Replies View Relatedjust as we export in db2 database
as db2move databasename export -u username -p password
how we will export in sql server
Hello.
I have a query that takes 1,5second to execute, but only 150ms of CPU. The query is quite simple, just one where statement against a clustered index.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 1595 ms.
SELECT column1, column3, column4, ..., column10 FROM table WHERE column2 IN (37, 41, 43, 45, 49, 53, 55) ORDER BY column3 DESC
|--Sort(TOP 1000, ORDER BY:([u].[LastActivityDate] DESC))
|--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS [u]), SEEK:([u].[searchparamid]=37 OR [u].[searchparamid]=41 OR [u].[searchparamid]=43 OR [u].[searchparamid]=45 OR [u].[searchparamid]=49 OR [u].[searchparamid]=53 OR [u].[searchparamid]=55 OR [u].[searchparamid]=59) ORDERED FORWARD)
I have tried to rewrite the query to an INNER JOIN instead.
|--Sort(TOP 1000, ORDER BY:([u].[LastActivityDate] DESC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([spal].[number]))
|--Index Seek(OBJECT:([MP].[dbo].[__search_parameters_lookup].[IX___search_parameters_lookup] AS [spal]), SEEK:([spal].[hash]=-1726604993) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS [u]), SEEK:([u].[searchparamid]=[spal].[number]) ORDERED FORWARD)
but the query still takes 1,5 seconds.
It spends 59% (according to execution plan) of sorting. 14% for the index seek of the __search_parameters_lookup table and then 24% of a clustered index seek of the __searchtest table.
How come it only uses that small of CPU but it still takes 1,5 seconds? It seems to be reading from memory as well so it shouldnt be an IO-problem?
The index I have on the table is a clustered index on (column 2).
Any ideas of how I can improve this? I have tried with DTA, also with a non clustered index on column3.
If I remove some columns from the SELECT-list the query will execute alot faster:
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 32 ms.
Booth the CPU and the elapsed time goes down and now appears to be more normal.
So there seems to be a problem caused by data transfer.
I tried to do a remake and normalize the table and when I do that I get the query execute with a speed of 400ms CPU and 400ms total. And this is still the exact same result, so why does it only spend 400ms of "rendering" or fetching the data when the tables are normalized but 1500ms when its denormalized?
Any ideas?
I am running Microsoft SQL Server 2000 - 8.00.2039
I'm performing an insert and I not only need to remove the << character, but also need to take one field and dump it into two fields. So in essence -
KAREL>>MONTES
needs to look like
Col1 Col2
Karel Montes
Thanks :)
I have a custom .net application that uses SQL 2000 server. All users are compaining performance issues and white-outs while they are using the application. I am almost certain that it's the SQL server that is the curprit. All the other components involved in the application hardly has any CPU or memory usage when I check the performance in the task manager.
On SQL server, I see that the process sqlserver.exe is taking like 2.8GB of memory. Is there a way to find out which exact SQL query or process is taking so much of memory? I belive there may be a bad SQL process that is stuck and taking all the memory? Is there a way to find out?
Thanks
Hello.
I have a query that takes 1,5second to execute, but only 150ms of CPU. The query is quite simple, just one where statement against a clustered index.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 1595 ms.
Code Snippet
SELECT column1, column3, column4, ..., column10 FROM table WHERE column2 IN (37, 41, 43, 45, 49, 53, 55) ORDER BY column3 DESC
Code Snippet
|--Sort(TOP 1000, ORDER BY:([u].[LastActivityDate] DESC))
|--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS [u]), SEEK:([u].[searchparamid]=37 OR [u].[searchparamid]=41 OR [u].[searchparamid]=43 OR [u].[searchparamid]=45 OR [u].[searchparamid]=49 OR [u].[searchparamid]=53 OR [u].[searchparamid]=55 OR [u].[searchparamid]=59) ORDERED FORWARD)
I have tried to rewrite the query to an INNER JOIN instead.
Code Snippet
|--Sort(TOP 1000, ORDER BY:([u].[LastActivityDate] DESC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([spal].[number]))
|--Index Seek(OBJECT:([MP].[dbo].[__search_parameters_lookup].[IX___search_parameters_lookup] AS [spal]), SEEK:([spal].[hash]=-1726604993) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS [u]), SEEK:([u].[searchparamid]=[spal].[number]) ORDERED FORWARD)
but the query still takes 1,5 seconds.
It spends 59% (according to execution plan) of sorting. 14% for the index seek of the __search_parameters_lookup table and then 24% of a clustered index seek of the __searchtest table.
How come it only uses that small of CPU but it still takes 1,5 seconds? It seems to be reading from memory as well so it shouldnt be an IO-problem?
The index I have on the table is a clustered index on (column 2).
Any ideas of how I can improve this? I have tried with DTA, also with a non clustered index on column3.
If I remove some columns from the SELECT-list the query will execute alot faster:
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 32 ms.
Booth the CPU and the elapsed time goes down and now appears to be more normal.
So there seems to be a problem caused by data transfer.
I tried to do a remake and normalize the table and when I do that I get the query execute with a speed of 400ms CPU and 400ms total. And this is still the exact same result, so why does it only spend 400ms of "rendering" or fetching the data when the tables are normalized but 1500ms when its denormalized?
Any ideas?
I am running Microsoft SQL Server 2000 - 8.00.2039
HI allI want to take a back of my serverthere is 40 data base into my server and i have to take back of every database every day is there any way which can take back up of hole server at one time.regard
View 1 Replies View RelatedI have the below query which returns thousands of records. can I optimize the returned result set faster without changing the structure of the database?
SELECT dbo.tblComponent.ComponentID, dbo.tblComponent.ComponentName, dbo.tblErrorLog.ShortErrorMessage, dbo.tblErrorLog.LongErrorMessage, dbo.tblErrorLog.LogDate, dbo.tblErrorLevel.Description,dbo.tblErrorLog.ErrorLogIDFROM dbo.tblErrorLevel INNER JOIN dbo.tblErrorLog ON dbo.tblErrorLevel.ErrorLevelID = dbo.tblErrorLog.ErrorLevelID INNER JOIN dbo.tblComponent ON dbo.tblErrorLog.ComponentID = dbo.tblComponent.ComponentID
Thanks.
I have a VB application which uses SQL server as the database and uses Crystal reports for reporting.We are using a stored procedure to create a report and we pass ID from the vb side to run the stored procedure.
In boston the report shows up in 4 sec.But in california it takes 7 min.
We have a very good network(T3).Why it is taking more time in california ?.
Any ideas ?
Hi,
I am running this query and it is taking over 3 minutes.
"select * from table1 where CONVERT(varchar(10),dated,5) = '13-09-01' "
Table1 has a column called dated which is datetime datatype.
Any suggestions how can i optimize this query?I tried Non-clustered index on Dated column and time came down to less than 3 but still more than 2min.
TIA.
I have been dealing with an intermittent problem for several months that manifests itself on my computer as well as a customers computer. It is happening so often, upon booting the computer, that I just open and then minimize the Task Manager so that it will be in the Tool Tray and the bargraph will be visible.
From time to time the processor bargraph will "Max out" and when I open Task Manager and click on CPU in the Processes Tab, SQLServr.exe is using 99% of the CPU.
In Enterprise Manager I have set maximum Memory to 25% of the available system memory. I have tried this in both Fixed mode as well as Dynamic mode, no change.
I was told that there was a SQL Server version that was susceptible to a WORM that caused this. I have since upgraded to SQL ver. 8.0.194. I'm not sure of the version that I replaced, but I thought that the previous version was the one that was susceptible to the worm.
Has anyone fought this battle and if so can you offer any experience or advice?
Thanks very much for your help,
Doc
hi i am taking date from user.
if i want to take only date or only time from user and save into database than what to do?
i am using MS SQL Server which takes datatype as 'datetime' which takes both date and time as value to store in database.
Hi all,
2 weeks ago I deleted about 200GB of data from a 300GB+ database. It's a custom DB we want to use to test few things. We wanted a smaller size DB for our testing and since we didn't have any we grabbed a production backup, removed sensitive data and ran a large archiving script on it... Anyway so far so good but our data file was still the same size as before.
So we started a shrinkdatabase... it has been running for 2 weeks now! After about 1 week I interrupted the shrinkdatabase process and ran a
dbcc shrinkdatabase('DB', truncateonly)
just to see if the data file will get reduced a bit or not. It did get reduced by about 20GB. I assume that
dbcc shrinkdatabase('DB', 0)
has free up enough pages at the end of the data file so a truncateonly was able to free up some space... Anyway after this we started the
dbcc shrinkdatabase('DB', truncateonly)
again... still running...
The database was never shrank before and every index is highly fragmented... Is that why it's taking so long? Am I actually going to have to wait for another few weeks before that thing finishes??
Anyone has experience running shrink on large DBs?
thanks!
I have following common table expression query which is taking like 15 hours to run. would someone suggest what can I do to speed this thing up..
; with
a as (select proj_id, proj_start_dt,proj_end_dt, case when charindex('.', Proj_ID) > 0 then left(Proj_ID, len(Proj_ID) - charindex('.', reverse(Proj_ID))) end as Parent_Proj_ID from ods32.dbo.Proj a), --add Parent_Proj_ID column
b as (select proj_id, proj_start_dt,proj_end_dt,Parent_Proj_ID from a where PROJ_START_DT is not null and PROJ_END_DT is not null --get all valid rows
union all
select a.Proj_Id, b.PROJ_START_DT, b.PROJ_END_DT, a.Parent_Proj_ID from b inner join a on b.Proj_Id = a.Parent_Proj_ID where a.PROJ_START_DT is null or a.PROJ_END_DT is null) --get all invalid children of valid rows and give them the dates of their parents
update a set PROJ_START_DT = b.PROJ_START_DT, PROJ_END_DT = b.PROJ_END_DT
from WPData a left outer join b on a.Proj_ID = b.Proj_ID -- join up and update
thanks
im rstoring a db the file is 7gig. Its taking more than 10 minuts..
how do i know if the backup file is ok to restore it
=============================
http://www.sqlserverstudy.com
what can I do?
all queries that used to work are taking forever now???
what can I do?
is there a max size for the db that I may have reached
please advise asap
Hi:
I have issued the following ALTER TABLE CHECK ADD CONSTRAINT on a table which has around 100K rows and it is taking long time (it's been more than 30 mins the alter table is running) to add the constraint. Is this normal or should I kill the process.
ALTER TABLE [dbo].[tblAbsHeqAnalyticOutputSimulationPathValues]
WITH CHECK ADD CONSTRAINT [CK_tblAbsHeqAnalyticOutputSimulationPathValues_1]
CHECK ([dbo].[svfConstraintVerifyTableUniqueActiveEntryFacade]('tblAbsHeqAnalyticOutputSimulationPathValues')<=(1) AND [dbo].[svfConstraintVerifyTableUniqueActiveEntryFacade]('tblAbsHeqAnalyticOutputSimulationPathValues')>=(0))
Thanks !
Dear Experts,
i've one table named table11. in this perticular table, i've 30 columns and 40,000 rows of data.
this table is taking 35 sec for select * from table11.
defnetly it will take more time if i used this in some places like procedures and functions or views like that.
where is the problem? generally it takes that much of time or is there any problem?
guidence please.....
Vinod
Even you learn 1%, Learn it with 100% confidence.
Dear All,
here i'm posting my query which is taking 3 minutes
please suggest me the best query
SELECT distinct INP.COLUMN001 REPORT_INPUT_ID, INP.COLUMN002 REPORT_ID, INP.COLUMN003 OPERATION_ID,
OPER.COLUMN004 OPERATION_CODE, OPER.COLUMN005 OPERATION_NAME, INP.COLUMN004 ITEM_ID,
CONVERT(NVARCHAR , INP.COLUMN005, 110) RECEIVED_DATE, INP.COLUMN006 LOT_NO, INP.COLUMN007 RECEIVED_QTY,
INP.COLUMN008 CONSUMED_QTY, (select CODE from view1 where item_id = INP.COLUMN004) my_val,
(select NAME from view1 where item_id = INP.COLUMN004) Item_Name, INP.COLUMN009 UOM_ID,
U.UOM_CODE, INP.COLUMN010 BASE_RECEIVED_QTY, INP.COLUMN011 BASE_CONSUMED_QTY,
case when INP.COLUMN012 ='1' then 'Progress' when INP.COLUMN012 ='2' then 'Closed' end OPERATION_STATUS,
case when INGDTL.COLUMN006 ='0' then 'Ingredient' when INGDTL.COLUMN006 ='1' then 'Intermediate' end INPUT_TYPE,
INP.COLUMNB01 COLUMNB01, INP.COLUMNB02 COLUMNB02, INP.COLUMNB03 COLUMNB03, INP.COLUMNB04 COLUMNB04,
INP.COLUMNB05 COLUMNB05, INP.COLUMNB06 COLUMNB06, INP.COLUMNB07 COLUMNB07, INP.COLUMNB08 COLUMNB08,
INP.COLUMNB09 COLUMNB09, INP.COLUMNB10 COLUMNB10, INP.COLUMND01 BRANCHID, INP.COLUMND02 COMPANYID, INP.COLUMND03 CREATEDBY,
INP.COLUMND04 CREATEDDATE, INP.COLUMND05 LASTUPDATEDBY, INP.COLUMND06 LASTUPDATEDDATE, INP.COLUMND07 ROWGUID,
INP.COLUMND08 UPDATEDSITE, INP.COLUMND09 LANGID, WC.COLUMN009 WIP_WAREHOUSE_ID,
(SELECT (sum(WIP.COLUMN011) - sum(wip.column010))
FROM TABLE066 WIP where wip.column008 = INP.column004 and WIP.COLUMN005 = '8cd741c7-1ac6-4839-88e7-df85518170f1' and wip.column006 = inp.column003 ) WIP_Qty ,
WIPM.Column005 WIP_ITEM_ID
FROM TABLE073 INP
left join view1 I on I.ITEM_ID = INP.COLUMN004
left join view2 U on U.UOM_ID = INP.COLUMN009
left join TABLE022 OPER ON OPER.COLUMN001 = INP.COLUMN003
left join TABLE066 WIP on WIP.column008 = INP.column004
left join TABLE015 WC on WC.COLUMN001 = OPER.COLUMN008
left JOIN TABLE040 INGDTL ON INGDTL.COLUMN002 = INP.COLUMN004 AND WIP.column008 = INGDTL.COLUMN002
left join TABLE065 WIPM on WIPM.column005 = INP.column004
where INP.COLUMN002 = '057f87aa-7884-43fa-8984-9b74c971da62' order by my_val
thank you very much
Hi All
I am having a serious problem which I need some help with regarding our SQL Server backup.
Basically it has started to take ages (as in 48hrs +), when it should only take about 4 hrs. The database is only 380GB and up until monday our backups have not been completing. When I check the activity monitor I have seen that the 'BACKUP DATABASE' process is set to suspended with a huge wait time and the wait type is ASYNC_IO_COMPLETION.
I am not sure how to solve this, but I am going to have to!
So if anyone has any ideas please help me! If you need any othe info please let me know.
Thanks
Gopher
I have one query with 3 statments in it, which then creates a table called HCSReturnFile. My problem is that I have an order by in my query, but when I go to create a table that puts all 2 statements into 1, it does not do the order by. What am I doing wrong? Below is my Query.
USE [Impact_PROD]
GO
/****** Object: StoredProcedure [dbo].[p_GenerateHCSReturnFileUPDATE] Script Date: 01/18/2008 14:20:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Wendy & Mike
-- Create date: 11/08/2007
---Create Date to Production - 12-06-2007
-- Description:This was a touch one.
---This report drop both Queries into a tabled called HCSRetrunFile
-- =============================================
ALTER PROCEDURE [dbo].[p_GenerateHCSReturnFileUPDATE]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF OBJECT_ID('IMPACT_PROD..HCSReturnFile') IS NOT NULL
DROP TABLE HCSReturnFile
CREATE TABLE [dbo].[HCSReturnFile](
[EventNumber] [varchar](50) NULL,
[ClaimNumber1] [varchar](50) NOT NULL,
[Resolution] [varchar](50) NULL,
[Resolution2] [varchar](50) NULL,
[ProviderType] [varchar](2) NULL,
[Negotiation] [varchar](50) NULL,
[NEGOYESORNO] [varchar](50) NULL,
[ProviderID] [varchar](50) NOT NULL,
[HCSAuthorizationID] [varchar](20) NULL,
[PROLASTNAME] [varchar](20) NULL,
[proFirstName] [varchar](15) NULL,
[ProOffice] [varchar](35) NULL,
[TOTALBILLEDAMT] [varchar](50) NOT NULL,
[SAVINGS] [varchar](50) NOT NULL,
[OONNEGO] [varchar](50) NOT NULL,
[CLM_ATT1] [varchar](50) NULL,
[CLM_ATT2] [varchar](50) NULL,
[CLM_ATT3] [varchar](50) NULL,
[CLM_ATT4] [varchar](50) NULL,
[CLM_ATT5] [varchar](50) NULL,
[NoteData] [varchar](8000) NULL)
INSERT INTO [IMPACT_PROD].[dbo].[HCSReturnFile]
([EventNumber]
,[ClaimNumber1]
,[Resolution]
,[Resolution2]
,[ProviderType]
,[Negotiation]
,[NEGOYESORNO]
,[ProviderID]
,[HCSAuthorizationID]
,[PROLASTNAME]
,[proFirstName]
,[ProOffice]
,[TOTALBILLEDAMT]
,[SAVINGS]
,[OONNEGO]
,[CLM_ATT1]
,[CLM_ATT2]
,[CLM_ATT3]
,[CLM_ATT4]
,[CLM_ATT5]
,[NoteData])
SELECT Distinct
--n.note_sys,
--c.CLM_id1 AS 'ClaimNumber',
e.EVE_id1 AS 'EventNumber',
ClaimNumber1 = '',
--e.EVE_clm As 'EventEventTable',
--c.CLM_elrc AS 'EventClaimTable',
e.eve_resl1 as 'Resolution',
p.EVEP_RESL1 as 'Resolution2',
p.evep_reas as 'ProviderType',
p.evep_nego as 'Negotiation',
--e.EVE_STAT AS 'STATUS',
e.EVE_SW01 AS 'NEGOYESORNO',
ProviderID = '',
e.EVE_EXNO AS 'HCSAuthorizationID',
--p.EVEP_LNAME AS 'PROVIDERLASTNAME',
--p.EVEP_FNAME AS 'PROVIDERFIRSTNAME',
p.EVEP_LNAME AS PROLASTNAME,
p.evep_fname AS proFirstName,
p.evep_offic AS ProOffice,
--c.CLM_EDID AS 'LOCKDATE',
TOTALBILLEDAMT = '',
SAVINGS = '',
OONNEGO = '',
c.CLM_ATT1,
c.CLM_ATT2,
c.CLM_ATT3,
c.CLM_ATT4,
c.CLM_ATT5,
n.NoteData
FROM dbo.EVE e
RIGHT JOIN dbo.clm c
ON e.EVE_id1 = c.clm_id1
RIGHT JOIN dbo.EVEP p
ON e.eve_id1 = p.EVEP_id1
JOIN dbo.notes2 n
ON n.eve_id1 = e.EVE_id1
--JOIN evep p2
--ON p.evep_id1 = p2.evep_id1
Where
e.eve_resl1 = 'CL'and
p.EVEP_RESL1 <> 'NG' and
p.evep_reas <>'FA'--and ---means closes Events
--p2.evep_nego = 'Y' and
--((LTRIM(p.evep_id2) <> '0002' AND p.evep_id2 <> '0001' AND p2.evep_id2 <> '0001') OR (SELECT COUNT(*) FROM evep WhERE evep_id1 = p.evep_id1) = 1)
--e.EVE_STAT = 'CL'and
--e.EVE_id1 IN ('00101965','00102080','00102084','00101962','00101963') --and
Order by e.eve_resl1,p.EVEP_RESL1
INSERT INTO [IMPACT_PROD].[dbo].[HCSReturnFile]
([EventNumber]
,[ClaimNumber1]
,[Resolution]
,[Resolution2]
,[ProviderType]
,[Negotiation]
,[NEGOYESORNO]
,[ProviderID]
,[HCSAuthorizationID]
,[PROLASTNAME]
,[proFirstName]
,[ProOffice]
,[TOTALBILLEDAMT]
,[SAVINGS]
,[OONNEGO]
,[CLM_ATT1]
,[CLM_ATT2]
,[CLM_ATT3]
,[CLM_ATT4]
,[CLM_ATT5]
,[NoteData])
SELECT Distinct
--n.note_sys,
--c.CLM_id1 AS 'ClaimNumber',
e.EVE_id1 AS 'EventNumber',
ClaimNumber1 = '',
--e.EVE_clm As 'EventEventTable',
--c.CLM_elrc AS 'EventClaimTable',
e.eve_resl1 as 'Resolution',
p.EVEP_RESL1 as 'Resolution2',
p.evep_reas as 'ProviderType',
p.evep_nego as 'Negotiation',
--e.EVE_STAT AS 'STATUS',
e.EVE_SW01 AS 'NEGOYESORNO',
ProviderID = '',
e.EVE_EXNO AS 'HCSAuthorizationID',
--p.EVEP_LNAME AS 'PROVIDERLASTNAME',
--p.EVEP_FNAME AS 'PROVIDERFIRSTNAME',
p.EVEP_LNAME AS PROLASTNAME,
p.evep_fname AS proFirstName,
p.evep_offic AS ProOffice,
--c.CLM_EDID AS 'LOCKDATE',
TOTALBILLEDAMT = '',
SAVINGS = '',
OONNEGO = '',
c.CLM_ATT1,
c.CLM_ATT2,
c.CLM_ATT3,
c.CLM_ATT4,
c.CLM_ATT5,
n.NoteData
FROM dbo.EVE e
RIGHT JOIN dbo.clm c
ON e.EVE_id1 = c.clm_id1
RIGHT JOIN dbo.EVEP p
ON e.eve_id1 = p.EVEP_id1
JOIN dbo.notes2 n
ON n.eve_id1 = e.EVE_id1
--JOIN evep p2
--ON p.evep_id1 = p2.evep_id1
Where
((e.eve_resl1 = '' and p.evep_reas = 'HO')or (e.eve_resl1 = '' and p.evep_reas = 'PH') or (e.eve_resl1 = '' and p.evep_reas = 'AN'))
Order by e.eve_resl1,p.EVEP_RESL1
--and ---means Open Events will not have claims attached to them. we just need the fields in this report.
--p2.evep_nego = 'Y' and
--((LTRIM(p.evep_id2) <> '0002' AND p2.evep_id2 <> '0001') OR (SELECT COUNT(*) FROM evep WhERE evep_id1 = p.evep_id1) = 1)
INSERT INTO [IMPACT_PROD].[dbo].[HCSReturnFile]
([EventNumber]
,[ClaimNumber1]
,[Resolution]
,[Resolution2]
,[ProviderType]
,[Negotiation]
,[NEGOYESORNO]
,[ProviderID]
,[HCSAuthorizationID]
,[PROLASTNAME]
,[proFirstName]
,[ProOffice]
,[TOTALBILLEDAMT]
,[SAVINGS]
,[OONNEGO]
,[CLM_ATT1]
,[CLM_ATT2]
,[CLM_ATT3]
,[CLM_ATT4]
,[CLM_ATT5]
,[NoteData])
SELECT Distinct
c.CLM_elrc AS 'EventNumber',
CASE c.clm_ips
WHEN 'C' THEN c.clm_pclm
ELSE c.clm_id1
END as "claimnumber1", --ClmClaims."CLAIMNUMBER",
--c.CLM_PCLM as "CLAIMNUMBER",
--n.note_sys,
--c.clm_id1,
--e.EVE_clm As 'EventEventTable',
e.eve_resl1 as 'Resolution',
p.EVEP_RESL1 as 'Resolution2',
p.evep_reas as 'ProviderType',
p.evep_nego as 'Negotiation',
--e.EVE_STAT AS 'STATUS',
e.EVE_SW01 AS 'NEGOYESORNO',
c.CLM_5 AS 'ProviderID',
e.EVE_EXNO AS 'HCSAuthorizationID',
--p.EVEP_LNAME AS 'PROVIDERLASTNAME',
--p.EVEP_FNAME AS 'PROVIDERFIRSTNAME',
--p.EVEP_OFFIC AS 'PROVIDEROFFICE',
p.evep_lname AS ProLastName,
p.evep_fname AS proFirstName,
p.evep_offic AS ProOffice,
c.CLM_MCHG AS 'TOTALBILLEDAMT',
c.CLM_SPPO AS 'SAVINGS',
c.CLM_55d AS 'OONNEGO',
c.CLM_ATT1,
c.CLM_ATT2,
c.CLM_ATT3,
c.CLM_ATT4,
c.CLM_ATT5,
n.NoteData
FROM dbo.EVE e
Right Join dbo.clm c
ON e.EVE_id1 = c.clm_elrc
Right join dbo.EVEP p
ON e.eve_id1 = p.EVEP_id1
--JOIN evep p2
--ON p.evep_id1 = p2.evep_id1
JOIN dbo.notes2 n
ON n.eve_id1 = e.EVE_id1
Where
p.EVEP_RESL1 = 'NG'and
clm_adjto = '' ---"NG"This report is going to be changed to the negoitated report the field is going to be "NG"
--e.eve_resl1 = 'NG'and
--p2.evep_nego = 'Y' and
--((LTRIM(p.evep_id2) <> '0002' AND p.evep_id2 <> '0001'AND p2.evep_id2 <> '0001') OR (SELECT COUNT(*) FROM evep WhERE evep_id1 = p.evep_id1) = 1)
Order by e.eve_resl1,p.EVEP_RESL1
END