Friday, August 16, 2013

[T-SQL] while loop

[T-SQL] while loop

while loop

Posted: 15 Aug 2013 01:23 PM PDT

hii want to do batch and insert records from 1 table to another.code :declare @min intdeclare @end intset @min = select min(id) from empset @end = select max(id) from empwhile(@min<@end)insert into table bselect * from tableawhere id between @min and @endset @min = @min +1000here when i am inserting i need to do it with increment,there is no need forset @min = @min +1000.example,if @min =1 and @max = 1000.first i want to do from 1 to 1000then 1001 to 2000here its taking from 1 to 10000 directly,not doing in batchalso i need to make sure that @max<=@endplease help me

Add Check Constraint in Two Columns

Posted: 15 Aug 2013 04:46 PM PDT

Hi All,I have a database called EmployeeDB and now in this database I have two tables (dbo.employeeDetails) and dbo.couresDetails.Now In course details table I have only columns Emp_id and IsEmployee. In Emp_id column there is a foreign key and the primary is located on employeedetails table. And IsEmployee column contains only 2 values either true or False.Now I am trying to insert the data into dbo.couresDetails table. I want to add a check constraint on Isemploee column. As Emp_id has a foreign column the value should be multiple. Where the value of isdelted will be [b]TRUE [/b] it should be inserted and where the value is False, only 1 time it should be inserted for a id. Output should be like the following. Empid Isdeleted 1 True -------it should be inserted 1 Flase ------- it should be inserted 1 False-- when trying to insert False value for 1 it should stopped--error 1 True -------it should be inserted ---Similarly for for other ids 2 True -------it should be inserted 2 Flase ------- it should be inserted 2 False-- when trying to insert False value for 1 it should stopped--error 2 True -------it should be insertedHow do I apply check constraint here? Will I use some other function like triggers?Please help!!Thanks in advance

display two different resultset from single query or SP

Posted: 15 Aug 2013 11:22 PM PDT

Hi,I want to display capusewise cout of students as per attached excel.i.e. 1) first resultset will display statuswise cout of student in each campus 2) Second result set will display Subjectwise + statuswise cout of student in each campusPlease guide.ThanksAbhas.

Help Required!!

Posted: 15 Aug 2013 03:31 AM PDT

I have the values in a column like 'email=abc@microsoft.com;rota=mon-fri'And i need in my select statements to pickup email value and rota value.Can anybody help please? or can anybody come up with a better solution to store the values in a column to easily retrieve those two values.Thank you.

Query Performance

Posted: 15 Aug 2013 02:21 PM PDT

