Combining Two Select Staements

Feb 25, 2004

Folks

I have two select statements which gives two counts ie two numbers.


select 'count' = count(*) from account

select 'count1' = count(*) from employee


I want to combine these two select statements and write one select statement where I can get two columns 'count' and 'count1' with the respective values.

The result should be EX:

count count1
3 5


Thanks

View 1 Replies


ADVERTISEMENT

Combining Select Staements

Mar 1, 2004

Folks

I have three select statements. I want to display q_text based on
the respective where condition. How do i combine these three and write
as one select statement.


select q_text Questions from question
where new_account_flag = '1'

select q_text Questions from question
where disc_account_flag = '1'

select q_text Questions from question
where disc_account_flag = '0'

Remember that all the queries returns more than 1 value.


I tried to use
select (query1),
(query2),
(query3)
but because it is returning more than one value, there is error.


Can any suggest me any other syntax??


Thanks

View 2 Replies View Related

Combining 2 Select With Count And Datediff Into 1 Select. Need Help.

Jun 21, 2006



I have created two select clauses for counting weekdays. Is there a way to combine the two select together? I would like 1 table with two columns:

Jobs Complete Jobs completed within 5 days

10 5

-------------------------------------------------------------------------------------------------

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @SDCompleted) AND (SDCompleted < @SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

---------------------------------------------------------------------------------------

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @SDCompleted) AND (SDCompleted < @SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

View 9 Replies View Related

Combining Two Select Statements

Apr 2, 2007

I have a SP returning the following result The select statement for this is



Code:

          SELECT dbo.TEST1.[OFFICE NAME], COUNT(dbo.TEST1.[ACCOUNT ID]) AS AccountCount
FROM dbo.Test2 INNER JOIN
dbo.test3 INNER JOIN
dbo.Test4 ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code] INNER JOIN
dbo.TEST1 ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID] ON dbo.Test2.[Model ID] = dbo.test3.ID INNER JOIN
dbo.[Inquiry Details] ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryID
WHERE (dbo.Test2.InquiryDate BETWEEN CONVERT(DATETIME, @startDate, 102) AND CONVERT(DATETIME, @endDate, 102)) AND dbo.Test1.[Account ID] IN(SELECT [account id] FROM test5 WHERE [Contact ID] = @contactId)
GROUP BY dbo.TEST1.[OFFICE NAME]
ORDER BY COUNT(dbo.TEST1.[ACCOUNT ID]) DESC  name id count case1 226 320 case2 219 288 case3 203 163 case4 223 90 case5 224 73 i have another select stnat which returns like this The select statement is



Code:

Select  test1.[office name], count(test1.[office name]) From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code] Group by test1.[Office Name] order by count(test1.[office name]) DESCname count case6 10 case2 56 case4 66 case1 74 case3 88 case7 100 case5 177 How can i combine this select stament with the SP, so that, i get a fourth column with case1 226 320 74 case2 219 288 56 .......................... ........................... Hope i am not confusing you all Please help me, if someone knows how to combine this? Thanks

View 2 Replies View Related

Combining Select-Statements

Sep 5, 2006

Hi !

I have to divide and analyse a bigger table to get a smaller one. My target is to make this division in only one SQL-Statement.

My table looks like this:


Code:


ArtNo Code
16637 C
38827 A
16637 D
44883 C
44883 C
63853 H
24564 D
24564 A


(ArtNo is not the Primary Key)

My SQL-Statement should now find out how often every "code" belongs to a "ArtNo".

The result should be:

Code:



ArtNr A C D H
16637 1 1
38827 1
34343 1
44883 2
63853 1
24564 1 1


Does anyone have ANY Idea how I could realize this as easy as possible (without View etc.) ??

Regards
Gawan

View 1 Replies View Related

Combining Fields In A Select

Sep 19, 2005

I have:
Dim queryString As String = "SELECT distinct [CSULOG5].[status] + [CSULOG5].[lmca_nbr] FROM [CSULOG5]" I even tried & instead of +.
but it doesn't like it. Is it legit syntax for mssqlserver?
This is a vb project aspnet

View 7 Replies View Related

Combining Two SELECT Statements (How To?)

Mar 26, 2008

I have two select statements on a single table as follows:


SELECT * FROM DOCUMENTS

WHERE FILEDATE LIKE '%1987'


SELECT DOCNUM, COUNT(*) AS TOTALS FROM DOCUMENTS

GROUP BY DOCNUM

HAVING (COUNT(*)>1)


I want to combine the them to provide a list of rows in the table that have duplicate "DocNum" but only within the subset of rows LIKE %1987. When I first looked into how this is done I was sure that you would use a subquery. I tried to combine them using the first one as the outer query and the COUNT as the subquery. However, this gave an error that the subquery was returning a value that did not match what the outer query was expecting. So my questions are: Do I really need a subquery in the first place since this is all within one table? And, if not, how can this be done?

Thanks!

DeBug

View 12 Replies View Related

Multiple Sql Staements In SqlDataSource's Update Command

May 5, 2006

I have a gridview with a sqlDataSource with the SelectCommand as
"SELECT Movie.Title, Movie.Category, Movie.ReleaseDate, ItemForSale.Quantity, ItemForSale.HasUnLimitedQuantity FROM ItemForSale INNER JOIN Movie ON ItemForSale.ID = Movie.ID"

