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
2 s-5 1000
1 s-6 2000
1 s-7 3000
2 s-7 3000
3 s-7 3000
1 s-8 4000
1 s-9 2000
2 s-9 2000
Here is what I need to output to show:
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...
View 4 Replies
ADVERTISEMENT
Nov 7, 2006
I'm using SQL server 200
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
View 1 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Aug 29, 2005
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.
Here's a sample of my tables/data/etc.
t1
------------
speciesid | species
1 | Mammals
2 | Rodents
3 | Reptiles
t2
---------
animalid | animal
3 | Skink
3 | Iguana
3 | Rattlesnake
2 | Meerkat
1 | Hippo
1 | Elk
What I want to do is pull up a list of all the species and under each list all the animals currently listed under that species.
So the result I want should look like:
Mammals (Hippo, Elk)
Reptiles (Skink, Iguana, Rattlesnake)
Rodents (Meerkat)
so currently I have:
SELECT A.animalid, S.speciesid, A.animal, S.species from t2 as A, t1 as S where S.speciesid=A.animalid order by species
this works great, it's just that it only returns one animal instead of all of the animals. Any help would be appreciated.
View 4 Replies
View Related
Jul 7, 2015
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
View 9 Replies
View Related
Dec 9, 2014
We have Three Tables in sqlserver2012
Master Table
OrderID PackageID CustomerName
1 1 Abc
2 2 Bcd
3 1 xyz
Child1 Table
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
[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
View 5 Replies
View Related
Feb 24, 2004
Hi,
I am using DTS in SQL server2k
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.
Thanks and regards,
Henry
View 1 Replies
View Related
Oct 10, 2005
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 :)
View 4 Replies
View Related
Dec 9, 2014
We have Three Tables in sqlserver2012
Master Table
OrderID PackageID CustomerName
1 1 Abc
2 2 Bcd
3 1 xyz
Child1 Table
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
View 2 Replies
View Related
May 15, 2002
Hi all -
Heres the deal,
I have six tables that are loosly related,
four in one join, two in the other....
in the first join there is a column called commentID -
and is based on a join of the four tables, the results of the
query is based on a where clause
and this needs to be tied into the second set of joined
tables (they are joined on a commentID themselves)
I have both the queries and joins working -
The primary query (4 joined tables) needs to do a lookup on the second (2 joined tables) and add its selected value, into the final result set....
thanks a million for the help!!
take care
tony
View 1 Replies
View Related
Feb 1, 2008
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.
CREATE PROCEDURE sp_AdminListApplicants
@LocationID int,
@FolderID smallint,
@JobID int,
@SortOrder char(1)
AS
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
Miranda
View 5 Replies
View Related
Dec 7, 2006
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.
Is this possible via the Management Studio?
Thanks,
Ron
View 4 Replies
View Related
Mar 11, 2008
I have two tables - products and productpropertyvalue
I need to select multiple fields from the productpropertyvalue as it corresponds to the product id. The script I am using is
select a.id, a.productname, a.siteprice,
b.propertyvalue
from product a, productpropertyvalue b
where a.id = b.productid and propertyid=590
This allows me to extract only 1 propertyid. I need to make it add 3 other columns for propertyid=589, 617, 615
Any help solving this problem would be appreciated - thanx!
Ron
View 14 Replies
View Related
Feb 25, 2008
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!
View 3 Replies
View Related
Sep 12, 2006
Hi!
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
View 2 Replies
View Related
Apr 25, 2008
Hello, i have this problem
Table1
tbl1
pcb varchar(30)serial varchar(30)result varchar(30)
tbl2:
pcb varchar(30)date_time varchar(30)result varchar(30) data1 varchar(30)data2 varchar(30),
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
tbl2= pcb1,date1,pass,dataX1,dataY1
pcb1,date2,pass,dataX2,dataY2
pcb2,date3,pass,dataX3,dataY3
pcb3,date4,pass,dataX4,dataY4
pcb1,date5,pass,dataX5,dataY5
pcb2,date6,pass,dataX6,dataY6
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.
The correct ResultSet should be
pcb1,sn1,date1,dataX1,dataY1
pcb2,sn2,date3,dataX3,dataY3
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
How can i resolve this issue?
Thank you very much
View 1 Replies
View Related
Jun 11, 2008
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?
View 1 Replies
View Related
Feb 14, 2014
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
View 6 Replies
View Related
Dec 8, 2005
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?
View 3 Replies
View Related
Jan 18, 2006
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.
View 2 Replies
View Related
Nov 27, 2007
Hello,
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
Thanks,
Andy
View 3 Replies
View Related
Mar 2, 2007
I have created the following view:
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.
View 4 Replies
View Related
Apr 7, 2008
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)?
View 3 Replies
View Related
May 12, 2008
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?
View 4 Replies
View Related
Sep 30, 2013
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')
View 4 Replies
View Related
Jan 11, 2014
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.
View 4 Replies
View Related
Nov 5, 2014
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.
View 2 Replies
View Related
May 3, 2007
Hi
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"
Please help, any advice would be helpful
Kind Regards
Carel Greaves
View 1 Replies
View Related
Jul 29, 2015
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:
'Mailing1, Mailing2, Mailing3'
View 9 Replies
View Related
Oct 21, 2014
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?
SELECT
a1z103acno AccountNumber
, a1z103frnm FirstName
, a1z103lanm LastName
, a1z103ornm OrgName
, a3z103adr1 AddressLine1
, A3z103city City
, A3z103st State
[code]...
View 1 Replies
View Related
May 17, 2007
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?
Thanks
Scott
View 1 Replies
View Related
Feb 11, 2004
Hi ,
Is there a way in SQL Server stored procedure to return multiple records/ more than one records....?
Thanks in Advance...
-Mohit.
View 1 Replies
View Related