Hi All,I have written a T SQL statement and i estimate it will take 8 hours to complete, it has been running now for 3 hrs 20 mins and it has produced around half the expected results. I am querying a data warehouse with read only access at the moment, so i can't use the query analyzer. Can anyone look at my code suggest some improvements?[code="sql"];WITHcteClosingBalance(CLINAME1,CLICODE,AGPNAME1,COANAME1,DIVNAME1,COACODE2CODE,COACODE2NAME1,CLTNAME1,closingBalance)AS (select c.CLIName1 ,c.CLICode ,g.AGPName1 as [GL Account Group] ,a.COAName1 as [GL Account] ,d.DIVName1 ,a.COACode2Code as [GLChart Code 2] ,a.COACode2Name1 as [GLChart Code 2 Name] ,t.CLTName1 as [Client Type] ,SUM(l.GNLBalanceBase) AS 'Closing Balance' from DW.vwChartOfAccount ainner join DW.vwChartOfAccountGroup gon a.COAAGPID = g.AGPIDinner join DW.vwGeneralLedger lon a.COAID = l.GNLCOAIDinner join DW.vwClient con l.GNLCLIID = c.CLIIDinner join DW.vwClientType ton c.CLICLTID = t.CLTIDinner join DW.vwDivision don l.GNLDIVID = d.DIVIDwhere COACode2Code in ('CA100','CA180','CA200','CA210') and l.GNLFSMID IN (127,128,129,130)GROUP BY CLIName1 ,CLICode ,AGPName1 ,COAName1 ,DIVName1 ,COACode2Code ,COACode2Name1 ,CLTName1),cteOpeningBalance(DIVNAME1,OpeningBalance,CLTNAME1,COACODE2CODE)AS( select d.DIVName1 ,SUM(l.GNLBalanceBase) AS 'Opening Balance' ,t.CLTName1 ,a.COACode2Code as [GLChart Code 2] from DW.vwChartOfAccount ainner join DW.vwChartOfAccountGroup gon a.COAAGPID = g.AGPIDinner join DW.vwGeneralLedger lon a.COAID = l.GNLCOAIDinner join DW.vwClient con l.GNLCLIID = c.CLIIDinner join DW.vwClientType ton c.CLICLTID = t.CLTIDinner join DW.vwDivision don l.GNLDIVID = d.DIVIDwhere COACode2Code in ('CA100','CA180','CA200','CA210') and l.GNLFSMID IN (127)GROUP BY DIVName1 ,COACode2Code ,t.CLTName1 ),cteJan13(DIVNAME1,JBalance,CLTName1,COACODE2CODE)AS( select d.DIVName1 ,SUM(l.GNLBalanceBase) AS 'January' ,t.CLTName1 ,a.COACode2Code as [GLChart Code 2] from DW.vwChartOfAccount ainner join DW.vwGeneralLedger lon a.COAID = l.GNLCOAIDinner join DW.vwDivision don l.GNLDIVID = d.DIVIDinner join DW.vwClient con l.GNLCLIID = c.CLIIDinner join DW.vwClientType ton c.CLICLTID = t.CLTIDwhere COACode2Code in ('CA100','CA180','CA200','CA210') and l.GNLFSMID IN (128)GROUP BY DIVName1 ,t.CLTName1 ,COACode2Code ),cteFeb13(DIVNAME1,FBalance,CLTName1,COACODE2CODE)AS( select d.DIVName1 ,SUM(l.GNLBalanceBase) AS 'February' ,t.CLTName1 ,a.COACode2Code as [GLChart Code 2] from DW.vwChartOfAccount ainner join DW.vwGeneralLedger lon a.COAID = l.GNLCOAIDinner join DW.vwDivision don l.GNLDIVID = d.DIVIDinner join DW.vwClient con l.GNLCLIID = c.CLIIDinner join DW.vwClientType ton c.CLICLTID = t.CLTIDwhere COACode2Code in ('CA100','CA180','CA200','CA210')and l.GNLFSMID IN (129)GROUP BY DIVName1 ,COACode2Code ,t.CLTName1 ),cteMar13(DIVNAME1,MBalance,CLTName1,COACODE2CODE)AS( select d.DIVName1 ,SUM(l.GNLBalanceBase) AS 'March' ,t.CLTName1 ,a.COACode2Code as [GLChart Code 2] from DW.vwChartOfAccount ainner join DW.vwGeneralLedger lon a.COAID = l.GNLCOAIDinner join DW.vwDivision don l.GNLDIVID = d.DIVIDinner join DW.vwClient con l.GNLCLIID = c.CLIIDinner join DW.vwClientType ton c.CLICLTID = t.CLTIDwhere COACode2Code in ('CA100','CA180','CA200','CA210')and l.GNLFSMID IN (130)GROUP BY DIVName1 ,COACode2Code ,t.CLTName1)select cb.CLIName1 as [Client] ,cb.CLICode as [Client Code] ,cb.AGPName1 as [GL Account Group] ,cb.COAName1 as [GL Account] ,cb.closingBalance as [Closing Balance] ,cb.COACode2Code as [GLChart Code 2] ,cb.COACode2Name1 as [GLChart Code 2 Name] ,cb.DIVName1 as [Division] ,cb.CLTName1 as [Client Type] ,ob.OpeningBalance as [Opening Balance] ,j.JBalance as [January] ,f.FBalance as [Feburary] ,m.MBalance as [March] ,SUM(ISNULL(ob.OpeningBalance,0) + ISNULL(j.JBalance,0) + ISNULL(f.FBalance,0) + ISNULL(m.MBalance,0)) as [March End] from cteClosingBalance cb inner join cteOpeningBalance obon ob.DIVNAME1 = cb.DIVNAME1 AND ob.COACODE2CODE = cb.COACODE2CODE AND ob.CLTNAME1 = cb.CLTNAME1left outer join cteJan13 Jon j.CLTName1 = cb.CLTNAME1 and j.DIVNAME1 = cb.DIVNAME1 AND j.COACODE2CODE = cb.COACODE2CODE left outer join cteFeb13 fon f.CLTName1 = cb.CLTNAME1 and f.DIVNAME1 = cb.DIVNAME1 AND f.COACODE2CODE = cb.COACODE2CODEleft outer join cteMar13 mon m.CLTName1 = cb.CLTNAME1 and m.DIVNAME1 = cb.DIVNAME1 AND m.COACODE2CODE = cb.COACODE2CODEGROUP BY cb.CLIName1 ,cb.AGPName1 ,cb.COAName1 ,cb.DIVName1 ,cb.COACode2Code ,cb.COACode2Name1 ,cb.CLTName1 ,cb.closingBalance ,ob.OpeningBalance ,cb.CLICODE ,JBalance ,FBalance ,MBalanceorder by cb.DIVName1[/code]Thanks for any help.

