Transact SQL :: Returning Different Data When Part Of Query Is Separate?
Aug 3, 2015
I am writing a stored procedure to prepare some reports.
The issue is that I am summing up the combined bid, 'QuoteTotal' + Sum(InvoiceItemAmount) (eg, quote add ons).
When part of the larger query it returns a different, and incorrect amount. The query listed after the main query is just that line and it's appropriate parts and it returns the correct amount. What can I do to correct this and where lies the problem so I can learn from this situation?
alter PROCEDURE [dbo].[Select_Quote_Info_By_Salesmen_By_Status]
@Salesmen nvarchar(50),
@QuoteStatus nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
[code]...
View 5 Replies
ADVERTISEMENT
Jan 12, 2008
Hello,
I recently created a linked server to db2. When I do a join on the linked server it is returning to much data.
select * from table1 left join [linkedServer]..Schema.Table as tb1 on table1.id=tb1.id
For now, I had to first get the ids I wanted from table1 and then use a subquery to join table1 on the a result set produced from a query to the linked server that only specified those ids. Below is an example
select * from table1 left join (select col1 from [linkedServer]..Schema.table where id=1 or id=2) as tb1 on table1.id=tb1.id
I'm guessing I have some setting wrong. One of the biggest reasons for me to use the four part name is so I can do joins like the first example.
Please let me know if you have any ideas.
Thanks
View 4 Replies
View Related
Oct 16, 2015
I am working on a query that is quite complex. I need the query to return as part of the fields a field that will contain the total percentage of tickets in a version.The query is below
select cat.name as name,count(distinct bug.id) as numberOfBugs,cast(count(bug.id) * 1000.0 / sum(count(bug.id) * 10.0) over() as decimal(10,2))/100 AS qnt_pct, vers.version, dateadd(s,vers.date_order,'1/1/1970') as "Release_Date"
from mantis_bug_table bug
INNER JOIN mantis_category_table cat on cat.id = bug.category_id
LEFT OUTER JOIN mantis_project_version_table vers on vers.project_id = vers.project_id and vers.version = bug.version
[code]....
View 12 Replies
View Related
Feb 6, 2008
Hi
We are planning to setup log shipping model being setup between two sqlserver 2005 enterprise edition.
Our transaction log backup sizes are not consistent through out the day.The following is the scenario.
T1 at 8.00 -- 315MB
T2 at 8.30 -- 152MB
T3 at 9.00 -- 2.5GB
T4 at 10.00 -- 500MB
The bandwidth available is 25MB/minute. We are taking the backups of Tlogs at 30mins interval.Now at 'T3' log transfer we want to zip the file on the primary server,copy the file over the WAN and unzip it standby server.
Now i wanted to know whether to perform this kind of action(Zip,copy,unzip), can i create a separate job on primary ?? which will put the unzipped file on standby and allow the log shipping job to restore the log file (T3)
Also i wish to carry my T3,T4 etc backup -- copy-- restores through my Log shipping activity.
I cannot take 15mins backup on primary server to reduce the transaction log backup size.Thts another constraint i have.
Also tell me how to zip and unzip thru command line if u hve any link.
Please advice me
thnks for the help in advance
Regards
Arvind L
View 1 Replies
View Related
Feb 6, 2008
Hi
We are planning to setup log shipping model being setup between two sqlserver 2005 enterprise edition.
Our transaction log backup sizes are not consistent through out the day.The following is the scenario.
T1 at 8.00 -- 315MB
T2 at 8.30 -- 152MB
T3 at 9.00 -- 2.5GB
T4 at 10.00 -- 500MB
The bandwidth available is 25MB/minute. We are taking the backups of Tlogs at 30mins interval.Now at 'T3' log transfer we want to zip the file on the primary server,copy the file over the WAN and unzip it standby server.
Now i wanted to know whether to perform this kind of action(Zip,copy,unzip), can i create a separate job on primary ?? which will put the unzipped file on standby and allow the log shipping job to restore the log file (T3)
Also i wish to carry my T3,T4 etc backup -- copy-- restores through my Log shipping activity.
I cannot take 15mins backup on primary server to reduce the transaction log backup size.Thts another constraint i have.
Also tell me how to zip and unzip thru command line if u hve any link.
Please advice me
thnks for the help in advance
Regards
Arvind L
View 1 Replies
View Related
Mar 23, 2006
I have the following 2 Sql queries. They both are rowcounts of the same column but based on different criteria. What I want to do is return the two results side by side in separate columns:
-- Subscriptions since Sept. 24th
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate is not null
and c.clientunsubscribe = 0
and c.validemailaddr = 1
and s.unsubscribe = 0
-- Subscriptions in February
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate BETWEEN '2006-02-01 00:00:00.000' AND '2006-03-01 00:00:00.000'
AND c.clientunsubscribe = 0
AND c.validemailaddr = 1
AND s.unsubscribe = 0
It seems like a UNION ALL should work but it just returns the results in one column. I tried changing the count by specifying a different column for each but that doesn't work either. I also tried writing it as one query and using alias to differentiate the two tables but that just gives me syntax errors. I suspect there is a more elegant way to do this but I'm at a loss. Any help would be greatly appreciated!
Caeanis
View 1 Replies
View Related
Sep 13, 2004
Hi, I have a table that has the following field and results.
I want a where clause that returns records that start 0000........
I need to exclude those records that start 0001..........
Can anyone help
oseq
-------
0000
0000.0000
0000.0000.0000
0000.0000.0000.0000
0000.0000.0000.0000.0000
0000.0000.0000.0000.0001
0000.0000.0000.0001
0000.0000.0001
0001
0001.0000
0001.0001
0001.0002
0000.0000.0002
0000.0000.0003
0000.0001
0000.0001.0000
0000.0001.0000.0000
0000.0001.0000.0000.0000
0000.0001.0000.0000.0000.0000
0000.0026.0004.0009
0000.0026.0004.0010
View 3 Replies
View Related
Aug 25, 2015
Msg 240, Level 16, State 1, Line 14
Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,
DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),
[code]....
View 2 Replies
View Related
Nov 18, 2015
I have a column with the data as below :-
<Items>
 <Item Value="Value1" />
 <Item Value="Value2" />
<Items>
How to get this data into seperate columns asÂ
Items
value1
value2
View 2 Replies
View Related
Oct 21, 2007
Hi! I have a sql query in stored procedure: SELECT Salutation + ' ' + FirstName + ' ' + LastName AS fullname
Ok, this returns a value if salutation is not null, but if the salutation is null it doesn't return any value, I was thinking if the saluation is null then I would atleast get the firstname and last name. Any help appreciated on this.
View 4 Replies
View Related
May 29, 2008
Hello everyone,
I set up a View in SQL Server 2005. The syntax checks ok, however, when I execute it, it doesn't return any data.
This is my Query:
SELECT DATEPART(hh, Time) AS Time, COUNT([Recipient-Address]) AS [CountOfRecipient-Address], ROUND(SUM([Total-bytes]) / 1048576, 2)
AS [SumOfTotal-Bytes]
FROM dbo.TrackingLog
WHERE (RIGHT([Recipient-Address], LEN([Recipient-Address]) - PATINDEX([Recipient-Address], '@')) IN
(SELECT Domains
FROM dbo.Domains))
GROUP BY DATEPART(hh, Time)
The part that I am most concerned about is the WHERE section. If I remove it, I get some data returned. If I don't, obviously I don't get anything back.
Any suggestion would be great.
Thanks
View 4 Replies
View Related
Apr 21, 2015
Using below script to export the select statement result to .xls
declare @sql varchar(8000)
select @sql = 'bcp "select * from Databases..Table" queryout c:bcpTom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.
how to export the data in columns to separate tabs in excel.
View 2 Replies
View Related
Jul 23, 2005
I am using SQL7 Query Analyzer. A simple select * from myMLSview andthen I save results as a .csv file has the commas messed up in quite afew places. The data is messed up before I save it to the .csv file. Sothere are blank spaces being added here and there causing them to readas 'add comma' when saving to .csv format. Even the column data ismessed up... example: ,photo_mod_time,photo_mo,d_date, should read,photo_mod_time,photo_mod_date,I have a view on a remote MS-SQL7 Server, the view has about 200columns and the data returned from the my querys can be from 10 to 25MBin size. Connection is via Roadrunner/cable. Should I use anothermethod, maybe command line? Any suggestions?
View 3 Replies
View Related
Oct 14, 2015
I am using aloha POS and they have the date for every check in separate fields and now I want to calculate the total time for the checks but unable to get the how of it..
The date is DOB and it's datetime but I just need to extra the getdate() from it.The open time is OPENHOUR and OPENMINThe close time is CLOSEHOUR and CLOSEMIN
so basically the open time for the check will be the DATE FROM DOB + OPENHOUR + OPENMIN
And the close time will be DATE FROM DOB + CLOSEHOUR + CLOSEMIN
How can I get the total minutes for the check?
View 2 Replies
View Related
May 16, 2015
I have a table that has some comma separated values and then have another table with the lookup values. I"m not sure why it was created this way and have not had to do this. This is a vendor database so I can't go around and changing things.Â
Table AÂ
IDStageIDs
188
288,86,87
388,87
Table B (Lookup table)
IDName
86test1
87test2
88test3
Expected results
StageIDsResult
88 test3
88,86,87test3, test1, test2
88,87 test3, test2
View 2 Replies
View Related
Jun 7, 2007
I am sending out an SOS.
Here is the situation:
We recently upgrade to 2005(sp). We have one report that ran fine in 2000 but leaves out data from certain columns (date related) in the results, so we chalked it up to being a non compatiable issue. So, I decided to try and switch the DB back to 2000 compatibility (in our test env) and then back to 2005. After that the report started returning the proper data. We can€™t really explain why it worked but it did. So we thought we would try it in prod (we knew it was a long shot) and it didn€™t work. So the business needs this report so we thought we would refresh the test system from prod, but now we are back to square one. I was wondering if anyone else has heard or seen anything like this. I am open to any idea€™s, no matter how crazy. J The systems are configured identically. Let me know if you need more information.
Thank you. Scott
View 4 Replies
View Related
Jun 24, 2015
I have a flat text file with lots HTML tags and corresponding valuesÂ
For exampleÂ
<Near_Side> 5563 </Near_Side>
<Top_Down_Code> Xe345 <Top_Down_Code>Â
So, For example I can use the a functionÂ
ALTER function dbo.StripHTML( @text varchar(max) ) returns varchar(max) as
begin
  declare @textXML xml
  declare @result varchar(max)
  set @textXML = REPLACE( @text, '&', '' );
[Code] ...
To which Select dbo.StripHTML('<Near_Side> 5563 </Near_Side>')  ValueÂ
I'll get 5563
However how would you get the values within the tag itself ? E.g.Â
Value NameÂ
5563 Near_side
Xe345 Â Â Â Top_Down_Code
I was thinking along the Charindex but cant seem to get it right.
View 4 Replies
View Related
Sep 14, 2015
I have values in two columns separated by commas, like shown below:
I need the Output Like
How to do this in SQL server ?
View 6 Replies
View Related
Sep 4, 2015
I have a table tbl_User(ID,UserName,UserPassword,UserStatus).
When some value insert into tbl_User the log data should insert in to another database table. How can I achieve this ???
Is any new features related to trigger that can be done in new database from one???
SQL Server 2014 Enterprise
View 5 Replies
View Related
Mar 12, 2008
If a Select is done on a column whose data type is nvarchar(16) and contains only numerals (UPC numbers) the select does not return the record.
1. Query with numerals in nvarchar column works as long as multiple records are returned (LIKE '012%')
2. Numeric (INT only one tested) columns works as expected
3. String columns with alpha data works as expected
4. Problem only exist when running in Device Emulator and/or actual device.
5. Same test on desktop app runs as expected.
6. Windows Mobile 6, Vista Ultimate
7. Same results when when connection to device from SSMS
8. SQL Servers comes on
Previous thread discussion of this problem (I thought that Parameters corrected problem, but not in all cases???)
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.ce&mid=0cd9cd3a-f9b0-477f-b1e7-c27eb76158ae
Here is the complete code:
SqlCeConnection _conn = null;
_conn = new SqlCeConnection(@"Data Source=program FilesTestResultSetevsoft.sdf;");
_conn.Open();
// DOES NOT WORK *** This statement does not return the record (it exist)
string _sql = "SELECT * FROM Product where RegDescr='0123456' ";
// works correctly
string _sql = "SELECT * FROM PRODUCT where ProdNum = 6523 ";
// works correctly *** as long as multiple records are returned
string _sql = "SELECT * FROM PRODUCT where RegDescr LIKE '01%' ";
// works correctly
string _sql = "SELECT * FROM PRODUCT where RegDescr='BACARDI SILVER RAZZ'";
SqlCeCommand _cmd = _conn.CreateCommand();
SqlCeDataReader _rdr;
_cmd.CommandText = _sql;
_cmd.CommandType = CommandType.Text;
// Same results using ExecuteResultSet or ExecuteReader
//_rdr = _cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
_rdr = _cmd.ExecuteReader();
listBox1.Items.Add("In the while loop");
while (_rdr.Read())
{
listBox1.Items.Add(_rdr.GetValue(1) + " / " + _rdr.GetValue(3));
}
listBox1.Items.Add("Done");
View 1 Replies
View Related
Nov 4, 2015
I have stored procedure on Server A which goes to ServerB to check and update table and then update on Server A as well.I have Trigger which suppose to execute stored procedure (as i mentioned above). But it failed with this error:--
Trigger code:--
CREATE TRIGGER [tr_DBA_create_database_notification] ON ALL SERVERÂ
AFTER CREATE_DATABASE
ASÂ
--execute dbadmin.dbo.usp_DBA_Refresh_DBAdmin_Tables
Error:--The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "xxx" was unable to begin a distributed transaction.Process ID 62 attempted to unlock a resource it does not own: DATABASE 21. Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
Same stored procedure, if i execute manually or if i create sql job and execute this stored procedure, it works just fine..In trigger also, if i execute start job which has stored procedure, it works.My question is,why it failed when i execute stored procedure in TRIGGER.
View 5 Replies
View Related
Jul 14, 2015
I have a table like this :
reg.no |Â Â description |Â start_date |Â end_date |Â Â load_date
I want to join this table with itself on reg. no. But not all the rows in table must be joined.
But for example, rows with load_date 01-07-2015 to be joined with rows with load_date 02-07-2015. And the rest of the rows should not used in join (for example, rows having other load_dates)
Is this possible ? and how?
View 4 Replies
View Related
Jul 17, 2015
I am using SQL Server 2008. I have strings like this:
AB-123
CDW-32
declare @First_Part varchar(3)
declare @Second_Part varchar(5)
I want to split the string with delimiter '-' and store as first part and second part.
I saw few sample functions to split a string but these return table of values.
I simply want first part and second part.
In above examples context:
@First_Part = 'AB'
@Second_Part = '123'
Any simple way to do this?
View 6 Replies
View Related
May 13, 2015
I have a text filed in my table.I have sample data looks like <<some status>> << 3/9/2008 10:00:45 PM>> <<personname>>Im interested in searching <<some status>>Â and <<personname>> together by skipping date in between so my query set should return status and same person name i m looking for.
View 11 Replies
View Related
Jun 10, 2015
I have to extract a specific part of a string from a column in a sql server table. Following are the details and I have given the sample table and the sample strings.
I have 2 columns in my table [dbo].[StringExtract] (Id, MyString)
The row sample looks like the following
I have to extract the Id and a part of the column from mystring.
Id      MyString
1 Â Â Â Â Â ABC|^~&|BNAME|CLIENT1||CLIENT1|20110609233558||BIC^A27|5014589635|K|8.1|
ABC1|^~&|BNAME1|CLIENT1||CLIENT1|20110609233558||CTP^A27|5014589635|I|7.1|
DEF||5148956598||||Apprised|Bfunction1||15|LMP|^^^201106101330|
alloys3^ally^crimson^L||||alloys3^ally^crimson^L||||alloys3^ally^crimson^L|||||Apprised|
[Code] ....
The part I want to extract is in the line "ZZZ" and the string part that i want to extract is between the 5th and 6th pipes (|). So my output looks like the following
Id      DesiredString
1 Â Â Â Â Â Extracts^This^String1
2 Â Â Â Â Â Extracts^This^String2
3 Â Â Â Â Â Extracts^This^String3
Is there a way to extract this either using TSQL or SSIS.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
DROP TABLE [dbo].[StringExtract]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[StringExtract](
[Id] [int] NULL,
[Code] ....
View 4 Replies
View Related
Apr 20, 2015
I have a string column in a DB where it's values contain the following midway through the string ([DOCUMENTGUID] is a uniqueidentifier that is different for each row):
<a href="../downloadDoc.aspx?dg=[DOCUMENTGUID]" target="_blank">
I would like to replace this part of the string with a different piece of text.
I know that I should be using PATINDEX and REPLACE functions but I don't know how to utilise.
View 4 Replies
View Related
Sep 18, 2015
I need to set only the date part of tblEventStaffRequired.StartTime to tblEvents.EventDate while maintaining the time part in tblEventStaffRequired.StartTime.
UPDATE tblEventStaffRequired
SET StartTime = <expression here>
FROM tblEvents INNER JOIN
tblEventStaffRequired ON tblEvents.ID = tblEventStaffRequired.EventID
View 4 Replies
View Related
Aug 31, 2015
Below. I have also pasted the current result of this query and the desired result.Â
Query can be updated to get the desired result as given below?
Query:
Select c.OTH_PAYER_ID, c.PAID_DATE, f.GROUP_CODE, f.REASON_CODE, f.ADJUSTMENT_AMOUNT
From MMIT_CLAIM_ITEM b, mmit_tpl c , mmit_attachment_link d, MMIT_TPL_GROUP_RSN_ADJ f
where b.CLAIM_ICN_NU = d.CLAIM_ICN and b.CLAIM_ITEM_LINE_NU = d.CLAIM_LINE_NUM and c.TPL_TS = d.TPL_TS and f.TPL_TS = c.TPL_TS and b.CLAIM_ICN_NU = '123456788444'
Current Result which I am getting with this query
OTH_PAYER_ID PAID_DATE GROUP_CODE REASON_CODE ADJUSTMENT_AMOUNT
5501 07/13/2015 CO 11 23.87
5501 07/13/2015 PR 12 3.76
5501 07/13/2015 OT 32 33.45
2032 07/14/2015 CO 12 23.87
2032 07/14/2015 OT 14 43.01
Desired/Expected Result for which I need updated query
OTH_PAYER_ID PAID_DATE GROUP_CODE_1 REASON_CODE_1 ADJUSTMENT_AMOUNT_1 GROUP_CODE_2
REASON_CODE_2 ADJUSTMENT_AMOUNT_2 GROUP_CODE_3 REASON_CODE_3 ADJUSTMENT_AMOUNT_3
5501 07/13/2015 CO 11 23.87 PR 12 3.76 OT 32 33.45 2032 07/14/2015 CO 12 23.87 OT 14 43.01
Using DB2.
View 2 Replies
View Related
Aug 18, 2015
I'm running into an interesting issue when returning a response from an XML-based web service. The following code returns good values from PRINT
@response
DECLARE @address varchar(50) = '90210'
DECLARE @URL varchar(MAX)
SET @URL = 'https://somewebsite/map.xml?zip=' +
CASE WHEN @Address IS NOT NULL THEN @Address ELSE '' END
SET @URL = REPLACE(@URL, ' ', '+')
[code]...
However, on some calls to the web service, the value returned is longer than 4000 characters. In these instances, it appears as though it breaks the whole thing and returns nothing. However, if I change the @Response parameter to nvarchar(max), it never returns anything, even on responses that are shorter than 4000 characters. what the fundamental difference is here between nvarchar(4000) and nvarchar(max) with respects to how it stores responseText. More importantly, how to get this to return a value even when it exceeds 4,000 characters?
View 2 Replies
View Related
Jul 6, 2015
I have a column that has data in column user4 such as :
CALBRIGÂ Â ALBIONÂ COMPANY LTDÂ Â
1900 No. 8 Road, RIchmond, BC, V6V 1W3
I can do a LEFT(user4, 7) That returns me the first 7 charcters but I need to retrieve the company information but the number of characters may vary with each field. Is there anyway I can do this without returning the entire line?
View 3 Replies
View Related
Oct 21, 2015
I have a requirement for the following query which uses a timestamp data type field
[eventdate]Select field1, fileds2, filed3, {min(eventdate) as max(eventdate) per day/date} from sometable
View 4 Replies
View Related
May 27, 2015
I am having a problem with the GETDATE().
WHERE TableName.ColumnNamne = Getdate()
The above SQL function does not return any results whereas the below SQL code returns results. Am I doing anything wrong?
WHERE SalesOrder.New_ActualShipmentDate >= GETDATE()
View 35 Replies
View Related
Dec 2, 2008
Howto write a query which will find the date from yesterday 12.00am till yesterday 11.59.59PM. that means yesterday 24 hrs only. If I use getdate it will show me the date which is right now which i don't want. everyday i need to search the data from yesterday whole day.
View 13 Replies
View Related
