Any Help With Returning The Last Instance Of A Multiple Version Record In Joined Tables?
Sep 28, 2007
We have an archive table which keeps each instance of a sales order that was archived under a "Verion No" field. Each time the sales order is archived it is entered into the archive tables (Sales Header Archive, Sales Line Archive). What I am trying to do is write a query to return all sales orders but only the most recent archived version.
For example this table layout is similar to what I am working with. Version No, Order No and Customer No. are the keys between the Header and Line tables, Customer Name column in the output is from only the Sales Header Archive table
SALES LINE ARCHIVE TABLE
Version No - Order No. - Customer No -----> (other columns)
1 s-5 1000
RESULTS OF JOINED TABLES
Version No - Order No - Customer No - Customer Name ---> (other columns)
2 s-5 1000 Something, Inc.
1 s-6 2000 Acme
3 s-7 3000 Company, LLC
1 s-8 4000 Blah & Associates
2 s-9 2000 Acme
It should return the last Version No of each Sales order.
Does that make sense? It is something probably easy... But, I've spent two days using multiples and multiples of different ways, that just aren't working: I'm about to dropkick my server cabinet...
Table A has columns CompressedProduct, Tool, Operation
Table B in a differnt database has columns ID, Product, Tool Operation
I cannot edit table A. I can select records from A and insert into B. And I can select only the records that are in both tables.
But I want to be able to select any records that are in table A but not in Table B.
ie. I want to select records from A where the combination of Product, Tool and Operaton does not appear in Table B, even if all 3 on their own do appear.
This code return all the records from A. I need to filter out the records found in Table B.
SELECT ID, CompressedProduct, oq.Tool, oq.Operation FROM OPENQUERY (Lisa_Link, 'SELECT DISTINCT CompressedProduct, Tool, Operation FROM tblToolStatus ts JOIN tblProduct p ON ts.ProductID = p.ProductID JOIN tblTool t ON ts.ToolID = t.ToolID JOIN tblOperation o ON ts.OperationID = o.OperationID WHERE ts.ToolID=66 ') oq LEFT JOIN Family f on oq.CompressedProduct = f.Product and oq.Tool = f.Tool and oq.Operation = f.Operation
Hi,In the process of localizing the 'regions' table, we added three newtables. The localized data will be stored in the TokenKeys andTokenValues tables. It would be easier if we did away with theTokeyKeys/TokenValues tables and just added a localeid in the regionstable, but this is the desired schema by the client. Here's theschema:Table: regionsid nameabbreviation1 United StatesUSTable: localesid locale1 en_US2 fr_CATable: TokenKeysid key1 db.regions.name2 db.regions.abbreviationTable: TokenValuesid keyid valuelocaleid1 1 Etas Unis22 2 EU2The old sql was simply this:select name, abbreviation from regionswhich returns:United States, USBut the new sql needs to link in the localized data from the tokeykeysand tokenvalues tables using the localeid... I’m trying to figure outwhat the sql statement would look like to return this:Etats Unis, EU (This is supposed to be the French version)My confusion is we are trying to return multiple column values fromthe same column (TokenValues.value) and make them act as separatecolumns in the same record, like it was with the original.Thanks
I have two tables and I want to return data from both. Currently my select statement is returning just 1 child record for each parent record and I want to return all child records that match the parent record.
We currently have an application whereby the user will come along sign up add some information about them, choose what interests / hobbies they're in to and then click save.After completing the sigh up they're able to search for other individuals within locations (look at this functionality as a dating site) We currently have a stored procedure whereby when the user clicks Advance Search they fill in all the fields they filled in when signing up and when they click search we return all users that match the data the user has selected. This part is working correctly.
The issue im having is extending this stored procedure to take a user defined table type which has two columns UserId and ActivityId both columns are nullable.This user defined table type is referred to in two places, when the user signs up and when they do an advance search.When the user does an advance search he / she can choose a list of hobbies / interests this will then be passed in to the database and I will join on my User_Activities table to find the users that have the Activities passed in.When I join on this table I get duplicate records purely because one user may have 5 - 10 activities depending on how active the individual is. My current stored procedure looks like this
@Ambition int = null, @Body int = null, @Diet int = null, @Drinking int = null, @Ethnicity int = null, @Exercise int = null, @HeightFrom int = null,
[code]...
So my question is how can I select all users that match the criteria passed in, as well as finding the users that match the activities passed in (the user can either match all of them or a minimum of 1). Also my User_Activities has two columns UserId and ActivityId identical to the user defined table type
OrderID ControlName 1 Row1COlumn1 (It Means Pant in Red Color is selected by user(relation with Child2 Table)) 1 Row3Column1 (It Means Gown in Blue Color is selected by user(relation with Child2 Table)) 1 Row4Column3 (It Means T Shirt in White Color is selected by user(relation with Child2 Table)) 2 Row1Column2 (It Means Tie in Green Color is selected by user(relation with Child2 Table)) 2 Row3Column1 (It Means Bow in Red Color is selected by user(relation with Child2 Table))
Child2 Table
PackageID Product Color1 Color2 Color3 1 Pant Red Green Blue 1 Shirt Blue Pink Purple 1 Gown Blue Black Yellow 1 T Shirt Red Green White 2 Tie Red Green White 2 Socks Red Green White 2 Bow Red Green White
We want to have result like
OrderID PackageID CustomerName Pant Gown T Shirt Tie Bow
What is the best way to insert CSV records, where 1 record maps onto multiple tables with parent/child or PK/FK relationship.
eg. CSV record (ID, param1, param2, param3)
ParentTable(ID as PK) Param1Table(ID as FK in ParentTable, param1) Param2Table(ID as FK in ParentTable, param2) Param3Table(ID as FK in ParentTable, param3)
Currently i am using multiple Transform Data tasks with ActiveX script. But it seems to work slow.
Hello everyone, I have a query problem.I'll put it like this. There is a 'publishers' table, and there is a'titles' table. Publishers publish titles (of course). Now I want to make aquery (in MS SQL Server) that would return the last title published by everyof the publishers. Quite clear situation. But I can't make it work.If I use inner join (which I should, because I need data from both tables)then I get a result showing all publishers and all titles. What I want toget is all publishers, and only their last title, so I don't have more thanone line for the same publisher, and this line should contain publisherdetails and last title details.I tried using DISTINCT, but it works on a whole resultant row rather then acolumn, and since rows are all distnict (because they also contain columnsfrom titles) this didn't help me.What I can do is (in my application) first get a list of publishers, andthen loop through them selecting only the last title belonging to eachpublisher. I want to see if there is a way to accomplish the same thing withan SQL query (or maybe a stored procedure, view, or whatever). Anything ispossible, as long as it stays within SQL server and doesn't rely on theclient application.Of course, both 'publishers' and 'titles' tables have a primary key('publisherID', and 'titleID'), and 'titles' has a 'publisherID' columnwhich relates titles with publishers.Help :)
OrderID ControlName 1 Row1COlumn1 (It Means Pant in Red Color is selected by user(relation with Child2 Table)) 1 Row3Column1 (It Means Gown in Blue Color is selected by user(relation with Child2 Table)) 1 Row4Column3 (It Means T Shirt in White Color is selected by user(relation with Child2 Table)) 2 Row1Column2 (It Means Tie in Green Color is selected by user(relation with Child2 Table)) 2 Row3Column1 (It Means Bow in Red Color is selected by user(relation with Child2 Table))
Child2 Table
PackageID Product Color1 Color2 Color3 1 Pant Red Green Blue 1 Shirt Blue Pink Purple 1 Gown Blue Black Yellow 1 T Shirt Red Green White 2 Tie Red Green White 2 Socks Red Green White 2 Bow Red Green White
We want to have result like
OrderID PackageID CustomerName Pant Gown T Shirt Tie Bow
1 1 ABC Red Blue White x x Blue 2 2 Bcd x x x Green Red
I have tried
;with mycte as ( select ms.OrderID,ms.PackageID ,ms.CustomerName , Replace(stuff([ControlName], charindex('Column',ControlName),len(ControlName),''),'Row','') rowNum ,Replace(stuff([ControlName], 1, charindex('Column',ControlName)-1 ,''),'Column','') columnNum From child1 c inner join MasterTable ms on c.Orderid=ms.orderid)
[code]....
it works if we have a product in one color only. like if we have pant in red and blue then its showing just first record
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
I am using SQL Server 2005 to publish joined tables for SQL Mobile subscribers for merge replication and column level tracking.
Using Management Studio I am trying to join tables and specify row filters on the joined tables. I.E. table 1 is joined with table 2. I need to define row filters for table 1 and row filters specific to table 2.
An example would be: Table 1 is a customer table that I filter on a specific customer. Table 2 might be an orders table that I need to join to get the customers orders but I also want to filter for open orders only.
When I specify the row filter for table 2 the join appears to be ignored and I receive the complete table 2 with the row filter applied.
I have searched the online books and the web and I have not run accross an example of using both joins and row filters where the filters are specified for both joined tables.
Hi - I'm new to SSRS/SQL and have a situation I can't figure out. I've tried a number of things, but I'm not sure the best way to return a calculated column based on the structure of my tables.
I have a primary Detail table that tracks product testing results. The Detail records need to be calculated against a "Year" table that contains a set of values by year. This table contains a single record for each year. There isn't a direct key that joins these two tables.
The Detail table looks like: Record ID: 9999 PO Number: 12345 Date: 12/1/2007 Test Result: 1.52
The Year table looks like: Record ID: 1 Start Date: 10/6/2007 End Date: 6/14/2008 LowVal1: 1.0 HighVal1: 1.49 Incentive1: .05 LowVal2: 1.50 HighVal2: 1.59 Incentive2: .06 and so on...
The Detail records need to find the correct Year record based on the Detail date, find the correct value within the LowVal - HighVal range based on the Test Result value, and then multiply the Test Result by the correct Incentive value and return that value.
I was able to find the correct LowVal-HighVal range using an Expression in the report column. Now that I need multiple years, I can't figure out the best way to configure the query.
Any suggestions would be appreciated. Thanks in advance!
I have 4 tables inner joined. Two of tables have ~500,000 rows, while other 2 have ~60,000. There are 4-5 WHERE conditions for 3 tables. Is it normal that a query lasts ~13-15 seconds? I tried indexing in all ways, subselects, temp tables etc, nothing helped.
I think it is unuseful to use indexes because WHERE conditions apply not to one, but to 3 tables.
Is there anyone who is expert in this topic? Thanx B
what i need is query the tbl1 for a range of serials,get the pcb and for those pcb's query the tbl2 for data1,data2 The resultSet should be a join on the two tables, Columns {serial} from tbl1 and {pcb,date_time,data1,data2} from tbl2
Please follow my simple example: Suppose tbl1 has these 2 records tbl1 = pcb1,sn1,pass pcb2,sn2,pass pcb3,sn3,pass
where date1 is the most recent date and date6 the least recent
Request:what i want is for serial>=sn1 and serial<=sn2,get the pcbs from tbl1(which are pcb1 and pcb2) and based on these, query the tbl2 for the other data but retrieve only most recent records.
and not pcb1,date1,pass,dataX1,dataY1 pcb1,date2,pass,dataX2,dataY2 pcb1,date5,pass,dataX5,dataY5 pcb2,date3,pass,dataX3,dataY3 pcb2,date6,pass,dataX6,dataY6
What i already did is this:
select max(CONVERT(DATETIME,tbl2.date_time,103)),tbl1.serial,tbl2.pcb from tbl2 left JOIN tbl1 ON tbl2.Pcb=tbl1.pcb where tbl1.serial>='1' and tbl1.serial<='53' and tbl2."Result" like 'pass' and tbl1."result" like 'pass' group by tbl2.pcb,tbl1.serial;
This works correctly for getting serial from tbl1, date_time and pcb from tbl2.But unfortunately i also need data1 and data2 columns from tbl2. If i include them in the Select Clause i have to include them also in the group by ,and this gives me also duplicate records (by using this OR philosophy).I mean, it would give all records containing (pcb1,pcb2),much like my example
I'm managing an amature online university and I've been charged with creating a deans list. I have a table for exam results for each course.. currently totaling 5. I have an employeeID column and a total_points column in each table. Sooooo I need to join all the tables and get an average for total_points where the employeeID matches across tables. I have no idea how to write this select.. any help?
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 get a 90-120 second blocking when send 15 or so simultaneous queriesto SQL Server 2000 that query a view made up of two joined tables.After each query is blocking for the same amount of time they allreturn. Further identical queries of this type work in 3-4 seconds(caching?) until hours later where it happens again. If I query thetables directly (without the view) I still get the same blocking. If Iremove the join (it is a simple inner join on two columns) I do not getthe blocking.Any ideas?
This seems like a very simple question but i have never been able tofind an easy answer to it.I have a user table and i do a join with another table, we'll call theother table a results table.The results table has numerous rows with the userid foreign key.I want to make a query that will give me the number of rows in theresults table for each user where the result is some valueThe query is simple to make but will only show the users who have arecord in the results table the meet the where criteria, however i wantto display each user and show a record count of 0 when there are noresults in the results table that match the criteria.for example i have 2 tables.tblUsers_______________userid | username--------------------------1 | user12 | user2tblAnswers________________userid | answer----------------------------1 | 11 | 01 | 42 | 12 | 0if i run the query:select max(username), count(answer) from tblUsersleft outer join tblanswers on tblAnswers.userid = tblUSers.idwhere tblAnswers.answer = 4group by tblUsers.idi just getuser1 | 1i want to getuser1 | 1user2 | 0the only way ive found to do this is with a temp table and a curser tocreate all the users records and go back through an insert the answercount for each user. This approach seems very expensive and requires aquery that is 3 times larger than is needed for the same resultswithout including 0 count records. I know there must be a better way todo this.Any help is appreciated.
I'm working on a query for a report. I've done this before and it works, but I think it's a little slow due to the joins and I'm wondering if I'm doing this the best way.
This is from a Microsoft CRM system. I'm only using the LEAD table. There is a field on the lead table called StateCode. When a user "Qualifies" a lead, the statecode changes. The report requires a column for total leads, a column for # of leads qualified, and a column for % of leads qualified. There are other columns, but those three will illustrate the problem.
Because total leads means all statecode values are included, and Qualified leads means only one statecode value is included, I can't get those two values from the same query (that I know of). So what I do is take two queries, one for total leads, and one for qualified leads, put them in parenthesis and name them, and then join them on the name of the leadsource, like below. I often end up with 10 or 15 of these "Query Tables" in my main query. Is this the best way?
Code Block SELECT * FROM ( SELECT LeadSource , COUNT(CreatedOn) FROM Leads GROUP BY LeadSource ) as A
LEFT OUTER JOIN
( SELECT LeadSource , Count(CreatedOn) , Count(CreatedOn) / (SELECT COUNT(CreatedOn) FROM leads) AS "% of Leads Qualified from this Lead Source" FROM Leads WHERE StateCode = 2 GROUP BY LeadSource ) as B ON A.LeadSource = B.LeadSource
Create view vwOrderItemTotal2 AS SELECT ItemName, fkMenuItemID, Sum(Quantity) as [SumOfMenuITems] FROM OrderItems GROUP BY fkMenuItemId, ItemName
When I present my data in a GridView, it works fine. For example, several orders for milk are returned as a summary quantity of 26 gallons in a single row of the GridView like this:
26 Milk
Now I need to filter my data by OrderDate and Zipcode. I created this new view:
Create view vwOrderItemTotal5 AS SELECT Orders.Zipcode, Orders.OrderDate, OrderItems.ItemName, OrderItems.fkMenuItemID, Sum(Quantity) as [SumOfMenuITems] FROM Orders INNER JOIN OrderItems ON Orders.OrderID = OrderItems.fkOrderID GROUP BY fkMenuItemId, ItemName, Zipcode, OrderDate
When I present my data in a Gridview using the new view I get a GridView with multiple rows for milk where each order has its own row like this:
1 Milk 5 Milk 6 Milk 6 Milk 3 Milk 1 Milk 4 Milk
But I want the data presentation in one row for each ItemName (e.g. Milk) as with my first view. Can I adjust my new view to achieve this, or should I stick with my first view (vwOrderItemTotal2) and adjust the Select Command in my SqlDataSource (hasn’t worked yet). I think that what I want is for the returned data to be grouped by fkMenuItemId only, but the sql server admin won’t let me create a view without including the other fields in the Group By clause. Thanks for any help provided in solving this.
I've read that if particular tables are frequently queried together through a join then these tables should be placed on different devices on different physical disks. What does this mean exactly and how would you configure this? Is this a common practice in high-performance real-world environments (or should it be)?
Each row of my datagrid comes from two tables, A and B, which are (left) joined: not every row from table A has a corresponding row in table B. I think this is quite a common scenario.If I want to edit a row in my datagrid which contains data from both Table A and Table B then presumably I can just use an UPDATE statement behind the scenes.But what happens if I want to edit a particular row in the datagrid which contains data from Table A but no corresponding data from table B? I can't use an UPDATE statement because the record in Table B doesn't yet exist. So what do I do?Does anyone know the answer to this, or could you point me to a good tutorial please?
I have a query written that filters on joined table data. The SELECT looks like this:
SELECT * FROM tbl_bol AS a LEFT OUTER JOIN bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN tbl_carrier AS c ON c.carrier_id = a.carrier_id WHERE (a.carrier_name LIKE 'five%') AND (a.accrueamt = 0) AND (a.imported = 1) AND (b.description = 'tendered') AND (a.ship_date BETWEEN '9/1/13' AND '9/30/13') ORDER BY a.bol_number DESC
If I want to do an UPDATE query that uses those filters in the WHERE clause, how do I go about doing that? It doesn't look like you can used joined tables in the UPDATE line like this:
UPDATE tbl_bol AS a LEFT OUTER JOIN bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN tbl_carrier AS c ON c.carrier_id = a.carrier_id SET accrueamt='1348' WHERE (a.carrier_name LIKE 'five%') AND (a.accrueamt = 0) AND (a.imported = 1) AND (b.description = 'tendered') AND (a.ship_date BETWEEN '9/1/13' AND '9/30/13')
I joined different tables and got a result like this:
result | process | goal | date | ------- ---------- ------ ----------- ok | process4 | 1 | 12.10.2013 bad | process1 | 2 | 13.10.2013 ok | process1 | 4 | 12.12.2013 good | process4 | 1 | 03.01.2014 ok | process1 | 3 | 10.04.2013 bad | process3 | 6 | 09.01.2014 bad | process4 | 3 | 30.12.2013 best |NULL| NULL
Now I want to count the results by counting the processes and group them by the result.
But it should be count the latest result per process only, e.g. for goal "1" just "good" at 03.01.2014. I solved that with a subquery (date=SELECT MAX(...)..).
But now the result "best" disappears, because that column has no date.
Secondly I want to count results for a specific process, e.g. for process4. Every goal has max. one process, with different dates. But one process could have more than one goal.
I want to have this result for process4:
count | result ------ ------- 1 | good 1 | bad 0 | ok 0 | best
But I got only:
count | result ------ ------- 1 | good 1 | bad
I have tried a lot, but nothing works.
The whole result (best, good, ok, bad) are stored in an other table and I joined it.
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'm trying to compare two varchars to check if they are the same, if they are the same then the color must turn red, if not then they must remain black
SELECT *
from members m, client c
where C.ClientID = m.ClientID
AND c.ClientID in (87,86)
AND m.email in ('dassd@fdskjh.com','asdfas@sdfd.net', etc...)
my results will give me two of the same email addresses but with different ClientID's, now when it
finds the same email it needs to make them both "RED"
I have a script that is supposed to run thru 2 joined tables and update a field in the 3rd table. The script works but takes approx. 4 hours to run against 250k records.
UPDATE a SET Con_Mailings = STUFF((SELECT '; ' + c.ListName FROM [server].[xxxxx_MSCRM].[dbo].ListBase c with (nowait) INNER JOIN [server].[xxxxxx_MSCRM].[dbo].[ListMemberBase] b with (nowait) ON b.ListID = c.ListID WHERE b.EntityID = a.TmpContactID FOR XML PATH('')),1,1,'') FROM [xx_Temp].[dbo].[Lyris_CombinedTest] a
I should end up with something like this in the con_mailings field:
Im trying to delete duplicate records from the output of the query below, if they also meet certain conditions ie 'different address type' then I would merge the records. From the following query how do I go about achieving one and/or the other from either the output, or as an extension of the query itself?
I work for a mid size company with the usual collection of enterprise applications. We are trying to consolidate our SQL environment consisting of around 100 databases none of which are over 100GB with the average database size around 5GB. Recently a large server was purchased as the "Big Daddy" SQL box. Obviously not all the databases will be moved to this one box but within reason a number can.
My question: What is the best way to configure the box?
1 OS & 1 Default Instance - How do we isolate loops memory leaks etc... 1 OS & Multiple instances, ie:Dedicated instance for each application. - How do you allocate CPU & memory, allow for optimum performance without putting others at risk from a rouge process? Virtualized OS for each application with a default instance - Too much overhead impacts performance?
Future plans include offsite replication, perhaps mirroring with failover.
I'd appreciate anyone else's thoughts ideas? How have you tackled this problem in your environment?