Dynamic Sorting Issue

Posted: 15 Aug 2013 07:54 AM PDT

Hello Everyone,I'm pretty sure that this is possible but I am stuck. I am trying to write a query that sorts based on a couple variables... This query works perfectly:[code="sql"]--WORKINGDECLARE @Sort1 varchar(10)='val3';DECLARE @Sort2 varchar(10)='val2';DECLARE @x TABLE (val1 varchar(10) not null, val2 varchar(10) not null,val3 varchar(10));INSERT @x VALUES ('xxx','ccc','1a'),('yyy','bbb','5a'),('zzz','aaa','2a'), ('xxx','ccc','5a'),('yyy','bbb','5a')SELECT * FROM @xORDER BY CASE @Sort1 WHEN 'val1' THEN val1 WHEN 'val2' THEN val2 WHEN 'val3' THEN val3 END, CASE @Sort2 WHEN 'val1' THEN val1 WHEN 'val2' THEN val2 WHEN 'val3' THEN val3 END[/code][u]The issue is that val3 needs to be an[b] int.[/b][/u] In the updated sample below the query works if @Sort1 = 'val3'. If @Sort='val1' or 'val2' the query will fail with the error: 'Conversion failed when converting the varchar value 'ccc' to data type int.' I can get it to work if I change[b] [i]WHEN 'val3' THEN val3[/i][/b] to [b][i]WHEN 'val3' THEN CAST(val3 AS varchar(10))[/i][/b] but then it will sort it as a string (1,11,2,21,22,3,4,etc...) which is not what I need.[code="sql"]DECLARE @Sort1 varchar(10)='val2'; --NOTE: @Sort2 ommitted from this example because I am still stuck on @sort1--DECLARE @Sort1 varchar(10)='val3';DECLARE @x TABLE (val1 varchar(10) not null, val2 varchar(10) not null,val3 int);INSERT @x VALUES ('xxx','ccc',1),('yyy','bbb',5),('zzz','aaa',11), ('xxx','ccc',5),('yyy','bbb',5)SELECT * FROM @xORDER BY CASE @Sort1 WHEN 'val1' THEN val1 WHEN 'val2' THEN val2 WHEN 'val3' THEN val3 END[/code]I have been CASTing and CTEing by brains out with no luck. Any help would be appreciated. Thanks!

Pivot and Merge Columns

Posted: 15 Aug 2013 07:03 AM PDT

Is there any way to take the following:Part Whse11c ATL11c Day11c BHAM21E ATL21E CINCY23M DAYand merge the various WHSE into a single column.. like11c ATL, Day, BHAM21e ATL, CINCY23m DAYWhat I want to do.. is the above in a CTE then join for the main query on CTE.part = main.part.

Need help in query...

Posted: 15 Aug 2013 04:42 AM PDT

[u]Number[/u] 10 20 30 40 50table has a column 'Number'expected result is[u]Numer-range[/u] 10 - 20 20 - 30 30 - 40need to diaplay as it is

Help on inserting results into Table

Posted: 15 Aug 2013 02:02 AM PDT

Hi everyone.Could someone show my how to insert this result into an SQL table.DECLARE @total_buffer INT;SELECT @total_buffer = cntr_value FROM sys.dm_os_performance_counters WHERE RTRIM([object_name]) LIKE '%Buffer Manager' AND counter_name = 'Total Pages';;WITH src AS( SELECT database_id, db_buffer_pages = COUNT_BIG(*) FROM sys.dm_os_buffer_descriptors --WHERE database_id BETWEEN 5 AND 32766 GROUP BY database_id)SELECT [db_name] = CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id]) END, db_buffer_pages, db_buffer_MB = db_buffer_pages / 128, db_buffer_percent = CONVERT(DECIMAL(6,3), db_buffer_pages * 100.0 / @total_buffer)FROM srcORDER BY db_buffer_MB DESC; I know the error is in relation to declaring the variable , but I am at a bit of loss of how to correct it ?Any help appreciated.Thank you
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog