I've got a working query which returns all leads within a supplied proximity to a city. I followed a tutorial I googled a couple months ago (can't find it now). It works, but would love others to look the query over (provided DDL and sample data) and tell me if it's as it should be.
Two things I don't like about query:
1. I have to do a UNION to another query that retrieves everything that is in the same city in order to have complete results. 2. very slow to retrieve results (> 1 minute)
I need to divide amount in joint account. So if joint account has 2 account holders & amount is 35622.15 then one person should have 17811.08 and other person should have 17811.07
If I used below query it just give me 17811.08 for both account holders so when we sum it it's one penny extra.
Hello to all, help, help,... i have with this problem since 3 weeks, until now i cann't resolve this problem. Maybe can somebody help me. I am hopeless. i have a data table ValidRelationship, i will check if there is a relationship between two members by this table. datas in the table ValidRelationship: IDMember IDOtherMember IDType
3700 3726 10000
3726 3700 10000
3742 3672 10000
3672 3742 10000
3422 3548 10000
3548 3422 10000
3548 3717 10000
3717 3548 10000
3675 3695 10000
3695 3675 10000 I will give two member and check their Relationship with a sql query. but it can be that this two member have no relationship. So i define here that man should search processor <= 6 . To better describe i use a example: max. Result of this query is: 1-2-3-4-5-6. If this is a relationship between 1-7 is 1-2-3-4-5-6-7, but i will give a answer that this is no relationship between 1-7. because processor > 6. But my problem is: this query executing is too slow. if i habe two member no relationship, the time of this complete sql query to execute is more than 1 minutes. Is my algorithm wrong, or where is the problem, why this executing is so slow? How can i quickly get the relationships between two member, if my algorithms is not right? The following Query is only to processor = 3, but it works too slowly, so i don't write remaining processors. declare @IDM int; declare @IDO int; set @IDM = 3418; set @IDO = 4270 select top 1 IDMember from v_ValidRelationships where IDMember = @IDM and @IDO in (select a.IDOtherMember from v_ValidRelationships as a where a.IDMember = @IDM)
select top 1 a.IDMember, b.IDMember from v_ValidRelationships as a, v_ValidRelationships as b where a.IDMember = @IDM and b.IDMember in (select c.IDOtherMember from v_ValidRelationships as c where c.IDMember = @IDM) and @IDO in (select d.IDOtherMember from v_ValidRelationships as d where d.IDMember = b.IDMember )
select top 1 a.IDMember, b.IDMember, e.IDMember from v_ValidRelationships as a, v_ValidRelationships as b, v_ValidRelationships as e where a.IDMember = @IDM and b.IDMember in (select c.IDOtherMember from v_ValidRelationships as c where c.IDMember = @IDM) and e.IDMember in (select f.IDOtherMember from v_ValidRelationships as f where f.IDMember = b.IDMember) and @IDO in (select d.IDOtherMember from v_ValidRelationships as d where d.IDMembe = e.IDMember)
If someone has a idea, please help me. Thank a million Best Regards Shasha
I am trying to configure Reporting Services to allow a domain group access to reports. I am able to configure the domain and group (mydomaingrpname) in both Report Manager and BIDS. I'm sure I entered the correct name because I purposely misspelled it and received an error. I think this tells me it is finding the group correctly.
However, when my test user goes to Report Manager, there are no folders displayed. I checked and he is in the domain group I am using. If I explicitly add him (mydomainandy) to the folders, he can see them and execute the reports.
After searching the forums and other websites, I have checked IIS is using Windows Integrated Security and not anonymous access.
Hi am i am facing problem adding a domain group to the reporting services. while setting the security of a report, i am getting the rsUnknownUserName error while adding a domain group. the group is valid and it does exists. i tried creating a windows group on the machine running reporting services and tried adding the domain group and it accepted. but the reporting services is not accepting. can somebody tell me whats the problem with this. i am able to add other domain group belonging to the same domain and the SSRS accepts but not this particular domain group which is like any other domain group.
I have a situation (on SQL Server 2000 SP3a) where the guest account appears in the list of database users despite the account being removed via sp_dropuser.
The guest account appears in the list of users with Database Access set as 'Via Group Membership'
Once in this state it cannot be removed as sp_drop user will now report:
Server: Msg 15008, Level 16, State 1, Procedure sp_revokedbaccess, Line 36 User 'guest' does not exist in the current database.
My conerns are:
(a) Does this imply any security risks ? (b) How can I remove all reference to the guest account?
I am joining on table A AND Table B ON the accountnumber field. The accountnumber on the tblB has some invisble characters at the end, ie "12344 ". The Table A does not have these invisble chars and is "12344". Due to the extra spaces at the end, the join fails and does not find a match for that acctno. I have used ltrim(rtrim) hoping that will elimante the extra spaces and find a match. Heres my qry:
select * from Tbla a inner join tblb B on ltrim(rtrim(ACCTNO))=ltrim(rtrim(ACCT))
How do I do this, so that it finds a match and returns the acctno.
I am attempting to configure my SQL Server instance to use a service account with the minimum privileges. I thought I had everything configured correctly, when I realized that having the service account as a member of the "SQLServer2005MSSQLUser" Windows Group meant that the service account was now in the "sysadmin" fixed server role. This was not the configuration I wanted.
I went through the Books Online article "Setting Up Windows Service Accounts" and made sure the login had access to the appropriate folders used by SQL Server. Then I stopped the SQL Server service and tried to restart it, without success. These are the error messages:
Code Snippet
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
FCB::Open failed: Could not open file E:MSSQL$STAGINGDatamodel.mdf for file number 1. OS error: 5(Access is denied.).
TDSSNIClient initialization failed with error 0x5, status code 0x1.
TDSSNIClient initialization failed with error 0x5, status code 0x90.
I checked some other posts on this board, and they suggested the problem might be that the "VIA" protocol was enabled. I checked for this protocol in the Configuration Manager, and it is DISABLED in both the SQL Server 2005 Network Configuration and the SQL Native Client Configuration. What else could be causing this error?
The errors do not occur when I add the service account back to the "SQLServer2005MSSQLUser" Windows Group. The SQL Server service starts successfully when the account is back in this group.
Here are my answers to the questions posted at the top of this board:
What is the MS SQL version? - SQL Server 2005 SP2 (9.00.3054.00)
What is the SKU of MS SQL? - Enterprise Edition (SKU ID: 1804890536)
What is the SQL Server Protocol enabled? - TCPIP, Named Pipes
Does the server start successfully? - NO
If not what is the error messages in the SQL server ERRORLOG? - See above.
If SQL Server is a named instance, is the SQL browser enabled? - YES
What is the account that the SQL Server is running under? - Domain Account
Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? Not applicable, Windows Firewall is not used
Do you make firewall exception for SQL Browser UDP port 1434? Not Applicable, Windows Firewall is not used
Am trying to run SQL Server Agent with a service account which is not in the Administrators group. Have done the following - 1. Removed the service account from the Administrators group on the machine 2. Assigned sysadmin privileges to the service account 3. Added it to the SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER role 4. Through SQL Configuration Manager assigned this account to the SQL Server Agent service However, this does not start the Agent as a service. What is it that is missing?
I am trying to write a report that includes different lab values for an account number depending on the test. What I mean is if patient xyz had lab work and procedure number 1012 was ordered I need to include one line for the highest result value and one for the lowest result value. If I have procedure number 1032 I only need a line for the lowest value. I have a list of about 40 lab procedures that some require both highest and lowest, some just the lowest and some the highest. I have played around with CASE, but that hasn't worked for me.
I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable.i need to check whether count > 0 or not in stored procedure.I have used below query.It is not working.
SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0 IF (@_Stat_Count >0) BEGIN SELECT @Status = status_cd from status-table where status_id = 10 END
Setting up a test AlwaysOn Availability Group for one database.
However, whenever I restore the database to the replica server and join it, it ends up with my user account as the owner of the database.
Obviously I do not want a user account as the database owner, but since it is read-only I cannot modify it directly. If I were able to fail the AG over to the replica, I could change the owner then, but I cannot due to business requirements. this AG is to essentially serve as a replacement to log shipping.
I tried doing the backups and restores using EXECUTE AS login = 'sa', and yet it still shows up as my user account.
During install of SQL Server 2005, we can of course use a domain account or the built-in system account for running the services. I lean toward domain for obvious reaons but would like to know a +/- to each option and why I'd choose one over the other and what consequences or limitations one may encounter if I choose one over the other.
We have a medium sized database with the next tables:
- PA: 525000 records - PR: 780000 records - R: 1000 records - B: 45 records
PA: PK = PAARDCODE
PR: PK = PAARDREGISTERCODE PR: FK = PAARDCODE PR: FK = REGISTERCODE
R: PK = REGISTERCODE R: FK = BOEKCODE
B: PK = BOEKCODE
When I group by B.BOEKCODE the query lasts: 10 (or more when 'where' option is added) seconds When I group by R.BOEKCODE the query lasts less than 2 seconds.
SELECT B.BOEKOMSCHRIJVING, B.BOEKCODE -- or R.BOEKCODE FROM PA INNER JOIN PR ON PA.PAARDCODE = PR.PAARDCODE INNER JOIN R ON R.REGISTERCODE = PR.REGISTERCODE INNER JOIN B ON R.BOEKCODE = B.BOEKCODE GROUP BY BOEKOMSCHRIJVING, B.BOEKCODE -- or R.BOEKCODE ORDER BY BOEKOMSCHRIJVING Why is the option of B.BOEKCODE a lot slower compared to R.BOEKCODE?
Table Clients is master table with all records, joining against Assets table that may or may not have a matching entry.
Trying to sum an asset type against table and no matter what kind of join I do I cannot get SQL to return a NULL match against the Clients ID value. All I get back are matching rows.
Here's the SQL:
select c.cindex,SUM(a.value) AS 'Total Assets' from Clients c "the join" Assets2012 a on a.clientid=c.cindex where (c.ClientClass<=7 AND c.ClientClass<>6) AND a.assettype = 2 group by c.cindex
But no matter what type of join I do, left, right, left outer, inner, I am not getting back NULL values for client records that have no matching asset records.
Strange thing is, by removing the "and assettype=2" part I get the whole database back, with NULL's but not the range I'm looking for.
I have a table with a text field and a grouping number.
1, A 1, B 1, C 1, D 1, E
2, A 2, C 2, D 2, E
3, A 3, B 3, C 3, F
4, A 4, B 4, D 4, F 4, G
I assemble the text fields into strings by group, using the FOR XML PATH('') function, so that I end up with
1, A-B-C-D-E 2, A-C-D-E 3, A-B-C-F 4, A-B-D-F-G
This all works fine. I now have a requirement to leave out some groups, based on whether the GROUP does or does not contain a certain text. For instance, if I want only GROUPS with 'E', my result set should be
1, A-B-C-D-E 2, A-C-D-E
If I want only those GROUPS that DO NOT have 'E', my result set should be
3, A-B-C-F 4, A-B-D-F-G
I can (and have) put a condition on the assembled string, but it seems to me that it should be possible to make the cut earlier. That is, rather than assembling the string, scanning it with a Like '%E%' condition and discarding what doesn't meet my needs, I would like to stop scanning the incoming GROUP as soon as a mismatch is detected.
If I want all groups that will not contain an 'A', for instance, it is pointless for the query engine to read in 'A', 'B', 'C', 'D', 'E', put them all together into a string, compare it with a wildcard and throw it away. As soon as it sees the 'A', it has enough information to completely skip over the rest of that group. The abbreviated examples here are trivial, but the real code is not.
All I've been able to dream up so far results in skipping the RECORD that contains the 'A', but not the entire group.
Let's assume that I input a gap value of 5. I need to fill in the data between the Record 1 and Record 2 by increment of 5 as specified in the input parameter on the counter column.
For example using Record 1 and Record 2, here are the additional data needs to be inserted into the table.
1234510.1 --> Record 1 1235010.1 1235510.1 1236010.1 1236510.1 1237010.5 --> Record 2 1237510.5 1238010.5 1238510.5 123909.7 --> Record 3
Currently, I am using a cursor to read from the table and select MIN counter from the table. Then use a LOOP to fill in the gap and insert it into another table. I have over 10000 records and after fill up the gap, I might end up with even more records. Just want to see if I can get any other efficient way to achieve this.
why I want to fill in the gap, I need to calculate the average value for my record set after considering all valid data points in between.
SQL Server (TSQL) how to divide a number by either 2,3,4, or 6 and spreading it across that number of rows depending on what we are dividing the number by where it comes back to the original number...That is confusing I know so let me break it down...
Bill for 143.23 that will be paid out through 2 months...When you divide that by 2, you come back with 71.62, but if you multiply that number by 2, you come back with 143.24, not the amount for the bill...the end result has to be 71.62 for month1 and 71.61 for month2...Basically when there is a remainder, that has to be applied to the first month...
143.23: Month1 = 71.62 Month2 = 71.61
Another example...Same amount but have to divide by 6
I have a pretty complex query that returns three records. For simplicity sake, the results can be simulated with this query:
Select 5 AS InternalAuditTeamEmployeeID, 1 as InternalAuditTeamID UNION ALL Select 11, 2 UNION ALL Select 14, 3;
I want to take this result and update the Flag field to true in my table tblInternalAuditTeamEmployee (CREATE statement below) for any InternalAuditTeamEmployeeID that is less than or equal to the ones in the results above, but by group. My results would look something like this using the data below and the results above.
I was thinking I could somehow use ROW_NUMBER(PARTITION BY InternalAuditTeamID ORDER BY InternalAuditTeamEmployeeID DESC), but not sure how to get the results of "WHERE <= InternalAuditTeamEmployeeID For each particular group".
CREATE TABLE STATEMENT:
CREATE TABLE [tblInternalAuditTeamEmployee]( [InternalAuditTeamEmployeeID] [int] IDENTITY(1,1) NOT NULL, [InternalAuditTeamID] [int] NOT NULL, [EmployeeID] [int] NOT NULL,
CREATE TABLE #Turnover ( location varchar(50), Total int )
insert into #Turnover (location,Total) values('A', 500) insert into #Turnover (location,Total) values('AB', 200) insert into #Turnover (location,Total) values('ABC', 100) insert into #Turnover (location,Total) values('BA', 100) insert into #Turnover (location,Total) values('BAC', 500) insert into #Turnover (location,Total) values('BAM', 100)
Now i want output order by total but same time i want to create two groups. i.e. location starting with A and order by total and after locations starting with B and order by total.
I've got a fairly standard query that does a group by a type column, and then sums the lengths of a VARCHAR column. I'd like to add into that a concatenated version of the string always concatenating in primary key order. Is that possible?
I need creating date of birth using ID number the ouput that im looking is a follows
e.g. RSA ID: 800101 (80 is year, 01 is month and 01 is day) that will be 1980 01 01 e.g. RSA ID: 000101 (00 is year, 01 is month and 01 is day) that will be 2000 01 01
The desired format I need is to take the above and create date of birth with the below format as required by the application used.
