hello experts
i have two sql select statements that selects data from two different tables. I want to concatenate these two sql queries into one singl query (through programming only not through joins). After concatenation i want to sent the single created query to my transection function so that it can select data from two different tables.
How can i acomplish this in vb .net or c#
thanks
fine regards
ali
I know this has been addressed before but I can't find it...
I have a table with with a column called PersonId. I want a query that will return all the PersonId's as a comma delimited string...
Anyone able to help?
Is it possible to relate two tables (in the same db) using the PK (int) from one table to a UK (int, allownulls=No) from another?
I've tried, but had no success. Both tables are empty, so there is no way of having a null in the UK column that would prevent relating it to a PK. Just wanted to know if such relation is even possible before spending additional time on figuring out how to do it...
Hi,
I am trying to relate two tables but both are from different databases. I have a database 'Current' which has a table 'case' and another database 'Org' which has a table 'employee'. I want to establish a one-to-many relationship between employee and case. I created a coulmn 'employee_key' in the case table & entered values matching the pkey of the employee table. Then I wrote a query using joins to access the table records.
select Org..employee.pkey, Current..case.assignedengineer, Current..case.pkey from Current..case join Org..employee on Current..request.employee_key=Org..employee.pkey
Although the above query works fine, there is no relation between the two tables of the two databases, I wanted to know if this is the right way to achieve what i want to or is there a way in which i can actually create a relationship between tables of two different datbases. Can anyone suggest/help???
Also, I wanted to know if i can relate 1 table of SQL Server db with another table of Oracle DB. Please help ...........
Thanks
I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.
I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.
This is the basic logic goes soemthing like this:
select case new_accountant = 1 then 'acct/' end +
case new_advisor = 1 then 'adv/' end +
case new_attorney = 1 then 'atty/' end as String
from new_database
The output would be
Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/
So far, nothing I have tried has worked.
Any ideas?
Hi,
I have couple of DBF files.. and when i get them into my scrub database the datatype for all the fields in char. but after i scrub the data i put them into a money feild.. so that if so that i can do any calculations that i want in the report...
Suppose if i keep as a char or varchar in my production database... is it possible to any calculation on it in my report.. just simple additions... and if yes... do i need to convert them into anything before doing the additions... The reason i am asking this is Some of the fields in the scrub database are empty and i want to keep them the same in my production database instead of showing 0.00 as a default.
Any help will be appreciated.
Regards
Karen
I am attempting to determine if a record for TODAY has been written. If one, I update it. If no records for TODAY have been created yet, I INSERT a new one. I am basing my "if" (C#) on a Select count(*) WHERE as shown below. My problem is that I do not know how to obtain the result. The debugger displays a -1 as the count and the IF falls through to its companion ELSE. Can anyone please set me straight? Oh, I build 'TestDate' from getdate and it ends up as a str like "6/10/2008". The Date column is also a varchar with the same kind of string as its contents.String fpsqlA = ("SELECT count(*) from DailyCounts WHERE Date = '"+testDate+ "'");
SqlCommand fpcmdA = new SqlCommand(fpsqlA, connStringDaily);fpcmdA.CommandType = CommandType.Text;
int iCount = fpcmdA.ExecuteNonQuery();SqlDataAdapter ad1 = new SqlDataAdapter(fpcmdA);
ad1.Fill(dtA);
//Test to see if a record for 'today' has already been written to the DailyCounts table. if (iCount <= 0)
{
Hi all,
I am still relatively new to the SQL world, and have just started looking at purging data from my databases which is older than 8 years old. I am using a routine from another forum on here to get a before Table size pre purge, and then running it again to get the new size after.
The thing i have found is that even though i have deleted over 1.5 million rows, there is actually no difference in the table size.
I have been looking at the UPDATE STATISTICS on the msdn web pages to see if i need to run this to update the information i am using for size comparisons, but it doesnt really tell me if this is one of the things that it updates, and was wondering if i was being dumb and missing something completely obvious here. The other thing i can think of is that even though the data has been deleted, the space has been retained in the indexes on the tables, and until i do a re-index i will not realise the space savings?
Many thanks in advance, and feel free to point and snigger if i have just missed something staring me in the face
Mark
hi!
i have some what a definition table of the fields of the other table
name as DefinitionTable with data as follows
FieldType FieldName1 FieldDesc TxtColor FontStyle
8 FIELD1 ABC #1111 [fsbold]
8 FIELD2 CDE #2222 [fsitalic]
8 FIELD3 EFG #3333 [fsbold]
OrigTable
OrigKey FieldType FIELD1 FIEDL2 FIELD3 Results
1 8 test1 test2 test3 ok
so the e.g. FIELD1 is a data in DefinitionTable and a field name in OrigTable..
my problem is how to get the TxtColor in merging this table.. my sp in fetching the data but only the fielddescription is
create procedure Definition
@SearchKey int,
@FieldTypeint
as
begin
DECLARE @FieldName varchar(10),
@FieldDesc varchar(30),
@TextColor int,
@mysql varchar(4000)
SET NOCOUNT ON
SELECT @mysql = 'select o.origkey, '
DECLARE MyCursor CURSOR READ_ONLY FOR
SELECT FieldName1, FieldDesc FROM DefinitionTable
WHERE FieldType = @ResultType
Open MyCursor
FETCH NEXT FROM MyCursor INTO @FieldName, @FieldDesc
WHILE @@FETCH_STATUS = 0
BEGIN
select @mysql = @mysql + 'o.' + @FieldName + ' as ' + @FieldDesc + ', '
FETCH NEXT FROM MyCursor INTO @FieldName, @FieldDesc
END
CLOSE MyCursor
DEALLOCATE MyCursor
select @mysql = @mysql + ' o.results from OrigTable o where
FieldType = ' + @ResultType
execute(@mysql)
end
the result is Ok as
OrigKey ABC CDE EFG Result
8 test1 test2 test3 ok
But the problem is how to include the TxtColor or concatenate TxtColor with this output below
OrigKey ABC CDE EFG Result
8 test1+##1111 test2+#222 test3+#3333 ok
thanx,
mygt
EDIT: Please skip over this and go to the 2nd post. After writing this, I realized it may be too complicated to comprehend, so I tried to simplify with another example which is found in the 2nd post. Or if you want you can read this one and try to understand it.
I would like to go over a design we came up with to handle a certain reporting requirement from an OLAP cube to see if this is a good approach. Also, I would like to hear about other designs that we can consider.
Here is the condensed version of the requirement.
We are measuring the performance of a bunch of portfolios. So among other dimensions, we have a portfolio dimension that holds the list of portfolios we are tracking. In a typical portfolio analysis, an analyst almost always looks at a portfolio's measures along side a benchmark portfolio's measures. A benchmark is another portfolio against which a selected portfolio is compared. So, for a given portfolio, there is a determined benchmark portfolio.
For example, an analyst might want to look at a measure called Annual_Yield for a portfolio called Portfolio_A. There will be an attribute on the member Portfolio_A that tells us what its benchark portfolio is. That attribute will basically be another member from the portfolio dimension. So, as a function of what an analyst chooses as the first portfolio (the subject portfolio), the cube should be smart enought to know what to select as the second portfolio (the benchmark portfolio).
To implement this, I am proposing the following structure for the table that will create the portfolio dimension.
PortfolioID, PortfolioName, Attr1, Attr2, ..., AttrN, BenchMarkID
1, Portfolio_A, blah, blah, ...., blah, 3
2, Porfolio_B, blah, blah, ...., blah, 3
3, Portfolio_C, blah, blah, ...., blah, null
4, Portfolio_D, blah, blah, ...., blah, 3
5, Portfolio_E, blah, blah, ...., blah, 6
6, Portfolio_F, blah, blah, ...., blah, null
So, the above data says that Portfolios A, B, and D uses Portfolio C as their benchmarks. And Portfolio E uses Portfolio F as its benchmark. Lastly, if a portfolio is used as a benchmark for other portfolios, then its BenchmarkID is null, meaning it doesn't get compared to anything, thus Portfolios C and F have null BenchmarkIDs. With this data, I would create a hierarchy in the portfolio dimension that has two levels. At the top level, there will be a portfolio and if that portfolio has a benchmark, then it will drill down to it.
With a portfolio dimension designed as such, I envision a fact table that looks like the following.
PortfolioID, SomeOtherDimIDs,....., Annual_Yield
1, , blah, ,....., 10%
2, , blah, ,....., 7%
3, , blah, ,....., 0%
4, , blah, ,....., 15%
5, , blah, ,....., 20%
6, , blah, ,....., 9%
So, going back to the original report which was looking at Annual_Yield by Portfolio_A, I would do an MDX statement such as
With Set [PortfolioAndBenchmark] As
DESCEDANTS(Portfolio_A)
Select
[Annual_Yield], [PortfolioAndBenchmark] On Columns,
Whatever Other Dimension On Rows
From
[Cube]
Does this make sense? The only concern I have is that I don't want the measures to aggregate along the portfolio hierarchy.
Our mobile application using VS2005 with VB.net is ready and we are trying to set up the SQL CE on the server to test the remote connection procedures. Sql 2005 is based on a dedicated server and is accessed via the Web server which does not have Sql installed on it. We have installed the Sql Server Me Server Tools on the Web server and configured the Virtual directory with IIS. What we cannot get our heads around is how we link the database on the separate Sql server via the web server. When the Pda contacts the Web server via IIS we somehow need to redirect to the dedicated Sql server as it cannot be contacted outside of our network. We have been told that Sql Ce can link with an existing Sql 2005 database, how can we do this?
View 6 Replies View RelatedI've been searching everywhere for a solution to this problem and no answers exist anywhere. When I try to create a new maintenance plan I get the following error. I've been told it may be related to SSIS but nobody has a solution. How do I fix this issue so I can create a maintenance plan.
Exception has been thrown by the target of an invocation. (mscorlib)
ADDITIONAL INFORMATION:
An OLE DB error 0x80004005 (Client unable to establish connection) occurred while enumerating packages. A SQL statement was issued
and failed.
An OLE DB error 0x80004005 (Client unable to establish connection) occurred while enumerating packages. A SQL statement was issued
and failed.
I'm currently setting up a Tabular Model to do some research between several fact tables. In this example i have two fact tables (table 1 and table 2) which I've created a 1 to 1 relationship on phone number. Typically I create a relationship between these tables to find common data between the two. However, in this case I am trying to figure out the best way to model the data so that I can easily surface data from one table that does not exist in the other. I would liken this to a LEFT JOIN or a WHERE NOT EXISTS in SQL.
Table 1 has all of the data and Table 2 Only has a subset of the data from Table 1. What I'm trying to do here is display what attributes in Table 1 may play a part in records not existing in Table 2. What is the best way to model this?
Dear Developres,
Actually I'm on the half way of making a portal and I get some problem I need your kindly helps.
at first I use the membership feature of ASP.net 2.0 to have login and all so by default it has generate an ASPNETDB.MDF file which its is (Microsoft SQL Server Database File (SqlClient)) and also I have two more databases one for file managemnet and one for Calander and Contacts but I need all to be one so whenevr one user can login it can show his own file in his page but now everyone can see all,Can anybody guide me should it all be in one database and if yes how can I connect all since one is generated by default by Visual studio2005.Should I use a Microsoft SQL Server (SqlClient)???
Thanks in advance.
Hi all,
I need some help in combining two results. I am using the Northwind Database and the Orders Table. The first select outputs the table shown below, Table 1 and the second select outputs the result in the second table Table 2. How can I combine these two to get the third table, Table 3 ?
SELECT TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia1
FROM dbo.Orders
WHERE (ShipVia = 1)
GROUP BY EmployeeID
ORDER BY EmployeeID
Table 1 Results
EmployeeID CountShipVia1
1
82
2
71
3
81
4
116
5
29
6
48
7
44
8
75
9
29
SELECT TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia2
FROM dbo.Orders
WHERE (ShipVia = 2)
GROUP BY EmployeeID
ORDER BY EmployeeID
Table 2 results
EmployeeID CountShipVia2
1
44
2
36
3
45
4
70
5
15
6
25
7
24
8
48
9
19
Table 3 the desired result:
EmployeeID CountShipVia1 CountShipVia2
1
82 44
2
71 36
3
81 45
4
116 70
5
29 15
6
48 25
7
44 24
8
75 48
9
29 19
thanksrobby
hello everyone
there is a smalllll problem facing mee...well i want to combine the result of 2 queries together
, the queries are :
select x1,x2,x3 from Table1 inner join Table2 on Table1.x1=table2.y inner join table3 on table1.2 = table3.z where table1.anything = 5
and the other query
select x1, x2 from Table1 where table1.anything = 5
is there anyway????
Thank you
Hello everyone,
I'm having problems transfering data. I don't even know if this is even possible, but this is what I'm trying to do. I have two tables: ZipRegionUps, ZipRegionUsps. Both tables have the same two columns: Zip, Region.
I want to combine the two. Having one table ZipRegion with three columns: Zip, UpsRegion, Usps Region. I've tried everything I can think of, but no luck. Here's the most sensible Stored Procedure I have tried:
If I wasn't very clear with my explanation, I'm hoping the procedure will clear things up:
CREATE PROCEDURE CMRC_Databases_DataTransfer
AS
DELETE FROM CMRC_ZipRegionTest
INSERT INTO CMRC_ZipRegionTest
(
Zip,
UpsRegion,
UspsRegion
)
SELECT
CMRC_ZipRegionUps.Zip,
CMRC_ZipRegionUps.UpsRegion,
CMRC_ZipRegionUsps.UspsRegion
FROM
CMRC_ZipRegion,
CMRC_ZipRegionUsps
GO
Is there any way to do this? Or do I have to manually enter all the entries?
Any help would be great. Thank you.
-Alec
I have a transactions table that stores prices for products bought and sold.
If I want average buying prices I use:
SELECT AVG(price), product FROM transactions WHERE transactiontype=1 GROUP BY product
and for selling prices:
SELECT AVG(price), product FROM transactions WHERE transactiontype=2 GROUP BY product
Is there a way to combine this into one SQL query, to create one bindable dataset ?
I'm trying to combine the following two strings to create a single Insert statement (and thus only generate one record instead of two).
insertString = "Insert comments (uID) Select uID FROM users WHERE uName = @uName"
insertString2 = "INSERT comments (eventID, text) VALUES ( @eventID, @comment)"
I have tried:
Insert comments (uID, eventID, text) SELECT uID FROM users WHERE uName = @uName VALUES (uID, @eventID, @comment)
Individually they work fine, but I can't get the syntax correct to allow them to work together. As you can tell, I'm not very good with SQL, so any help would be greatly appreciated!
Thanks in advance.
I have 2 columns in a table and would like to combine
the 2 columns into 1 column separates by a delimiter.
Do anyone know the syntax??
Thanks, Vic
Hi,
Can anybody please tell how can I combing all records in field into one field.
For example
If my table is like
Final
_______
aaa
bbb
ccc
ddd
and i want result as
final1
_____
aaabbbcccddd
I do not want to use cursors for this. Please let me know if somebody knows the answer
Thanks
I have a database table tblobjects like this:
object_name, reference_id
a 1
a 2
a 3
a 4
b 2
b 3
b 1
b 4
c 2
c 4
c 5
c 6
d 2
d 4
d 5
d 6
I now would like to have a SQL query which gives me the number of
unique object and reference combinations, like this:
a
b
c
d shouldn't be displayed, because it's equal to c. The problem is also that a sequence of object references is
also important. So, for instance, object a shouldn't be equal to object b. The solution should also work is MS SQL and Mysql.
Any ideas how can I do this?
Thanks!
Is there an easy way to combine to SQL databases? Both DBs have the same structure but different data. If there just so happens to be duplicate records what will happen? Does anyone have any idea of where I should start at? :confused:
View 12 Replies View RelatedThese similar queries do much the same thing: the first one gets a list of ticket ID's that have been bought as 'standalone' tickets by a particular user, along with the total quantity they purchased. The second one also gets a list of ticket ID's along with the quantity purchased by that user, but the list of ID's is driven by tickets that appear in their basket as part of packages, instead of standalone tickets.
I hope that's clear; if not, maybe the SQL will make it clearer:
SELECT
[tblTickets].[id] AS TicketId,
SUM([tblBasket].[ticket_quantity]) AS SingleTicketsTotal
FROM
[tblOrders]
INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id]
INNER JOIN [tblTickets] ON [tblTickets].[id] = [tblBasket].[ticket_id]
WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @userID AND ([tblOrders].[order_status]=@purchasedOrder OR [tblOrders].[id]=@currentSessionOrder))
GROUP BY [tblTickets].[id]
SELECT
[tblCombinations_Tickets].[ticket_id] AS cTicketId,
SUM([tblBasket].[ticket_quantity]*[tblCombinations_Tickets].[quantity]) AS PackageTicketsTotal
FROM
[tblOrders]
INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id]
INNER JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblBasket].[combination_id]
WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @userID AND ([tblOrders].[order_status]=@purchasedOrder OR [tblOrders].[id]=@currentSessionOrder))
GROUP BY [tblCombinations_Tickets].[ticket_id]
I need to combine these. So that I get one result set with: ticketID, quantity bought as standalone, quantity bought as part of package.
I can't figure it out. I've tried inner joins, outer joins, left joins, right joins, nested subqueries and, briefly, banging on the screen. But every time, what happens is that I only get the rows where the ticket ID occurs in both queries. I need everything.
This has got to be laughably simple. But I'm stuck :( Can anyone help?
Not concatenation, more... err.. I don't know what you'd call it.
SELECT
DISTINCT [C01241 Opened].[Col004] AS OpenerEmail,
[C01241 External Data].[DMCEMAIL] AS ExternalDataEmail,
[C01241 Internal Data].[Col15] AS InternalDataEmail
FROM [C01241 Opened]
LEFT JOIN [C01241 External Data] ON [C01241 External Data].[DMCEMAIL] = [C01241 Opened].[Col004]
LEFT JOIN [C01241 Internal Data] ON [C01241 Internal Data].[Col15] = [C01241 Opened].[Col004]
(Apologies for the table/col names, this is all very temporary)
So I've got a table, [C01241 Opened], which details all the people who registered. Those people might turn up in table [C01241 External Data], or they might turn up in [C01241 Internal Data]. Yes, they will always be in one or the other, and no, they won't appear in both.
At the moment, I just pull in the email address. But the client, of course, wants a whole bunch of fields that occur in the 'original data' tables: Firstname, Lastname, Company, Favourite color, etc.
What I want to know is if - and how - I can make the query output one column for each of the required fields, but populate it from either of the two 'original data' tables, depending on where their email address pops up in.
Does that make sense?
If I have two tables with the following data:
Table_A
A
B
C
Table_B
1
2
3
is there a way to make a select the gives me this result(in separate columns):
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3
I have this data I need to query where if there is more than one startdate for a person, I need to get the earliest startdate, however get the latest enddate and money associated with that enddate. Highlighted in blue is an example of the values I need to return within one record.
Personstartdateenddate Money
7d3397/1/201412/31/2014 1000
7d3391/1/20145/23/2014 355
Hi,
I was wondering if there's a way to combine last, first and middle name together in one field instead of three different ones?
Hello,
I was wondering what is the best way to have multiple joins?
Here are the two statements I've been trying to combine
--------------------------------------------------------------------
SELECT CASE when
t1.Cust_DB_Shipment_Key = 'Used:' Then Description_1
Else (stuff(t1.Cust_DB_Shipment_Key,1,5,''))
End,
t1.Airway_Bill_No,
t1.Shipper_Reference,
t1.External_Product_Cd,
t1.Chargeable_Weight,
dt.CountTrackingNumber,
dt.SumProductCharge,
t1.Consignee_Company_Name,
CONVERT(CHAR(8),Ship_DT,112)
FROM Shipping t1
Inner Join
(
SELECT Cust_DB_Shipment_Key,
sum(PRODUCT_CHARGE_AMOUNT) as [SumProductCharge],
count(Airway_Bill_No) as [CountTrackingNumber]
FROM Shipping
Where Ship_DT = '2008-05-12' and status != 'voided'
GROUP BY Cust_DB_Shipment_Key
) dt
ON (stuff(t1.Cust_DB_Shipment_Key,1,5,'')) =
(stuff(dt.Cust_DB_Shipment_Key,1,5,''))
Where Ship_DT = '2008-05-12' and status != 'voided'
----------------------------------------------------------------------
SELECT CASE when
Cust_DB_Shipment_Key = 'Used:' Then Description_1
Else (stuff(Cust_DB_Shipment_Key,1,5,''))
End,
Airway_Bill_No,
Shipper_Reference,
Service_Name,
Chargeable_Weight,
Consignee_Company_Name,
CONVERT(CHAR(8),Ship_DT,112)
FROM Shipping
s JOIN Shipping..Distinct_Service_by_SAS_Code d ON s.External_Product_CD = d.SAS_Code
Where Ship_DT = '2008-05-12' and status != 'voided'
Thanks,
Stephen
Hello everybody,
I have the following problem.
I have a database containing about 300 million record made out of 4 years worth of Exchange Logfiles.
I would like to make a query that retreives al send emails, its recipients and message_size.
SELECT msgid, sender_address, recipient_address, number_recipients, total_bytes FROM tbllogfiles
Results:
MSGID, SA , RA , NR , TB
5566 me@domain.nl 1@domain.nl 3 55
5566 me@domain.nl 2@domain.nl 3 55
5566 me@domain.nl 3@domain.nl 3 55
But i'd like to get;
MSGID, SA , RA , NR , TB
5566 me@domain.nl 1@domain.nl,3@domain.nl,2@domain.nl 3 55
Can anyone help me with this query?
Best Regards,
Sidney
I need to combine two queries into one.
Query 1 (main query)
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.Name, dbo.Job.ChangeDate,
dbo.Job.Active,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production
FROM dbo.job
left outer join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid
left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid
left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
left outer JOIN dbo.Item ON Event.ItemGuid = dbo.Item.ItemGuid
inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
WHERE dbo.Job.CompanyJobId = 3505048
and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')
GROUP BY dbo.Job.CompanyJobId, dbo.job.name, dbo.Job.ChangeDate, dbo.job.Name, dbo.Job.Active
Result
3505048
SR 434 T-5201SR 434 T-5201
2007-10-11 16:36:45.647
Y
1314.26 (material qty)
1569.26 (production qty)
(where 1314.26 is sum material and 1569.26 is production)
Query 2
selectsum(EmployeeLaborEvent.Hours) as hours
fromdbo.job
left outer join dbo.Event ON dbo.Job.JobGuid = Event.JobGuid
Left outer join dbo.EmployeeLaborEvent ON Event.EventGuid = dbo.Employeelaborevent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048
Result:
1647.50 (which are sum of hours, this figure is correct)
Now I try to merge query 2 into Query 1 like this:
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.Name, dbo.Job.ChangeDate,
dbo.Job.Active,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production,
sum(EmployeeLaborEvent.Hours) as hours
FROM dbo.job
left outer join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid
left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid
left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
left outer JOIN dbo.Item ON Event.ItemGuid = dbo.Item.ItemGuid
inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
left outer join dbo.EmployeeLaborEvent ON Event.EventGuid = dbo.Employeelaborevent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048
and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')
GROUP BY dbo.Job.CompanyJobId, dbo.job.name, dbo.Job.ChangeDate, dbo.job.Name, dbo.Job.Active
When I run the query the result is:
3505048
SR 434 T-5201SR 434 T-5201
2007-10-11 16:36:45.647
Y
1314.26(material)
1569.26 (production)
NULL (hours)
The material and production stay the same (and is correct). Hours are wrong.
Any clues? Thank you.
Hi
What is the best way to add two columns into a single column. Lets say I have two columns with first and last names. How can I have a new column with first,last name??
Thanks
Hello,
I have two tables that I would like to combine but with some rules.
Table 1 has two columns with Actual Time and Actual People
Table 2 has two columns also with Planned Time and Planned People
I would like to combine these two tables if the actual time is not more than 30 minutes from a planned time
If I use one of the planned times, i would not want to show it again, even if there is another actual time that is not more than 30 minutes of it.
For ex.
Planned Time has 20:00,23:00
Planned People has 2,5
Actual Time has 19:00,19:30,19:45,21:15
Actual People has 5,2,5,3
Output should be like
Planned_Time,Actual_Time,Planned_People,Actual_People
,19:00,,5
20:00,19:30,2,2
,19:45,,5
,21:15,,3
23:00,,5,
I can do this in asp but if this can be done in sql it would be better.
Any ideas appreciated.
Thanks.