I have four tables (all inner joined) and currently they give me the results i need. However, my boss has now asked me to return all associated accounts as well.
I am currently pulling data from the four tables to make up my results table, and the returned results are based on the loan types in my loans tables having a loan type of '1A'
So if the loan type is 1A I get a result.
However, Mr Smith (for example) may have three loans but only one of them is type '1A'. The other two might be type '5H' and '2'.
What I need to be able to do is return all the associated accounts of any customer that has a type '1A' loan.
This is my code:
Select c.customernumber, l.accountsuffix, c.forename, c.surname, lt.code, l.balance, j.journeynumber from customers c inner join loanagreements l on c.customerid = l.customerid inner join loantypes lt on l.loantypeid = lt.loantypeid inner join journeys j on c.journeyid = j.journeyid Where j.journeynumber = 93 and lt.code = '1a' and l.balance >0
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance, Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
I've searched high and low for info on how to do this... If anyone has an idea, I'd really appreciate it.
I have three tables: PEOPLE, PROJECTS, COMMENTS. I want users from the PEOPLE table to retrieve a list of PROJECTS and be able to add/edit COMMENTS on those projects.
The tables look like:
PEOPLE people_id (primary key) first_name last_name
I'd like to be able to output something like what I have below, but I don't know how to loop over the comments/people within the select statement for the projects.
DESIRED OUTPUT
Project #1 Comment #1 by Person #1 Comment #2 by Person #3 Comment #3 by Person #8
Project #2 Comment #1 by Person #2 Comment #2 by Person #3 Comment #3 by Person #6
Etc...
I've done it before by just listing the projects and then providing a detail page with all the comments, but it's much less confusing to access all the comments from the same page, grouped by project.
I have a query which returns all parts and labour lines for a particular work order. It returns all parts lines seperately, but the labour lines are repeated for each row. What I want to accomplish for a given work order, is a list of all the parts lines, followed underneath by a list of all labour lines.This is the code from the report:
select h.worknumber, --- Select parts lines and charges wp.description as [charges desc], case when wp.charge_to_cust = 1 then wp.sale_price
[code]...
For this example what I'd like to see is 5 lines here - the labour description and charge under charges description, unit price, qty and est_parts_sale etc, and of course, there could be more than 1 labour line.
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,* FROM giftregistryitems gi LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id WHERE gi.registryid=2
How can I achieve what I need?
Here are my table definitions and data:
/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[giftregistryitems]( [id] [int] IDENTITY(1,1) NOT NULL,
I'm reporting from a Microsoft SQL database (poorly documented unfortunately) and would like to find a 3rd party application to assist me in rapidly making Select queries. The ability to browse data in a field from the interface would be a plus.
What are the best alternatives for rapidly creating these queries from some sort of builder or wizard?
Can someone help me with an advanced query?I have two tablesTable 1: EmployeesCols: UID, lname, fnameTable 2: StatEntryCols: UID, Timestamp, descriptionThe queary should reaturn all the information in both tables. If morethan one entry exists in the second table, it should return the onewith the greatest timestamp. If not entries exist I would like thesecond table columns set to "no value"Something link:select Employees.*,StatEntry.* from Employees JOIN StatEntry ONemployees.uid == statentry.uid WHERE timestamp in (selectMAX(timestamp) from statentry where uid=employees.uid).Anyone db guru's out there?-SJ
Hi,I have problem about writing a proper SELECT query for the followinggoal:Table name: peoplelistcolumn 1: id (not NULL, auto_incremental)column 2: namecolumn 3: countrynow, there are about 7,000 rows in this table. I want to select out:first 10 or less people in the table for each country.for example: suppose there are :1000 people from US3000 people from UK3000 people from CanadaI want to list totally 30 people, i.e. 10 people from each country.The problem is , the actual table includes many countries, not onlythree. How can I do this by a SELECT sql query ?Thanks.Han
Hi all I im trying to write a SELECT query to display a set of my logged in user's 'Friends'. Although the way that i have designed my tables means that its very complex, and im hoping someone out there can tackle it! To start ill show you how i contruct friends: Friends FriendshipID Incrementing PKInviteeID Unique UserID of person who offered the friendship linkInvitedID Unique UserID of person who was invitesApprovedBInvitee True/False - sets to 'True' by default (probably isnt needed come to think of it )ApprovedByInvited True/False/Declined - an nvarchar Next, I have my UserDetails table: UserDetails UserID Unique UserID PKUserName Unique Username (foreign key from aspnet_Users as created by aspnet_regsql.exe)Avatar Integer which represents an image name in a photos folder So, on the myFriends.aspx i firstly set an invisible label's text property to the unique UserID of the logged in user. This gives me a control paremater for the select statement. The information I want to display is just the UserName and Avatar of all users who are friends with the logged in user. I know that to get the records where the logged in user is either that Invited or the Invitee, I do this:WHERE (@loggedInUser = Friends.IniteeID) OR (@loggedInUser = Friends.InvitedID) (that will show the logged in user as his own friend but i dont mind that) After that I am stuck more or less... it seems to become very complex... maybe i need 2 queries? If anyone can help i would be very very grateful
Hi All, My sql is a little rusty, i ve been trying to do few things but still no luck. I m trying to query some data in one column based on certain . Here is my puzzle: I have 7 tables: categories, characteristics, configs, rm_cat, rm_chars, rm_conf and rooms. And here are the details on these tables: - categories: {cat_id, cat_name} - characteristics: {char_id, char} - configs: {conf_id, conf} - rm_cat: {room_id, cat_id} - rm_chars: { room_id, char_id} - rm_conf: {room_id, conf_id} - room: {room_id. room_name}
I m trying to select a "room_name" based on a certain cat_id, char_id and conf_id and i don't know how to do this.
Hi. I have two tables. The first is a price proposal table which contains fields like the index of the proposal, proposal decription, proposal date etc. The second table contains data about the products associated with a certain price proposal. This table has a proposal index field and other fields like the index of the product (a unique identifier), product catalog number, price of the product etc. I want the rows which returned by my select query to display the following: The Proposal index, description, date, product catalog number grouping, the number of products with a certain catalog number in this proposal, the sum of the prices of these products. Example: PriceProposalTable : ProposalIndex = 1 -----> ProductsTable: ProposalIndex = 1, ProductIndex = 1, CatalogNumber=A, Price=100 ProposalIndex = 1, ProductIndex = 2, CatalogNumber=A, Price=90 ProposalIndex = 1, ProductIndex = 3, CatalogNumber=B, Price=80
There are 3 tables, VendorLists, Vendors, and Referrals.
VendorLists is a linking table. It has VendorListID, VendorID, and ListID fields.
Vendors is linked to VendorLists through the VendorID field (one to many)
Referrals is linked to VendorLists through VendorListID (one to many)
I'm given a value for ListID and have to pull records from both the Vendors and Referrals table (a referral is a description of a vendor, one to many).
I am able to do this with the following SQL select statement:
SELECT Referrals.Description, Vendors.Company FROM Referrals CROSS JOIN Vendors WHERE Referrals.VendorListID IN (SELECT VendorListID FROM VendorLists WHERE (ListID = lid)) AND (Vendors.VendorID IN (SELECT VendorID FROM VendorLists WHERE ListID = lid)) ORDER BY Vendors.VendorID
This pulls all the appropriate records and values that i need and orders them by the identifier for the vendor. However, I want to randomly order the vendors but still group them together by company, so, if the VendorID is 1 for "joe's crab shack" and 2 for "billy's ice cream shop", the above will always list joe's crab shack first and all it's referrals. i want to be able to randomly order the vendors, but still keep the referrals of those vendors grouped together so that when i iterate over them, they're grouped.
Does anyone have any idea how to do this? I'm stumped!!
Ok, I have a table with two columns in the group by clause, ServerID and Database ID. I have 9 unique ServierIDs and 117 unique DatabaseIDs. I need a query that says gimme the 5 highest database id hitcount totals for each server id. I would like to do this without a #temp table or a cursor. here's what I have:
------------------------------------------------------------------- create table #server_hitcount_summary( SID int not null, DBID int not null, ORD int not null, SUM_HITCOUNT bigint not null, SUM_MAXWRITES bigint not null, SUM_MAXREADS bigint not null, SUM_MAX_DURATION bigint not null, SUM_MAX_CPU bigint not null )
declare @id int set @id = 1 while (@id < 20) begin insert #server_hitcount_summary select top (@top) SID, DBID, ROW_NUMBER() over (order by sum(hitcount) desc) as ORD, sum(hitcount) as SUM_HITCOUNT, sum(MAX_WRITES) as SUM_MAXWRITES, sum(MAX_READS) as SUM_MAXREADS, sum(MAX_DURATION) as SUM_MAX_DURATION, max(max_cpu) as MAX_MAX_CPU from hitcounts_table group by SID, DBID having SID = @id and DBID <> 1 order by sum_HITCOUNT desc set @id = @id + 1 end select SID, DBID, ORD, SUM_HITCOUNT, SUM_MAXWRITES, SUM_MAXREADS , SUM_MAX_DURATION, SUM_MAX_CPU from #server_hitcount_summary order by SID asc drop table #server_hitcount_summary -------------------------------------------------------------------
What d'ya think? is there a better way to do this?
-The Universe is naturally cold. It takes the power of a star to change that.
Here is my SQL string:"SELECT to_ordnum, to_orddate," _& "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) *(DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ONDDPROD.pr_prodnum = DOBOM2.b2_prodnum INNER JOIN DDORD ONDOBOM2.b2_orid = DDORD.or_id INNER JOIN DDTORD ON DDORD.OR_TOID =DDTORD.TO_ID WHERE DOBOM2.b2_ordnum = ''order number here from resultof outer select) AS Total" _& "FROM DDTORD WHERE to_trak2id IN (39, 40, 41) AND to_ordtype = 's'AND to_status = 'c' GROUP BY to_ordnum, to_orddate ORDER BY to_ordnumDESC"The outter Select statement returns various amounts of order numbersrepresented by 'to_ordnum' in the outer Select clause which has tomeet the critera in the outer WHERE clause. I would like to placethese numbers selected into the inner WHERE clause for the innerselect statement where DOMBOM2.b2_ordnum = ?the order selected byouter select statement.I have tried placing to_ordnum into that location but the SQL2000server does not process it.Any suggestions, ideas?Thank you,Brett
I have a pretty complex SQL statement that looks like this: SELECT aspnet_Employers.active, aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title AS Contact, SUM(aspnet_Employers.EmployeeCount) AS [# Emps], COUNT(aspnet_Signups.account) AS [# Email Addresses], COUNT(aspnet_ContactMe.username) AS [# Contact Me], COUNT(aspnet_AppsSubmitted.account) AS [# Apply Now]FROM aspnet_Employers LEFT OUTER JOIN aspnet_AppsSubmitted ON aspnet_Employers.UserName = aspnet_AppsSubmitted.account LEFT OUTER JOIN aspnet_ContactMe ON aspnet_Employers.UserName = aspnet_ContactMe.username LEFT OUTER JOIN aspnet_Signups ON aspnet_Employers.UserName = aspnet_Signups.accountGROUP BY aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title, aspnet_Employers.active It does work the way i want it, but the problem is, on my Gridview when i change the Employers accounts "Active" status either way, it changes the username field from the username of the account, to "null". Why does it do this? What would i change to prevent this from happening?
I may not be seeing the forest through the trees here but here goes. I've got a table of computer configurations with columns for cpu, ram, m/b, hdd, etc. The values in those columns are related to the id field in another table named parts. The parts table has columns, id, partnumber, description, and cost. What I want to do is be able to pull a record from the computer configurations table and instead of getting the integers in the cpu, ram, etc. fields I want to put the corresponding description field from the parts table. For example:I want this...id Name CPU RAM MB HDD ... 1 Fast Machine Fast CPU Big RAM Greate MB Huge HDDNOT this....id Name CPU RAM MB HDD ... 1 Fast Machine 1 3 2 7 Below is a screenshot of my actual table relationships.Thanks in advance
hi! i have two tables: Pictures (PictureID, UserName, UserID, DateAdded, Comments int) and UserVisits (VisitID, UserName, UserID, PictureID, NewComment bit) if a new comment is added on a picture with PictureID = 4, i update NewComment from UserVisits with 1 (true) where PictureID = 4 to all rows, and if a user visits a picture with PictureID = 5, NewComment will be set to 0 back where pictureID = 5 (something like asp.net, when a reply is added to a therad) i need to make a stored procedure with UserName input parameter witch selects ALL pictures from Pictures table and selects NewComment (true or false) from UserVisits witch corresponds with that UserName !!! Is possible that in UserVisits to not have yet any column where UserName corresponds with the user who visits if the user did not visited that picture yet (this stored procedure will be used on a Repeater witch lists all the pictures) if i was not clear please tell me, sorry i am verry bad at T-SQL language thanks in advance
This is difficult to explain in words, but the following code outlines what I am trying to do:
with myTableWithRowNum as ( select 'row' = row_number() over (order by insertdate desc), myValue from ( select table1Id As myValue from myTable1 union select table2Id As myValue from myTable2 ) )
select * from myTableWithRowNum Can anyone think of a work around so that I can use the Row_Number function where the data is coming from a union?
I have a very confusing/complicated query that I am trying to run and getting not the results that i want.
Essentially I have three tables (t1, t2, t3) and I want to select data from two of them, but there are conditions on the data where I need fields to match across pairs of tables. When I run my select query I am getting far too many records - it's giving me all possible combinations, instead of the proper combinations that I want.
Select t1.*, t3.field2, t3.field3 FROM, t1, t2, t3WHERE t2.field4=t3.field4 AND t1.field5=x AND t1.field6=t2.field6
I suspect there is plenty wrong with this query - where should I start correcting it?
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
I have a table with a composite key (two primary keys) that is a foreign key to another table. I need to select all records in the primary key table where there are no matches in the foreign key table... I have no idea how to do this..
Here is how to join the tables:
Select A.Key1, A.Key2 From PrimaryKeyTable A, ForiegnKeyTable B Where A.Key1 = B.Key1 and A.Key2 = B.Key2
I need to query all records that do not join, so basically the inverse of this query. How do I do this? Please let me know if this is in the wrong forum or you need any clarifcation.
Just FYI, on a single Primary key column I would just simply do the following:
Select A.Key From PrimaryTable A, ForiegnTable B Where A.Key not in ( Select A.Key From PrimaryTable A, ForignTable B Where A.Key = B.Key)
with cte as ( select DataID, Name, SubType, FileType, MimeType, VersionID, Version
from dtree A1, dversdata A2 where A1.dataid=A2.Docid And A1.Subtype='144' AND A2.mimetype='application/news-message-id' update cte set MimeType = 'application/x-outlook-msg', Subtype=749
what I want to do is to update two columns. Both are from different tables and I get an error..
I have a table with almost a million rows, although it's quite slim with just ID, date, userID, JobID etc.
Now I want to the ability to add comments to some (probably less than 1%) of those lines.
The question is whether to create a separate comments table to join to it, or to create a comments field within the existing table? The comments field would obviously default to NULL, so wouldn't bloat the table unnecessarily if I add that field (right?), and would always be selected with the row from that table, so I'm leaning towards the latter alternative.
I posted Wednesday thinking a SELECT Distinct would solve my problem but it didn't. I have a stored procedure that is used to grab data from 4 tables that I need to join. The 1st table (Application) holds a job applicant's name and some other data The 2nd table (Jobs) holds the Job name and test type The 3rd table (Locations) holds the locations Then there is a foreign key many to many table (Application_Locations) that holds the applicants UserID and a LocationID. This table may have multiple rows for the same applicant with different locations in each row.
When the procedure is ran I want all the data that I am requesting from the Application table, and all the data that I am requesting from the Jobs table but only the 1st returned result of the Join on the Locations and Application_Locations table. What do I need to do to correct this so that I only display 1 row for each UserID no matter how many locations thay may have applied to. (You will notice that there are some IF statements so only the 2nd and 4th queries in the sproc are the ones that apply )
Here is the SPROC that is currently in place but is displaying a row for each location.
IF @JobID <> 9999 BEGIN IF @LocationID <> 9999 BEGIN SELECT A.UserID, A.Completed, A.FolderID, A.AppDateTimeStart, A.ResumeFileName, A.FirstName, A.LastName, A.PrescreenScore, A.JobID, A.ViewPre, A.ViewApp, A.ViewReport, A.ViewResume, J.JobTitle, J.TestType, L.BranchAbbreviation, AL.LocationID FROM Locations L INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID INNER JOIN Application A ON AL.UserID = A.UserID INNER JOIN Jobs J ON J.JobID = A.JobID WHERE AL.LocationID= @LocationID AND A.FolderID= @FolderID AND A.JobID = @JobID ORDER BY CASE WHEN @SortOrder = '4' THEN A.AppDateTimeStart END DESC, CASE WHEN @SortOrder = '6' THEN A.PreScreenScore END DESC, CASE WHEN @SortOrder = '2' THEN A.LastName END DESC, CASE WHEN @SortOrder = '5' THEN A.PreScreenScore END ASC, CASE WHEN @SortOrder = '3' THEN A.AppDateTimeStart END ASC, CASE WHEN @SortOrder = '1' THEN A.LastName END ASC END
ELSE BEGIN SELECT A.UserID, A.Completed, A.FolderID, A.AppDateTimeStart, A.ResumeFileName, A.FirstName, A.LastName, A.PrescreenScore, A.JobID, A.ViewPre, A.ViewApp, A.ViewReport, A.ViewResume, J.JobTitle, J.TestType, L.BranchAbbreviation, AL.LocationID FROM Locations L INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID INNER JOIN Application A ON AL.UserID = A.UserID INNER JOIN Jobs J ON J.JobID = A.JobID WHERE A.FolderID= @FolderID AND A.JobID = @JobID ORDER BY CASE WHEN @SortOrder = '4' THEN A.AppDateTimeStart END DESC, CASE WHEN @SortOrder = '6' THEN A.PreScreenScore END DESC, CASE WHEN @SortOrder = '2' THEN A.LastName END DESC, CASE WHEN @SortOrder = '5' THEN A.PreScreenScore END ASC, CASE WHEN @SortOrder = '3' THEN A.AppDateTimeStart END ASC, CASE WHEN @SortOrder = '1' THEN A.LastName END ASC END END
ELSE BEGIN IF @LocationID <> 9999 BEGIN SELECT A.UserID, A.Completed, A.FolderID, A.AppDateTimeStart, A.ResumeFileName, A.FirstName, A.LastName, A.PrescreenScore, A.JobID, A.ViewPre, A.ViewApp, A.ViewReport, A.ViewResume, J.JobTitle, J.TestType, L.BranchAbbreviation, AL.LocationID FROM Locations L INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID INNER JOIN Application A ON AL.UserID = A.UserID INNER JOIN Jobs J ON J.JobID = A.JobID WHERE AL.LocationID= @LocationID AND A.FolderID= @FolderID ORDER BY CASE WHEN @SortOrder = '4' THEN A.AppDateTimeStart END DESC, CASE WHEN @SortOrder = '6' THEN A.PreScreenScore END DESC, CASE WHEN @SortOrder = '2' THEN A.LastName END DESC, CASE WHEN @SortOrder = '5' THEN A.PreScreenScore END ASC, CASE WHEN @SortOrder = '3' THEN A.AppDateTimeStart END ASC, CASE WHEN @SortOrder = '1' THEN A.LastName END ASC END
ELSE BEGIN SELECT A.UserID, A.Completed, A.FolderID, A.AppDateTimeStart, A.ResumeFileName, A.FirstName, A.LastName, A.PrescreenScore, A.JobID, A.ViewPre, A.ViewApp, A.ViewReport, A.ViewResume, J.JobTitle, J.TestType, L.BranchAbbreviation, AL.LocationID FROM Locations L INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID INNER JOIN Application A ON AL.UserID = A.UserID INNER JOIN Jobs J ON J.JobID = A.JobID WHERE A.FolderID= @FolderID ORDER BY CASE WHEN @SortOrder = '4' THEN A.AppDateTimeStart END DESC, CASE WHEN @SortOrder = '6' THEN A.PreScreenScore END DESC, CASE WHEN @SortOrder = '2' THEN A.LastName END DESC, CASE WHEN @SortOrder = '5' THEN A.PreScreenScore END ASC, CASE WHEN @SortOrder = '3' THEN A.AppDateTimeStart END ASC, CASE WHEN @SortOrder = '1' THEN A.LastName END ASC END END GO
Hi all,Currently our product has a setup that stores information abouttransactions in a transaction table. Additionally, certain transactionspertain to specific people, and extra information is stored in anothertable. So for good or ill, things look like this right now:create table TransactionHistory (TrnID int identity (1,1),TrnDT datetime,--other information about a basic transaction goes here.--All transactions have this infoPrimary Key Clustered (TrnID))Create Index TrnDTIndex on TransactionHistory(TrnDT)create table PersonTransactionHistory (TrnID int,PersonID int,--extended data pertaining only to "person" transactions goes--here. only Person transactions have thisPrimary Key Clustered(TrnID),Foreign Key (TrnID) references TransactionHistory (TrnID))Create Index TrnPersonIDIndex on PersonTransactionHistory(Person)A query about a group of people over a certain date range might fetchinformation like so:select * from TransactionHistory THinner join PersonTransactionHistory PTHon TH.TrnID = PTH.TrnIDwhere PTH.PersonID in some criteriaand TH.TrnDT between some date and some dateIn my experience, this poses a real problem when trying to run queriesthat uses both date and personID criteria. If my guesses are correct thisis because SQL is forced to do one of two things:1 - Use TrnPersonIDIndex to find all transactions which match the personcriteria, then for each do a lookup in the PersonTransactionHistory tofetch the TrnID and subsequently do a lookup of the TrnID in the clusteredindex of the TransactionHistory Table, and finally determine if a giventransaction also matches the date time criteria.2 - Use TrnDTIndex to final all transaction matching the date criteria,and then perform lookups similar to the above, except for personID insteadof datetime.Compounding this is my suspicion (based on performance comparison of whenI specify which indexes to use in the query vs when I let SQL Serverdecide itself) that SQL sometimes chooses a very non optimal course. (Ofcourse, sometimes it chooses a better course than me - the point is I wantit to always be able to pick a good enough course such that I don't haveto bother specifying). Perhaps the table layout is making it difficult forSQL Server to find a good query plan in all cases.Basically I'm trying to determine ways to improve our table design here tomake reporting easier, as this gets painful when running report forlarge groups of people during large date ranges. I see a few options basedon my above hypothesis, and am looking for comments and/or corrections.1 - Add the TrnDT column to the PersonTransactionHistory Table aswell. Then create a foreign key relationship of PersonTransactionHistory(TrnID, TrnDT) references TransactionHistory (TrnID, TrnDT) and createindexes on PersonTransactionHistory with (TrnDT, PersonID) and(PersonID, TrnDT). This seems like it would let SQL Server makemuch more efficient execution plans. However, I am unsure if SQL servercan leverage the FK on TrnDT to use those new indexes if I give it a querylike:select * from TransactionHistory THinner join PersonTransactionHistory PTHon TH.TrnID = PTH.TrnIDwhere PTH.PersonID in some criteriaand TH.TrnDT between some date and some dateThe trick being that SQL server would know that it can use PTH.TrnDT andTH.TrnDT interchangably because of the foreign key (this would support allthe preexisting existing queries that explicitly named TH.TrnDT - any thatdidn't explicitly specify the table would now have ambigious columnnames...)2 - Just coalesce the two tables into one. The original intent was to savespace by not requiring extra columns about Persons for all rows, many ofwhich did not have anything to do with a particular person (for instance acontact point going active). In my experience with our product, the enduser's decisions about archiving and purging have a much bigger impactthan this, so in my opinion efficient querying is more important thanspace. However I'm not sure if this is an elegant solution either. It alsomight require more changes to existing code, although the use of viewsmight help.We also run reports based on other criteria (columns I replaced withcomments above) but none of them are as problematic as the situationabove. However, it seems that if I can understand the best way to solvethis, I will be able to leverage that approach if other types of reportsbecome problematic.Any opinions would be greatly appreciated. Also any references to goodsources regarding table and index design would be helpful as well (onlineor offline references...)thanks,Dave
In our frustration we dropped database "Search" completely as restoring it from backup was giving us other authorization errors. Fortunately we could rebuild the searches on the fly.
So now our service broker activation procedure tells us:
04/03/2008 17:49:51,spid55s,Unknown,The activated proc [dbo].[MessageProcessor_NameChange] running on queue AMG_Search.dbo.NameChangeQueue output the following: 'The database owner SID recorded in the master database differs from the database owner SID recorded in database 'Search'. You should correct this situation by resetting the owner of database 'Search' using the ALTER AUTHORIZATION statement.'
My username created the database. I am dbo. So what exactly am I supposed to change the owner to?
I try this:
ALTER AUTHORIZATION ON DATABASE::[Search] TO MySelf
Where MySelf is my username. And I am told Msg 15110, Level 16, State 1, "The proposed new database owner is already a user or aliased in the database" So what the heck we'll make SA the owner.
And that works.
I DON'T GET THIS PEOPLE. I created the database I am not allowed to own it anymore? We formatted the server. We reinstalled SQL server from scratch. We recreated all the usernames. Was our mistake restoring the old data from backup? Was I supposed to re-run the DDL and key all the data back in again? Is that the reccomended restore procedure? The users will be thrilled to hear this :-)
My days as an Oracle DBA were nothing like this. Security was just not this troublesome...
So if someone older, wiser, and with more experience would explain the above error message and how it occurs I would appreciate it, because something tells me I'm going to be stuck in this place again...
