select dssid,cpt,count(*) from enc_vis_cpt group by dssid,cpt
will give me the cpt's and their frequency for each dssid.
dssid cpt count
SPINAL CORD INJURY 9934120
AMB SURGERY EVAL BY NON-MD 622703
PSYCHOSOCIAL REHAB - GROUP 993414
SPINAL CORD INJURY 983419
AMB SURGERY EVAL BY NON-MD 6327031
PSYCHOSOCIAL REHAB - GROUP 9734114
SPINAL CORD INJURY 9934280
AMB SURGERY EVAL BY NON-MD 6227353
PSYCHOSOCIAL REHAB - GROUP 9934524
How do I limit the output to just display the 10 most frequent cpt's for each dssid. Thank you...
Hello all I'm not sure if this is anything to be concerned about, but I'd appreciate some input. I've created an alert that invokes a log backup scheduled job to start if one of the production database log becomes over 80% full. I've noticed that the log gets backed up every couple of minutes- sometimes even more frequently- while a series of scheduled jobs are in the process of execution. Otherwise, it runs and backs up the log every hour as scheduled. I've also noticed that some of the jobs are taking longer to complete (they've been running for about 6 months now). Each job truncates the table that it populates with data, so I'm not sure what the cause of the delay is. It doesn't look like there is any fragmentation. Am I missing anything? Thanks
I have used Base SAS for analysis for a while and it was really great.. everything is easy just with a simple command.. I am sure it's not the same in SQL Server but I need some help on how to start with the following:
I have a field called call_country and another field called call_minute. Each call will be saved with the destination country and the total number of minutes..
and I want to run a query to see what are the TOP frequent destinations in this format:
There must be a way to do this simply. We're running SQL Server 2000. I'm looking for some generic SQL statement that I can apply.
If I have a table with a person column and a location column and multiple records for the same person / locatioin combination, how do I select the person with the location they most frequently visited? Say George visits Mexico 5 times, and the Bahamas twice and costa rica once. I would have 8 records in my table for George. The data looks something like this:
I have a reviews table where all reviews are submitted. On the main page I want to display the 10 most reviewed products. I have a Product_ID column in this table which identifys the product. How can i write a query which will select the product_ID of records which have the most frequent product_ID's?
I came up with something like this: "Select Top 10 Product_ID, COUNT(*) AS Occurances FROM reviews GROUP BY Product_ID ORDER BY occurances DESC"
But it does not work.?? It says "Declaration expected" as error
We have 7 databases mirrored on two servers which are mirroring partners. 3 of the 7 databases are live on server1 and mirrored on server2; and the remaining 4 databases are live on server2 and mirrored on server1. The data is exposed through .NET Widows Application.
The configurations of the servers are as follows.
System: Microsoft Windows Server 2003 R2
Standard x64 Edition
Service Pack 2
Computer: Intel(R) Xeon (R) CPU
5130 @ 2.00 GHz
2.00 GHz, 32.0 GB of RAM
SQL Version: Microsoft SQL Server 2005 - 9.00.3175.00 (X64) Jun 14 2007 11:45:39
Copyright (c) 1988-2005 Microsoft Corporation Enterprise Evaluation Edition (64-bit)
on Windows NT 5.2 (Build 3790: Service Pack 2)
Problem:
The databases for no apparent reason keep randomly failing over to one server quite frequently. At least twice a day. There is no pattern associated for me to make out as to why this is happening.
My Questions:
1. Is it a good practice to divide the databases on each server, the way it is now? Or should all the databases be kept on one server and mirrored on other all the time?
2. From the above mentioned scenario, do you find the reason for database to 'failing over' so frequently? Could the Win Application which is used to expose the data be responsible for the failovers?
3. What steps can be taken to check for the reason which is causing the databases to failover? Alternatively and most importantly, how can I this problem of 'Databases Failing over randomly' be solved?
What function(s) can be used to find the mode of data? I have a column that is populated with codes and I'd like to summarize the data by the code that occurs the most frequently. Any help is appreciated!!
How can I make a statement that will return the 10 most frequently occuring values in a column?
I have no idea if that is even possible, if you have an idea on how I could do that I would really appreciate it.
Im trying to make a page that would show some statistics on a table I have.
Im also trying to make something that would show the count of the number of records inserted in the last 24 hours, week, month, year etc. The table has a column called "DateInserted" as SmallDate, right now i can use a Where DateInserted > '20080310' to get the count, but its not dynamic, is there anyone to merge all these results into one row with each column being a diffrent time period?
I know this a lot of questions, but I would really appreciate any pointers.
we have a handful of developers and each of us are responsible for laying out and creating our own database backends. This often leads to inconsistencies in table and column structures.One obvious situation that comes up often is whether or not the other developers are building in history into their primary tables, using history/archive tables or (usually in cases of helper tables) no historical data at all.
My thought on how to alleviate this a little was to suggest that we all build a IS_DELETED computed column into our tables so that someone else trying to work with their data doesn't have to play the guessing game. In most cases, this column would just be running date comparisons on an Expiration Date and either checking to see if it's in the future (usually 12/31/9999) or NULL.
I have read that computed columns can be a performance hit if used/returned unnecessarily but is that also the case on fields where their main use would be filtering? It just seems that the calculation that the computed column is doing would be necessary for the WHERE anywhere so it seems like a wash ... and worth the benefit of not having to decipher someone else's work.
I am working on a text mining application wherein I need to detect unusual/anomalous sentences in text. Certain sentences, that I know occur very frequently, are given a likelihood of 0.2 by PredictCaseLikelihood. Other sentences that are just as frequent get a much higher likelihood (>0.9). I am using the NORMALIZED option. The only significant difference between these sentences is their length. The one with the lower likelihood has only 2 words in it, whereas the one with the higher likelihood has more than 10 words. The problem is that the shorter sentences end up being interpreted as anomalous, when in fact they are'nt. Any suggestions?
I am constantly getting this error message in the Application log after installing SQL 2005 last night followed by SP1 (say 5 times a minutes). See below:
I am using ODBC to connect SQL Server 2000 + SP4 server runnig on Windows 2003 standard edition Server +SP1. But, sporadically my application server connectivity to DB fails and i receive the following error messages
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (WrapperWrite()). [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.) In D:de.cpp 702 Apr 12 2006 20:34:47 [Microsoft][ODBC SQL Server Driver]Communication link failure) In D:de.cpp 702 Apr 12 2006 20:34:47 20071017 08:23:10 TID (00000ff0) Sev (3) Err (0) Msg (Read failure. General SQL error.
Strange thing is that i receive this error only sporadically, Please Please advice !
