Thursday, June 27, 2013

[T-SQL] "Running totals" query

[T-SQL] "Running totals" query

"Running totals" query

Posted: 26 Jun 2013 02:22 AM PDT

I need to create a table of running totals. Suppose initially we start with $50, then write a check for $3 (leaving $47), then write another check for $7 (leaving $40), and finally a check for $9 (leaving $31). Here's the end table.check # check_amount Available_Funds----------------------------------------------0000 0 500001 3 470002 7 400003 9 31Other than using a cursor to populate the Available_funds column how could you generate those values using t-sql?TIA,BD

Need help ANSI-92'ing a subquery join

Posted: 26 Jun 2013 01:04 PM PDT

I'm working on converting some old Sybase DBs to SQL, and am stuck on one query. Here's the original:/* originalSELECT A.recid, A.id, A.procid, A.pstatus, B.pname, B.pdesc, LEN(pname) AS plen, CASE WHEN C.seq IS NULL THEN 0 ELSE 1 END AS DISABLEDFROM lprocs A, pmaster B, pmap CWHERE A.procid = B.procid AND A.id = @id AND C.procid =* A.procid AND C.typeid =* ( SELECT D.typeid FROM loan D WHERE D.id = A.id )ORDER BY pname*/And here's my (weak) attempt /* my attempt - obviously produces a different query planSELECT A.recid, A.id, A.procid, A.pstatus, B.pname, B.pdesc, LEN(pname) AS plen, CASE WHEN C.seq IS NULL THEN 0 ELSE 1 END AS DISABLEDFROM lprocs A JOIN pmaster B ON A.procid = B.procid LEFT OUTER JOIN pmap C ON C.procid = A.procidWHERE A.id = @id AND C.typeid in ( SELECT D.typeid FROM loan D WHERE D.id = A.id )ORDER BY pname*/Please help, thanks!

Query showing tables with a specific column in them

Posted: 27 Jun 2013 12:48 AM PDT

I must be doing this wrong..becouse I get columns with no matching table.Here is the query I created to show tables that have a column with the name "solutionscenariocode" as the column name.USE [asccdb]select col.[name] as Column_Name, tbl.[name] as Table_Name from sys.columns colinner join sys.tables tblon tbl.[object_id] = col.[object_id]where col.[name] like 'solutionscenariocode'Checking query should be empty, but it is notUSE [asccdb]select col.[name] as Column_Name, tbl.[name] as Table_Name from sys.columns colleft outer join sys.tables tblon tbl.[object_id] = col.[object_id]where col.[name] like 'solutionscenariocode' and tbl.[object_id] is NULL

Date Query Issue

Posted: 26 Jun 2013 07:32 AM PDT

Hello Everyone - this seams easier than it has been.Here's the sample data:[code="sql"]DECLARE @tblJobHist_source TABLE( SeqNo int UNIQUE NOT NULL ,startYear int NULL ,endYear int NULL ,number int NOT NULL ,name varchar(255) NOT NULL);DECLARE @tblJobHist_resultSet TABLE( SeqNo int UNIQUE NOT NULL ,startYear int NULL ,endYear int NULL ,number int NOT NULL ,name varchar(255) NOT NULL);INSERT INTO @tblJobHist_source SELECT 5,1986,1988,1,'C & B LLC (US)' UNION ALL SELECT 10,1991,1992,1, 'Government of the Iceland' UNION ALL SELECT 15,1992,1996,1, 'O''Martin LLP' UNION ALL SELECT 20,1996,1998,1, 'WorldCo, Inc.' UNION ALL SELECT 25,1998,2000,1, 'WorldCo, Inc.' UNION ALL SELECT 30,2000,2002,1, 'XXX, Inc.' UNION ALL SELECT 35,2002,2003,1, 'WorldCo, Inc.' UNION ALL SELECT 40,2004,2006,1, 'Government of the Iceland' UNION ALL SELECT 45,2006,2010,1, 'Government of the Iceland' UNION ALL SELECT 50,2010,NULL,1, 'Pauls Equity Partners LLC' UNION ALL SELECT 55,NULL,NULL,1, 'ACME Consulting, Inc.';INSERT INTO @tblJobHist_resultSet SELECT 55,NULL,NULL,1,'ACME Consulting, Inc.' UNION ALL SELECT 50,2010,NULL,1,'Pauls Equity Partners LLC' UNION ALL SELECT 45,2004,2010,2,'Government of the Iceland' UNION ALL SELECT 40,2004,2010,2,'Government of the Iceland' UNION ALL SELECT 35,2002,2003,1,'WorldCom, Inc.' UNION ALL SELECT 30,2000,2002,1,'XXX, Inc.' UNION ALL SELECT 20,1996,2000,2,'WorldCo, Inc.' UNION ALL SELECT 25,1996,2000,2,'WorldCo, Inc.' UNION ALL SELECT 15,1992,1996,1,'O''Martin LLP' UNION ALL SELECT 10,1991,1992,1,'Government of the Iceland' UNION ALL SELECT 5,1986,1988,1,'C & B LLC (US)';-- WHAT I HAVE NOW...SELECT * FROM @tblJobHist_source;-- WHAT I NEED...SELECT * FROM @tblJobHist_resultSet ORDER BY SeqNo DESC; [/code]What I have now: [code="plain"]SeqNo startYear endYear number name5 1986 1988 1 C & B LLC (US)10 1991 1992 1 Government of the Iceland15 1992 1996 1 O'Martin LLP20 1996 1998 1 WorldCo, Inc.25 1998 2000 1 WorldCo, Inc.30 2000 2002 1 XXX, Inc.35 2002 2003 1 WorldCo, Inc.40 2004 2006 1 Government of the Iceland45 2006 2010 1 Government of the Iceland50 2010 NULL 1 Pauls Equity Partners LLC55 NULL NULL 1 ACME Consulting, Inc.[/code]What I need:[code="plain"]SeqNo startYear endYear number name55 NULL NULL 1 ACME Consulting, Inc.50 2010 NULL 1 Pauls Equity Partners LLC45 2004 2010 2 Government of the Iceland40 2004 2010 2 Government of the Iceland35 2002 2003 1 WorldCom, Inc.30 2000 2002 1 XXX, Inc.25 1996 2000 2 WorldCo, Inc.20 1996 2000 2 WorldCo, Inc.15 1992 1996 1 O'Martin LLP10 1991 1992 1 Government of the Iceland5 1986 1988 1 C & B LLC (US)[/code]As you can see there are a couple companies that have connecting startYear & endYear... For those the I need to merge the dates; in other words, I need to change this:20 1996 1998 1 WorldCo, Inc.25 1998 2000 1 WorldCo, Inc.into this:25 1996 2000 2 WorldCo, Inc.20 1996 2000 2 WorldCo, Inc.Let me know if you need more clarity. Order is not important. Thanks a ton!

Logic for a complex query which involves grouping and average in SQL

Posted: 26 Jun 2013 07:09 AM PDT

I have 2 tables. Below are the steps I need to follow to get my desired output. I could follow upto Step 3 .Kindly help me as it is a little complex and Im unable to understand how to proceed further. Table 1 [code="sql"] Site Code FailFlag Comments ModifiedDate ModifiedBy ABT A01 F Dfasdf 10/11/2011 Anna ABT A01 F dsfsdf 15/12/2012 Mand ABT A01 Rds 30/03/2011 Tim ABT A01 GHDs 02/12/2012 Andy ABT A02 F dfd 09/05/2012 Anna ABT A02 sdada 11/02/2013 Kathy ABT A02 Dfg 15/05/2011 Rob AFL A02 F asda 13/02/2011 Dan AFL A02 dsaa 24/12/2010 Ryan TRG A01 sdasd 16/04/2010 Richard TRG K05 jksdh 23/04/2012 Mark KLD K05 F sd 18/05/2013 Jim KLD K05 dsfsd 10/03/2012 James KLD K05 sdsd 12/05/2011 Luther KTY K05 F saq 09/09/2012 Ryan KTY K05 asd 04/04/2010 Kathy KMD C02 F nas 29/02/2012 Rob KMD C02 asda 11/11/2011 Andy[/code][code="sql"]CREATE TABLE Table1 ( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Site VARCHAR(5), Code VARCHAR(5), FailFlag CHAR(1), Comments VARCHAR(100), ModifiedDate DATETIME, ModifiedBy VARCHAR(50) )[/code][code="sql"]INSERT INTO Table1 (Site, Code, FailFlag, Comments, ModifiedDate, ModifiedBy)SELECT 'ABT', 'A01', 'F', 'Dfasdf', '10/11/2011', 'Anna' UNION ALLSELECT 'ABT', 'A01', 'F', 'dsfsdf', '15/12/2012', 'Mand' UNION ALLSELECT 'ABT', 'A01', NULL, 'Rds', '30/03/2011', 'Tim' UNION ALLSELECT 'ABT', 'A01', NULL, 'GHDs', '02/12/2012', 'Andy' UNION ALLSELECT 'ABT', 'A02', 'F', 'dfd', '09/05/2012', 'Anna' UNION ALLSELECT 'ABT', 'A02', NULL , 'sdada', '11/02/2013', 'Kathy' UNION ALLSELECT 'ABT', 'A02', NULL, 'Dfg', '15/05/2011', 'Rob' UNION ALLSELECT 'AFL', 'A02', 'F', 'asda', '13/02/2011', 'Dan' UNION ALLSELECT 'AFL', 'A02', NULL, 'dsaa', '24/12/2010', 'Ryan' UNION ALLSELECT 'TRG', 'A01', NULL, 'sdasd', '16/04/2010', 'Richard' UNION ALLSELECT 'TRG', 'K05', NULL, 'jksdh', '23/04/2012', 'Mark' UNION ALLSELECT 'KLD', 'K05', 'F', 'sd', '18/05/2013', 'Jim' UNION ALLSELECT 'KLD', 'K05', NULL, 'dsfsd', '10/03/2012', 'James' UNION ALLSELECT 'KLD', 'K05', NULL, 'sdsd', '12/05/2011', 'Luther' UNION ALLSELECT 'KTY', 'K05', 'F', 'saq', '09/09/2012', 'Ryan' UNION ALLSELECT 'KTY', 'K05', NULL, 'asd', '04/04/2010', 'Kathy' UNION ALLSELECT 'KMD', 'C02', 'F', 'nas', '29/02/2012', 'Rob' UNION ALLSELECT 'KMD', 'C02', NULL, 'asda', '11/11/2011', 'Andy' [/code]Table 2 :[code="sql"] Site Code Freq StartDate EndDate ABT A01 43 01/01/2011 01/02/2012 ABT A02 254 01/01/2011 19/02/2011 ABT A02 109 20/02/2011 01/01/2012 ABT A02 12 02/01/2012 01/01/2013 AFL A02 13 01/01/2011 01/02/2012 TRG A01 122 01/01/2011 01/02/2012 TRG K05 61 01/01/2011 01/02/2012 KLD KO5 33 01/01/2011 15/05/2012 KLD K05 79 16/05/2012 01/01/2013 KTY K05 52 01/01/2011 01/02/2012 KMD C02 78 01/01/2011 01/02/2012 ZYT G01 11 01/01/2011 01/02/2012 PYN A01 15 01/01/2011 01/02/2012 DYN F08 122 01/01/2011 01/02/2012[/code][code="sql"]CREATE TABLE Table2 ( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Site VARCHAR(5), Code VARCHAR(5), Freq int, StartDate DATETIME, EndDate DATETIME )[/code][code="sql"] INSERT INTO Table2 (Site, Code, Freq, StartDate, EndDate) SELECT 'ABT', 'A01', 43, ,'01/01/2011', '01/02/2012' UNION ALL SELECT 'ABT', 'A02', 254, ,'01/01/2011', '19/02/2011' UNION ALL SELECT 'ABT', 'A02', 109, ,'20/02/2011', '01/01/2012' UNION ALL SELECT 'ABT', 'A02', 12, ,'02/01/2012', '01/01/2013' UNION ALL SELECT 'AFL', 'A02', 13, ,'01/01/2011', '01/02/2012' UNION ALL SELECT 'TRG', 'A01', 122, ,'01/01/2011', ,01/02/2012' UNION ALL SELECT 'TRG', 'K05', 61, ,'01/01/2011', '01/02/2012' UNION ALL SELECT 'KLD', 'KO5', 33, ,'01/01/2011', '15/05/2012' UNION ALL SELECT 'KLD', 'K05', 79, ,'16/05/2012', '01/01/2013' UNION ALL SELECT 'KTY',' 'K05', 52, ,'01/01/2011', '01/02/2012' UNION ALL SELECT 'KMD', 'C02', 78, ,'01/01/2011', '01/02/2012' UNION ALL SELECT 'ZYT', 'G01', 11, ,'01/01/2011', '01/02/2012' UNION ALL SELECT 'PYN', 'A01', 15, ,'01/01/2011', '01/02/2012' UNION ALL SELECT 'DYN', 'F08', 122, ,'01/01/2011', '01/02/2012' [/code]Steps : 1. The combination of two columns 'Site' and 'Code' in Table 1 are looked upon the combination of the columns 'Site' and 'Code' in Tabel2. 2. Filter the same on the 'Failure' column and find out the number of failures Below is the query and the output: [code="plain"]SELECT Site,Code,COUNT(*) as [Count], FailFlagCount= SUM(CASE WHEN F = 'F' THEN 1 ELSE 0 END) FROM Table1 GROUP BY Site,Det[/code] [code="sql"] Site Code Count FailFlagCount ABT A01 4 2 ABT A02 3 1 AFL A02 2 1 TRG A01 1 0 TRG K05 1 0 KLD KO5 3 1 KTY K05 2 1 KMD C02 2 1[/code]3.We check for the same combination in Table 2. i.e., the Site and Code of the step 2 output are looked into Table 2 to get its Frequency 4.Calculations : a. CC % = [1-(FailCount / Count)]*100 = [1-(2/4)]*100 b. B.P.O % = [1-(FailCount / Freq)]*100 = [1-(2/43)]*100 c.Forecast% = Let us assume the current month is March.Calculate the Number of failures over the last 3 years and find the average, and let's say as 'X'.Calculate the failure for remaining months, 'Y' = (X * Remaining months/12)Total failure over 12 months = Current Failure + Y Hence Forecast% = [1-(Total failure over 12 months/ Freq)]*100. For our example, Let us assume the value of X = 2 Hence Y = (2 * 9/12) Forecast % = [1-(1+1.5)]*100 5.The above calculations would be done for all the possible combinations of Site and Code. [code="sql"] Site Code CC B.P.O Forecast StartDate EndDate ABT A01 01/01/2011 01/02/2012 ABT A02 01/01/2011 19/02/2011 ABT A02 20/02/2011 01/01/2012 ABT A02 02/01/2012 01/01/2013 AFL A02 01/01/2011 01/02/2012 TRG A01 01/01/2011 01/02/2012 TRG K05 01/01/2011 01/02/2012 KLD K05 01/01/2011 15/05/2012 KLD K05 16/05/2012 01/01/2013 KTY K05 01/01/2011 01/02/2012 KMD C02 01/01/2011 01/02/2012[/code] 6.The grouping of the above table is done on Site type i.e. first letter of Site.Then the average of the all the calculations( cc,BPO,Forecast) are done after GroupingFor example: - 'A' for 'ABT', 'T' for 'TRG'. (I am assuming that we either create multiple tables for multiple types and then do a union to get the below query) Examples :- [code="sql"] Site Code CC B.P.O Forecast A A01 A A02 [i.e.,Avg value of (ABT and A02) and (AFL and A02)] T A01 T K05 avg [i.e., Avg value of (KLD and K05) and (KTY and K05)] K K05 K C02 [/code] This should be my final output. Kindly help.

Using Pivot When There are two sum(upload),sum(pending)

Posted: 26 Jun 2013 05:08 PM PDT

i used pivot when there was 1 sum(count) and it is working fine, but now the requirement is to do more than 1 sum. below are the required details.My sample data (Sql server 2005)region branch trdate reportcnt pendingBangalore BG40-BELGAUM 06/05/2013 0 1Bangalore BG40-BELGAUM 08/05/2013 2 0Bangalore BG40-BELGAUM 09/05/2013 2 0Bangalore BG40-BELGAUM 14/05/2013 4 1Bangalore BN03-BANGALORE 06/05/2013 6 2Required out putRegion Branch 03/06/2013 04/06/2013 Report Pending Report Pending Bangalore BG40-BELGAUM 1000 20 1 200 30plz help me out

Split total into specified intervals.

Posted: 26 Jun 2013 02:24 PM PDT

Hello,I need to distribute total usage into specified intervals. Please find below an exampleTotal Usage: 1,000Specified Intervals: Block 1 - 0 To 100 ---->100 (First 100 units will be allocated into block #1)Block 2 - 101 To 250 ---->150 (Next 150 units allocated into block #2) etcBlock 3 - 251 To 800 ---->550Block 4 - 900 To 1,000 -->200Block 5 - More than 1,000100+150+550+200= 1,000Blocks intervals is stored in a table and it can change based on geography.Appreciate if anyone can please send me function or SQL code which can do the above.Thanks

Removing similar data

Posted: 26 Jun 2013 12:04 PM PDT

create table dbo.tableinfo(list1 varchar(10),list2 varchar(10),Num int)insert into dbo.tableinfo values('A','A',5),('A','B',2),('A','C',7),('A','D',2),('B','A',2),('B','B',4),('C','A',7),('C','B',6),('C','C',7),('C','D',8),('C','E',3),('D','A',2),('D','B',5),('D','C',8)Expected out put is as following:A B 2A C 7A D 2C B 6C D 8C E 3D B 5Condition1, is do not display same same in list1 and list2, eg A A 5condition2, is do not display "A B 2" and "B A 2" twice, show any one only once.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog