HI, I have a dataflow that has two raw files as source and I would like to merge them upon a range condition:
RawFile1.Date <= RawFile2.Date
Usually, using tables, I would have used a lookup with partial cache to achieve it. Now, since we cannot use lookup transform with raw files, I was wondering how I could achieve this using raw files as source. Is it possible to merge raw files using merge or merge join?
Thank you,
Ccote
I have a database with 2 log files VE_log and VE_log2 (the split is left from legacy system with limited disk space). I want to eliminate the 2nd log.
I've tried SHRINKFILE (EMPTYFILE), which of course reduces the file to the default minimum pages, SHRINKFILE (TRUNCATEONLY), SHRINKDATABASE (VE), and in every case the ALTER DATABASE REMOVE FILE command fails cause the log isn't empty.
Ideas???
Thanks,
Al
Hello,
I need to generate a report, which should display 4 reports. Two tables and some charts. I have all these reports (I mean the .RDL files) individually. I can render the reports separately. But, now the need is to combine these reports in the one RDL file. Is this possible? If yes, how?
Also, I tried to create a stored procedure, which would call all these 4 SP inturn and provide 4 result sets. I thought of have an RDL by calling only this SP which would give 4 result sets. But infortunately, it gave only the first SP's result set. So, I have to combine the 4 RDL files into one to show on the Reporting Console. Can anyone please help me in this? Help would be grately appreciated.
Thanks a lot. Let me know if the question is not clear.
Mannu.
I have an export file that updates each night and dumps into prices.txt
I have to send them to an ftp site that processes them automatically if they're formatted correctly. They contain the same header information (saved in header.txt) and footer information (saved in footer.text). I then combine them all
header.txt + prices.txt + footer.txt = glpcwholesale.prn
The script below is what I have so far but it isn't working and errors out on line 23.
Any help would be great.
Function Main()
' OpenTextFile Method needs a Const value
' ForAppending = 8 ForReading = 1, ForWriting = 2
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
' Create the File System Object
Dim objFSO1, objFSO2,objFSO3,objFSO4
Set objFSO1 = CreateObject("Scripting.FileSystemObject")
Set objFSO2 = CreateObject("Scripting.FileSystemObject")
Set objFSO3 = CreateObject("Scripting.FileSystemObject")
Set objFSO4 = CreateObject("Scripting.FileSystemObject")
'Creating string references to file locations
Dim strDirectory, strFileHeader, strFilePrices, strFileTrailer
Dim strFileGLPC
strDirectory = "C:DataGarrowPRN"
strFileHeader = "header.txt"
strFileTrailer=" railer.txt"
strFilePrices="prices.txt"
strFileGLPC='glpcwholesale.prn"
'Creating File Objects Text File Reference
Dim objFileHeader, objFilePrices, objFileTrailer, objFileGLPCWholesale
Set objFileHeader = objFSO1.OpenTextFile _
(strDirectory & strFileHeader, ForReading, True)
Set objFilePrices = objFSO2.OpenTextFile _
(strDirectory & strFileTrailer, ForReading, True)
Set objFileTrailer = objFSO3.OpenTextFile _
(strDirectory & strFilePrices, ForReading, True)
Set objFileGLPCWholesale = objFSO4.OpenTextFile _
(strDirectory &strFileGLPC, ForWriting, True)
Do While (objFileHeader.AtEndOfStream <>
True)
objFileGLPCWholesale.WriteLine(objFileHeader.ReadL ine)
loop
objFileGLPCWholesale.Close
Set objFileGLPCWholesale = objFSO4.OpenTextFile _
(strDirectory &strFileGLPC, ForAppend, True)
Do While (objFilePrices.AtEndOfStream <>
True)
objFileGLPCWholesale.WriteLine(objFilePrices.ReadL ine)
loop
Do While (objFileTrailer.AtEndOfStream <>
True)
objFileGLPCWholesale.WriteLine(objFileTrailer.Read Line)
loop
objFileHeader.Close
objFilePrices .Close
objFileTrailer .Close
objFileGLPCWholesale.Close
SET objFileHeader = NOTHING
SET objFilePrices = NOTHING
SET objFileTrailer = NOTHING
SET objFileGLPCWholesale = NOTHING
Main = DTSTaskExecResult_Success
End Function
I€™ve created with the help of some great people an SSIS 2005 package which does the follow so far:
1) Takes an incoming txt file. Example txt file: http://www.webfound.net/split.txt
The txt file going from top to bottom is sort of grouped like this
Header Row (designated by €˜HD€™)
Corresponding Detail Rows for the Header Row
€¦..
Next Header Row
Corresponding Detail Rows
€¦and so on
http://www.webfound.net/rows.jpg
2) Header Rows are split into one table, Maintenance Detail Rows into another, and Payment Detail Rows into a third table. A uniqueID has been created for each header and it€™s related detail rows to form a PK/FK relationship as there was non prior to the import, only the relation was in order of header / related rows below it when we first started. The reason I split this out is so I can massage it later with stored proc filters, whatever€¦
Now I€™m trying to somehow bring back the data in those table together like it was initially using a query so that I can cut out each of the Header / Detail Row sections into their own txt file. So, if you look at the original txt file, each new header and it€™s related detail rows (example of a cut piece would be http://www.webfound.net/rows.jpg) need to be cut out and put into their own separate txt file.
This is where I€™m stuck. How to create a query to combine it all back into an OLE DB Souce component, then somehow read that souce and split out the sections into their own individual txt files.
The filenames of the txt files will vary and be based on one of the column values already in the header table.
Here is a print screen of my package so far:
http://www.webfound.net/tasks.jpg
http://www.webfound.net/Import_MaintenanceFile_Task_components.jpg
http://www.webfound.net/DataFlow_Task_components.jpg
Let me know if you need more info. Examples of the actual data in the tables are here:
http://www.webfound.net/mnt_headerRows.txt
http://www.webfound.net/mnt_MaintenanceRows.txt
http://www.webfound.net/mnt_PaymentRows.txt
Here's a print screen of the table schema:
http://www.webfound.net/schema.jpg
I'm trying to find a way to count the number of times a ContentFile, appears in a ContentLog, for a specified period of time.
SELECT DISTINCT ContentFile FROM ContentLog
Shows a list the list of content files.
I'm not sure though how to count for a specific ContentFile, or how to count for on a specific date range.
Thanks for any help you can give.
Hello,
I currently have a For Each File container that loops through all files from a specific directory. The files have a naming convention that looks like this;
CDNSC.CDNSC.SC00015.01012007
The last segment of the file name is the date of the data in the file (mmddyyyy). The create date for these files is always a day later than indicated in the file name.
What I would like to do is to have more control over the 'range' of files that are looped through by using the date portion of the file name to define what group of files should be looped through. Ideally, I would like to have a 'StartDate' variable and an 'EndDate' variable that I could define at run time for the package, and the package would loop through all of the files where the date portion of the file name fell between 'StartDate' and 'EndDate'.
Any ideas on this?
Thank you for your help!
cdun2
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
View 6 Replies View RelatedI'm running a pre-defined script which used to work fine on a file which was resupplied every month. below is the script used and the error message. looking at the error I assume that there is a rouge record within the file but have looked in Textpad and cannot find it.
UPDATE [matching].[dbo].[hot_nov] SET [AOV] = (CAST([Demand] AS DECIMAL)/CAST([Orders] AS INT)) WHERE [Demand] <> '';
UPDATE [matching].[dbo].[hot_nov] SET [POST2] = left([PostCode], PATINDEX('%[0-9]%', [PostCode] + '1') - 1) ;
UPDATE [matching].[dbo].[hot_nov] SET [POST4] = left([PostCode],LEN([PostCode])-4);
UPDATE [matching].[dbo].[hot_nov] SET [POST6] = left([PostCode],LEN([PostCode])-2);
error message
(1000 row(s) affected)
Msg 245, Level 16, State 1, Line 181
Conversion failed when converting the varchar value '2014-09-03 00:00:00' to data type int.
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate
V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000
B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000
C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate
A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000
B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000
C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Table B
Item ProcessStartDate ProcessEndDate
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
I have a need to select rows where values in a column are within a range AND ALSO the first row that is highest (MAX) but just UNDER the range. For example:
NAME VAL
Jim 4
John 5
Tom 6
Julie 7
Rich 8
Rob 9
RANGE TO SELECT is 6-8 (inclusive)
Rows with a value in the range are returned (6, 7, & 8) as well as the row with a highest value that is JUST BELOW the range (5)
NAME VAL
John 5
Tom 6
Julie 7
Rich 8
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 RelatedHai 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)+'%'
I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies View RelatedIn the For Loop, How to Iterate from Older flat files to Newer flat files based on File's Timestamp. If there are some older files in that folder, it should be processed first and then continue with the newer one.
Any Suggestions?
In the first step of my SSIS package I need to get files from FTP and dump it/them in a local directory, but it's more than that, the logic is like this:
1. If no file(s) found, stop executing and send email saying no file(s) found;
2. If file(s) found, then compare it/them with existing files in our archive folder; if file(s) already exist in archive folder, stop executing and send email saying file(s) already existed, if file(s) not in archive folder yet, then transfer it/them to the local directory for processing.
I know i have to use a script task to do this and i did some research and found examples for each of the above 2 steps and not both combined, so that's why I need some help here to get the logic incorporated right.
Thanks for the help in advance and i apologize for the long lines of code!
example for step 1:
----------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim cDataFileName As String
Dim cFileType As String
Dim cFileFlgVar As String
WriteVariable("SCFileFlg", False)
WriteVariable("OOFileFlg", False)
WriteVariable("INFileFlg", False)
WriteVariable("IAFileFlg", False)
WriteVariable("RCFileFlg", False)
cDataFileName = ReadVariable("DataFileName").ToString
cFileType = Left(Right(cDataFileName, 4), 2)
cFileFlgVar = cFileType.ToUpper + "FileFlg"
WriteVariable(cFileFlgVar, True)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class
example for step 2:
-------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Try
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "ftp.name.com")
cm.Properties("ServerUserName").SetValue(cm, "username")
cm.Properties("ServerPassword").SetValue(cm, "password")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server
ftp.Connect()
'ftp.SetWorkingDirectory("..")
ftp.SetWorkingDirectory("directoryname")
Dim folderNames() As String
Dim fileNames() As String
ftp.GetListing(folderNames, fileNames)
Dim maxname As String = ""
For Each filename As String In fileNames
' whatever operation you need to do to find the correct file...
Next
Dim files(0) As String
files(0) = maxname
ftp.ReceiveFiles(files, "C: emp", True, True)
' Close the ftp connection
ftp.Close()
'Set the filename you retreive for use in data flow
Dts.Variables.Item("FILENAME").Value = maxname
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
I have the need to delete old backup files via TSQL job. Found this solution online:
PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
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
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?
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!
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 :)
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
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 RelatedHere 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
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
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.
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
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
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
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
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
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
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