Transact SQL :: Getting Delta Value With Lead Windows Functions?
Jul 1, 2015
I have a table with the next structure:
DECLARE @MaxCountHistogram TABLE
(
MaxId INT IDENTITY PRIMARY KEY NOT NULL,
PublicationId INT NOT NULL,
ProviderId INT NOT NULL,
DateLog DATETIME NOT NULL,
Amount FLOAT NOT NULL
)
INSERT INTO @MaxCountHistogram
VALUES(432,3,'20150530',10.2564),(432,3,'20150630',13.2564),(432,5,'20150530',8),(432,5,'20150630',13),(433,3,'20150530',9),(433,3,'20150630',11),(433,5,'20150530',13),(433,5,'20150630',21)
I need to take for each Publication and Provider and getting the diferential between two different months, for example:
Period Delta Amount Provider PublicationId
20150530 10.2564 3 432
20150630 3 Result of (13.2564- 10.2564 ) 3 432
View 4 Replies
ADVERTISEMENT
Apr 30, 2015
Currently through my stored procedure, I am pulling the data from my source systems in the way Daily Truncate and loading the data in to my database table. Currently my query looks as follows,
SELECT
a.col1,a.col2,b.col3,b.col4,c.col5
FROM
a
left outer join b ON a.col1 = b.col1
left outer join c ON b.col1 = c.col1.
Now, I want to change the above one to delta process(I have updateddate field in all the tables). The requirement is any of these 3 tables has updateddate = getdate() then i need to take "a.col1,a.col2,b.col3,b.col4,c.col5" columns and push to destination
table.
I think usually delta process means we will use to consider the key table, in that table if there is an updated record will take that record and join with the other required tables to pull the updated record. But these logic seems different.how to build the query for the above delta process? Since it is an urgent requirement need to implement asap.
View 9 Replies
View Related
May 3, 2005
Ladies and Gentlemen,
I would like to offer you the following string functions Transact-SQL
GETWORDCOUNT() Counts the words in a string
GETWORDNUM() Returns a specified word from a string
AT() Returns the beginning numeric position of the first occurrence of a character expression within
another character expression, counting from the leftmost character
RAT() Returns the numeric position of the last (rightmost) occurrence of a character string within
another character string
OCCURS() Returns the number of times a character expression occurs within another character expression
PADL() Returns a string from an expression, padded with spaces or characters to a specified length on the left side
PADR() Returns a string from an expression, padded with spaces or characters to a specified length on the right side
PADC() Returns a string from an expression, padded with spaces or characters to a specified length on the both sides
PROPER() Returns from a character expression a string capitalized as appropriate for proper names
RCHARINDEX() Is similar to a built-in function Transact-SQL charindex but the search of which is on the right
ARABTOROMAN() Returns the character Roman number equivalent of a specified numeric expression
ROMANTOARAB() Returns the number equivalent of a specified character Roman number expression ...
More than 2000 people have already downloaded my functions. I hope you will find it useful as well.
For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
Please, download the file
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115
With the best regards
View 2 Replies
View Related
Nov 17, 2005
Ladies and Gentlemen,
I am pleased to offer, free of charge, the following string functions Transact-SQL:
AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character.
RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string.
OCCURS(): Returns the number of times a character expression occurs within another character expression (including overlaps).
OCCURS2(): Returns the number of times a character expression occurs within another character expression (excluding overlaps).
PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side.
PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side.
PADC(): Returns a string from an expression, padded with spaces or characters to a specified length on the both sides.
CHRTRAN(): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.
STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters.
STRFILTER(): Removes all characters from a string except those specified.
GETWORDCOUNT(): Counts the words in a string.
GETWORDNUM(): Returns a specified word from a string.
GETALLWORDS(): Inserts the words from a string into the table.
PROPER(): Returns from a character expression a string capitalized as appropriate for proper names.
RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right search.
ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified numeric expression (from 1 to 3999).
ROMANTOARAB(): Returns the number equivalent of a specified character Roman numeral expression (from I to MMMCMXCIX).
AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP.
Plus, there are CHM files in English, French, Spanish, German and Russian.
More than 6000 people have already downloaded my functions. I hope you will find them useful as well.
For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
Please, download the file
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115
With the best regards.
View 3 Replies
View Related
May 27, 2005
Ladies and Gentlemen,
I would like to offer you the following string functions Transact-SQL
GETWORDCOUNT() Counts the words in a string
GETWORDNUM() Returns a specified word from a string
AT() Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character
RAT() Returns the numeric position of the last (rightmost) occurrence of a character string within another character string
CHRTRAN() Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression
STRFILTER() Removes all characters from a string except those specified
OCCURS() Returns the number of times a character expression occurs within another character expression (include overlaps)
PADL() Returns a string from an expression, padded with spaces or characters to a specified length on the left side
PADR() Returns a string from an expression, padded with spaces or characters to a specified length on the right side
PADC() Returns a string from an expression, padded with spaces or characters to a specified length on the both sides
PROPER() Returns from a character expression a string capitalized as appropriate for proper names
RCHARINDEX() Is similar to a built-in function Transact-SQL charindex but the search of which is on the right
ARABTOROMAN() Returns the character Roman number equivalent of a specified numeric expression (from 1 to 3999)
ROMANTOARAB() Returns the number equivalent of a specified character Roman number expression (from I to MMMCMXCIX)
AT, PADL, PADR, CHRTRAN, PROPER are similar to functions Oracle PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP
More than 2000 people have already downloaded my functions. I hope you will find it useful as well.
For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
Please, download the file
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115
With the best regards.
View 2 Replies
View Related
Jun 3, 2005
Ya know...I don't think I would Ever be able to build those functions if I needed them.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50370
You must be a very clever developer
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
View 20 Replies
View Related
Apr 17, 2005
Ladies and Gentlemen,
I would like to offer you the following string functions Transact-SQL
GETWORDCOUNT() Counts the words in a string
GETWORDNUM() Returns a specified word from a string
AT() Returns the beginning numeric position of the first occurrence of a character expression within
another character expression, counting from the leftmost character
RAT() Returns the numeric position of the last (rightmost) occurrence of a character string within
another character string
OCCURS() Returns the number of times a character expression occurs within another character expression
PADL() Returns a string from an expression, padded with spaces or characters to a specified length on the left side
PADR() Returns a string from an expression, padded with spaces or characters to a specified length on the right side
PADC() Returns a string from an expression, padded with spaces or characters to a specified length on the both sides
PROPER() Returns from a character expression a string capitalized as appropriate for proper names
RCHARINDEX() Is similar to a built-in function Transact-SQL charindex but the search of which is on the right
ARABTOROMAN() Returns the character Roman number equivalent of a specified numeric expression
ROMANTOARAB() Returns the number equivalent of a specified character Roman number expression ...
For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
Please, download the file http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115
With the best regards
View 1 Replies
View Related
Apr 17, 2005
Description
GETWORDCOUNT() Counts the words in a string
GETWORDNUM() Returns a specified word from a string
AT() Returns the beginning numeric position of the first occurrence of a character expression within
another character expression, counting from the leftmost character
RAT() Returns the numeric position of the last (rightmost) occurrence of a character string within
another character string
OCCURS() Returns the number of times a character expression occurs within another character expression
PADL() Returns a string from an expression, padded with spaces or characters to a specified length on the left side
PADR() Returns a string from an expression, padded with spaces or characters to a specified length on the right side
PADC() Returns a string from an expression, padded with spaces or characters to a specified length on the both sides
PROPER() Returns from a character expression a string capitalized as appropriate for proper names
RCHARINDEX() Is similar to a built-in function Transact-SQL charindex but the search of which is on the right
For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
View 1 Replies
View Related
Sep 11, 2013
I have this function called fn_GetTimedKey() when I run it in my SQL2005 environment it works fine.
When I run it in my SQL2008R2 environment I get the error:
Only functions and some extended stored procedures can be executed from within a function.
The SQL2008R2 server is new. What can I look for?
Here's the code for the function:
BEGIN
DECLARE @v_Key CHAR(12)
EXEC master..xp_usg_gettimedkey_VAL @v_Key OUTPUT
RETURN @v_Key
END
View 4 Replies
View Related
Feb 7, 2006
User-Defined string Functions MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net)
Ladies and Gentlemen,
I am pleased to offer, free of charge, the following string functions MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net):
AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character.
RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string.
OCCURS(): Returns the number of times a character expression occurs within another character expression (including overlaps).
OCCURS2(): Returns the number of times a character expression occurs within another character expression (excluding overlaps).
PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side.
PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side.
PADC(): Returns a string from an expression, padded with spaces or characters to a specified length on the both sides.
CHRTRAN(): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.
STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters.
STRFILTER(): Removes all characters from a string except those specified.
GETWORDCOUNT(): Counts the words in a string.
GETWORDNUM(): Returns a specified word from a string.
GETALLWORDS(): Inserts the words from a string into the table.
PROPER(): Returns from a character expression a string capitalized as appropriate for proper names.
RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right search.
ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified numeric expression (from 1 to 3999).
ROMANTOARAB(): Returns the number equivalent of a specified character Roman numeral expression (from I to MMMCMXCIX).
AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP.
Plus, there are CHM files in English, French, Spanish, German and Russian.
Plus, there are versions for MS SQL SERVER, SYBASE ASA, DB2, Oracle.
More than 8000 people have already downloaded my functions. I hope you will find them useful as well.
For more information about string UDFs MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net) please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,29527
Please, download the file
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,29527
With the best regards.
View 1 Replies
View Related
Apr 18, 2008
i want to create a new table from one table like this
TIME_ID SALES LAG1 LEAD1
--------- ---------- ---------- ----------
10-OCT-00 238,479 23,183
11-OCT-00 23,183 238,479 24,616
12-OCT-00 24,616 23,183 76,516
13-OCT-00 76,516 24,616 29,795
14-OCT-00 29,795 76,516
i think i use some functions in my stored procedure can anyone help me about this stuation Thanks!!
View 3 Replies
View Related
Nov 30, 2005
Hi Guru,After spening quite sometimes to watch my box, I've seen PAGEIOLATCH isa lead blocker in my SQL Server 2000 server. Below is the detailed:SPID lastwaittype waitresource blocked status cmd57 LCK_M_S KEY: 7:963690681:8 65 sleeping execute65 PAGEIOLATCH_SH 7:1:217904 0 sleeping selectI thought, latching should be very short-term synchronization. Fromsystemprocess table, I saw the latch waited in a minute sleepingwithout doing any work.My database is about 23GB and more than 5000 tables. The RAID subsystemis RAID1 with 1 disk mapped to C and D logically. Data files and tempdbfiles are located in one location. Tranlog file and log backup filesare located in the same location with different disk spindle.Currently, we are experiencing very slowness and IO bound. I'm ready torebuild the server by putting the RAID10 and 1 and distributingmultiple data files to different RAID10 and tempdb and log files toRAID1.Other than this, how to minimize the IO latch contention?Thanks so much,Silaphet,
View 1 Replies
View Related
May 27, 2008
Hi,
I am performing data migration from a legacy sql server (2000) to a new one (SQL 2005). I am using SSIS packages to perform the migration.
The source legacy sql server is a live machine (used by the customer). Therefore, during the data transfer there would certainly be new records inserted/updated/deleted on the source legacy sql server.
Now, after the migration i wish to migrate the delta to the new sql server. how can i do this ?
Does SSIS have any provision to take care of it ?
View 2 Replies
View Related
Oct 13, 2006
how can I capture delta in SQL Server 2005 to refresh base tables in a data warehouse?
View 8 Replies
View Related
May 25, 2006
Hi ,
i'm new to this platform and am wondering if there is an easy way to identify ADDs changes and deletes between two tables - or whether i will have to do a ror for row comparison using the business key?
Any suggestions would be greatly appreciated..
thank you George Bassili
View 1 Replies
View Related
Jun 5, 2006
Hi guys,
Do you have any stored procs or utility to track down the lead blocker as well as the blocked processes on SQL Server 2000? Similar to a tree structure with the lead blocker on top followed by the processes being blocked by the lead blocker.
Thank you very much.
View 3 Replies
View Related
Mar 5, 2007
Starting with a SQL Server 2000 database, my company built a new server with SQL Server 2005 and restored the SS2000 database from backups. We're now seeing some extreme issues where queries that took 30 seconds now take 3 minutes. Would this make you suspect index issues? Any recommendations on diagnosing and fixing this would be appreciated.
View 4 Replies
View Related
Jul 29, 2005
-- Business Rule, first name, middle name and last name can all be null-- ddlcreate table #cat (catID char(8) primary key, first_name varchar(15)null, middle_name varchar(2) null, last_name varchar(15) null)-- dml, populate sample datainsert into #catvalues ('Black123','ghost','','bigger')insert into #catvalues ('Arab0123','Hama','','Abbas')insert into #catvalues ('Mixed001','',null,null)insert into #catvalues ('Mixed002',null,null,null)insert into #catvalues ('Mixed003',null,'','Smith')insert into #catvalues ('White123','','','Talley')insert into #catvalues ('Yello123','Nick','H','Pisa')-- dml, name concatenation, get all or anyselect (first_name + ' ' + middle_name + ' ' + last_name) as namefrom #cat-- the above does not meet with requirement-- option 1select (IsNull(first_name,'') + ' ' + Case Len(middle_name) when 0 then'' else IsNull((middle_name + ' '),'') end + IsNull(last_name,'')) asnamefrom #cat-- option 2select (IsNull(first_name,'') + ' ' +IsNull(NullIf(Coalesce((middle_name + ' '),''),''),'') +IsNull(last_name,'')) as namefrom #catq:both option 1 and option 2 produces same result, which one is moredesirable?TIA.
View 4 Replies
View Related
Aug 31, 2015
I am migrating the BE of an Access app. to SQL server 2012. I need to get the user's login name (Windows Authentication login). This can be done using xp_cmdshell, but, xp_cmdshell is considered dangerous and I wouldn't be able to run it once I deploy the app. to the company servers (currently I have SQL server on my computer and as an admin I can enable xp_cmdshell to run, but IT doesn't allow it in company servers for security reasons).
Another question, is it possible to send data from the logged in user from Access to SQL server? What I need to do is let SQL know the username of the logged in user, then, use it to filter the data on SQL. Idea is that user can only run queries for his data (he can't view other user's data unless he is a manager or an admin (currently the app. in Access logs the user in automatically if his Windows Domain username is found in the user's table, and set's his role found in the Roles table). It is this functionality that is giving me some problems to migrate to SQL.
I created a function that uses the System_User SQL built-in function, this retrieves the SQL login username, but, the app. uses 1 SQL local account to connect to the server, so in essence it doesn't work as I need the Windows Domain account username.
View 10 Replies
View Related
Feb 14, 2007
Suppose a database server and client are separated by a low bandwidthlink such as DSL, and the client repeatedly issues a query for, say, acurrent product list.Suppose the product list is large, but only a handful of entries havetypically changed between queries. It would be nice if only the changesfrom last query to current one could be sent, saving bandwidth.Is there any way to do this?Thanks,--"Always look on the bright side of life."To reply by email, replace no.spam with my last name.
View 3 Replies
View Related
Apr 4, 2007
We are trying to configure Delta View in our senario and are confused at a couple of points:
1. What is the difference between modify and modify_attribute? How does modify_attribute works at attribute level? Is it only for multivalued attributes?
2. Rather than having an extra table for Delta. If we would use the prime table itself with an extra field acting as a flag (0/1) (0=Prime Record, 1=Delta Record) and generate the delta view according to the flag. Would it be fine or we should have a different delta table only?
View 2 Replies
View Related
Feb 25, 2008
I'm working on a program that reads in zip codes and then does some checking on them. Occasionally, the end user typos or transposes digits. It's easy enough to identify bad zip codes, but I would like to offer up a single recommendation. The problem is that there are often multiples to choose from. Is there a function or has anyone tried to figure out the delta between two strings?
Thanks
View 4 Replies
View Related
Jan 27, 2006
I am using Northwind database to Create a view showing every order that was shipped to Spain. Name the destination column 'DestinationSpain'. Include code that checks if the view already exists. If it does, it should be dropped and re-created.
Here is my script:
use Northwind
GO
/*STEP 2, #1*/
/* does it exist, if so drop it */
if exist (select * from dbo.sysobjects
where id = object_id(N'[dbo].[OrdersToSpain]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[OrdersToSpain]
GO
/* Create the View */
create view "OrdersToSpain" AS
SELECT
Orders.OrderID AS Order_ID,
Orders.CustomerID AS Customer_ID,
Orders.OrderDate AS Ordered_Date.
Orders.ShippedDate AS Shipped_Date,
Orders.ShipCountry AS DestinationSpain
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.ShipCounty LIKE '%SPAIN%'
GO
Here are the errors I am getting:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.
Server: Msg 170, Level 15, State 1, Procedure OrdersToSpain, Line 7
Line 7: Incorrect syntax near '.'.
View 6 Replies
View Related
Jan 20, 2007
I'm a newbe in the realm of database reporting. At my current position, I'm reporting off of CRM databases using Crystal V-11. Previously I'd experience working with HR databases using the same reporting tool. I am interested in progressing to work with database design and scripting. Any suggestion from anyone on which certifications to pursue?
Thanks.
View 1 Replies
View Related
Jun 16, 2015
SQL Sever 2012, I have a table where I store data related with a Log, the columns of the table are three, IDTable, DateLog and CountLog, the column CountLog is a cumulative value, each new rows can increment this value, my query will receive two parameters related with the Date, @dateFrom and @dateTo, with this information is necesary to get the diff between two months, for example:
CREATE TABLE MyTable
(
IDTable INT IDENTITY NOT NULL,
DateLog DATE NOT NULL,
CountLog INT NOT NULL
)
[code]....
In this case I need to apply a filter with the Dates:
@dateFrom ='2014-01-01',
@dateTo ='2014-04-28'
But according with this filter I need to limit the results only for this period between the date, but I need that the first month (January 2014) take the information of the max value from previous month even not included in the filter, in this case the results will be the next:
Id MonthYear Diff
1 Jan2014 5
2 Feb2014 3
3 March2014 0
4 April2014 5
With the results, for the first mont (January) is, according to the range of times, January is the lower limit,the last cumulative value of CountLog before the first month of the range was 2 (December 2013), the max value of the CountLog for this first month was 7, the diff is 7-2 equal to 5 , in the case of the next month (February) the max value of the CountLog was 10, the diff is 10 - 7 equal to 3 and this way.
[URL]
View 16 Replies
View Related
Sep 24, 2015
I have set up two SQL server (e.g. Server1 and Server2) which are set up to use Windows Authentication with my login (e.g. login1).On Server1 I have created a linked server to Server2, I have then selected the Security tab and added my local login and selected impersonate. I have then selected ‘Be Made using the login’s current security context’.I then receive the error message ‘The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?’
View 9 Replies
View Related
Dec 5, 2015
How can i subtract two columns:
I have table:
ID COL 1 COL 2
-------------------------
1 200.00 70.00
2 200.00 30.00
3 200.00 90.00
4 200.00 110.00Col1 - COL2
But to continue for each row and value is reduced by the previously value.
My output should be like as:
ID COL 1 COL 2 [COL3 AS RESULT]
-------------------------
1 200.00 70.00 130
2 200.00 30.00 100
3 200.00 90.00 10
4 200.00 110.00 -100
View 3 Replies
View Related
Dec 30, 2011
I need to get the replacement records between the 2 tables. I have table A and table B with same structure. I have 5 fields. Table A has 50,000 records and table B has 20,000 records. I have fields id , name, address,meter_flag,end_Date.
Some of the records in Table B are just replacement records of table A. I mean for example I have records like this in Table A
id name address meter_flag end_date
23 john 1201 salt lake dr no 2011-12-28
24 tom 1222 gibson ln yes 2011-12-16
25 alex 1334 qayak dr no 2011-12-17
In Table B
23 john 1344 mc kinney st yes 2011-12-18
24 tom 1222 gibson ln yes 2011-12-16
56 gary 1335 pruitt rd no 2011-12-18
25 alex 1334 qayak dr no 2011-12-17
So here in Table B i have an update for john with id 23 in table A in address field and meter_flag has changed to yes. There is new record with id 25 in table b but that is not in table A. so I need to find all these difference records by querying these 2 table
View 9 Replies
View Related
Dec 3, 2006
Hi all,
In an approach of building an ETL tool, we are into a situation wherein, a table has to be loaded on an incremental basis. The first run all the records apporx 100 lacs has to be loaded. From the next run, only the records that got updated since the last run of the package or newly added are to be pulled from the source Database. One idea we had was to have two OLE DB Source components, in one get those records that got updated or was added newly, since we have upddate cols in the DB getting them is fairly simple, in the next OLEDB source load all the records form the Destination, pass it onto a Merge Join then have a Conditional Split down the piple line, and handle the updates cum insert.
Now the question is, how slow the show is gonna be ? Will there be a case that the Source DB returns records pretty fast and Merge Join fails in anticipation of all the records from the destination ?
What might be the ideal way to go about my scenario.. Please advice...
Thanks in advance.
View 13 Replies
View Related
Jan 9, 2014
We have accounts that pay for a particular "premium" service. It's entirely possible an account paid for this service for three consecutive months in 2013, then stopped paying, then started paying again. Why I'm trying to establish is, for the FIRST period of time the accout paid for this service, for how many consecutive months did they pay? Here is my test data:
if object_id('tempdb..#SampleData') is not null
drop table #SampleData
go
if object_id('tempdb..#DateAnalysis') is not null
drop table #DateAnalysis
go
-- Create temp tables for sample data
create table #SampleData
(AccountID int,
RandomDate datetime)
create table #DateAnalysis
(RowID int identity(1,1),
AccountID int,
RandomDate datetime,
NextDate datetime,
LeadInMonths int)
-- Insert some sample data
insert into #SampleData values
(1, '1/1/13'), (1, '2/1/13'), (1, '3/1/13'), (1, '6/1/13'), (1, '10/1/13'), (1, '11/1/13'), (1, '12/1/13'),
(2, '1/1/13'), (2, '4/1/13'), (2, '5/1/13'), (2, '6/1/13'),
(3, '2/1/13'), (3, '3/1/13'), (3, '4/1/13'), (3, '9/1/13'), (3, '10/1/13'), (3, '11/1/13')
-- Use lead function to determine how many months are between
-- consecutive dates per account
; with DateInterval as
(select AccountID, RandomDate,
NextDate = lead (RandomDate, 1, NULL) over (partition by AccountID order by RandomDate)
from #SampleData)
insert into #DateAnalysis
select AccountID, RandomDate, NextDate,
datediff(mm, RandomDate, NextDate) as 'Lead'
from DateInterval
where NextDate is not null -- Last row will contain NULL for NextDate. Don't include these rows.
-- Show the results
select *,
'NTile' = NTILE(3) over (partition by AccountID order by RandomDate),
'RowNum' = row_number() over (partition by AccountID order by RandomDate)
from #DateAnalysis
Results (this is not getting me what I'm looking for):
RowIDAccountIDRandomDateNextDateLeadInMonthsNTileRowNum
11 2013-01-012013-02-01111
21 2013-02-012013-03-01112
31 2013-03-012013-06-01323
41 2013-06-012013-10-01424
51 2013-10-012013-11-01135
61 2013-11-012013-12-01136
72 2013-01-012013-04-01311
82 2013-04-012013-05-01122
[code]....
This is what I'm trying to achieve:
RowIDAccountIDRandomDateNextDateLeadInMonthsRankForThisLeadInMonths
11 2013-01-012013-02-0111
21 2013-02-012013-03-0111
31 2013-03-012013-06-0132
41 2013-06-012013-10-0143
51 2013-10-012013-11-0114
61 2013-11-012013-12-0114
[code]....
The problem comes with accounts like AccountID = 1. They paid consecutively to start, then skipped, then started paying consecutively again. When using window functions, I'm running into trouble attempting to partition by AccountID and LeadInMonths. It's putting all the LeadInMonths = 1 together and that will give me skewed results if I want to know the earliest and latest date within the FIRST consecutive range of dates where the account paid. I've tried NTILE but it expects an integer and there's no telling how many "tiles" would be in AccountID partition.
I've looked at the OVER clause and the new "ROWS BETWEEN" syntax and still cannot get the desired results.
View 5 Replies
View Related
Mar 25, 2014
I have 12 month report and I need show volume and difference between current and prev month volume, what is the smart way to do this, do I need to put prev month value onto same row horizontally? I think should be some other smart way, I heard about LEAD function?
This what I think for now, It should be listed per ClientID also, in my example I have single ClientID for simplicity.
I tried to do LEAD but with not success..
/*
IF OBJECT_ID('tempdb..#t') is not null drop table #T;
WITH R(N) AS
(SELECT 1 UNION ALL SELECT N+1 FROM R WHERE N <= 12 )
SELECT N as Rn,
10001 ClientID,
DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())) AS [Month],
[code]....
View 2 Replies
View Related
Apr 8, 2015
i would like to know the best practices to choose the columns which should be used for delta?.If, i consider Customer ID as part of delta.
View 1 Replies
View Related
Feb 5, 2015
function
/*Title:
Created By:
Create Date:
Notes:This function is used to concatenate the fields of a table except any identity and hashbyte column passed into the function. It works for temp tables are phyisical tables.
** NOTE: The temp table has be on the same SQL connection to work. If you use this in SSIS you will need to make your connection persistant.
The original concept came from [URL] .... and was modified.
Revisions:
*/
ALTER FUNCTION [dbo].[get_hash_fields] ( @p_table_name VARCHAR(128),
@p_schema_name VARCHAR(20),
@chksum_col_name varchar(255) )
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @sqlString as varchar(max)
[Code] ....
View 0 Replies
View Related
