Combine Two Datatables
Mar 20, 2008
Hi,
I have a Excel file as data source. On sheet 1 there are some header information. On sheet 2 are a quiet simple data table. Both sheets will be read into memory with SSIS.
Now i want to combine some header information with the data table.
Sheet 1:
Unit A, 3/20/2008, Group 1
Sheet 2:
34‚¬, 56‚¬, 12‚¬
43‚¬, 43‚¬, 12‚¬
32‚¬, 42‚¬, 13‚¬
...
This should be result:
34‚¬, 56‚¬, 12‚¬, Unit A, 3/20/2008, Group1
43‚¬, 43‚¬, 12‚¬, Unit A, 3/20/2008, Group1
32‚¬, 42‚¬, 13‚¬, Unit A, 3/20/2008, Group1
...
How can I do this?
Regards
Johannes.W
View 3 Replies
ADVERTISEMENT
Jan 12, 2007
VS05
How are dates removed / nullified?
I have a SQL datetime field that is being editted via a datatable / adapter - The table structure defines the datetime as a date.
From here I want to remove the date - i.e. write DBNull back to the database. Setting the Date to Nothing or .minvalue results in a min date exception and DBNull cannot be cast to the datetime either.
R
View 3 Replies
View Related
Oct 24, 2007
I am still confounded with how to create a VIEW involving multiple DATATABLES in a DATASET. Could someone point out a MSDN page where I can see this working and how I go about doing this? Let me re-iterate - I want to create a VIEW that uses JOIN type statements that involves multiple DATATABLES in my loaded DATASET.
View 1 Replies
View Related
Feb 18, 2008
I dragged two tables into the dataset designer. And I have a query with a resultset over both of the tables (joined).Is there ANY way I can have a strongly typed datatable out of the resultset?The automatically created dataset adapters won't allow me to create a tableadapter or datatable for the joined resultset but for one single table at a time only.Any solution to that? Theoretically it should be possible since I don't "lose" any type information with a join.. why can't I have a strongly typed datatable over the resultset?The join is not very sophisticated. In fact it couldn't be any more simple.(I'm refering to my previously posted question at http://forums.asp.net/t/1220481.aspx)
View 4 Replies
View Related
Mar 21, 2007
Good Day:
I have a business object with a set of public DataTables and Public Strongly Typed Dataset DataTable Types defined. When I add the business object class to the report as a datasource, only public System datatypes (int, string) show up. I added System.Data to the report properties Assemblies, with no change. Any ideas?
Thanks,
Omar
View 1 Replies
View Related
Feb 1, 2008
Hi, I am new to Crystal Reports. I have a dataset with three datatables that are related. How can I do a subquery on these tables and display its result on my Crystal report ?
View 1 Replies
View Related
May 25, 2006
The test sub below operates on a SQL Server Table with an xml-type field ("xml"). The purpose of the sub is to learn about storing and retrieving a whole xml document as a single field in a SQL Server table row.When the code saves to the xml field, it somehow automagically strips the xml.document.declaration (<?xml...>). So when it reads the xml field back and tries to create an xmldocument from it, it halts at the xmldocument.load.I order to get the save/retrieve from the xmlfield to work, I add the <?xml declaration to the string when I read it back in from the xml field (this is in the code below).At that point the quickwatch on the string I'm attempting to load into the xmldocument is this:-----------------------------------------------------<?xml version="1.0" encoding="utf-16" ?><Control type="TypeA"><Value1><SubVal1A>Units</SubVal1A><SubVal1Btype="TypeA">Type</SubVal1B></Value1><Value2><SubVal2A>Over</SubVal2A><SubVal2B>Load</SubVal2B></Value2></Control>-----------------------------------------------------The original xml document string is this:-----------------------------------------------------<?xml version="1.0" encoding="utf-16" ?><Control type="TypeA"> <Value1> <SubVal1A>Units</SubVal1A> <SubVal1B type="TypeA">Type</SubVal1B> </Value1> <Value2> <SubVal2A>Over</SubVal2A> <SubVal2B>Load</SubVal2B> </Value2></Control>-----------------------------------------------------which seems to have all the same characters as the quickwatch result above, but clearly is formatted differently because of the indenting.THE FIRST QUESTION: Is there a simpler way to do this whole thing using more appropriate methods that don't require adding the xml.document.declaration back in after reading the .xml field, or don't require using the memorystream to convert the .xml field in order to load it back to the XML document.THE SECOND QUESTION: Why does the original document open in the browser with "utf-16", but when I write the second document back to disk with "utf-16" it won't open...I have to change it to "utf-8" to open the second document in the browser.Here's the test sub'============================================ Public Sub XMLDSTest() '=========================================== Dim ColumnType As String = "XML" '=========================================== '----------Set up dataset, datatable, xmldocument Dim wrkDS As New DSet1() Dim wrkTable As New DSet1.Table1DataTable Dim wrkAdapter As New DSet1TableAdapters.Table1TableAdapter Dim wrkXDoc As New XmlDocument wrkXDoc.Load(SitePath & "App_XML" & "XMLFile.xml") Dim str1 = wrkXDoc.OuterXml Dim wrkRow As DSet1.Table1Row wrkRow = wrkTable.NewRow '=======WRITE to SQL Server============== '------ build new row With wrkRow Dim wrkG As Guid = System.Guid.NewGuid TestKey = wrkG.ToString .RecordKey = TestKey .xml = wrkXDoc.OuterXml '<<< maps to SQL Server xml-type field End With '----- add row to table and update to disk wrkTable.Rows.Add(wrkRow) wrkAdapter.Update(wrkTable) wrkTable.AcceptChanges() '----- clear table wrkTable.Clear() '=======READ From SQL Server ============== '----refill table, read row, wrkAdapter.FillBy(wrkTable, TestKey) Dim wrkRow2 As DSet1.Table1Row = _ wrkTable.Select("RecordKey = '" & TestKey & "'")(0) '===== WRITE TO New .xml FILE =========================== Dim wrkS1 As New StringBuilder Select Case ColumnType Case "XML" '---if xml build xml declaration: '---add this to xml from sql table => <?xml version="1.0" encoding="utf-16" ?> wrkS1.Append("<?xml version=" & Chr(34) & "1.0" & Chr(34)) wrkS1.Append(" encoding=" & Chr(34) & "utf-16" & Chr(34) & " ?>") wrkS1.Append(wrkRow2.xml) End Select Dim wrkBytes As Byte() = (New UnicodeEncoding).GetBytes(wrkS1.ToString) Dim wrkXDoc2 As New XmlDocument Dim wrkStream As New MemoryStream(wrkBytes) wrkXDoc2.Load(wrkStream) '=========================================== '---- this just shows that the file actually was touched Dim wrkN2 As XmlNode = wrkXDoc2.CreateNode(XmlNodeType.Text, "ss", "TestNode2") wrkN2 = wrkXDoc2.SelectSingleNode("//Value1/SubVal1B") wrkN2.Attributes("type").Value = "This was from the xml field" '---------------- '------ update the encoding....otherwise the file won't open in the browser with utf-16 Dim wrkN1 As XmlNode = wrkXDoc2.CreateNode(XmlNodeType.Element, "ss", "TestNode") wrkN1 = wrkXDoc2.FirstChild wrkN1.InnerText = Replace(wrkN1.InnerText, "utf-16", "utf-8") '------------Now write the file back as an .xml file Dim wrkFilePath As String = SitePath & "App_XML" & "XMLFile2.xml" Dim wrkXW As XmlWriter = XmlWriter.Create(wrkFilePath) wrkXDoc2.WriteContentTo(wrkXW) wrkXW.Close() End Sub===============================
View 8 Replies
View Related
Jan 30, 2008
I'm writing a Windows application (Visual Studio 2005, c#) utilizing a local SQLExpress database. It consists of about 10 tables and I've created about 15 Stored Procedures to address various functions... I can run the update Stored Procedures interactively within the designer and the data tables update as designed. However, if I run the update Stored Procedures from within my windows application is where they fail. I get no error messages, if I return a rowcount variable from the Stored Procedure it tells me that one row was updated (SELECT @RtnVal == @@rowcount)... but when I open the subject table in the designer, there is no new data.
My update queries address both insert and update functions, so if it is new row of data, it performs the insert action, otherwise it updates an existing row.
I can query the data using my stored procedures to load default values into my windows form, I can search and find client records to display in the form... I just can't update records. I'm of the opinion that it is a rights issue, but I can't find any resources that address user access accounts with SQLExpress and windows apps. The current connection string for my local database is set for Integrated Security = true; User Instance = true
I've tried two approaches... one utilizes my stored procedure....
bool bSave;
SqlConnection conn = new SqlConnection(KadaDesk.Properties.Settings.Default.dbKadaConnectionString.ToString());
SqlCommand cmd = new SqlCommand("SavAuthTesterData", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CtrId", SqlDbType.NVarChar, 20).Value = TestCenterID.ToString();
cmd.Parameters.Add("@AT_Id", SqlDbType.NVarChar, 15).Value = GnuAuthTesterId.ToString();
cmd.Parameters.Add("@AT_Name", SqlDbType.NVarChar, 50).Value = GnuAuthTesterName.ToString();
cmd.Parameters.Add("@AT_Pwd", SqlDbType.NVarChar, 15).Value = GnuAuthTesterPwd.ToString();
cmd.Parameters.Add("@Maint", SqlDbType.NChar, 1).Value = GnuAuthTesterStatus.ToString();
cmd.Parameters.Add("@ModBy", SqlDbType.NVarChar, 15).Value = sAuthTester.ToString();
try
{
conn.Open();
cmd.ExecuteNonQuery();
bSave = true;
}
Then I tried a direct insert...
SqlConnection conn = new SqlConnection(KadaDesk.Properties.Settings.Default.dbKadaConnectionString.ToString());
SqlCommand cmd = new SqlCommand();
SqlTransaction myTrans;
conn.Open();
cmd.Connection = conn;
myTrans = conn.BeginTransaction();
cmd.Transaction = myTrans;
string disDte = DateTime.Now.ToShortDateString();
try
{
string sCmdText = "INSERT INTO Tester (CenterId, AuthTesterId, AuthTesterName, AuthTesterPwd, "
+ "CreateDte, Maint, ModBy) "
+ "VALUES ('" + TestCenterID.ToString() + "','"
+ GnuAuthTesterId.ToString() + "','"
+ GnuAuthTesterName.ToString() + "','"
+ GnuAuthTesterPwd.ToString() + "','"
+ disDte.ToString() + "','"
+ GnuAuthTesterStatus.ToString() + "','"
+ sAuthTester.ToString() + "')";
cmd.CommandText = sCmdText;
cmd.ExecuteNonQuery();
myTrans.Commit();
bSave = true;
}
Both fail... which points to the only common point in both approaches, the connection string and user rights... but I can't find any place to address user accounts or configurations for windows apps.
Thanks for whatever help you can offer...
Jim-
View 5 Replies
View Related
Oct 10, 2005
I can use the IN with the WHERE clause as example:SELECT * FROM NORTHWIND WHERE LASTNAME IN ('FULLER','KING')I want to use the IN and LIKE at the same time:SELECT * FROM NORTHWIND WHERE LASTNAME LIKE ('A%','B%')I know this is a simplistic example, but the members for the IN will bemany, 5 to 10.I'm trying to avoid:SELECT * FROM NORTHWIND WHERE LASTNAME LIKE 'A%' OR LASTNAME LIKE 'B%'OR LASTNAME LIKE 'FU%' OR LASTNAME LIKE 'JON%' <...>and so forth.Any Ideas?TIARob
View 3 Replies
View Related
Aug 23, 2007
Hai All,
Could some one help me how to combine IN & LIKE in the query selection,
i'v try many syntac and i just have no find the way out
this is what I would like to write:
Select DSCRIPTN,ORMSTRID,ACTINDX
from T50001
where ACTINDX = 350
and DSCRIPTN Like in '%'+(select distinct rtrim(ORMSTRID) ORMSTRID from L10001)+'%'
View 3 Replies
View Related
Feb 20, 2007
Hi all,
I have a table with multiple rows with the same ID.
a) How do I combine all columns into one row with the same ID?
b) Is this better to do the combine in the store procedure/trigger or a sub that involked by some sort of datarepeater, datagrid controls?
Since I am trying to brush up my sql. I appreciate any examples on top of conceptual solution.
Thanks
View 6 Replies
View Related
Jun 14, 2007
Combine 3 Databases, Not tables.
Let me spell this out -- I have 3 databases (they are in isolation when in use, its a field app) that need to be merged into 1 "masterDB" database. I've discovered I can use something like this to get to each DB in a query...
1 USE [database1]2 SELECT table1.Name, table1.Location, table1.Date, table2.Blog3 FROM table2 INNER JOIN4 table1 ON table2.ID = table1.ID5 ORDER BY table1.Date
and then just repeat for database2 and database3. Ok, fine, rah rah. My question is how do I "merge" all of these into 1. No data on each db will be identical, at all, ever so that is not a concern. I just need all the data from db1, 2 and 3 into masterDB.
Ideas? Direction?
View 2 Replies
View Related
Feb 19, 2008
Hello, I have these variables on my page:
userid = "10101"
RequestHost = "example.com"
What would be the best way performace wise to first check if the userid 10101 exists in my sql server db. If it does exist I would then need to check if "example.com" exists for the userid in the userdomains table. If these both exist I would then like to query some additional data. I was hoping its possible to combine this into one query somehow. I dont think this is the best solution:
sqlcommand.CommandText = "SELECT UserId From Users Where UserID = '10101'"
Conn.Open()
dr = sqlcommand.ExecuteReader
if dr.hasrows then
sqlcommand2.CommandText = "SELECT UserDomain From UserDomains Where UserID = 'example.com'"
dr2 = sqlcommand2.ExecuteReader
if dr2.hasrows then
sqlcommand3.CommandText = 'Select Additional Data
dr3 = sqlcommand3.ExecuteReader
'read values
conn.close
else
conn.close
'do something
end if
else
conn.close
'do something
end if Thanks Very Much!
View 2 Replies
View Related
Dec 7, 2005
I have an stored procedure that returns 3 columns. Month, Date, and Total Number of Calls.
Here is the stored Proc:
SELECT DATEPART(mm, CALLSTARTTIME) , DATEPART(dd, CALLSTARTTIME), COUNT(*)
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1') AND (PINCODE IS NOT NULL)
GROUP BY DATEPART(mm, CALLSTARTTIME), DATEPART(dd, CALLSTARTTIME)
ORDER BY DATEPART(mm, CALLSTARTTIME), DATEPART(dd, CALLSTARTTIME)
It returns a table:
MONTH DATE TOTAL NUMBER OF CALLS======= ===== ===========1 1 10
1 2 15
My question is: is it possible to combine the Month and Date column into one column. e.g.
Date Total Number of Calls==== ==============1/1 101/2 15
Please Help, Thanks in advance :)
View 2 Replies
View Related
Mar 27, 2002
I am looking for the correct T-sql statement. I want to put parameters in a SP from a select statement. And make the SP exec for each records that the select statement returns. The following is the SP that I want to pass the parameters.
CREATE PROCEDURE sp_sendSMTPmail (@To varchar(8000),
@Subject varchar(255),
@Body text = null,
@Importance int = 1, -- 0=low, 1=normal, 2=high
@Cc varchar(8000) = null,
@Bcc varchar(8000) = null,
@Attachments varchar(8000) = null, -- delimeter is ;
@HTMLFormat int = 0,
@From varchar(255) = null)
/* Name: sp_sendSMTPmail
Purpose: Send an SMTP mail using CDONTS object.
Requirements: SMTP server (comes with IIS but doesn't require IIS) must be loaded.
Returns: 0 if successful, 1 if any errors
Sample Usage:
sp_sendSMTPmail 'vince.iacoboni@db.com', 'Testing', 'testing sp_sendSMTPmail, please reply if you receive this',
@cc='irmsqlmail@db.com',
@Importance=1,
@Attachments='c:oot.ini;c:autoexec.bat'
History:
02/07/2001 VRI Created.
*/
AS
SET NOCOUNT ON
DECLARE @object int,
@hr int,
@StrEnd int,
@Attachment varchar(255),
@return int,
@Msg varchar(255)
SELECT @From = isnull(@From, @@SERVERNAME)
-- Create the CDONTS NewMail object.
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT
IF @hr <> 0 GOTO ObjectError
-- Add the optional properties if they are specified
IF @Body IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Body', @Body
IF @hr <> 0 GOTO ObjectError
END
IF @Cc IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Cc', @Cc
IF @hr <> 0 GOTO ObjectError
END
IF @Bcc IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Bcc', @Bcc
IF @hr <> 0 GOTO ObjectError
END
IF @HTMLFormat <> 0
BEGIN
EXEC @hr = sp_OASetProperty @object, 'MailFormat', 0
IF @hr <> 0 GOTO ObjectError
END
-- Loop through the ; delimited files to attach
CREATE TABLE #FileExists (FileExists int, FileIsDir int, ParentDirExists int)
WHILE isnull(len(@Attachments),0) > 0
BEGIN
SELECT @StrEnd = CASE charindex(';', @Attachments)
WHEN 0 THEN len(@Attachments)
ELSE charindex(';', @Attachments) - 1
END
SELECT @Attachment = substring(@Attachments, 1, @StrEnd)
SELECT @Attachments = substring(@Attachments, @StrEnd+2, len(@Attachments))
-- Ensure we can find the file we want to send.
DELETE #FileExists
INSERT #FileExists
EXEC master..xp_fileexist @Attachment
IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
BEGIN
RAISERROR ('File %s does not exist. Message not sent.', 16, 1, @Attachment)
RETURN 1
END
EXEC @hr = sp_OAMethod @object, 'AttachFile', NULL, @Attachment
IF @hr <> 0 GOTO ObjectError
SELECT @Msg = 'File ' + @Attachment + ' attached.'
PRINT @Msg
END
-- Call the Send method with parms for standard properties
EXEC @hr = sp_OAMethod @object, 'Send', NULL, @From, @To, @Subject, @Importance=@Importance
IF @hr <> 0 GOTO ObjectError
-- Destroy the NewMail object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0 GOTO ObjectError
PRINT 'Message sent.'
RETURN 0
ObjectError:
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN 1
END
GO
View 1 Replies
View Related
Sep 20, 2007
I have two tables in MS SQL 2000 that I need to combine into one. they will share 3 columns and the rest will stay the same. the existing tables are very large and I REALLY don't want to plug in all the data by hand...Any nifty tricks??? I found software but dont want to spend $$ on it.
View 5 Replies
View Related
Mar 5, 2005
Here is my dilemma, i'm trying to combine the results of two different tables. Both tables are very similar but the data returned must be sorted before they are combined because I'm only returning the top xx records based on a hits column.
Here is a sample of the two databases:
Table 1
ID - SONG - HITS
1 - tb1SONG 1 - 356
2 - tb1SONG 2 - 1459
3 - tb1SONG 3 - 278
4 - tb1SONG 4 - 965
5 - tb1SONG 5 - 124
Table 2
ID - tb2SONG - HITS
1 - tb2SONG 1 - 412
2 - tb2SONG 2 - 85
3 - tb2SONG 3 - 2035
4 - tb2SONG 4 - 693
5 - tb2SONG 5 - 745
I have tried the following union query which combines the two RS's then sorts the data:
SELECT Top 2 ID, Song, Hits FROM Table1
UNION SELECT Top 2 ID, Song, Hits from Table2
Which would return the first two records from each then sort them like this:
2 - tb1SONG 2 - 1459
1 - tb2SONG 1 - 412
1 - tb1SONG 1 - 356
2 - tb2SONG 2 - 85
I would like to sort based on the hits column then combine the RS producing this:
3 - tb2SONG 3 - 2035
2 - tb1SONG 2 - 1459
4 - tb1SONG 4 - 965
5 - tb2SONG 5 - 745
Any ideas or solutions will be greatly appreciated.
Thanks
View 2 Replies
View Related
Oct 30, 2006
I have two fields that I would like to combine into 1 field is this possible.
Example: Document # 555, Doc Description ABCD in the 3 field I would like 555-ABCD.
Is that possible in SQL Server thanks
View 4 Replies
View Related
Jun 2, 2004
SELECT bms_id,email_address,COUNT(*)
INTO #temp
FROM emp_db
WHERE email_address IS NOT NULL
GROUP BY bms_id,email_address
ORDER BY bms_id DESC,COUNT(*) DESC
SELECT bms_id COUNT(*)
FROM #TEMP
GROUP BY bms_id
ORDER BY COUNT(*) DESC
How can i put these two statements into a single sql statement.
Thanks.
View 3 Replies
View Related
Jun 16, 2008
I need to combine to sql queries. Separately they work fine, but I need the "total qty" from second query put into the first query
Query 1
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Region.CompanyRegionID, dbo.Job.Active,
dbo.Job.ChangeDate
FROM dbo.Job
LEFT OUTER JOIN dbo.Division ON dbo.Job.DivisionGuid = dbo.Division.DivisionGuid
LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
LEFT OUTER JOIN dbo.JobType ON dbo.Job.JobTypeGuid = dbo.JobType.JobTypeGuid
WHERE dbo.job.CompanyJobId = 3505048
ORDER BY dbo.Job.CompanyJobId
Query 2
SELECT case dbo.SourceType.CompanySourceTypeId
when 'PR' then SUM(dbo.ProductionEvent.Quantity)
end
AS Ttl_Qty
FROM dbo.Batch INNER JOIN
dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
dbo.Item ON dbo.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.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join
dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048 and dbo.SourceType.CompanySourceTypeId = 'PR'
GROUP BY dbo.SourceType.CompanySourceTypeId
I have tried this but it doe not work:
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Region.CompanyRegionID, dbo.Job.Active,
dbo.Job.ChangeDate, Ttl_Qty
FROM dbo.Job
LEFT OUTER JOIN dbo.Division ON dbo.Job.DivisionGuid = dbo.Division.DivisionGuid
LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
LEFT OUTER JOIN dbo.JobType ON dbo.Job.JobTypeGuid = dbo.JobType.JobTypeGuid
WHERE dbo.job.CompanyJobId = 3505048 and where Ttl_Qty =
(SELECT case dbo.SourceType.CompanySourceTypeId
when 'PR' then SUM(dbo.ProductionEvent.Quantity)
end
AS Ttl_Qty
FROM dbo.Batch INNER JOIN
dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
dbo.Item ON dbo.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.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join
dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048 and dbo.SourceType.CompanySourceTypeId = 'PR'
GROUP BY dbo.SourceType.CompanySourceTypeId)
ORDER BY dbo.Job.CompanyJobId
View 4 Replies
View Related
Dec 18, 2013
I have data as below:
IDJourneySegmentType Depart Arrive 1st2nd
1234511A UK AUS UKNULL
1234512A AUS US NULLUS
How can i make it to 1 row for 1st and 2nd column?
ID1st2nd
12345UKUS
View 2 Replies
View Related
Jun 12, 2006
Hi,
i have a huge db and i wanna combine "date" fields with "time" fields.
eg. date time
03/06/1979 1758
03/09/1979 1759
i wanna datetime
03/06/1979 17:58:00
03/09/1979 17:59:00
View 2 Replies
View Related
Nov 11, 2006
hai,
i have the data like this
Id [name] value
------------------------------
1 sam abc
1 sam def
1 sam ghi
2 john abc
2 john def
for a unique Id all the fields are same except the value.Now I want to join them and the data should appear like below.
Id [name] value
------------------------------------
1 sam abc,def,ghi
2 john abc,def
please help me regarding the query.
thanks
from,
pavansagar
View 3 Replies
View Related
Nov 5, 2007
I have 3 fields: DOB_DAY (ex: 15), DOB_MO (ex: 8), and DOB_YEAR (ex: 1975). I have a blank field named BIRTH_DATE.
What would be the SQL to set the BIRTH_DATE field to be equal to the Day, Month, and Year field. I need it in "DateTime" format.
Thanks
View 11 Replies
View Related
Nov 9, 2007
Hi,
I have a query that looks at the stock levels in one warehouse and returns the quantity, have been asked to create a new column that shows the total of the same stock that is available in our two other warehouses.
Have tried this:
SELECT ItemCode, WhsCode, InStock FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.WhsCode = TABLE1.WhsCode WHERE WhsCode = '31' or WhsCode = '61' GROUP BY InStock, WhsCode,ItemCode
This returns the results in one column rather than in a seperate column for 31 & 61, I then need to add the two columns together so a total stock is shown.
I realise this may be a basic query but I'm batting my head against a wall at the moment.
Thanks
Garry
View 3 Replies
View Related
Mar 25, 2008
Help please!
I have two statements getting data from two different tables.
1: select dataDate from table1,- gives ‘20080325’
2: select count (recordNumber) from table2,-- gives ‘4566’
Each table has one row but more than one column. The columns are not the same in each table.
Question. I need to combine two statements to give the outcome in one row as '200803254566'. '2008032' from first statement and '54566'from second statement. How do you combine them?
?
Josephine
View 2 Replies
View Related
Mar 29, 2008
Hi,
I wonder if anyone can help. All I am trying to do is combine three SQL SELECT queries into one. They are:
SELECT COUNT(ISNULL(Result, 0)) AS Win FROM Games WHERE (CurrentSeason = 'True') AND Result = 'Win'AND ([MatchType] = @MatchType)
SELECT COUNT(ISNULL(Result, 0)) AS Lose FROM Games WHERE (CurrentSeason = 'True') AND Result = 'Lose'AND ([MatchType] = @MatchType)
SELECT COUNT(ISNULL(Result, 0)) AS Draw FROM Games WHERE (CurrentSeason = 'True') AND Result = 'Draw'AND ([MatchType] = @MatchType)
As you can see they are all doing pretty much the same thing. I have experimented by using GROUP and HAVING but end up with no results. Sorry if this is obvious but I am new to SQL!
Many Thanks
View 2 Replies
View Related
Jul 23, 2005
I have a table that has two dates in it, a date opened and a dateclosed. I would like to create one query to give me the number ofrecords that have been opened each month plus, and this is the hardpart the number of those records that have been closed each month. Ican get the result with two seperate queries but have been unable toget it combined into one query with three values for each month, i.e.,the month, the number opened and the number of those that were openedin the month that have been subsequently closed.Here's my two queries. If anyone can help I'd appreciate.SELECT COUNT(*) AS [Number Closed], LEFT(DATENAME(m, DateOpened),3) + '' + CAST(YEAR(DateOpened) AS Char(5)) AS [Month Opened]FROM tableWHERE (DateClosed IS NOT NULL)GROUP BY CONVERT(CHAR(7), DateOpened, 120), LEFT(DATENAME(m,DateOpened), 3)+ ' ' + CAST(YEAR(DateOpened) AS Char(5))ORDER BY CONVERT(CHAR(7), DateOpened, 120)SELECT COUNT(*) AS [Number Opened], LEFT(DATENAME(m, DateOpened),3) + '' + CAST(YEAR(DateOpened) AS Char(5)) AS [Month Opened]FROM tableGROUP BY CONVERT(CHAR(7), DateOpened, 120), LEFT(DATENAME(m,DateOpened), 3)+ ' ' + CAST(YEAR(DateOpened) AS Char(5))ORDER BY CONVERT(CHAR(7), DateOpened, 120)TIABill
View 2 Replies
View Related
Oct 18, 2006
I have the following 3 SQL statements that need to be combined, ifpossible. The output of one feeds the input of the next. I need to viewall of the defined output fields (the output needs to be used in aCrystal Report).The SQL Follows:/* Input is ISBN (vendor_part_number) */QUERY_1 - returns 1 recordselect p.product_id, v.name, m.description, p.author, p.title,p.revision_number, p.copyright_edition, p.vendor_part_number,p.conforming_flag,m.code, mp.unit_price_product, mm.quota_pricefrom T_PRODUCT p, T_VENDOR v, T_PRODUCT_VENDOR pv,T_MULTILIST_PRODUCT mp, T_MULTILIST m,T_MULTILIST_MEMBERSHIP mm where/* p.vendor_part_number == input */p.vendor_part_number = '0153364475' and p.medium_type ='TEXTBOOK' andp.product_id = pv.product_id and pv.type = 'CONTRACT' andpv.vendor_id = v.id andp.product_id = mp.product_id andm.code = mp.multilist_code and m.proclamation_year =mp.proclamation_yearand m.proclamation_seq_id = mp.proclamation_seq_id andm.code = mm.multilist_code and m.proclamation_year =mm.proclamation_yearand m.proclamation_seq_id = mm.proclamation_seq_id/* The above should return a single record */QUERY_2 - returns 2 recordsselect p.product_id, p.consumable, p.title, p.copyright_edition,p.vendor_part_number, p.product_type,p.item_type, p.hardware_requiredfrom T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type ='AID'or pr.relationship_type = 'KIT') andp.product_id = pr.child_product_id and pr.parent_product_id =90321/* 90321 = result from above: pr.parent_product_id = p.product_id*/QUERY_3 - returns 18 recordsselect p.product_id, p.consumable, p.title, p.copyright_edition,p.vendor_part_number, p.product_type,p.item_type, p.hardware_requiredfrom T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type ='AID'or pr.relationship_type = 'KIT') andp.product_id = pr.child_product_id and pr.parent_product_id in(90322, 90323)/* 90322, 90323 = result from QUERY_2: pr.parent_product_id =p.product_id */Only 21 records are returned from these combined queries. I need accessto all of them even though there are 3 different resultsets, 2 of whichcontain the same fields. Is there a way to simplify this into a storedprocedure or a view that can take 1 input parameter? It needs to beused in a Crystal Report, which is limited in its handling of thesetypes of complex queries.
View 1 Replies
View Related
Jan 22, 2007
Hello,I am working with a database that is in use in several locations.The structure of the databases are identical, but the data isdifferent.I am tasked with combining the data into one large database.(It is SQL 2000)I have .bak files for each location.Is there any way to restore a backup into a database, combining thedata?If not, what is the best way to accomplish this?Thanks,Rodjk #613
View 5 Replies
View Related
Mar 8, 2007
I'm sure this has been brought up many times, but I will ask anyway.Let's say I have 2 tables related:Owner:---------o_ido_nameDog:---------d_idd_nameo_id - for Owner table.If the data is laid out aso_id o_name1 Johnd_id d_name o_id1 Skippy 12 Fido 1How can I make a query that will produce the following results:o_id o_name owned dog names1 John Skippy, FidoI think it has something to do with unions but I can't seem to get it. I'musing SQL Server Compact Edition.
View 20 Replies
View Related
Jul 6, 2007
Hi!I have a table looking like(username) (account number) (start date) (end date) (product)wich I can have up to 4 lines for the same client.I wist to transfert those lines into a new table looking like(username) (account number) (start date 1) (end date 1) (product 1)(start date 2) (end date 2) ... (product 4)How (in SQL) I could do it?
View 1 Replies
View Related
Aug 18, 2007
Hi guys..is there any query to do this action:i want to combine view record into a single record.exm.table 1Name A BJack 10 22jack 12 21jack ... ....jack 1 11ben 12 2ben 3 2ben ... ...into:View 1Name combinejack 10,22 and 12,21and1,11 and .....ben 12,2 and 3,2 and......thx before..
View 1 Replies
View Related