what kinda 'UpdateCommand' do I set so that ItemForSale is also updated from the grid? I tried two update statements seperated with a semicolon but that wouldn't work, any suggestions...

View 3 Replies View Related

Combining Multiple Select Statements In A SP

Jul 27, 2007

I was wondering if it's possible to have a stored procedure that has two select statements which you can combine as a single result set.  For instance:select name, age, titlefrom tableaselect name, age, titlefrom tablebCould you combine these queries into a single result set? 

View 2 Replies View Related

Combining 2 SELECT Statements (with JOINs) Into 1

Sep 17, 2013

I'm looking for a way to combine the following 2 sets of code into one select statement. They're similar in that they reference the same tables, but they have different conditionals:

Code:
SELECT TABLE_01.Date
, TABLE_01.State
, TABLE_01.City
, ISNULL((SUM(A.Bandwidth)),0) AS SD_Bandwidth
INTO TABLE_FINAL_01
FROM TABLE_01
LEFT OUTER JOIN TABLE_02 A

[Code] .....

View 4 Replies View Related

Select Statement Combining Rows

Mar 30, 2008

I need some help with a query . I have two tables "config" and "item".

I have table config as follows:
ConfigID
ItemID

With Values
ConfigID ItemID
14583 2776
14583 2798
14583 3112

And table item as follows:
ItemID
ItemTypeID
ItemValue

With Values
ItemID ItermTypeID ItemValue
2776 1 123
2798 2 ABC
3112 3 789

So the query:
SELECT ConfigID,
(SELECT ItemValue WHERE ItemTypeID = '1') AS Model,
(SELECT ItemValue WHERE ItemTypeID = '3') AS Minor,
(SELECT ItemValue WHERE ItemTypeID = '2') AS Customer
FROM config c, item i
WHERE ConfigID = '14583'
AND c.ItemID = i.ItemID

Produces the result:
ConfigID Model Minor Customer
14583 123 NULL NULL
14583 NULL NULL ABC
14583 NULL 789 NULL

How do I change the above query to get one row:
ConfigID Model Minor Customer
14583 123 789 ABC

Thanks for your help

View 6 Replies View Related

'combining' Values From A Select Statement Into A String

Jan 10, 2008

Hi,

I got a really simple question here. Say I have a table with

ID, Name
1, A1
2, A2
3, A3
....
10, A10

Now I want to combine the names into another table grouped by their ID (say 1-5, 6-10), so this new table has two names instead of 10:
A1 A2 A3 A4 A5
A6 A7 A8 A9 A10

Is there a function that allows me to 'combine' the names from a select statement?

Thanks in advance,
Steven

View 6 Replies View Related

Combining Databases

Jun 5, 2007

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.
 

View 5 Replies View Related

Need Help In Combining Results ..

Oct 22, 2007

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 

View 5 Replies View Related

Combining 2 Sql Queries

Nov 22, 2003

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

View 2 Replies View Related

Combining Tables...

May 9, 2004

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

View 1 Replies View Related

Combining Two Queries

Jan 23, 2006

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 ?

View 2 Replies View Related

Help With Combining Sql Statements

Mar 7, 2006

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.

View 2 Replies View Related

Combining Columns

Mar 3, 2000

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

View 1 Replies View Related

Combining Records

Aug 27, 2007

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

View 2 Replies View Related

Combining Records

Dec 18, 2006

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!

View 1 Replies View Related

Combining 2 SQL Databases

Dec 13, 2004

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 Related

Combining Two Queries

Apr 12, 2006

These 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?

View 3 Replies View Related

'Combining' Fields

Jul 12, 2006

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?

View 3 Replies View Related

Combining Two Tables

May 15, 2007

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

View 2 Replies View Related

Combining Two Rows Into One

Jul 23, 2014

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

View 2 Replies View Related

Combining Two Fields Together

Apr 3, 2008

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?

View 9 Replies View Related

Combining 2 Different Joins

May 13, 2008

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

View 4 Replies View Related

Combining Columns

Jun 19, 2008

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

View 3 Replies View Related

Combining Two Queries

Jun 19, 2008

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.

View 4 Replies View Related

Combining Columns

Aug 29, 2005

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

View 8 Replies View Related

Combining Tables

Feb 2, 2006

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.

View 4 Replies View Related

Combining Two Tables

Feb 5, 2007

hi

this is my query:
tabel1:
select userid,user_name,password,role_code,convert(varchar,expiry_date,101) as expiry_date,created_date,active from usermaster where userid='1' and active='1'
if i run this query i will get output like this:
userid user_name password role_code expiry_date created_date
1 karthik karthik AD 01/17/20082007-01-24
active
0:00:00.000 1

i have another table which has the following records

tabel 2:
select * from code master

codename codedescription
AD admin
sp supervisor

so in the first query i need the output as like tithe following:

userid user_name password role_code expiry_date created_date
1 karthik karthik admin 01/17/20082007-01-24
active
0:00:00.000 1

so how to combine this two tables inorder to get the codedescription as admin for the roll_code=ad

so please give me query for this

View 18 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved