[help] Possible To Script A Diff Into SQL Query?
Sep 11, 2007
My goal is to add a diff into a query that grabs data from 2 different tables.
The code:
SELECT
MIN(TableName) as TableName,
ID1, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8
,COL9, COL10, COL11, COL12, COL13, COL14, COL15, --COL16,
COL17, COL18, COL19, COL20, COL21
FROM
(
SELECT 'Table A' as TableName,
SessionID as ID1,
StartDateCode as COL1,
StartTimeCode as COL2,
EndDateCode as COL3,
EndTimeCode as COL4,
HandledByCode as COL5,
DispositionCode as COL6,
DNISCode as COL7,
CallServiceQueueCode as COL8,
ApplicationCode as COL9,
IVREndPointCode as COL10,
BankCode as COL11,
TotalQueueTimeSS as COL12,
TotalAgentTalkTimeSS as COL13,
TotalAgentHoldTimeSS as COL14,
TotalAgentHandleTimeSS as COL15,
--TotalIVRTimeSS as COL16,
AfterHoursFlag as COL17,
SourceSystemID as COL18,
anubisTransferExtNumber as COL19,
anubisEndPoint as COL20,
AccountNumber as COL21
from [pdx0sql45].Rubicon_Marts.dbo.INB_Call_Fact
where startdatecode between 2738 and 2769
UNION all
SELECT 'Table B' as TableName,
SessionID as ID1,
StartDateCode as COL1,
StartTimeCode as COL2,
EndDateCode as COL3,
EndTimeCode as COL4,
HandledByCode as COL5,
DispositionCode as COL6,
DNISCode as COL7,
CallServiceQueueCode as COL8,
ApplicationCode as COL9,
IVREndPointCode as COL10,
BankCode as COL11,
TotalQueueTimeSS as COL12,
TotalAgentTalkTimeSS as COL13,
TotalAgentHoldTimeSS as COL14,
TotalAgentHandleTimeSS as COL15,
--TotalIVRTimeSS as COL16,
AfterHoursFlag as COL17,
SourceSystemID as COL18,
anubisTransferExtNumber as COL19,
anubisEndPoint as COL20,
AccountNumber as COL21
from pdx0sql04.Rubicon_Marts.dbo.INB_Call_Fact
where startdatecode between 2738 and 2769
) tmp
GROUP BY ID1, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8
,COL9, COL10, COL11, COL12, COL13, COL14, COL15, --COL16,
COL17, COL18, COL19, COL20, COL21
HAVING COUNT(*) = 1
ORDER BY 2,1
---------
Is it possible to add a command into the query to output diff/compare scenario?
Thanks in advance for any help.
View 3 Replies
ADVERTISEMENT
May 28, 2008
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007
but in query analizer I get the result of
12/31/2006
Why the different dates
View 4 Replies
View Related
Feb 12, 1999
I need to write a 'select' statement to fetch data from different tables, which are located on different servers.
Can any one help in writing this 'select' statement with out moving the tables on to same server.
Thanks in Advance.
Murali Raparla.
View 2 Replies
View Related
Apr 25, 2007
Hi @all,
I designed a query in report designer, data tab. This query runs 24 secs, when I execute it from the data tab in report designer.
Publishing the report and running it from the report server. The query needs 40x the time as in report designer.
Looking at the Execution Log, the report needs 800 secs for TimeDataRetrieval.
Why is it so much slower to run the query from report server? The report designer has also to retrieve the data, and runs so much faster.
Thanks for any hints
T
View 2 Replies
View Related
Apr 5, 2006
Hi.I'm trying but not getting correct results.I have two tablesone with app, msg, time(varchar,datetime,varchar)app1 start 2006-04-03 13:33:36.000app1 stuff 2006-04-03 13:33:36.000app1 end 2006-04-03 13:33:36.000app1 start 2006-04-03 13:33:36.000app2 start 2006-04-03 13:33:36.000app2 stuff 2006-04-03 13:33:36.000app2 end 2006-04-03 13:33:36.000app2 start 2006-04-03 13:33:36.000app3 start 2006-04-03 13:33:36.000app2 end 2006-04-03 13:33:36.000app2 start 2006-04-03 13:33:36.000app2 end 2006-04-03 13:33:36.000app2 start 2006-04-03 13:33:36.000app2 end 2006-04-03 13:33:36.000app3 end 2006-04-03 13:33:36.000app1 end 2006-04-03 13:33:36.000and another with dr watson crash info(varchar, datetime)app1 2006-04-03 13:33:36.000app2 2006-04-03 13:33:36.000app1 2006-04-03 13:33:36.000app1 2006-04-03 13:33:36.000app3 2006-04-03 13:33:36.000I'm trying to make a query that will allowme to see what entries in the first tableoccurred wtihin, say, a minute, or maybe 40seconds of any of the entries in the secondtable.I want all the entries in the second table tobe present, so I know it has to be some sortof join, probably an outer join.my syntax is giving me bad results, probablybecause I'm just out of practice.can someone tell me how to put a query togetherso I see the data I'm looking for?ThanksJeffJeff Kish
View 3 Replies
View Related
Sep 25, 2014
I have two tables table1 and table2 and having a common column "col1"
When i ran the following query
UPDATE table1, table2 SET col1=FALSE WHERE id = 1;
getting the following error
Error Code: 1052
Column 'col1' in field list is ambiguous
id column exist in both the tables and need to update both the tables to false where the id is equivalent to 1.
View 3 Replies
View Related
Jul 20, 2005
Hi group,I have a select statement that if run against a 1 million recorddatabase directly in query analyzer takes less than 1 second.However, if I execute the select statement in a stored procedureinstead, calling the stored proc from query analyzer, then it takes12-17 seconds.Here is what I execute in Query Analyzer when bypassing the storedprocedure:USE VerizonGODECLARE @phonenumber varchar(15)SELECT @phonenumber = '6317898493'SELECT Source_Identifier,BADD_Sequence_Number,Record_Type,BAID ,Social_Security_Number ,Billing_Name,Billing_Address_1,Billing_Address_2,Billing_Address_3,Billing_Address_4,Service_Connection_Date,Disconnect_Date,Date_Final_Bill,Behavior_Score,Account_Group,Diconnect_Reason,Treatment_History,Perm_Temp,Balance_Due,Regulated_Balance_Due,Toll_Balance_Due,Deregulated_Balance_Due,Directory_Balance_Due,Other_Category_BalanceFROM BadDebtWHERE (Telephone_Number = @phonenumber) OR (Telephone_Number_Redef =@phonenumber)order by Service_Connection_Date descRETURNGOHere is what I execute in Query Analyzer when calling the storedprocedure:DECLARE @phonenumber varchar(15)SELECT @phonenumber = '6317898493'EXEC Verizon.dbo.baddebt_phonelookup @phonenumberHere is the script that created the stored procedure itself:CREATE PROCEDURE dbo.baddebt_phonelookup @phonenumber varchar(15)ASSELECT Source_Identifier,BADD_Sequence_Number,Record_Type,BAID ,Social_Security_Number ,Billing_Name,Billing_Address_1,Billing_Address_2,Billing_Address_3,Billing_Address_4,Service_Connection_Date,Disconnect_Date,Date_Final_Bill,Behavior_Score,Account_Group,Diconnect_Reason,Treatment_History,Perm_Temp,Balance_Due,Regulated_Balance_Due,Toll_Balance_Due,Deregulated_Balance_Due,Directory_Balance_Due,Other_Category_BalanceFROM BadDebtWHERE (Telephone_Number = @phonenumber) OR (Telephone_Number_Redef =@phonenumber)order by Service_Connection_Date descRETURNGOUsing SQL Profiler, I also have the execution trees for each of thesetwo different ways of running the same query.Here is the Execution tree when running the whole query in theanalyzer, bypassing the stored procedure:--------------------------------------Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))|--Bookmark Lookup(BOOKMARK:([Bmk1000]),OBJECT:([Verizon].[dbo].[BadDebt]))|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))|--Concatenation|--IndexSeek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Index]),SEEK:([BadDebt].[Telephone_Number]=[@phonenumber]) ORDERED FORWARD)|--IndexSeek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Redef_Index]),SEEK:([BadDebt].[Telephone_Number_Redef]=[@phonenumber]) ORDEREDFORWARD)--------------------------------------Finally, here is the execution tree when calling the stored procedure:--------------------------------------Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))|--Filter(WHERE:([BadDebt].[Telephone_Number]=[@phonenumber] OR[BadDebt].[Telephone_Number_Redef]=[@phonenumber]))|--Compute Scalar(DEFINE:([BadDebt].[Telephone_Number_Redef]=substring(Convert([BadDebt].[Telephone_Number]),1, 10)))|--Table Scan(OBJECT:([Verizon].[dbo].[BadDebt]))--------------------------------------Thanks for any help on my path to optimizing this query for ourproduction environment.Regards,Warren WrightScorex Development Team
View 5 Replies
View Related
Nov 14, 2007
Hi,
I have Table A . we already have 80 columns . we have to add 65 more columns.
we are populating this table from oracle .and we need to populate those 65 columns again from the same table.
Is it a better idea to add those new 65 columns to the same table or new table.
If we go for the same table then loading time will be double, If I go for new table and If i am able to run both the packages which loads table data from same oracle server to difffrent sql tables then we should be good. But if we run in to temp space issues on oracle server . Then i have to load the two tables separately which consumes the same time as earlier one.
I was thinking if there is a way in SSIS where I can pull data from same oracle table in to two diff sql tables at same time?
View 1 Replies
View Related
May 14, 2007
I need to return the number of min from a table I am using the following query. But it gives me an error "Msg 241, Level 16, State 1, Line 1Syntax error converting date time from character string". can someone please help.SELECT DateDiff(Mi, CAST((SCHDATE + ' ' + SUBSTRING(SCHTIME, 1,2) + ':' + SUBSTRING(SCHTIME, 3,4)) AS DateTime), CAST((ACTDATE + ' ' + SUBSTRING(ACTIME, 1,2) + ':' + SUBSTRING(ACTIME, 3,4)) AS DateTime)) AS StopMinutes, BACPY, BARTRM, BAORD, BSAPOR, BABLN, BSASSQ, BSACNO, CSTRDATA, BSASCY, BSASST, TTLREV, SHAALP, SCHDATE, SCHTIME, ACTDATE, ACTIME, OQTCOD, BAADES, PCS, WGT, Tractor, Driver FROM dbo.JCI_Delivery_Report
View 3 Replies
View Related
Dec 11, 2000
Hi,
i have 3 fields: start_inspect_datetime, end_inspect_datetime, Diag_Hrs.
so i want to get the difference of start and end datetime=Diag_Hrs.
here i am using the below stored proc.
but i am getting only the hours or minutes or seconds.
so how to get the hours(if diff>59 mins),minutes(if diff>59 sec),seconds.
for Ex: here diff=185 sec. then Diag_hrs should be 3 hours,0 mins, 5 secs.
so how we'll get this.
pl help me out asap.
Thanx
reddy
select Asset_Diag_Hrs= DATEDIFF(hh,start_inspect_datetime,end_inspect_dat etime) from asset_diag_trans_table
--where Gpc_no=@GPC_no
--select Asset_Diag_Hrs=(datediff(mm,start_inspect_datetime ,end_inspect_datetime)) from asset_diag_trans_table
-- where Gpc_no=@GPC_no
select Asset_Diag_Seconds=(datediff(ss,start_inspect_date time,end_inspect_datetime) ) from asset_diag_trans_table
where Gpc_no=@GPC_no
View 3 Replies
View Related
Oct 24, 2000
Can i restore a diff. backup alone without a complete backup?
(what i am trying is this.....there are two different servers at two diff. places....i need to have both servers in sync. at all times.modifications will take place in one server and the modifications have to be reflected in the other server.i could not go for replication as the servers cannot be connected.sending complete backups daily will be a overhead .hence planning to take complete backup once and send diff. backups alone on subsequent days to the other server.
how can i achieve this?)
View 1 Replies
View Related
Mar 22, 2006
Dear all
I am new to the MS SQL, my problem is as follows.
I am having a online database on sql 2k.
every 15 days we have to give payout from our system, so we
have a offline server in our office, we take the complete backup of that day & restore the same on the offline server,
& start the payout process.
The problem is that the full backup is a big file & take a lot of time for downloading from online server.
is it possible that we take on diff. backup of that day & will restore the same on the offline server so the file will take less time to download.
but my offline backup is 15 days old, will that update all the records or not?
regards
Abhijit
View 4 Replies
View Related
Jan 6, 2004
I have 2 databases:
1 from production and 1 from development.
None of the developers kept a changelog so i need to know what has changed (or what is different between the 2).
Any Ideas on how to do this?
View 7 Replies
View Related
Jun 11, 2008
In playing with differential backups, i have taken a full last night, 17GB, took my first diff today 16.5GB, took another diff right after, it was still 16.5GB, took another diff right after 16.5 GB. This database has 0 activity during the day, the import takes place @ night, and select's are done to it during the day. Any help would be greatly appreciated
View 3 Replies
View Related
Nov 20, 2007
Hey guys,
m new to SQL i mean really new so i appreciate all the help i can get on this as soon as possible.
Is it possible to extract 3 foreign keys from a single table into one record/row on a different problem ?
Thnx guys hope to hear soon
View 7 Replies
View Related
Nov 29, 2007
Hello,
I wanted to know the difference between SQL Server 2005 ( RTM) Version and the regular SQL Server 2005 SP2. And does the RTM version have any service packs.
Thanks
View 2 Replies
View Related
Jun 6, 2007
Are there any tools/utilties that can diff 2 sql server databases. I know the DB Pro Edition of VSTS can do it, but its hard to justify a jump from my $800 copy of VS2005 Pro to a $5k-6k version of VS just to get database diff. The tablediff looks promising but I need it to do SPROCS and SFUNCS too. Any helps or recommendations would be appreciated.
View 4 Replies
View Related
Jan 7, 2008
Hi All,
What is the difference betwen real and float datatype of SQL server.
Please one exapmle for each.
The real datatype is like a float except that real can only store numbers.What does it mean: float can store even characters.
Thanks
Abdul
View 2 Replies
View Related
Mar 15, 2008
What is the difference between money and smallmoney, and is there a way to format these?
View 3 Replies
View Related
Feb 4, 2004
Hi.
When I execute the following sql statement in SQL 2000 DTS or query analyzer, I received the following error message. I used [ ] because my field names have spaces in between.
The same sql statement ran perfect in SQL 7. What could be wrong? Please advise. Thanks a million.
Update SAPvsSQL set [Sales Organisation] = sales_org,
[Value Out By] = val_diff, [Qty Out By] = qty_diff
Error message:
Server: Msg 16882, Level 11, State 1, Procedure sp_runwebtask, Line ....
SQL Web Assistant: Web task not found. Verify the name of the name for possible errors.
Best regards
View 4 Replies
View Related
Apr 12, 2008
Hi, I need to know the difference between Numeric and Decimal datatypes. In which we case we use numeric and in which case we use decimal? I searched in some sql related websites and came to know that both are same. Then what is the need of these two datatypes? Either Numeric or Decimal is alone enough? Please explain me in detail ....
Awaiting your replies...
Thanks,
Rakesh.
View 4 Replies
View Related
Dec 6, 2005
HEY,
CAN YOU TELL ME THE DIFFERENCES BETWEEN VARIOUS SQL
View 2 Replies
View Related
May 22, 2006
hi
could any one help me in differentiating between varchar and nvarchar
Thanks in advance
View 4 Replies
View Related
Jan 3, 2008
I have a handful of jobs that I need to troubleshoot, but this one will hopefully give me enough insight to do the rest myself. I need to figure out why a Maintenance Plan that performs a Diff Backup on 4 databases, and then a transaction log back up on the 4 databases is failing. I just get the following error message, and I cannot tell what failed.
Executed as user: <SERVER_NAME>SYSTEM. ... 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:20:05 AM
Progress: 2008-01-03 11:20:05.95
Source: {SOME_STUFF_WAS_HERE}
Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete
End Progress
Progress: 2008-01-03 11:20:06.35
Source: Back Up Database (Differential)
Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete
End Progress
Progress: 2008-01-03 11:20:06.35
Source: Back Up Database (Differential)
Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete
End Progress
Progress: 2008-01-03 11:20:06.37
Source: Back Up Database (Differential)
Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete
End Progress
Progress: 2008-01-03 11:20:06.37
Source: Back Up Database (Differential) ... The package execution fa... The step failed.
How can I find out how to fix this problem? And please let me know if I am doing something wrong. (For instance, perhaps those two tasks shouldn't be together.)
- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/
View 9 Replies
View Related
Feb 16, 2007
I have a table t1 with two columns : c11 varchar(32) , c22 varchar(32)The data in the table is :'11', 'aa01'and on upto'11', 'aa50' : total 50 entries'22', 'b01''22', b'02''22', b'03''33', 'c01' to '33', 'c40' : total 40 entries'44', 'b02''44', 'd01''44', 'd01''44', 'd01'How can write a query which will bunch together values of c11with rows 5, and then bunch together values of c11 withrows < 6, and add them up.My output should be :'11' 50'33' 40'others' 7 (3 rows for '22' and 4 for '44' are bunchedtogetheras the # of rows < 6, and added. 3+4 = 7)
View 1 Replies
View Related
Jul 31, 2007
Country1
Country2
2006
2007
% diff
2006
2007
% diff
Segment1
80
90
12.50%
60
50
-16.67%
Segment2
150
120
-20.00%
80
100
25.00%
Total
230
210
-8.70%
140
150
7.14%
I need a to build a result as shown above in matrix, many countries will be in resultset and each country performance is evaluated based on previous year. (90-80)/80=12.50%. the problem is this year and segment values are calculated based on aggregate functions and is derived by means of specific formaule. for country1 in year 2006 for segment1 the value 80,90 is arrived by using sum(leadtime*Ordervalue)/sum(Ordervalue).
So i cannot store these current and previous year values for calculating the % bymeans of custom code, runningvalue cannot be used as the formulae already has an aggregate, is there any direct way to locate the cell references just like excel R1C1 with matrix? if so then i can refer the cells for calculating my %. please help.
Thanks in advance.
brinda
View 4 Replies
View Related
Aug 8, 2007
Friends,
I have taken backup of a database, of which Data (MDF) & Log (LDF) files are located on "E:...." directory.
Now, I am using the same backup file to restore on another PC which is having no partitions at all. I mean now I have to restore the database using same backup file, of which Data & Log files should be located on "C:...." directory only, as it has no partitions.
This restore process gives me error :
Restore failed for Server 'HSVMMICROFIT'.
Details : System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:Program FilesMicroFitHealthStar v6.0 Server EditionDataMICROFIT_HealthStar_Data.mdf'.
What is to be done, in order to restore the database from the same back up file successfully, regardless whether the original physical location of the MDF & LDF file, is existing on current PC ??????????????
Please try to solve the same for me.... W8ing...
View 7 Replies
View Related
Jan 25, 2007
Hi ,
What is the diff between Logins & Users in SQL Server?
thanks
Babu
View 3 Replies
View Related
Mar 31, 2008
The DB Enginee is SQL2005 SP1. In MSSMS
the following query always finishes within 1 minute
SELECT *
FROM t1 INNER JOIN t2 ON t1.key= t2.key
where t1.StartTime >= '3/19/2008' AND t1.EndTime <= '3/20/2008'
AND t2.StartTime >= t1.StartTime AND t2.StartTime < t1.EndTime
however, the following always takes 10+ minutes
declare @pStartDate datetime
declare @pEndDate datetime
set @pStartDate='3/19/2008'
set @pEndDate='3/20/2008'
SELECT *
FROM t1 INNER JOIN t2 ON t1.key= t2.key
where t1.StartTime >= @pStartDate AND t1.EndTime <= @pEndDate
AND t2.StartTime >= t1.StartTime AND t2.StartTime < t1.EndTime
Both return same result. The only diff. is the blue part. Why the second one performances so badly?
Thanks.
View 4 Replies
View Related
Jan 21, 2008
We recieve about 4 million rows of data from a client per week. Each file is a complete snapshot of their data, we need to be able to find the rows that are different or new in the current weeks file compared to last weeks file.
Is this possible using 2 Flat file sources and some kind of sort/merge join?
If you are familiar with ORACLE or postgres its similar to a "MINUS" or "EXCEPT" respectively. Right now we load both files to separate tables and compare them in sql server which is very slow.
View 8 Replies
View Related
Nov 28, 2006
Diff. b/w Stored Procedures and Function??
When any of them is appropriate to use?
View 2 Replies
View Related
Feb 2, 2007
wots the diff b/w ncharand integer....??
View 1 Replies
View Related
May 22, 2008
Hi alll,can anyone tell me that In which cases cast is used in db and what is diff betn cast and convert
View 9 Replies
View Related