Wednesday, April 24, 2013

[T-SQL] Add column with previous days results

[T-SQL] Add column with previous days results

Add column with previous days results

Posted: 24 Apr 2013 12:02 AM PDT

Hi,Please help me out with the below query.We are calculating Exposure column (Hilighted in the query) based on current date and my requirement now is to add a new column next to it with yesterdays values (Currentdate-1). Please help me out.SQLServer 2008 R2-----------------------------SELECT --bi.cobdate,fact.batchid, fact.mastergroup, fact.counterparty, fact.counterpartyname, fact.parentcounterpartyname AS ParentCounterparty --If No Parent, then show the original CP as Parent , fact.portfoliolevelcd AS AggregationScheme, fact.portfolionodevalue AggregationNode, ptycc.anzccr AS CCR, ptycc.securityindicator AS SI, fact.limittimeband, fact.limitstartdt AS [Start_Date], fact.limitenddt AS [End_Date], COALESCE(ext.currency, fact.limitcurrency) AS LocalCurrency, fact.limitcurrency AS LimitCCY, COALESCE(fun.currency, fun1.limitcurrency) LocalExchnagerate, fun1.limitcurrency AS LimitExchnagerate, ( fun1.curvepointvalue / fun.curvepointvalue ) * CONVERT(FLOAT, fact.limitamount) AS Limit --,fun.CurvePointValue LocalCurrency , fun1.curvepointvalue Limitcurrency, pfe.riskvalue AS Exposure --,pfe.RiskValue2 as "ExposureT-1" , ( ( fun1.curvepointvalue / fun.curvepointvalue ) * CONVERT(FLOAT, fact.limitamount) - pfe.riskvalue ) AS Availability, ( CASE WHEN ( ( fun1.curvepointvalue / fun.curvepointvalue ) * CONVERT(FLOAT, fact.limitamount) ) IS NULL OR pfe.riskvalue IS NULL THEN 0 WHEN ( ( fun1.curvepointvalue / fun.curvepointvalue ) * CONVERT(FLOAT, fact.limitamount) ) = 0 AND pfe.riskvalue > 0 THEN 1 WHEN ( ( fun1.curvepointvalue / fun.curvepointvalue ) * CONVERT(FLOAT, fact.limitamount) ) = 0 AND pfe.riskvalue = 0 THEN 0 ELSE Cast(( Isnull(pfe.riskvalue, 0) / ( ( fun1.curvepointvalue / fun.curvepointvalue ) * CONVERT( FLOAT, fact.limitamount) ) ) AS FLOAT) END ) AS Utilisation, ptycc.creditcontrolpoint AS ControlPoint, fact.[CollateralAgreementCd] CollateralApplied, fact.[NettingAgreementCd] NettingApplied, fact.israzor FROM dw.[factlimitutilizationbyportfolio] FACT (nolock) INNER JOIN (SELECT br.batchrunid BatchID, bi.businessdate CobDate, bi.batchinstanceid, br.startdatetime AS ReportingDate FROM logging.batchrun br (nolock) INNER JOIN logging.batchinstance bi (nolock) ON br.batchinstanceid = bi.batchinstanceid) BI ON fact.batchid = Bi.batchid INNER JOIN dw.partycreditcontrol ptycc (nolock) ON fact.counterpartyid = ptycc.partyid AND ( ptycc.effstartdate <= bi.cobdate AND ptycc.effenddate > bi.cobdate ) INNER JOIN dw.portfolio port (nolock) ON fact.portfolioid = port.portfolioid AND port.providersystemcd = 'Razor' AND port.portfoliolevelcd = 'Customer Asset Group' AND port.effstartdate <= bi.cobdate AND port.effenddate > bi.cobdate LEFT JOIN dw.portfoliobridge bport (nolock) ON ( bport.tgtportfolioid = fact.portfolioid AND bport.tgtprovidercd = 'Razor' AND bport.effstartdate <= bi.cobdate AND bport.effenddate > bi.cobdate ) LEFT JOIN (SELECT t.portfolioid, t.limittimeband, Max(t.maxexposure) AS RiskValue, t.batchid FROM dw.factlimitutilizationbyportfolio t (nolock) INNER JOIN dw.riskmeasuredefinition rmd (nolock) ON t.riskmeasuredefinitionid = rmd.riskmeasureid AND rmd.riskmeasurename = 'PFE_LC' AND t.isfact = 1 GROUP BY t.portfolioid, t.limittimeband, t.batchid) pfe ON bport.srcportfolioid = pfe.portfolioid AND bport.srcprovidercd = 'CRE' AND fact.limittimeband = pfe.limittimeband AND fact.batchid = PFE.batchid LEFT JOIN (SELECT DISTINCT portfolioid, currency, runid FROM extract.razorportfoliotraderelation)ext ON fact.batchid = ext.runid AND PFE.portfolioid = ext.portfolioid LEFT JOIN (SELECT cpt.curvepointvalue, cdt.observationdt, cid.curveidentifier currency FROM dw.curveidentifier cid (nolock) INNER JOIN dw.curvedata cdt (nolock) ON cid.curveid = cdt.curveid INNER JOIN dw.curvepoint cpt (nolock) ON cdt.curvedataid = cpt.curvedataid WHERE cid.curvetype = 'Exchange' AND cid.curvedomain = 'QuIC' AND cid.islatest = 1 AND cdt.islatest = 1 AND cpt.islatest = 1 AND cdt.ccycd = 'USD') fun ON fun.observationdt = bi.cobdate AND fun.currency = ext.currency LEFT JOIN (SELECT cpt.curvepointvalue, cdt.observationdt, cid.curveidentifier LimitCurrency FROM dw.curveidentifier cid (nolock) INNER JOIN dw.curvedata cdt (nolock) ON cid.curveid = cdt.curveid INNER JOIN dw.curvepoint cpt (nolock) ON cdt.curvedataid = cpt.curvedataid WHERE cid.curvetype = 'Exchange' AND cid.curvedomain = 'QuIC' AND cid.islatest = 1 AND cdt.islatest = 1 AND cpt.islatest = 1 AND cdt.ccycd = 'USD') fun1 ON fun1.observationdt = bi.cobdate AND fun1.limitcurrency = fact.limitcurrency WHERE isfact = 0-----------------------------Thanks,Nagarjun.

