Thursday, August 1, 2013

[T-SQL] query to get the size of all indexes in a database

[T-SQL] query to get the size of all indexes in a database

query to get the size of all indexes in a database

Posted: 01 Aug 2013 12:49 AM PDT

Hi Friends,Is there any query to get the size of all indexes in a database? Let us assume a database size is 400Gb. I need to know how much space index is occupying.Thanks in advance.

How to convert my cursor tsql to set based

Posted: 31 Jul 2013 11:31 AM PDT

Hello All....I was just thinking about a query I need to write and know how to do it with cursors but would like to try and make it happen using a set based approach. Not sure if it can be done.Here are my requirements:I need to group the following into a pool.step 1 - Get all House Idsstep 2 - For each house id, get all people linked directly to that housestep 3 - for each of those people , get any additional house ids linked to themstep 4 - for each of those additional house ids, get any additional people linked to theme.g. resultPool 1 = House Id | people Id 1 | 1 1 | 2 2 | 2 3 | 2 3 | 4I hope I have made sense, I can see how easy it would be to code using cursors but an not sure of the set based approach.Thoughts??Cheers

select distinct rows

Posted: 31 Jul 2013 07:50 AM PDT

himy stored prc return value slike thisrule date noabc1 03/17/2003 23abc1 04/15/2004 45abc2 04/43/2009 120i need to display only 1 rule on my front end.if i put distinct or group by still its not working

how to store result at variable?

Posted: 31 Jul 2013 10:03 PM PDT

Hi,create proc sp_temp--@temp varchar(10)asbegindeclare @Result varchar(5)select COUNT(*) from #temptableendthis simple SP working fine.. I want store the result in one variable..thanksananda

how to import excel file into sql server using identity column

Posted: 31 Jul 2013 07:18 PM PDT

I have an excel file with email ids as one column.i want those email ids into the database along with new column as autoid in which identity column must be used.I am able to import only emails id.but i want it along with autoid generation.So can you please help me.For example: i haveemaildsabc@gmail.comxyz@gmail.combut what i want is autoid emaild1 abc@gmail.com2 xyz@gmail.comin which autoid should generate automatically.

sp_executesql MAXDOP

Posted: 31 Jul 2013 08:53 PM PDT

Just trying to test the affect of MAXDOP = 1 inside a query executed by sp_executesql within CRM. The query itself has lots of variables, so takes a similar form to the below, (excuse the copying and pasting):exec sp_executesql N'select top 51 when 6 then @StatusCode1 when 1 then @StatusCode2 ORDER BY incident0.TicketNumber desc@StatusCode1=N'Canceled',@StatusCode2=N'In Progress'My question really is this, where to put the OPTION MAXDOP(1) command, I put it right at the end but its seemingly ignored?Many thanks!

Nulls in Dynamic Pivot - how to remove

Posted: 31 Jul 2013 05:13 AM PDT

Here is my code, i'm creating a dynamic pivot of sales for the last 6 days. Works great, except if I have a customer that does not have a sales on a particular day, I have nulls. I can't figure out how to take the nulls out of the pivot. I assume I have to use ISNULL but no where seems to work ... any suggestions or ideas on how to get rid of the pesky nulls?:crazy:SET @sql = N'SELECT *FROM (SELECT custid, sorderdate, qtyFROM dbo.dailysales) AS DPIVOT(SUM(qty) FOR sorderdate IN(' + @cols + N')) AS P;';EXEC sp_executesql @sql;custid 6/19/2013 6/26/2013 7/3/2013customer1 130 138 144customer2 11 20 17customer3 2 2 NULLcustomer4 42 37 41customer5 NULL NULL 1

Sum Negative Numbers

Posted: 14 Jul 2013 03:53 PM PDT

Hi All,I have a column GNLBalance with negative and positive balances. I want to add these numbers together when another column GNLFSMID in the same table is equal to a series of numbers.I need the total closing\opening balance for each client with there account, account group & division.Balance Buisness Rules:2013 Opening balance = Closing balance 2012 (opening 2012 + Jan 12 to Dec 12 balance)Closing balance 2013 = Opening + Jan13 to Current month balanceSoOpening: FSMID 113 to 125Closing: FSMID (113 to 125) + 127,128,129,130DDLTable GeneralLedgerGNLID GNLCLIID GNLCOAID GNLFSMID GNLBalanceBase1 1385 576 113 -4845.00002 1385 713 114 -395.85003 3139 39 115 8703.34004 727 39 116 -1321.65005 727 39 117 -8811.00006 3139 713 118 -15416.52007 1480 713 119 18429.92008 3144 39 120 1321.65009 1480 713 121 -10799.000010 3144 576 122 4371.000011 3139 713 123 1619.850012 727 45 124 1786.140013 3139 39 125 46.420014 727 576 127 -12802.400015 1480 713 128 1198.260016 1480 713 129 -1785.000017 3139 713 130 800.0000Table ClientsCLIID CLICode CLIName727 HARK HARK CONSULTING1385 3HPARC 3HARCHITECTS1480 GUYCAR GUY CARPENTER3139 ABNAP ABB PTE LTD3144 SYSACC SYSTEM ACCESSTABLE ChartOfAccountCOAID COANAME1 COACode2Code COACode2NAme2 COAAGPID39 Total Billings RV10 RV10 Billings 145 Prod Billing RV40 RV40 Revenue 2576 Loan FD100G FD100G Financial Debts 3713 Receivables FA301G FA301G Loans Advances 4Table AccountGroupAGPID AGPNAME11 Assets2 Goodwill3 Deferred4 Trade5 CashTable FiscalMonthFSMID FSMNAME1113 Opening Balance 12114 Jan 12115 Feb 12116 Mar 12117 Apr 12118 May 12119 Jun 12120 Jul 12121 Aug 12122 Sep 12123 Oct 12124 Nov 12125 Dec 12127 Opening Balance 13128 Jan 13129 Feb 13130 Mar 13Table ClientOwnerCLOID CLOCLIID CLODIVID1 727 12 1385 23 1480 34 3139 35 3144 46 727 5Table DivisionDIVID DIVName11 Digital2 Zenith3 Stars4 MSL5 LeoExpected ResultsClient code(CLICODE), GL Account Group(AGPNAME1), GL Account(COANAME1),3HPARC, Deferred, Loan,3HPARC, Trade, Receivables,HARK, Assests, Total Billings,GUYCAR, Trade, Receivables,--continue on same lineClosing balance(Total of GNLBalanceBase when FSMID = 113 to 130), Division(DIVName1), GLChartCode(COACode2Code), -4845, Zenith, FD100G -395.85, Zenith, FA301G -10132.65, Digital, RV10 7044.18, Stars, FA301G[code="sql"]select distinct c.CLIName1 as 'Client', c.CLICode as 'Client Code', g.AGPName1 as 'GL Account Group', a.COAName1 as 'GL Account', CASE WHEN l.GNLFSMID IN(113,114,115,116,117,118,119,120,121,122,123,124,125,127,128,129,130) THEN SUM(l.GNLBalanceBase) ELSE '0' END AS [Closing Balance], d.DIVName1 as 'Division', d.DIVName2 as 'Division (2)', t.CLTName1 as 'Client Type', a.[COACode2Code] as 'GLChart Code 2', a.COACode2Name1 as 'GLChart Code 2 Name', CASE WHEN l.GNLFSMID IN(113,114,115,116,117,118,119,120,121,122,123,124,125) THEN SUM(l.GNLBalanceBase) ELSE '0' END AS [Opening Balance] from vwClient cinner join [vwChartOfAccount] aon a.[COASourceID] = c.[CLISourceID]inner join [vwChartOfAccountGroup] gon g.[AGPID] = a.COAAGPIDinner join [vwGeneralLedger] lon l.GNLCLIID = c.[CLIID]inner join [vwclientOwner] oon o.CLOID = c.CLIID inner join [vwDivision] don d.DIVID = o.CLODIVIDinner join [vwClientType] ton t.CLTID = c.[CLICLTID]inner join [DW].[vwFiscalMonth] mon l.GNLFSMID = m.FSMIDgroup by c.CLIName1, c.CLICode, g.AGPName1, a.COAName1, l.GNLFSMID, d.DIVName1, d.DIVName2, t.CLTName1, a.COACode2Code, a.COACode2Name1[/code]

multiple records into single record

Posted: 31 Jul 2013 01:33 AM PDT

I have a task, here in my table I have mutiple records for single icn that means each record is different by its detail line level information about that icn. As this is health care data, now my task is to keep all the detail level information of a particular icn into single record.The thing is for example:icn =1 may have 10 records icn =2 may have 20 recordsnot all icn will have the same number of records. so how can I create a single chunk or record of a particular icn(where ten records should come in single record)?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog