Hello I'm just learning SQL, here's my problem. I have two tables. I'm trying to extract information from table 1. The information I'm trying to get is data that doesn't have a link on table 2. So for example trains is table 1 and train cars is table 2, All the trains have a car except one. How do I get that train without bringing up all the rest.
View 3 RepliesI keep getting this error on this script can some one help me out.
There is an error in the query. The data types varchar and sql_variant are incompatible in the add operator.The multi-part identifier "LANGUAGES.DESCRIPTION" could not be bound.The multi-part identifier "LANGUAGES.DESCRIPTION" could not be bound.
SELECT 'Quarter All' as 'qtr', COUNT(JOB.JOBID) as 'transcount', COUNT(DISTINCT JOB.PATIENTID) as 'patient count', SUM(JOB.LANGUAGE_TCOST) as 'lcost', SUM(JOB.LANGUAGE_DISC_COST) as 'dlcost', AVG(JOB.LANGUAGE_DISC) as 'avgLDisc', SUM(JOB.LANGUAGE_TCOST) + SUM(JOB.LANGUAGE_DISC_COST) as 'LGrossAmtBilled', SUM(JOB.LANGUAGE_TCOST) / COUNT(DISTINCT JOB.PATIENTID) as 'PatAvgL', SUM(JOB.LANGUAGE_TCOST) / COUNT(JOB.JOBID) as 'RefAvgL', SUM(JOB.LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, JOB.JOBOUTCOMEID, JOB.SERVICEOUTCOME, INVOICE_AR.INVOICE_NO, INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE, INVOICE_AR.CLAIMNUMBER, LANGUAGES.DESCRIPTION
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
WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE_AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3,4,5,6,7,8,9,10,11,12))AND (PAYER.PAY_COMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, JOB.JOBOUTCOMEID, JOB.SERVICEOUTCOME, INVOICE_AR.INVOICE_NO, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE, INVOICE_AR.CLAIMNUMBER, LANGUAGES.DESCRIPTIONOrder By 'QTR' asc
I have a sql 2005 dev ed running an application developed in vs.net 2005 C# I have several gridviews which have a field call first name and a field called lastname I need to put both the firstname and lastname in the same cell or colum row. I do not now how to join these. Can some one help me with a SQL query string that will do this for me.
View 2 Replies View RelatedI am having problem which is getting worst by the moment. I had a power failure which my battery backup fail while running a large report in my SSRS all of a sudden I started getting reportservertempdb.dbo.persistedstream error. I could not get my reports to run through the iis webservice. I could get them to run from within my reportbuilder. I was told to reload my sql and restore it but I can not get my sql to successfully reinstall I am using the sql2005 dev ed. It either gives me a name instance issue or fails the database, report server and notifaction portion of the reload. I am not sure why it will not reload. any assistance would be great.
View 1 Replies View Relatedselect bicycle_shop.bicycle_shop_id, bicycle_shop.company_name, order_.unit_price
from order_ inner join bicycle_shop on order_.order_serial_number = bicycle_shop.order_serial_number
order by order_.unit_price DESC;
this query will bring up a few records for each shop, of the one or more bikes they bought and each bike's price, how do i have these come out to a sum(of the price), so that each bike shop will have a sum of revenue. someone told me i need to use group by and the sum function but i dont know how
I want to add an extra blank page after my group, anyone know how to do this? More specifically, I have a report that is grouped by State, when printing I want a blank page to appear before the start of a new State, this will help when the user prints duplex, etc.
Hi
I want to install SQL Server 2000 Enterprise Edition on Windows XP but it isn't installing.
I have installed SQL Server 2000 Personal Edition but Reporting Services are not installing on the server.
PLZ some one help me out.
I have a sql2000 server that has failed
i moved the databases to another server
I am trying to restore msdb to another server is this possible in order to get the dts packets and the jobs
I have a very long transaction that runs on the same database thatother users need to use for existing data. I don't care if they seedata from the transaction before it is done and am only using thetransaction because I need a way to roll it back if any errors happenduring the transaction. Unfortunately all tables affected in the longrunning transaction are completely locked and nobody else can accessany of the affected tables while it is running. I am using thetransaction isolation level of read uncommitted, which from my limitedunderstanding of isolation levels is the least strict. What can I do toprevent this from happening?Below is the output from sp_who2 and sp_lock while the process isrunning and another process is being blocked by it.SPID Status LoginHostName BlkBy DBName Command CPUTimeDiskIO LastBatch ProgramName SPID----- ------------------------------------------------------------------------------ ---------- ----------------- ---------------- ------- ------ ------------------------------------------ -----1 BACKGROUND sa. . NULL LAZY WRITER 0 006/09 15:42:52 12 sleeping sa. . NULL LOG WRITER 10 006/09 15:42:52 23 BACKGROUND sa. . master SIGNAL HANDLER 0 006/09 15:42:52 34 BACKGROUND sa. . NULL LOCK MONITOR 0 006/09 15:42:52 45 BACKGROUND sa. . master TASK MANAGER 0 506/09 15:42:52 56 BACKGROUND sa. . master TASK MANAGER 0 006/09 15:42:52 67 sleeping sa. . NULL CHECKPOINT SLEEP 0 1206/09 15:42:52 78 BACKGROUND sa. . master TASK MANAGER 0 206/09 15:42:52 89 BACKGROUND sa. . master TASK MANAGER 0 006/09 15:42:52 910 BACKGROUND sa. . master TASK MANAGER 0 006/09 15:42:52 1011 BACKGROUND sa. . master TASK MANAGER 0 106/09 15:42:52 1112 BACKGROUND sa. . master TASK MANAGER 0 006/09 15:42:52 1251 sleeping SUPERPABLOAdministratorSUPERPABLO . PM AWAITING COMMAND 1813307 06/09 16:10:34 .Net SqlClient Data Provider 5152 sleeping SUPERPABLOAdministratorSUPERPABLO 54 PM SELECT 30 506/09 16:10:16 .Net SqlClient Data Provider 5253 RUNNABLE SUPERPABLOAdministratorSUPERPABLO . master SELECT 0 306/09 16:09:44 SQL Profiler 5354 RUNNABLE SUPERPABLOAdministratorSUPERPABLO . PM UPDATE 10095206 06/09 16:10:02 .Net SqlClient Data Provider 5456 RUNNABLE SUPERPABLOAdministratorSUPERPABLO . PM SELECT INTO 151 2706/09 16:10:33 SQL Query Analyzer 56(17 row(s) affected)spid dbid ObjId IndId Type Resource Mode Status------ ------ ----------- ------ ---- ---------------- -------- ------51 5 0 0 DB S GRANT52 5 0 0 DB S GRANT52 5 1117963059 4 PAG 1:7401 IS GRANT52 5 1117963059 4 KEY (5301214e6d62) S WAIT52 5 1117963059 0 TAB IS GRANT54 5 1117963059 0 TAB IX GRANT54 5 1852025829 0 TAB IX GRANT54 5 1181963287 3 PAG 1:9017 IX GRANT54 5 1117963059 4 KEY (5301934930a4) X GRANT54 5 1117963059 3 KEY (530187fc93f3) X GRANT54 5 1117963059 4 KEY (530154df71eb) X GRANT54 5 0 0 DB [BULK-OP-LOG] NULL GRANT54 5 0 0 FIL 2:0:d U GRANT54 5 1117963059 2 KEY (1d0096c50a7d) X GRANT54 5 1117963059 2 KEY (1b004a9a6158) X GRANT54 5 1117963059 2 KEY (1800a435d44a) X GRANT54 5 1181963287 6 PAG 1:8745 IX GRANT54 5 1181963287 4 PAG 1:8923 IX GRANT54 5 1181963287 2 PAG 1:8937 IX GRANT54 5 1117963059 4 KEY (5301112b0696) X GRANT54 5 0 0 PAG 1:10889 IX GRANT54 5 1181963287 5 PAG 1:8859 IX GRANT54 5 1181963287 6 PAG 1:10888 IX GRANT54 5 0 0 PAG 1:10891 IX GRANT54 5 0 0 PAG 1:10893 IX GRANT54 5 0 0 PAG 1:10892 IX GRANT54 5 0 0 PAG 1:10894 IX GRANT54 5 0 0 PAG 1:10882 IX GRANT54 5 1117963059 3 KEY (530135fbce35) X GRANT54 5 1117963059 0 RID 1:7387:57 X GRANT54 5 1117963059 0 RID 1:7387:59 X GRANT54 5 1117963059 0 RID 1:7387:61 X GRANT54 5 1117963059 3 KEY (5301406ad2bc) X GRANT54 5 1117963059 4 PAG 1:7401 IX GRANT54 5 0 0 PAG 1:7387 IX GRANT54 5 1117963059 2 PAG 1:7389 IX GRANT54 5 1117963059 3 PAG 1:7391 IX GRANT54 5 1117963059 0 RID 1:7387:10 X GRANT54 5 1117963059 0 RID 1:7387:56 X GRANT54 5 1117963059 0 RID 1:7387:58 X GRANT54 5 1117963059 0 RID 1:7387:60 X GRANT54 5 1117963059 3 KEY (530144afbed8) X GRANT54 5 1117963059 4 KEY (530115ee6af2) X GRANT54 5 1117963059 3 KEY (5301c6cd88ea) X GRANT54 5 1149963173 0 TAB IX GRANT54 5 1181963287 0 TAB X GRANT54 5 1117963059 4 KEY (5301d2782bbd) X GRANT54 5 1117963059 3 KEY (5301015bc9a5) X GRANT54 5 0 0 DB S GRANT54 5 0 0 DB [BULK-OP-DB] NULL GRANT54 5 1117963059 4 KEY (5301501a1d8f) X GRANT54 5 1117963059 2 KEY (1c00f3a2b6c5) X GRANT54 5 1117963059 2 KEY (1a002ffddde0) X GRANT54 5 0 0 PAG 1:7411 IX GRANT54 5 1117963059 2 KEY (1900c15268f2) X GRANT54 5 0 0 PAG 1:10840 IX GRANT54 5 1181963287 4 PAG 1:10841 IX GRANT54 5 0 0 PAG 1:10842 IX GRANT54 5 1117963059 3 KEY (5301059ea5c1) X GRANT54 5 0 0 PAG 1:10820 IX GRANT54 5 1181963287 4 PAG 1:10821 IX GRANT54 5 1181963287 5 PAG 1:10874 IX GRANT54 5 1181963287 5 PAG 1:10876 IX GRANT54 5 0 0 PAG 1:10877 IX GRANT54 5 1181963287 5 PAG 1:10878 IX GRANT54 5 0 0 PAG 1:10849 IX GRANT54 5 0 0 PAG 1:10850 IX GRANT54 5 1117963059 2 KEY (1700f225b712) X GRANT54 5 1117963059 4 KEY (5301214e6d62) X GRANT56 5 0 0 DB S GRANT56 1 85575343 0 TAB IS GRANT
View 29 Replies View RelatedHi, I have a table in which I will insert several redundant data. Don't ask why, is Integration services, it only reads data and inserts it in a SQL table. THis way, I have a SQL table with several lines repeating them selves. What I want to do is create a procedure that reads the distinct data and inserts it in another table, but my problem is that I am not able to select data line by line on the original table to save it in local variables and insert it on the another table, I just can select the last line. I've tried a while cycle but no succeed. Here is my code: create proc insertLocalizationASdeclare @idAp int, @macAp varchar(20), @floorAp varchar(2), @building varchar(30), @department varchar(30)select @idAp = idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization)select @macAp=macAp,@floorAp=floorAp,@building=building,@department=department from OLTPLocalizationif (@idAp <> null)beginInsert into dimLocalization VALUES(@idAp,@macAp,@floorAp,@building,@department)endGO This only inserts the last line in the "oltpLocalization" table. O the other hand, like this:create proc aaaaasdeclare @idAp as int, @macAp as varchar(50), @floorAp as int, @building as varchar(50), @department as varchar(50)while exists (select distinct(idAp) from OLTPLocalization)begin select @idAp =idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization) select @macAp = macAp from OLTPLocalization where idAp = @idAp select @building = building from OLTPLocalization where idAp = @idAp select @department = department from OLTPLocalization where idAP = @idApif (@idAp <> null)begin insert into dimLocalization values(@idAp,@macAp,@floorAp,@building,@department)endendgo this retrieves every distinct idAp in each increment on the while statement. The interess of the while is really selecting each different line in the OLTPLocalization table. I did not find any foreach or for each statement, is there any way to select distinct line by line in a sql table and save each column result in variables, to then insert them in another table? I've also thought about web service, that reads the distinct data from the oltpLocalization into a dataset, and then inserts this data into the dimLocalization table. Is there anything I can do?Any guess?Really needing a hand here!Thanks a lot!
View 1 Replies View Related