TSQL Join Query Problem
Sep 14, 2004
VIEW -ITMISSUES
Fields
issno-issdate-item-trxqty
1 - 010904-xyz - 2
2 - 020904-xyz - 5
3 - 080804-xyz - 6
4 - 020804-xyz - 9
VIEW-USR_VIEW_RPTS
Fields
receiptno- rptdate - item- rctqty
1 - 010804 - xyz - 10
2 - 010904 - xyz - 20
3 - 150804 - xyz - 25
4 - 150904 - xyz - 15
I have 2 views (ITMISSUES,USR_VIEW_RPTS) I want to retreive (Count of Issues Alis name)-noofissues,total issqty,
total (sum of Total Receipt Qty Alis name)- rctqty between a range of items and between a range of dates.How can i do it.
Result:
noofissues - totalIssqty - rctqty
2 - 15 - 35
i have tried with this Query
SELECT COUNT(DOCNUMBR)AS NOOFISSUES,ABS(SUM(TRXQTY))AS TOTISSQTY,
MAX(ITMISSUES.DOCDATE)AS LASTISSDATE,SUM(QTY) AS RCQTY
from ITMISSUES
INNER JOIN USR_VIEW_RPTS ON ITMISSUES.ITEMNMBR=USR_VIEW_RPTS.ITEMNMBR
WHERE ITMISSUES.ITEMNMBR BETWEEN 'xyz' AND 'xyz'
AND ITMISSUES.DOCDATE BETWEEN '2004-08-01' AND '2004-08-31'
View 1 Replies
ADVERTISEMENT
Aug 23, 2006
Dear all
some of quesiton i would like to ask someone who knew this method for right outer join
and use case when to catch NULL values , when i catch NULL this values i would like to release other character,
View 4 Replies
View Related
Mar 6, 2008
Hey all,
Anyone have a work around for using a TSQL FULL OUTER JOIN in CE? Something like a Right and a Left Outer Join for the below?
Thanks,
billb
Here's an example:
' Connection To SQL CE
Dim ConsqlShipTo As New SqlCeConnection
ConsqlShipTo.ConnectionString = "Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles) & "CRM.sdf"
'
'TSQL STATEMENT......
Dim reqSQL As New SqlCeCommand
reqSQL.CommandText = "SELECT " & _
" COALESCE(ShipTo.ShipToCust, Prospect.ProspectCust) AS [Customer ID], " & _
" COALESCE(ShipTo.ShipToName, Prospect.ProspectName) AS Name, " & _
" COALESCE(ShipTo.ShipToCity, Prospect.ProspectCity) AS City " & _
" FROM ShipTo FULL OUTER JOIN " & _
" Prospect ON ShipTo.ShipToName LIKE Prospect.ProspectName AND Prospect.ProspectCust LIKE ShipTo.ShipToCust " & _
" WHERE " & _
" (Prospect.Alias = '" & reqGetUserName & "' AND Prospect.ProspectName LIKE '%" & reqSearch & "%') " & _
" OR (ShipTo.Alias > '%%' OR ShipTo.Alias IS NULL) " & _
" AND (ShipTo.ShipToName LIKE '%" & reqSearch & "%') " & _
" GROUP BY " & _
" ShipTo.ShipToCust, Prospect.ProspectCust, ShipTo.ShipToName, Prospect.ProspectName, " & _
" ShipTo.ShipToCity, Prospect.ProspectCity " & _
" ORDER BY Name "
reqSQL.CommandType = CommandType.Text
reqSQL.Connection = ConsqlShipTo
ConsqlShipTo.Open()
'
'CREATE NEW SQL DATA ADAPTER
Dim sqlda As New SqlCeDataAdapter(reqSQL)
'
' FILL DATASET
Dim sqlds As New DataSet
sqlda.Fill(sqlds, "Customer ID")
'
' FILL DATATABLE - lightweight
Dim sqldt As New DataTable
sqlda.Fill(sqldt)
View 4 Replies
View Related
Apr 14, 2015
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
View 2 Replies
View Related
Jul 25, 2007
I'm using SQL Server 2005.
A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).
The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).
If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.
If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.
So, this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
and this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC
But this does't:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
What should I be looking for here to understand why this is happening?
Thanks,
john
View 1 Replies
View Related
Mar 2, 2004
Is there a better way to write this query?
SELECT t1.title,t1.record_id,
(SELECT SUM(t2.amount1) FROM table2 t2 WHERE t2.parent_id = t1.record_id)AS amt1,
(SELECT SUM(t2.amount2) FROM table2 t2 WHERE t2.parent_id = t1.record_id)AS amt2,
(SELECT SUM(t2.amount3) FROM table2 t2 WHERE t2.parent_id = t1.record_id)AS amt3
FROM table1 t1
View 13 Replies
View Related
Feb 4, 2008
Hi all..
i have a question about TSQL,
how it is possible to write this querry correctly:
Code SnippetSELECT dtEvent.EventNo as "Event ID:", FROM LIKE '%dtEvent%' AS dtEvent,WHERE dtMachine.id = dtEvent.agentmachineid
in the "FROM" part is a problem....LIKE '%%' isn't correct, but i don't know how can i write this query correctly...
by the way: for example just "dtEvent" is known ...but not the fullname like "dtEvent_XXXXXXXX"
Thanks
View 2 Replies
View Related
Nov 19, 2004
I have a table with a DateTime column called 'cutoff'. Is it possible to grab the TOP 1 record ordered by the cutoff column based on the current date?SELECT TOP 1 * FROM mytable WHERE cutoff >= GetDate() ORDER BY cutoff...works only if there is a cutoff < the current date. If GetDate() is greater then latest cutoff date it returns nothing. In this case, is it possible to just return the latest record? I'd like to keep this to 1 query if possible? :-)
View 1 Replies
View Related
Jan 30, 2006
I was hoping to get a little input on a problem I'm having.
In the DataAccessLayer of my application, I have a "search" function written that takes a bunch of parameters (in the form of a class object) and depending on what each of the paramaters are set to (to include search type parameters) it builds an appropriate select statement. The issue is that my company has recently decided to require all DataAccessLayer functions to use TableAdapters. TableAdapters can use StoredProceedures, and StoredProceedures can make external calls (it all seems a bit backward to me, but there does seem to be a bit of logic in that TableAdapters contain a connection string which is set in the app.Config file)... Anyway, here's an example of how I am doing it currently, and I was hoping someone could suggest a way I could do it with either TSQL, or otherwise. If CLR is the way to go, how does that work?
internal static dsStrongTypeDataSet GetAll(clsMyClass inData)
{
bool first = true;
dsStrongTypeDataSet data = new dsStrongTypeDataSet ();
string selectStatement = "Select * from tblMyTable where ";
//There is one of these if statements for each parameter
if ((inData.Paramater1 != null))
{
if (!first)//not as important in this section of code,
//but there are areas where there are up to 30 parameters
selectStatement += " and ";
if (inData.SrchParameter1 == SearchType.Fuzzy)
selectStatement += " Column1 LIKE ('%" + inData.Parameter1 + "%') ";
else if (inData.SrchParameter1 == SearchType.Literal)
selectStatement += "Column1 = '" + inData.Parameter1 + "'";
first = false;
}
//More if statements like above for EVERY parameter possible
SqlCommand selectCommand =
new SqlCommand(selectStatement, CorrectSqlConnection());
SqlDataAdapter dataAdapter = new SqlDataAdapter(selectCommand);
dataAdapter.Fill(data, "tblMyTable");
return data;
}
View 3 Replies
View Related
Apr 17, 2008
Hi,
I hope that someone can help me understand why my query is not returning what I expect. When I run this query:
SELECT DISTINCT(TransactionKey)
FROM Transactions_Fact
WHERE DateKey = 14550
AND TransactionKey BETWEEN 1 AND 90000000
AND TransactionKey NOT IN (SELECT DISTINCT(TransactionKey)
FROM tmpTransactions
WHERE TranDate = 14550
AND TransactionKey BETWEEN 1 AND 90000000)
I get 150 rows back, which is what I expect. However, if I leave out the 'AND TransactionKey BETWEEN 1 AND 90000000', then I don't get back anything?!?
SELECT DISTINCT(TransactionKey)
FROM Transactions_Fact
WHERE DateKey = 14550
AND TransactionKey NOT IN (SELECT DISTINCT(TransactionKey)
FROM tmpTransactions
WHERE TranDate = 14550)
Any ideas as to what I'm missing here? It seems like it should at least return the same 150 rows. Thanks for any help.
Gary Hines
View 6 Replies
View Related
Apr 10, 2008
I am trying to return an ouput parameter from my query. I have tested the stored proceedure extensivly within the SQL Management Studio, and I know it works fine, so that means the error is somewhere within my code, but for the life of my I cant figure where.
Here is my stored proc:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:Name
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[tblSiteVisits_FindOfficerName]
@VisitID int,
@OfficerName varchar(100) output
AS
BEGIN
-- Variables
Declare @OfficerID int
--Declare @OfficerName varchar(100)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Select @OfficerID = (Select top 1
OfficerID from tblSiteVisitOfficers Where VisitID = @VisitID)
IF (@OfficerID Is Null)
BEGIN -- Get the None Registered Officer
Select @OfficerName = (Select top 1 OfficerOther from dbo.tblSiteVisitOfficers Where VisitID = @VisitID)
print 'Got unregistered Officer ' + @OfficerName
END
ELSE
BEGIN -- Get the Registered Officer
Select @OfficerName = (Select OfficerFName + ' ' + OfficerLname from dbo.tblOfficers Where OfficerID = @OfficerID)
print 'Got Registered Officer ' + @OfficerName
END
END
And here is the code I am using to access this proceedure:1 Dim blah As String
2 Dim conn2 As New SqlConnection()
3 Dim cmd2 As New SqlCommand()
4 conn2.ConnectionString = _ConnString
5 cmd2.Connection = conn2
6 cmd2.CommandType = CommandType.StoredProcedure
7 cmd2.CommandText = "dbo.tblSiteVisits_FindOfficerName"
8
9 cmd.Parameters.AddWithValue("@VisitID", Convert.ToInt32(row("VisitID")))
10 cmd.Parameters.Add("@OfficerName", SqlDbType.VarChar, 100)
11 cmd.Parameters("@OfficerName").Direction = ParameterDirection.Output
12 Try
13 conn.Open()
14 cmd.ExecuteNonQuery()
15 blah = cmd.Parameters("@OfficerName").Value.ToString()
16
17 Catch ex As Exception
18 Throw ex
19 Finally
20 conn.Close()
21 End Try
22
However there I never recieve the output value, and because of the way my database is structures, there is no possible way, that there is no output value.If anyone can help, that would be great, kind regards.
View 2 Replies
View Related
Mar 8, 2006
Hi,I am using a .dqy file to import some rows from a sql server 2003database to microsoft excel. Unfortunately, I have am importing morerows than excel can handle. Is there a way around this?It appears that there is no equivalent of the LIMIT(offset, number ofrows to return) function of mysql in tsql. Had there been anequivalent, I would have created multiple .dqy files, which would querythe database for pages of results as appropriate. There is an upperlimit to the number of records that there can be in the database, sothis would have been a feasible solution.Also, I must use .dqy files (or something equivalen) because thequeries are invoked from a website, and it is necessary to download theresults, sort/filter, etc. (in excel).Thanks for any suggestions.
View 4 Replies
View Related
Sep 27, 2007
Hi All,
Please suggest me is there any performance/other differences between the below two queries.
----query1
select T1.name,T1.Number, T2.Dept, T2.Desig
From T1 Inner Join T2 on T1.EID = T2.EID
----query2
select T1.name,T1.Number, T2.Dept, T2.Desig
From T1 Inner Join (Select Dept, Desig From T2) As T2 on T1.EID = T2.EID
Thanks
Senthil
View 3 Replies
View Related
Mar 13, 2008
Hello.
Is there any possibility in SQL server 2005 to find out the source of some sql-query result? I need some kind of meta information about result I got. For example:
[if query is]
select
t1.id as id1,
t2.id as id2,
67 as col67
from t1, t2
[result will be]
| id1 | id2 | col67 | .......................
and at the end of all I need something like :
id1-from t1.id; id2-from t2.id; col67-unknown;
thanks for help
View 3 Replies
View Related
Aug 20, 2006
Anyone seen this before?
View 5 Replies
View Related
Sep 13, 2006
The title says it all. You can do it from a ADO.Net client so why not from TSQL.
It all uses the service broker stuff so why not?
View 5 Replies
View Related
Jan 2, 2006
I have 2 tables, I will add sample data to them to help me explain...Table1(Fields: A, B)=====1,One2,Two3,ThreeTable2(Fields: A,B)=====2,deux9,neufI want to create a query that will only return data so long as the key(Field A) is on both tables, if not, return nothing. How can I dothis? I am thnking about using a 'JOIN' but not sure how to implementit...i.e: 2 would return data- but 9 would not...any help would be appreciated.
View 3 Replies
View Related
Feb 13, 2004
Just Like these:
-- batch start
Select * from someTable --maybe a query which need much res(I/O,cpu,memory)
/*
can I do something here to free res used by prior statement?
*/
select * from someOtherTable
--batch end
The Sqls above are written in a procedure to automating test for some select querys.
View 4 Replies
View Related
Aug 30, 2007
Hello all:
Here is a sample query:
DECLARE @KEYID NVARCHAR (50) ; SET @KEYID = '1074958'
DECLARE @ENTITY NVARCHAR (100); SET @ENTITY = 'HouseDimension'
DECLARE @KeyCol NVARCHAR(50);
SET @KeyCol = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'
DECLARE @KeyValue NVARCHAR (1000)
SET @KeyValue = 'SELECT '+ @KeyCol + ' FROM HouseManagementFact WHERE HouseKey = ' + @KEYID +
' GROUP BY ' + @KeyCol + ' HAVING SUM(TotalClaimCount) > 0 OR SUM(HouseCount) > 0 '
The value resulting from Executing @KeyValue is an integer.
I want to store this value in a new variable say @VAR2
When I do this
DECLARE @VAR2 INT
SET @VAR2 = execute sp_executesql @KeyValue
its giving me an error.
can somebody let me know the correct form of storing the value resulting from @KeyValue in some variable ?
View 3 Replies
View Related
Nov 19, 2007
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005
1
-- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id
2
-- PK and Index
select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id
This sql is extracting some sort of the information about the structure of the sql server database[2005]
I need a sql whihc will return the same result for sql server 2000
View 1 Replies
View Related
Apr 30, 2008
Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You
View 1 Replies
View Related
May 9, 2008
Hi.
Me any my team are soon going to work on a performance critical application. My team has some experience of writing SQL, however we have not done performance oriented coding.
I am looking for a comphrehensive document which lists information for writing good SQL with performance. Please guide if there is such a document or web site.
Thanks,
Prasad
View 1 Replies
View Related
Mar 30, 2007
I need to run a DELETE query based on 2 tables. I can't use JOIN with delete queries, so how do I do this?
What I initially tried to do was:
Code:
DELETE FROM tblProductState
JOIN tblProduct
ON tblProduct.id_Product = tblProductState.id_Product
WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1
Basically, I need to delete FROM tblProductState, WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1
How can I do this without using JOIN. Use a sub-query? How?
Thanks
View 4 Replies
View Related
Sep 29, 2007
Code:
SELECT ISNULL(count (agencyCandidate.JobID) ,0) as Total,
MAX(RecJobAds.PostedDt) as PostedDt,
MAX(RecJobAds.JobTitle) as JobTitle,
RecJobAds.JobId,
MAX (AgencyCandidate.AgencyId) as agentID,
MAX(RecJobAds.AdStatus) as status,
MAX(RecJobAds.CompanyId) as CompanyId,
MAX(RecJobAds.RecId) as RecId
FROM RecJobAds
LEFT JOIN AgencyCandidate
ON RecJobAds.JobId = AgencyCandidate.JobId
GROUP BY RecJobAds.JobId
ORDER BY Total ASC
i have the above query. but i would like to join in a third table, where my query is
Code:
select * from RecruiterMA where Activated = '1'.
anyidea in which place i can put the second query in the first query? thank you very much!
View 4 Replies
View Related
Jun 10, 2013
I have to queries I need to combine with a left join and I am having trouble figuring out the syntax. I need to join the first query with a query that contains Unions. The queries need to by joined on File_NBR which is contained in vw_SBC_Employee_Info, vw_ADPFile and SBC_Best_Scores.
Query 1
SELECT
e1.File_NBR,
e1.Division,
e1.Department,
e1.Program,
e1.UNIT,
(e6.Mngr_FName + ' ' + e6.Mngr_LName) AS President,
[Code] .....
Query 2
SELECT
'Skill Rating' as Assessment_Type,
bs.File_NBR as ID,
bs.Skill_NBR,
bs.Best_Score as Score,
bs.Assesment_RND
[Code] .....
View 9 Replies
View Related
Nov 30, 2007
Hi, im searching for some help, here is my problem:i have an Appointments Table ex:Appointments AppointmentID : int ContactsList : xmlstruture of the ContactsList column:<ArrayOfContactsInfos> <ContactsInfos> <ContactID>1</ContactID> </ContactsInfos> <ContactsInfos> <ContactID>2</ContactID> </ContactsInfos></ArrayOfContactsInfos>And my Contacts Table:Contacts ContactID : int FirstName : varchar(200) LastName : varchar(200)What i want to do... is find the right query for retreive a list of Appointment based on the FirstName or LastName of a Contact in the ContactsList collumnis this possible to do a sort of join between the xml and the Contacts Table?and if yes how?Please can you help me?
View 1 Replies
View Related
Mar 5, 2008
Hello,There are 2 tables A(bot_id,bot_date,gomA,gomB) B(gom_id,hist_date, gom_name)
table B holds the names for gomA and gomB in tableA.I wish to select A table and inner join to it gom_name for gomA and gomB IDs.The problem begins when in table B there is multiple values for gom_id, with difference names.So, to retrieve the correct B.gom_id, there is 2 conditions. first bot_date>B.hist_date and after this, if we still gets multiple records then the top record will be selected (order by date)
Any ideas?
View 7 Replies
View Related
Jul 21, 2004
I really need some help. I'm going nuts. I been trying to get this to work all morning and i cant get it it work. the simple version is I am trying to join these two sql queries and make them one.
Code:
SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
FROM news
LEFT JOIN users ON news.news_author = users.user_id
WHERE news_id =41
SELECT CONCAT('users.user_fname', 'users.user_lname') *AS edit FROM users WHERE users.user_id=54
my previous attempts are
trouble with query.
I have a news table which has the ID of the original author and a column for the author who last edits the news article. both columns store the id of the author.
now when I'm editing the article I want to get the name of the author from the users table.
I have done a left join to get the first author (original author) but I'm having a real hard time trying to get the name of the author who last edited the record.
they both need to lookup the user_fname and user_lname fields from the users table.
my current sql is below
Code:
SELECT news.*, CONCAT(users.user_fname, " ",users.user_lname) AS org_author FROM news LEFT JOIN users ON news.news_author=users.user_id WHERE news_id=41
which gives me
Code:
news_id *news_subject *news_article *news_author *news_date *news_edited *edit_author *org_author *
41InterclubBunbury IC club has asked us all to attend a inter...771090247547109041836254Adam Green
Now how do i adapt it to get the name of the author to last edit the article? do I do a sub query another left join ???
every way I try doesn't work.
this is what I'm currently trying to get it with.
Code:
SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
FROM news
LEFT JOIN users ON news.news_author = users.user_id
LEFT JOIN (
SELECT CONCAT( users.user_fname, " ", users.user_lname ) AS edit_author
FROM news
WHERE news.edit_author = users.user_id
)
WHERE news_id =41
Heres a different approach that I thought would work
Code:
SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
FROM news
LEFT JOIN users ON news.news_author = users.user_id
LEFT JOIN (
SELECT concat( users.user_fname, users.user_lname ) AS edit
FROM users
WHERE users.user_id =54
) AS edit
WHERE news_id =41
but no i get this error
Code:
MySQL said:
#1064 - You have an error in your SQL syntax near '(
SELECT concat( users.user_fname, users.user_lname ) *AS edit
FROM users
WHERE' at line 4
I also tried
Code:
SELECT news.*, CONCAT(users.user_fname, " ",users.user_lname) AS org_author, *(SELECT concat(users.user_fname, users.user_lname) AS edit from users where users.user_id=54) AS edit FROM news LEFT JOIN users ON news.news_author=users.user_id
WHERE news_id=41
but got this error
Code:
MySQL said:
#1064 - You have an error in your SQL syntax near 'SELECT concat( users.user_fname, users.user_lname ) *AS edit
FROM users
WHERE us' at line 2
can anyone help me with this query its been driving me up the wall all morning.
View 2 Replies
View Related
Jul 28, 2004
Hi,
Please help me in writing a join query.
I have three tables with three columns each. Now I want to retrieve data from all the three
tables as one.
Table 1: EmpId, Date, Points
Table 2: EmpId, Date, Points
Table 3: EmpId, Date, Points
These are related to three different divisions. So, on analysis we have get the no. of points
accumulated on a day. On any day the points can be in all three divisions or in any one or two
divisions. My database SQL Server 2000.
So, how to get this sort of output.
EmpidDatePts(DIv1) Pts(DIv2) Pts(DIv3)
V00107-24-2004NullNull25
V00207-24-200420Null25
V00307-24-2004Null30NUll
V00107-23-200415NullNUll
V00207-23-2004102525
V00107-22-2004Null1025
I'm badly in need of help. Any sort of help is appreciated.
M.L.Srinivas
View 3 Replies
View Related
May 30, 2012
how do I add in a 3rd and 4 table to the mix and get their sums and group by ...The top select works fine the lower one is what I was thinking is this.....
Select Distinct
P.CustID, P.Acct, P.Title, P.FirstName, P.LastName,
A.Trips, A.MoneySpent, A.Bal
FROM dbo.Cust P
INNER JOIN
(SELECT
CustID, Sum(Days) as Trips, SUM(MoneySpent) as MoneySpent,
Sum(Balance) as Bal
FROM dbo.CAsh
Where Accumulator='DP'
GROUP BY CustID) A ON P.CustID = A.CustID
The 2 new tables are ViewDis (X) - Sum (Distance)and ViewACC (Y) Sum Fields required Hours, Min, (Group by) Building and Date.
Select Distinct
P.CustID, P.Acct, P.Title, P.FirstName, P.LastName,
A.Trips, A.MoneySpent, A.Bal, X.Distance, Y.Hours, Y.Min, Y.Building
FROM dbo.Cust P
INNER JOIN
[code]....
View 14 Replies
View Related
Apr 8, 2008
I've a table like
Object ObjIdParentObjId
------ ----------------
Obj1 |101 |0
Obj2 |102 |101
Obj3 |103 |0
Obj4 |104 |102
If the 'ParentObjId' is 0 it means no more parent,
If I filter the Object value as Obj4, it should show the following rows
Obj4 |104 |102
Obj2 |102 |101
Obj1 |101 |0
Guide to me write a SQL for this situvation?
View 17 Replies
View Related
Jun 10, 2008
i have two tables field1 is the primary key:
table1
--------------------
field1|field2|field3
---------------------
table2
---------------------
field1|field4|field5
---------------------
what we need is to get the results based on field1 matching datas, but is also a requirement that if there is no matching data in field1 of table2 w.r to table1 we just need to return table1 datas with null values for table2 fields.
is that possible, i used inner, right joins but all in vain.
what I need is a output like this
output
---------------------------------------
field1|field2|field3|field4|field5
---------------------------------------
View 5 Replies
View Related
May 17, 2006
i hav 2 tables which contain a common field called ID.
Table A Table B
ID ID
a a
b b
c
d
Now i want results from Table A in such way that ID's Which are in table B do not get selected in table A.
how do i use join query for this
View 2 Replies
View Related
