I would like to create a command that will make a new logical field that is true or false depending on weather field is a certian value for example
col1,col2,col3
col1 is firstname
col2 is lastname
col3 is numeric
SELECT col1,col2,(if col3=6)=true AS Boolean
FROM table
i know this code doesnt work but it is a representation of what i would like to happen
Is it possible to change table design without running an alter table SQL Script??
For example, change the following :
Table_A ( column1 int identity(1,1) <<----BEFORE
, column2 varchar(10) null
, column3 char(20) not null
)
to
Table_A ( column1 int identity(1,1) <<----AFTER
, column2 char(255) not null
, column3 varchar(20) null
)
Is it possible to change sysobjects and syscolumns or other system objects for this to occur??
I know this is not recommended but if I had to, what are the steps required to do this??
Thanks very much
Hello, this probably isnt the best place to ask but I can't find a moresuitable sql newsgroup so I hope y'all dont mind too much.I have 2 tables; Cellar and ColourCELLAR contains the wine name, its year and the no.of bottles.Wine Year BottlesChardonnay 87 4Fume Blanc 87 2Pinot Noir 82 3Zinfandel 84 9COLOUR contains wine name and it's colourWine ColourChardonnay WhiteFume Blanc WhitePinot NoirRedZinfandel RoseThis is from a past exam paper btwOne of the questions was:Write the sql to count how many white wines there are in the table cellar.The solution that the lecturers included is:SELECT count(wine)FROM cellarWHERE colour='White'Now i havent' been able to try out this sql yet but to me that looks wrong.My solution would be:SELECT count(wine)FROM cellarWHERE cellar.wine = colour.wine and colour.colour='White'Can anyone tell me which one is correct, and if mine isn't correct then whyisn't it?Thanks
View 3 Replies View RelatedOne of of server was restarted... In the early hours... And i found this has a resson in event viewer..
Does anybody knows what this means..
The computer has rebooted from a bugcheck. The bugcheck was: 0x0000007f (0x00000008, 0x00000000, 0x00000000, 0x00000000). Microsoft Windows NT [v15.1381]. A dump was saved in: C:WINNTMEMORY.DMP.
Thank you
Jessi.
Hi,
Below is the insert statement... it insert a new record by selecting the max ID and add plus 1 to the existing value.. with in the same statement..
My question is can i get the inserted value of NewID into a VARIABLE with in the same insert statement.. I dont want to pass another sql statement to select the MAX ID..
INSERT INTO tbTest(ID,EName)
SELECT (SELECT MAX(ID)+1 FROM TEST),'BRAIN'
Thanks
Jessi..
I am trying to write a DTS package at work that uses a loop to fire a query off against a different server/database at each loop iteration. Prior to the execute SQL task , I use ActiveX (VBScript) to change the Catalog and DataSource of the connection (created by drag and drop if icon). My Execute SQL task selects @@servername and getdate() just as a dummy query to make sure I am actually pointed at a different server.
While testing the properties of the connection using global variables and msgbox shows that the database (catalog) and server (data source) ip address is being changed, the result returned is always from the first server. I feel like I need to disconnect the connection object, change the parameters and then reconnect to the new server but there doesn't appear to be anyway to do this.
Anyone out there able to successfully change a connection object inthis manner??
I got the following error on my database when I ran DBCC CHECKDB
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'PhoneCall' (ID 254623950). Missing or invalid key in index 'PhoneCall0' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:98383:16) identified by (RID = (1:98383:16) ) has index values (PhoneCallID = 46361).
This error is on a couple of different indexii?? What do I do? Any help will be appreciated. Thank you
Ok guys I have read everything in my previous post but unfortunately can not seem to get it to work properly. Would anyone like to do this 10 minutes work (I am sure). Obviously I dont expect it to be free but if I continue I am going to get a sledge hammer to this now.
Thanks in advance
My boss asked me to interview 2 people on Monday for a programmer/developer position. They say they are gurus, but my boss wants me to find out for sure. I thought a question about bitwise operations would be good, maybe one about semi-joins. Are there any questions you can think of that will spot the phony pretty quick?
Thanks,
Jim
Hi,
I am trying to import comma delimitted text files which contain data for any particular country.
I have created SSIS package to import data into sql server table. table structure is exactly same as text file except one additional column which is to identify the CountryID. Text files we recieved does not contain CountryID column.
I am using BULK INSERT Task in SSIS to import the data as text files are quite big (up to 1 GB). during processing I do determine the country id for that text file as file name contain countrycode which can be use for lookup and get the countryid.
cyn_bills_20071208_032242.txt
CYN is the country code and country table has got country id for it.
I am using Format file with BULK INSERT task as table has additional column which does not present in source file.
currently after inserting all rows into table i have to run an UPDATE statement to update the value of CountryID column which is very expensive some time taking more then 30 min for (30 million rows)
Can anyone tell me how to insert an expression when using BULK INSERT command / Task in SSIS. there must be a way to insert litterals using Bulk Insert.
Thanks in Advance.
Furrukh Baig
Good afternoon one and all,
I have the following problem :
I have a table which currently has 40+ mill records. Some of the fields are of type nvarchar and these need changing to varchar. I have attempted to change these fields to varchar but the process proceeds to increase the transaction log (to 6 Gigs!) until there is no space left on the server (causing a multitude of other problems with existing programs on the server). Once this happens I have no choice but to kill the change.
Does any1 know how I can make these changes without SQL writing to the transaction log?
Any and all help appreciated,
Thanks
Gurmi
Hello,
I am executing the bcp statement with i a stored procedure, everything works fine. But the question is I want to pass the delimiter as a parameter to the BCP command. I tried but i couldnt make it. I declare a variable and set that variable to a delimiter and try to pass that variable to BCP, but it doesnt accept it, in the export file the delimiter is '@i' instead of ';'
DECLARE
@i CHAR(2)
SELECT @i = ';'
EXEC master..xp_cmdshell 'bcp " select city, name = case whenname like(''%''+char(13)+char(10)+''%'') then replace(name, char(13)+char(10),'''') whenname like(''%''+char(13)+''%'') then replace(name, char(13),'''')whenname like(''%''+char(10)+''%'') then replace(name, char(10),'''')else name end From dbDev..tbtest" queryout d:invoice.txt -c -Sdevelop_server -Uadmin -Padmin -t@i'
THANKS INDEED
JESSI.
Upon running DTS manually to transfer data from Excel into SQL Server, I
get the error:
-----------------------------ERROR OUPTUT ------------------------------------
Error at Source for Row number 264. Errors encountered so far in this task: 1. General error -2147217887 (80040E21).
Data for source column 3 ('Value') is too large for the specified buffer size.
---------------------------END ERROR OUTPUT----------------------------------
*** 'Value' is varchar(4000); largest having length of 1000.
*** The network packet size is 4096.
?? AM I SUPPOSED TO CHANGE THE BUFFER SIZE??
Your kind help is greatly appreciated
Thanks
Ziggy
Hi Guru,,
There are bunch of file on a SQL SERVER installed machine under WINNT floder they are as follows..
OTM1C.tmp
OTM2.tmp
OTM3.tmp
OTM4.tmp
OTM5.tmp
OTM6.tmp
OTMB.tmp
This files occupies 2G.B of disk space... We are in the cleaning process to create some disk space. The thing is no one really understand which application uses this files..
My question is does sql server uses this files... Can we delete this files..
Thank you..
truely
jessi.
Does anyone know if there is an updated Books Online for 2000 published anywhere?? Or even a more recent copy of the 7.0? MS has probably made corrections
to them.
Also, I'm looking for all the free info about DTS for 2000. Not anything that MS publishes, more of a third party evaluation.
Thanks in advance,
Adam
I have a application that uses a connection that is always active until the application is closed, and many other connections that are opened and closed as different data is requested. I DO close and dispose these connections, datasets, and data readers after requesting data to release the resources. BUT they still show in SQL Server 2005 Express Edition. I believe they are NOT active and are part of the Pooling system.
The problem is the connections don't seem to be reused, the Pooling system just makes more, so I end up with hundreds of them until no more connections can be created but SQL Server; hence timing out on some attempt to open a connection.
I have experimented with the 'Min Pool Size' and 'Max Pool Size' values in the connection string but the only change is behavior is that I can make more connection showing in SQL Server but the problem still happens anyway.
I am rewriting this application from VB6 to VB.Net. The VB6 version uses SQL Server 2005 Edition too and the connection problem doesn't exist.
Any ideas would be appreciated.
This is an upgrade issue :
I have a SQL server 6.5 with NT users mapped into the SQL Server running in production. At one point of time, this Server was set up for mixed security, which has since been changed to standard ( still with NT users mapped in ).
When upgrading to SQL 7.0, I noticed that it brought in both : Nt USers as well as the mapped login id's as if they were separate entities ( Probably since SQL Server stores mapped login id's in master..syslogins, and mapped NT logins information are stored in the registry ). It also, does not bring in passwords.
I am planning to re-write this part of the upgrade and write my own scripts to transfer the logins ( Unfortunately, I will have to drop them first from the database using sp_dropuser, immediately after the upgrade process ).
Here's how I'm planning to do this : BCP out the contents of the syslogins table from the 6.5, bring it in into, say 'sys_xlogins' ( New table ) in 7.0, update the 'sysxlogins' system table in 7.0 with the passwords from the 'sys_xlogins' table, and add all the users back into the database.
If anyone else has been thru this, or anything like this ( Upgrading from mixed security 6.5 to 7.0 ), I would appreciate their inputs. Any words of wisdom / experience welcome !
Hi,
Till couple of days ago.. I was able to watch different object counters in SQL Server performance monitor..
For some reason now i am unable to see non of the object counters related to sql server in performance monitor..
The error it records in event viewer is as follows..
The description for Event ID[1001] in Source [ SQLCTR70] could not be found.
It contains the following insertion string[s]: SQL Performance DLL Open function failed..
help is greatly appericated...
thanks indeed
jessi...
Hello all,
I have a question on whether the following stored precedure would be open to an SQL Injection attack. Assume that a string query would be passed to the SP.
I am told that because the password parameter is only varchar(8) that it is safe.
Can someone prove this wrong?
Thanks....
I have added sample code below.
CREATE TABLE [dbo].[JB_Test](
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_JB_Test_Name] DEFAULT (''),
[Email] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_JB_Test_Email] DEFAULT (''),
[Password] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_JB_Test_Password] DEFAULT (''),
) ON [PRIMARY]
GO
Insert dbo.JB_Test (Name, Email, Password )
values ('John', 'asdf@asdf.com', '2345')
Insert dbo.JB_Test (Name, Email, Password )
values ('Paul', 'asdf@asdf.com', '2345')
Insert dbo.JB_Test (Name, Email, Password )
values ('Geroge', 'asdf@asdf.com', '2345')
Insert dbo.JB_Test (Name, Email, Password )
values ('Ringo', 'asdf@asdf.com', '2345')
GO
Create procedure dbo.JB_Test_Login
@Username varchar(100),
@Password varchar(8)
AS
select Name
from dbo.JB_Test
where Name = @Username
and Password = @Password
GO
--Clean Up Your Mess
--Drop procedure dbo.JB_Test_Login
--GO
--Drop Table dbo.JB_Test
--GO
JBelthoff
• Hosts Station is a Professional Asp Hosting Provider
• Position SEO can provide your company with SEO Services at an affordable price
› As far as myself... I do this for fun!
Hello,
I've been trying to fill a tree view based on some criteria in another table.
I have the standard table with a ID - ParentID - Text
when I bind the table everything works fine.
What I need to do is display only the tree items based off a username and ID in another table.
Files can be found http://www.cthere.com/treeviewhelp.zip
EXAMPLE:
tblTourgroup(UserName = Jim)
tblTourgroup (GroupID = 110)
tblGroups (GroupID = 110)
Results from tblGroup:
- RESTAURANTS
- Cuisine
- Steakhouse
Query Results should be layed out like tblGroups
Any ideas?
This is what I have so far... But it does not traverse up and get the other nodes..
USE SQL2005_335573_cplanet;
GO
WITH DynamicTree (ParentID, GroupID, GroupName)
AS
(
SELECT e.ParentID, e.GroupID, e.GroupName
FROM tblGroups AS e
INNER JOIN tblTourGroup AS edh
ON e.GroupID = edh.GroupID
)
SELECT e.ParentID, e.GroupID, e.GroupName
FROM DynamicTree as e
INNER JOIN tblTourGroup AS dp
ON e.GroupID = dp.GroupID
WHERE dp.UserName = N'Test'
GO
As a developer, we always say "using a stored procedure, instead of a cliet side SQL statement, provides performance benefits". However, it seems it has not been true anymore since SQL Server 7.0.
See SQL online "Execution Plan Caching and Reuse" at http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409
I am quite confused with the following questions:
1. it seems since SQL 7.0, a SQL statement in client side uses the existing execution plan as a stored procedure does. That means SP doesn't has much advantage over SQL statement in terms of performance.
2. It seems, a stored procedure is not always compled ONLY once. If a stored procedure is not used for a long time, it could be kicked out from procedure cashe.
3. In order to use an existing execution plan, it seems that we have to use the fully qualified identifier, such as
SELECT * FROM Northwind.dbo.Employees
instead of
SELECT * FROM Employees
However, I rarely see anyone uses these kind of fully qualified references for objects both in SQL statements and SP. For example, in the sample database pubs and NorthWind, they don't use the fully qualified expression. I only see the use of it in master database.
I guess I might miss something in the issues above. I would like to get any explanation from SQL guru or anybody. Thanks a lot.
I need some help I have this massive sql script the problem is I tried to put it in to the query string box in my sql reports and it will not take it this script will run if I break it up but I think it is to large is there a sql guru out there that can show me how to reduce the size of this script maybe by using an out parameter to a stored proceedure. I just dont know what to do and need to produce the report from this script. Below is the entire script
SELECT 'Prior Year All ' as 'qtr', COUNT(JOB.JOBID) AS 'transcount', COUNT(DISTINCT JOB.PATIENTID) AS 'patientcount', SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost', SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost', AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc', SUM(JOB.TRANSPORTATION_TCOST) + SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled', SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID) AS 'PatAvgT', SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT', SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT', JOB.JURISDICTION, PAYER.PAY_GROUPNAME, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.PATIENTID, JOB.INVOICE_DATE, JOB.JOBOUTCOMEID, JOB.SERVICEOUTCOME, INVOICE_AR.INVOICE_NO, INVOICE_AR.INVOICE_DATE AS Expr1, INVOICE_AR.AMOUNT_DUE, INVOICE_AR.CLAIMNUMBER, PATIENT.LASTNAME, PATIENT.FIRSTNAME, PATIENT.EMPLOYERNAME, JOB_OUTCOME.DESCRIPTION, SERVICE_TYPE.DESCRIPTION, PAT_SERVICES_HISTORY.TRANSPORT_TYPE,
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedSuccessfullyItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithComplaintItems', (SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoShowItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoChargeItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with Situation') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithSituationItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Not Completed') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'NotCompletedItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Cancelled Prior to service') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CancelledPriorToServiceItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Cancelled During Service') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CancelledDuringServiceItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'AwaitingforcompletionItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Pending for review') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like'%T ')) AS 'PendingforreviewItems'
FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12))AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%')AND (INVOICE_AR.INVOICE_NO like '%T')
GROUP BY JOB.JURISDICTION, PAYER.PAY_GROUPNAME, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.PATIENTID, JOB.INVOICE_DATE, JOB.JOBOUTCOMEID, JOB.SERVICEOUTCOME, INVOICE_AR.INVOICE_NO, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE, INVOICE_AR.CLAIMNUMBER, PATIENT.LASTNAME, PATIENT.FIRSTNAME, PATIENT.EMPLOYERNAME, JOB_OUTCOME.DESCRIPTION, SERVICE_TYPE.DESCRIPTION, PAT_SERVICES_HISTORY.TRANSPORT_TYPE
UNION ALL
SELECT 'Current Year 2007 All ' as 'qtr', COUNT(JOB.JOBID) AS 'transcount', COUNT(DISTINCT JOB.PATIENTID) AS 'patientcount', SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost', SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost', AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc', SUM(JOB.TRANSPORTATION_TCOST) + SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled', SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID) AS 'PatAvgT', SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT', SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT', JOB.JURISDICTION, PAYER.PAY_GROUPNAME, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.PATIENTID, JOB.INVOICE_DATE, JOB.JOBOUTCOMEID, JOB.SERVICEOUTCOME, INVOICE_AR.INVOICE_NO, INVOICE_AR.INVOICE_DATE AS Expr1, INVOICE_AR.AMOUNT_DUE, INVOICE_AR.CLAIMNUMBER, PATIENT.LASTNAME, PATIENT.FIRSTNAME, PATIENT.EMPLOYERNAME, JOB_OUTCOME.DESCRIPTION, SERVICE_TYPE.DESCRIPTION, PAT_SERVICES_HISTORY.TRANSPORT_TYPE,
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@startDate) and DATEADD(@enddate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedSuccessfullyItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@startdate) and DATEADD(@enddate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithComplaintItems', (SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (startdate) and DATEADD(@enddate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoShowItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@startdate) and DATEADD(@enddate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoChargeItems',
&nb