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
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
I know that you can alter the maximum characters per column value in Query Analyzer by using the menu item 'TOOLS' then 'OPTIONS', then selecting the 'RESULTS' tab. I'm convinced that there is a way to do this programmatically (need it for a stored procedure) but cannot figure out how and would appreciate any help given.
The query below gets percentage of coils for a location by anneal cycle. It does this correctly (ie, if you change count(*)/b.total to count(*), b.total ... show both columns, the numbers its pulling are correct), but in its current state (trying to divide count by total), it always returns a zero (no decimal values).
If I change that section to : (count(*)*100/ b.total*100)/100, I get integer percentages: no decimals (this is a problem because some records round off to zero). I'm pretty sure this is the entire problem (why I'm getting zeros as written below), but I don't have a clue how to fix it. Is this some kind of server setting that allows the query to only return decimal values or something?
select a.department, a.cycle, count(*)/ b.total percentage from inventory a join (select department, count(*) total from inventory where location not like 'bas%' and archived = 0 and quality_code = 'p' group by department) b on a.department = b.department where a.archived = 0 and a.quality_code = 'p' and location not like 'bas%' group by a.department, a.cycle, b.total order by a.department, a.cycle
I have a table which has the column [itemNumber] Which contains numbers from 000 to 999. I have another table which has the UPC data for given items I am trying to get results from my query that will show me every number in the itemNumberSet table that does not already exist (in the substring) of the UPCcode column.
By using the query below i am able to retrieve the opposite, and it works by returning results that do exist in the UPCcode column. But I cannot seem to get it to do the opposite which is what i am after. I figured it would be as simple as using NOT IN but that returned 0 results.
SELECT itemNumber FROM itemNumberSet WHERE itemNumber IN (select SUBSTRING(UPCcode, 9, 3) FROM itemUPCtable) ORDER BY itemNumber
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
One 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.
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'
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.
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?
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.
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?
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'
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
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..
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.
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.
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 !
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..
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.
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!
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
Hey guys... i cant figure this out for the life of me. I have a long T-sql query, and when i enter the string "Rental" into the Listingtype, it says invalid column name "Rental" ... im not looking for the value to be a column, im looking for it to match the value in the ListingType column... here's the query:
IF @Studio IS NOT NULL SET @SQL = @SQL + ' AND r.StudioFlag = ' + CONVERT(VARCHAR(20), @Studio) IF @Br1 IS NOT NULL SET @SQL = @SQL + ' AND r.BRFlag1 = ' + CONVERT(VARCHAR(20), @Br1) IF @Br2 IS NOT NULL SET @SQL = @SQL + ' AND r.BRFlag2 = ' + CONVERT(VARCHAR(20), @Br2) IF @Br3 IS NOT NULL SET @SQL = @SQL + ' AND r.BRFlag3 = ' + CONVERT(VARCHAR(20), @Br3) IF @Br4 IS NOT NULL SET @SQL = @SQL + ' AND r.BRFlag4 = ' + CONVERT(VARCHAR(20), @Br4) IF @OverBr4 IS NOT NULL SET @SQL = @SQL + ' AND r.OverBRFlag4 = ' + CONVERT(VARCHAR(20), @OverBr4) IF @Condo IS NOT NULL SET @SQL = @SQL + ' AND r.CondoFlag = ' + CONVERT(VARCHAR(20), @Condo) IF @ListingType IS NOT NULL SET @SQL = @SQL + ' AND r.ListingType = ' + CONVERT(char, @ListingType) IF @WindowAir IS NOT NULL SET @SQL = @SQL + ' AND a.WindowAir = ' + CONVERT(VARCHAR(20), @WindowAir) IF @CentralAC IS NOT NULL SET @SQL = @SQL + ' AND a.CentralAir = ' + CONVERT(VARCHAR(20), @CentralAC) IF @BalconyDeckPatio IS NOT NULL SET @SQL = @SQL + ' AND a.BalconyDeckPatio = ' + CONVERT(VARCHAR(20), @BalconyDeckPatio) IF @UseOfYard IS NOT NULL SET @SQL = @SQL + ' AND a.UseOfYard = ' + CONVERT(VARCHAR(20), @UseOfYard) IF @Dishwasher IS NOT NULL SET @SQL = @SQL + ' AND a.Dishwasher = ' + CONVERT(VARCHAR(20), @Dishwasher) IF @WasherDryer IS NOT NULL SET @SQL = @SQL + ' AND a.WasherDryer = ' + CONVERT(VARCHAR(20), @WasherDryer) IF @Fireplace IS NOT NULL SET @SQL = @SQL + ' AND a.Fireplace = ' + CONVERT(VARCHAR(20), @Fireplace) IF @EIK IS NOT NULL SET @SQL = @SQL + ' AND a.EIK = ' + CONVERT(VARCHAR(20), @EIK) IF @HardwoodFloors IS NOT NULL SET @SQL = @SQL + ' AND a.HardwoodFloors = ' + CONVERT(VARCHAR(20), @HardwoodFloors) IF @BroadBandNet IS NOT NULL SET @SQL = @SQL + ' AND a.BroadbandNet = ' + CONVERT(VARCHAR(20), @BroadbandNet) IF @TV IS NOT NULL SET @SQL = @SQL + ' AND a.TV = ' + CONVERT(VARCHAR(20), @TV) IF @Thermostat IS NOT NULL SET @SQL = @SQL + ' AND a.Thermostat = ' + CONVERT(VARCHAR(20), @Thermostat) IF @LandlordNotPresent IS NOT NULL SET @SQL = @SQL + ' AND a.LandLordNotPresent = ' + CONVERT(VARCHAR(20), @LandLordNotPresent) IF @Smoking IS NOT NULL SET @SQL = @SQL + ' AND a.Smoking = ' + CONVERT(VARCHAR(20), @Smoking) IF @NoPetsAllowed IS NOT NULL SET @SQL = @SQL + ' AND a.NoPetsAllowed = ' + CONVERT(VARCHAR(20), @NoPetsAllowed) IF @Cat IS NOT NULL SET @SQL = @SQL + ' AND a.Cat = ' + CONVERT(VARCHAR(20), @Cat) IF @MoreCats IS NOT NULL SET @SQL = @SQL + ' AND a.MoreCats = ' + CONVERT(VARCHAR(20), @MoreCats) IF @SmallDog IS NOT NULL SET @SQL = @SQL + ' AND a.SmallDog = ' + CONVERT(VARCHAR(20), @SmallDog) IF @LargeDogs IS NOT NULL SET @SQL = @SQL + ' AND a.LargeDogs = ' + CONVERT(VARCHAR(20), @LargeDogs) IF @Doorperson IS NOT NULL SET @SQL = @SQL + ' AND a.Doorperson = ' + CONVERT(VARCHAR(20), @Doorperson) IF @IngroundPool IS NOT NULL SET @SQL = @SQL + ' AND a.IngroundPool = ' + CONVERT(VARCHAR(20), @IngroundPool) IF @AboveGroundPool IS NOT NULL SET @SQL = @SQL + ' AND a.AboveGroundPool = ' + CONVERT(VARCHAR(20), @AboveGroundPool) IF @Elevator IS NOT NULL SET @SQL = @SQL + ' AND a.Elevator = ' + CONVERT(VARCHAR(20), @Elevator) IF @UseOfGarage IS NOT NULL SET @SQL = @SQL + ' AND a.UseOfGarage = ' + CONVERT(VARCHAR(20), @UseOfGarage) IF @LaundryFacilities IS NOT NULL SET @SQL = @SQL + ' AND a.LaundryFacilities = ' + CONVERT(VARCHAR(20), @LaundryFacilities) IF @HealthCenter IS NOT NULL SET @SQL = @SQL + ' AND a.Health Center = ' + CONVERT(VARCHAR(20), @HealthCenter) IF @StorageAreas IS NOT NULL SET @SQL = @SQL + ' AND a.StorageAreas = ' + CONVERT(VARCHAR(20), @StorageAreas) IF @WheelchairAccess IS NOT NULL SET @SQL = @SQL + ' AND a.WheelchairAccess = ' + CONVERT(VARCHAR(20), @WheelchairAccess) IF @BusinessCenters IS NOT NULL SET @SQL = @SQL + ' AND a.BusinessCenters = ' + CONVERT(VARCHAR(20), @BusinessCenters) IF @RentChargeMin IS NOT NULL AND @RentChargeMAX IS NOT NULL SET @SQL = @SQL + ' AND a.RentCharge BETWEEN ' + CONVERT(VARCHAR(20), @RentChargeMin) + ' AND ' + CONVERT(VARCHAR(20), @RentChargeMax) IF @RentChargeMin IS NOT NULL AND @RentChargeMAX IS NULL SET @SQL = @SQL + ' AND a.RentCharge >= ' + CONVERT(VARCHAR(20), @RentChargeMin) IF @RentChargeMAX IS NULL AND @RentChargeMAX IS NOT NULL SET @SQL = @SQL + ' AND a.RentCharge <= ' + CONVERT(VARCHAR(20), @RentChargeMax)
Hi. I am trying to setup php4 to talk to MSSQL 7, but am having no luck. Both are on a Win2k server. I get the error below. Please can someone tell me what to do, or even a step by step guide to setting up php to connect to a MSSQL7 database. I originally created the database in access 2000 and now want to use MSSQL to use it. This is the error i get:
Warning: MS SQL message: Could not locate entry in sysdatabases for database 'customers'. No entry found with that name. Make sure that the name is entered correctly. (severity 16) in C:Websiteewsiteestdb.php on line 13
Warning: MS SQL: Unable to select database: customers in C:Websiteewsiteestdb.php on line 13 Database unavailable
I was running a server with TempDB in RAM (please don't comment on why that's not a good idea). Well, everything was fine until one of our administrator install teh backup exec agent and restarted the server. Since then the server won't start giving an error the Tempdb coulld not be moved into ram. Well, I started the server using the -f parameter and reset the Tempdb in RAM setting to zero. Howeever, it still won't start and gives the same error about not being able to move Tempdb into RAM. Is my only option to rebuild the master database? This is just a backup server but I'd like to avoid the hassle of rebuilding master and reinitializing the databases.
I'm trying to work out how to extract the information that I need from a set of database tables and can't think of a way of doing it with SQL.
The database forms the basis for a diary/calendar system for part-time employees and has two tables:
- One is called 'Availability' and holds info on each available hour slot in the calendar. The table just has fields 'time' and 'date', where the time is an integer representing an hour and date is datetime. The calendar runs from 07.00 to 23.00 each day, so there could be 16 rows in 'Availability' for one day. If any part of a day is unavailable (i.e. the employee doesn't work then) there will be no corresponding rows in the table.
- The second table is 'Appointments', which holds details of appointments that the employee is booked for. The main fields are 'date', 'time' and 'duration' (integer for minutes). All appointments will cover a time span that is also covered by an available period, but they are not actually linked in any way.
I need an SQL query that will return all available time slots that start at least 60 minutes after any appointments have FINISHED and at least 120 minutes before any appointment STARTS.
Since there is no link between the 'Appointments' table and the 'Availability' table, I can't think of any way of doing this.
Hi,I would like to convert real data type to datetime type. Example:I have a real data type which is: 23,613456 (23 hours and 0,613456). Iwould like to have it in hh:mm:ss format. How to do this? Can I useconvert/cas function?Thanks for helpRgdsMario
Timings... sometimes there are almost too many ways to do the same thing.The only significant findings I see from all the below timings is:1) Integer math is generally fastest, naturally. Bigint math isn't muchslower, for integers that all fit within an integer.2) Converting float to varchar is relatively slow, and should be avoided ifpossible. Converting from integer to varchar or varchar to int is severaltimes faster.3) Most significantly, and less obvious, CASE expr WHEN .... recomputes exprfor each WHEN condition, unfortunately, and is the same speed (or perhapsslightly slower) as listing WHEN expr = value for each condition. Perhaps anindexed computed column (somehow materialized) would be advisable whenpossible to avoid repeated computations in CASE..WHEN expressions (if thathelps..).Note that if you divide by COUNT(*), most timings below are below onemicrosecond per row, so this all may not be very significant in mostapplications, unless you do frequent aggregations of some sort.COUNT(*) FROM [my_sf_table] = 477446 rowsThe result from each query = either 47527 or 47527.0Platform: Athlon 2000 XP w/512MB RAM, table seems to be cached in RAM, SQL2000, all queries run at least 3 times and minimum timings shown (msec).SRP is a REAL (4 bytes)Fastest ones are near the end.CPU SQL(ms)-- Convert to varchar (implicitly) and compare right two digits-- (original version -- no I didn't write it)4546 select sum(case right(srp,2)when '99' then 1 when '49' then 1 else 0 end)from sf-- Use LIKE for a single comparison instead of two, much faster-- Note that the big speedup indicates that-- CASE expr WHEN y then a WHEN z then b .-- recalculates expr for each WHEN clause2023 select sum(case when srp like '%[49]9' then 1 else 0 end)from sf-- Floating point method of taking a modulus (lacking fmod/modf)2291 select sum(case round(srp - 100e*floor(srp*.01e),0)when 99 then 1 when 49 then 1 else 0 end)from sf-- Round to nearest 50 and compare with 491322 select sum(case round(srp-50e*floor(srp*.02e),0)when 49 then 1 else 0 end)from sf-- Divide by 49 by multiplying by (slightly larger than) 1e/49e811 select sum(floor((cast(srp as integer)%50)*2.04082E-2))from sf-- Integer approach without using CASE731 select sum(coalesce(nullif(sign(cast(srp asinteger)%50-48),-1),0))from sf-- My original integer approach651 select sum(case cast(srp as integer)%100when 99 then 1 when 49 then 1 else 0 end)from sf-- Modulus 50 integer approach without CASE481 select sum((cast(srp as integer)%50)/49)from sf-- Modulus 50 integer approach460 select sum(case cast(srp as integer)%50when 49 then 1 else 0 end)from sf-- bigint without CASE531 select sum((cast(srp as bigint)%50)/49)from sf-- bigint with CASE521 select sum(case cast(srp as bigint)%50when 49 then 1 else 0 end)from sf-- get SIGN to return -1 or 0, then add 1-- much better than the coalesce+nullif approach500 select sum(sign(cast(srp as integer)%50-49)+1)from sf-- SIGN with BIGINT551 select sum(sign(cast(srp as bigint)%50-49)+1)from sfBTW, I know srp should be int to begin with for this to be faster... Okay,so...select cast(srp as int) srp into sf from [my_real_sf_table]720 select sum(case when srp like '%[49]9' then 1 else 0 end) from sf339 select sum(1+sign(srp%50-49)) from sf310 select sum(srp%50/49) from sf300 select sum(case srp%50 when 49 then 1 else 0 end) from sfWhat if it were a char(7)?select cast(cast(srp as integer) as char(7)) srp into sf2 from[my_sf_table]801 select sum(case right(rtrim(srp),2) when '49' then 1when '99' then 1 else 0 end) from sf2717 select sum(case when srp like '%[49]9' then 1 else 0 end) from sf2405 select sum(srp%50/49) from sf2391 select sum(case srp%50 when 49 then 1 else 0 end) from sf2How about varchar(7)?drop table sf2select cast(cast(srp as integer) as varchar(7)) srp into sf2 from[my_sf_table]581 select sum(case right(srp,2) when '49' then 1when '99' then 1 else 0 end) from sf2569 select sum(case when srp like '%[49]9' then 1 else 0 end) from sf2LIKE is faster on VARCHAR than on CHAR columns...Apparently it has to effectively RTRIM the trailing spaces during the LIKEoperation.Is binary collation any faster?drop table sf2select cast(cast(srp as integer) as varchar(7))COLLATE Latin1_General_BIN srpinto sf2 from tbl_superfile561 select sum(case right(srp,2) when '49' then 1when '99' then 1 else 0 end) from sf2530 select sum(case when srp like '%[49]9' then 1 else 0 end) from sf2Binary collation comparisons are slightly faster, though it's not a bigdifference (with just two characters being compared).662 select sum(case convert(binary(2),right(srp,2))when 0x3439 then 1 when 0x3939 then 1 else 0 end) from sf2-----------5037 select right(srp,2) srp,count(*) from my_sf_tablegroup by right(srp,2)order by right(srp,2)920 select cast(srp as int)%100 srp,count(*) from my_sf_tablegroup by cast(srp as int)%100order by cast(srp as int)%100---On the one hand, premature optimization can be a waste of time and energy.On the other hand, understanding performance implications of variousoperations can help write more efficient systems.In any case, an indexed computed column or one updated on a trigger couldvirtually eliminate the need for any of these calculations to be performed,except upon insertion or update, so maybe my comparisons aren't verymeaningful for most applications, considering we're talking about less than3 microseconds per row here worst-case.But the results remind me, some recommend avoiding Identity when it's notnecessary. I find Identity(int,1,1) to be a nice, compact surrogate key thatis useful for quick comparisons, grouping, etc, and so on. Also, it seemsmost appropriate as the primary key to all lookup tables in a star schema inOLAP data warehousing. (?) Of course, in some situations, it's notappropriate, particularly when having a surrogate key violates dataintegrity by allowing duplicates that would not be allowed with a properprimary key constraint, or when the surrogate key is completely redundantwith (especially a short) single-column unique key value that would be abetter selection as the primary key. With multi-column primary keys, I thinkit's sometimes convenient to have a surrogate Identity if only for INclauses that reference that identity column (though EXISTS can usuallyreplace those, so maybe that's a weak excuse for an extra column.)
Showing the mySqlCommand.CommandString it will look like: (A) SELECT myColumn FROM myPersonsTable WHERE myColumn=@FirstName Is this the real string that is send to the Database (among with some hidden string "John")?...
...or does the real string which is send to the Database look like: (B) SELECT myColumn FROM myPersonsTable WHERE myColumn='John' ???
If so, where can I retrieve that last string (B)? Or is (A) the way 'transact sql' operates?
I have a SQL Server 2000 database using Integrated Security. Users are added directly to the database from their domain name. In the Login section of the Security node in Enterprise Manager they show up as DomainNameUserName. I have four users that have the System Administrator database role. When I use the user function to get their user name they show up as dbo. Is there a way to get their actual domain user name?