Set-Based Solution to this Problem?

Posted: 23 Apr 2013 02:36 AM PDT

Hello there! I've got an interesting situation I'm handling at present; I've developed a means of completing the task at hand, but it requires a WHILE loop at present, and I was wondering if I could remove the loop. Performance is good; for the use cases the procedure will run in, it completed in a few seconds, which is acceptable. However, should the use case scale up, I'd like to keep it from bogging down, if I can.Basically, I've got a lot of databases (100+), all with identical tables and table structures, and a PHP front-end where users will select projects from a checklist, and pass the project names to SQL Server, where a corresponding database name is picked up and a short UPDATE is run. So, it ends up like this:[code]CREATE TABLE #Test(ProjectName varchar(75), DBName varchar(75))INSERT INTO #Test(ProjectName, DBName)VALUES('Project1','DB1'),('Project2','DB2'),('Project3','DB3')[/code]The ProjectName is used to JOIN the #Test table to another table that contains the project names and their associated database name, and retrieves the DBName for each project, but I'm just supplying dummy DBNames here. From there, the update goes like so:[code]DECLARE @DBName varchar(75)DECLARE @sqlstatement varchar(1000)DECLARE @Counter intSELECT @Counter = (SELECT COUNT(DBName) FROM #Test)WHILE @Counter > 0BEGINSELECT @DBName = (SELECT TOP 1 DBName FROM #Test ORDER BY DBName ASC)SELECT @sqlstatement = 'UPDATE [' +@DBName+ '].dbo.tablename SET ...'EXEC(@sqlstatement)DELETE FROM #Test WHERE DBName = @DBNameSELECT @Counter = @Counter - 1END[/code]As stated, this works out nicely, and does what it needs to do quickly enough; however, is there any way to abolish the WHILE loop in there? I'm inclined to think there isn't, since this has to go across a subset of databases and doing so almost always requires a loop, but I could very well be wrong. Thanks in advance for any help provided here!

How To Sum...

Posted: 23 Apr 2013 10:08 PM PDT

I Having Following Table Structure....[code="sql"]Create Table Adding (ID int identity(1,1),Result int,ActualResult int)insert into Adding (Result) values (10),(10),(10),(10),(-10),(-10),(-10),(-10)select * from AddingBut My Required Result Should be below Mentioned...ID Result RequiredResult1 10 02 10 203 10 304 10 405 -10 306 -10 207 -10 108 -10 0Example :----------{( Result + ActualResult ) = ActualResult Then ( ActualResult+ Result ) = ActualResult ..... 10 + 0 = 10 Then 10 +10 = 20 Then 20 +10 = 30 like that is going....}[/code]Thanks & Regards,Saravanan.D

Two SQL Staatements different results

Posted: 23 Apr 2013 08:25 PM PDT

Got a feeling this is something to do with an implied cast that I dont understanddeclare @DataReady INTSELECT @DataReady = 1if not exists (select start_lsn from cdc.lsn_time_mapping where start_lsn <= 0X0002696800000AE90002 ) select @DataReady = 0 if not exists (select start_lsn from cdc.lsn_time_mapping where start_lsn >= 0X0002696E000002EE0001 ) select @DataReady = 2 select @DataReady as PkgLSNsValidated declare @start_lsn binary(10), @end_lsn binary(10) declare @start_lsn_str nvarchar(42), @end_lsn_str nvarchar(42) declare @DataReady1 int set @start_lsn_str = 0X0002696800000AE90002 set @end_lsn_str = 0X0002696E000002EE0001 set @start_lsn = sys.fn_cdc_hexstrtobin(@start_lsn_str) set @end_lsn = sys.fn_cdc_hexstrtobin(@end_lsn_str) select @DataReady1 = 1 if not exists (select start_lsn from cdc.lsn_time_mapping where start_lsn <= @start_lsn_str ) select @DataReady1 = 0 if not exists (select start_lsn from cdc.lsn_time_mapping where start_lsn >= @end_lsn_str ) select @DataReady1 = 2 select @DataReady1 as PkgLSNsValidated The first set of SQL returns 0 implying that the value is not validThe second set of SQL returns 1 implying that the value IS validThe second set has to accept the LSNs as string values (using SSIS)Whats going on? Which is right and why?Many thanksM

Pratical Advantage of Heap

Posted: 23 Apr 2013 07:39 PM PDT

Hi all experts,Is there any practical advantage of using Heap as a storage instead of clustered index for table.

incrementing

Posted: 23 Apr 2013 01:46 PM PDT

I have this table with these info:ID intFirstorder intSecondorder intThirdorder intsequenceNumber intHow would I write a script so that the result would be:Before the script:ID firstorder secondorder thirdorder sequencenumber 1 null null null 12 null null null 2100000 null null null 100000The logic for the script is: The initial value for firstorder, secondorder, and thirdorder is 1for 1..10 (sequencenumber) : first order would be 1, from 11..20 would be 2, etc…for 1..100 (sequencenumber): secondorder would be 1, from 101 to 200 would be 2, etc…for 1..10000 (sequencenumber): thirdorder would be 1, from 10001 to 20000 would be 2, etc..After executing the script:ID firstorder secondorder thirdorder sequencenumber1 1 1 1 110 1 1 1 1011 2 1 1 11Thanks for all the help.

SQL Server equivalent for MySQL's Substring_index

Posted: 23 Apr 2013 06:44 AM PDT

Thanks for looking in to my question.Does anyone know if there is an equivalent function available for MySQL's substring_index..? MySQL SUBSTRING_INDEX() returns the substring from the given string before a specified number of occurrences of a delimiter. SUBSTRING_INDEX(str, delim, count) SELECT SUBSTRING_INDEX('www.mytestpage.info','.',2)Returns -> 'www.mytestpage'.Thanks!Siva.

Trigger to delete old data before Insert

Posted: 23 Apr 2013 02:42 AM PDT

Hi guys .. i am trying to create Trigger on sql 2000 . creating trigger which deletes old data more then 30 days and then do inserts. any scripts for this .would be great help Thanks

While Loop/ CTE Statement

Posted: 23 Apr 2013 02:22 AM PDT

HiI have a table, PR_LINK_INV_HST, and I need to find all the records on PR_LINK_INV_HST where the "Client (CSN)" = PR_LINK_INV_HST.CLIENT_ID or PR_LINK_INV_HST.LINKED_CLIENT_ID. and return all LINK_CLIENT_ID's and CLIENT_ID's for the specified "Client (CSN)". then continue looping through the PR_LINK_INV_HST table to also find the clients that are linked to the linked clients of the specified "Client (CSN)".so this is my table PR_LINK_INV_HST(CLIENT_ID, LINK_CLIENT_ID)Can anyone help help me on how to get this right because the query I have gives me an infinite loop.My Query:Create TABLE #PR_LINK_INV_HST (CLIENT_ID varchar(15), NAME varchar(15), LINK_CLIENT_ID varchar(30))Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','linked to Diana (1-14)')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','linked to Mary (1-33)')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-14','Diana','not linked')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','linked to Smith (1-16)')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','linked to Pope (1-17)')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-16','Smith','not linked')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-17','Pope','linked to Thabo (1-19)')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-19','Thabo','not linked')DECLARE @CLIENT_ID VARCHAR(15)SET @CLIENT_ID = '1-23' ;WITH pr_linked(CLIENT_ID, NAME, LINK_CLIENT_ID, [LEVEL])AS( SELECT CLIENT_ID, Name, LINK_CLIENT_ID, 0 FROM #PR_LINK_INV_HST WHERE CLIENT_ID = @CLIENT_ID UNION ALL SELECT HST.CLIENT_ID, HST.Name, HST.LINK_CLIENT_ID, LNK.[LEVEL] + 1 FROM #PR_LINK_INV_HST HST JOIN pr_linked LNK ON HST.CLIENT_ID = LNK.CLIENT_ID )SELECT * INTO #RESULTSFROM pr_linkedselect * from #RESULTS

Is there any native support available for JSON in SQL SERVER ?

Posted: 05 Sep 2012 05:31 PM PDT

Is there any native support available for JSON in SQL SERVER ? ie. Is there any inbuilt tools in SSIS which can be used as a source ?

not exists versus not in

Posted: 23 Apr 2013 02:00 AM PDT

Looking at the 2 statements should they not return the same?select *,'I' from pharmdb.pat.dbo.patients where PHPatid not in (select PHPatid from Patients)select *,'I' from pharmdb.pat.dbo.patients where not exists (select phpatid from Patients inner join pharmdb.pat.dbo.patients p on p.phpatid = patients.PHPatid)2nd statement returns no records
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog