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')
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 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:
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
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
Say I have app, I want to know when I perform any operation like logging, changing username etc, can I get all the tables involved for that particular operation.
Hi,The following request select a constraint from TABLE_CONSTRAINT withthe name specified in the where clause:select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS whereconstraint_name = 'FK__51OtherParties__51Claims'It returns:http://graphicsxp.free.fr/constraint.JPGSo I have TABLE_NAME that correspond to the first table involved in theconstraint, but how do I get 51Claims ????Thank you
We have DTS package automatically pouring data into the publishing database(source tables). During this process, we want to temporary disable certain triggers. However, the command
Alter table 'tbl' disable trigger 'abc' errored out. The error message said:
''Cannot alter the table 'tbl' because it is being published for replication."
I've digged more into this and found although it's not allowed to disable a triggers,
the SQLServer do allow delete the trigger and recreate them.
Is there any way to disable the trigger directly?
Thanks in advance,
Don
BTW:
I've used the following sql directly, however the trigger still fires.
UPDATE sysobjects SET status = status|2048 WHERE type = 'TR' AND parent_obj = OBJECT_ID (@table_name)
The only other way around now is to create stored procedures that dynamically create the trigger. Because our trigger is normmally larger than 8000 bytes. We have to create one stored procedure per trigger. This option is not acceptable because not only it takes quite a time, but also a maintainance nightmare.
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 proc that have 3 in parameter that are actually values of some of the columns in that table one parameter for each table.what is the optimized way to write query to get records on the basis of in parameters from these tables.
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
Hello all, Iv been making a lot of progress on my first functional webapp, but I cannot get this bit of code to work correctly. I think my UPDATE SQL statement is where the problem is. It works fine the first time through when there is no Session("estimateid") set, but after that is set it gives me error this on line 40: Incorrect syntax near '('. 1 Dim CustID As Integer 2 3 Dim DbConnection As SqlConnection 4 DbConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("harringtonairdb").ConnectionString) 5 DbConnection.Open() 6 Dim DbCommand As SqlCommand 7 8 If Session("estimateid") = 0 Then 9 Dim DbSqlInsert As String 10 DbSqlInsert = "INSERT INTO tblcustomers (strname, strAddress1, strAddress2, strCity, strState, strZip, strPhone, strEmail, strContact) VALUES (@name, @address1, @address2, @city, @state, @zip, @phone, @email, @contact)" & "SELECT @@IDENTITY AS CustID" 11 DbCommand = New SqlCommand(DbSqlInsert, DbConnection) 12 Else 13 Dim DbSqlUpdate As String 14 DbSqlUpdate = "UPDATE tblcustomers SET (strcustname=@name, straddress1=@address1, straddress2=@address2, strcity=@city, strstate=@state, strzip=@zip, strphone=@phone, stremail=@email, strcontact=@contact) JOIN tblestimates ON pkcustomerid WHERE pkestimateid=@estimateid" 15 DbCommand = New SqlCommand(DbSqlUpdate, DbConnection) 16 DbCommand.Parameters.AddWithValue("@estimateid", Session("estimateid")) 17 18 End If 19 20 DbCommand.Parameters.AddWithValue("@name", txtCustName.Text) 21 DbCommand.Parameters.AddWithValue("@address1", txtCustAddress1.Text) 22 DbCommand.Parameters.AddWithValue("@address2", txtCustAddress2.Text) 23 DbCommand.Parameters.AddWithValue("@city", txtCustCity.Text) 24 DbCommand.Parameters.AddWithValue("@state", txtCustState.Text) 25 DbCommand.Parameters.AddWithValue("@zip", txtCustZip.Text) 26 DbCommand.Parameters.AddWithValue("@phone", txtCustPhone.Text) 27 DbCommand.Parameters.AddWithValue("@email", txtCustEmail.Text) 28 DbCommand.Parameters.AddWithValue("@contact", txtCustTimes.Text) 29 30 31 If Session("estimateid") = 0 Then 32 CustID = Convert.ToInt32(DbCommand.ExecuteScalar()) 33 DbCommand.Dispose() 34 Dim DbSqlInsert As String 35 DbSqlInsert = "INSERT INTO tblestimates (fkcustomerid) VALUES (@customerid)" & "SELECT @@IDENTITY AS EstimateID" 36 DbCommand = New SqlCommand(DbSqlInsert, DbConnection) 37 DbCommand.Parameters.AddWithValue("@customerid", CustID) 38 Session.Add("estimateid", Convert.ToInt32(DbCommand.ExecuteScalar())) 39 Else 40 DbCommand.ExecuteNonQuery() 41 End If 42 43 DbConnection.Close() 44 DbCommand.Dispose()
I want to update the startdate column (for all rows) so that when period is 0 then the new value is a hardcoded value (say '01-Dec-2000') but for all other rows it takes the value in the enddate column for the row of the previous column (with the same freq)
ie the startdate column for period 1 takes the enddate value for period 0 and so on for a particular freq
create table #periods (period int , startdate datetime , [enddate] datetime , freq int) insert #periods ( period , startdate , enddate , freq) select 0 , '01-Jan-1900' , '31-Jan-2001' , 1 union all select 1 , '01-Jan-1900' , '28-Feb-2001' , 1 union all select 2 , '01-Jan-1900' , '31-Mar-2001' , 1 union all select 3 , '01-Jan-1900' , '30-Apr-2001' , 1 union all select 4 , '01-Jan-1900' , '31-May-2001' , 1 union all select 0 , '01-Jan-1900' , '31-Jan-2002' , 3 union all select 1 , '01-Jan-1900' , '28-Feb-2002' , 3 union all select 2 , '01-Jan-1900' , '31-Mar-2002' , 3 union all select 3 , '01-Jan-1900' , '30-Apr-2002' , 3 union all select 4 , '01-Jan-1900' , '31-May-2002' , 3
/* I know I need a case statement to test for column 0 and to join the table on itself and have put something together but it fails for column 0 and updates to NULL - I think it must be to do with the join ??
This is what I've got so far :
UPDATE PA1 SET PA1.Startdate = CASE WHEN PA2.period = 0 THEN 2000-12-01 00:00:00.000 ELSE PA1.Enddate END FROM #periods AS PA1 JOIN #periods AS PA2 ON PA1.Freq = PA2.Freq AND PA1.Period = PA2.Period + 1
Hi i have a view that contain multiple tables from my database and i want to view it on datagridview and update it's data some people says you can update joined tables using instead of triggers how is that ?is there any example ?
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.
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.
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)?
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"
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?