| Parent/Child and Recursion Posted: 29 Apr 2013 11:12 PM PDT I have data in a table like this:[b]Parent[u][/u][/b] [b]Child[u][/u][/b]300001 110081300001 102157300001 102158300001 102159110081 101000110081 101504110081 102129Child 110081 of parent 300001 has children 101000, 101504, and 102129.I need a query that returns this result:[b]Parent[u][/u][/b] [b]Child[u][/u][/b]300001 101000300001 101504300001 102129300001 102157300001 102158300001 102159 I've tried a recursive CTE without success. Table values are in varchar format. Help Please? |
| T-SQL Greatest function workarounds - timing tests Posted: 29 Apr 2013 09:13 AM PDT So, I find myself in need of a GREATEST function equivalent, and I want to make sure before I best practice this concept in my head I do some functional testing. I've created a test bed and I'm getting similar results on the systems I've tested.It requires 2k8 to try the workarounds because they're based off the CROSS APPLY.Now, as a baseline, I've built the Case statement from heck, and a million row test structure. My results before you get to the code look like so:[code="plain"]TestType AvgLen MaxLen MinLen--------------- ----------- ----------- -----------Cascade Test 2152 2946 1713CASE Test 1889 2356 1506Values Test 1027 1433 833[/code]That said, I'd like some of the folks here to take a look at the process, if you're willing, and see what you can break in it. From what I can tell, a VALUES build is running at about twice the speed as any other alternatives I've come up with. The iTVF wasn't worth discussing when I timing tested it. It came in at about the same level as the Cascade, for similar reasons. You have to embed the function in the function to get it to work right and it just became a mess.UNPIVOT is right out. Because of the need in my scenario to alter what values are included, it became overly complex for maintenance. If someone has a clean way of performing it, I'd be interested. This test is simplified from my business case, but it has all the moving parts.So, the test harness:[code="sql"]IF OBJECT_ID ('tempdb..#blah') IS NOT NULL DROP TABLE #blahIF OBJECT_ID ('tempdb..#dump1') IS NOT NULL DROP TABLE #dump1IF OBJECT_ID ('tempdb..#dump2') IS NOT NULL DROP TABLE #dump2IF OBJECT_ID ('tempdb..#dump3') IS NOT NULL DROP TABLE #dump3IF OBJECT_ID ('tempdb..#results') IS NOT NULL DROP TABLE #resultsCREATE TABLE #blah ( RowNum INT IDENTITY(1,1), Date1 DATETIME, Date2 DATETIME, Date3 DATETIME, Date4_1 DATETIME, Date4_2 DATETIME, Date4_3 DATETIME )CREATE CLUSTERED INDEX idx_c_Blah ON #Blah ( RowNum)CREATE TABLE #dump1 (RowNum INT, MaxDT DATETIME)CREATE TABLE #dump2 (RowNum INT, MaxDT DATETIME)CREATE TABLE #dump3 (RowNum INT, MaxDT DATETIME)CREATE TABLE #results (TestType VARCHAR(15), MillisecondLength INT );WITH cteTally AS ( SELECT TOP 1000000 ROW_NUMBER() OVER(ORDER BY sc1.Type) AS N FROM sys.syscolumns sc1, sys.syscolumns sc2, sys.syscolumns sc3 )INSERT INTO #blahSELECT -- N, GETDATE(), GETDATE() + 1, GETDATE() - 1, GETDATE() + 2, GETDATE() + 3, GETDATE() + 4FROM cteTally-------------- End Structure Build ------------------GODECLARE @StartTestTime DATETIMETRUNCATE TABLE #dump1TRUNCATE TABLE #dump2TRUNCATE TABLE #dump3-- Did you know that discard results in options in 2k8R2 actually dumps the statistics in messages, too?-- Yeah, I didn't either until just now...--SET STATISTICS TIME, IO ONSET @StartTestTime = GETDATE()-- Values GREATEST --INSERT INTO #dump1SELECT RowNum, drv.MaxDtFROM #blah CROSS APPLY (SELECT MAX( dt) AS MaxDt FROM ( VALUES ( Date1), (Date2), (Date3), (CASE RowNum %4 WHEN 1 THEN Date4_1 WHEN 2 THEN Date4_2 WHEN 3 THEN Date4_3 WHEN 0 THEN '19000101' END) ) ValueList (dt) ) AS drvINSERT INTO #results VALUES ( 'Values Test', DATEDIFF( ms, @StartTestTime, GETDATE()))SET @StartTestTime = GETDATE()-- Cascading CrossApply GREATEST --INSERT INTO #dump2SELECT RowNum, ca3.GrtDt3FROM #blah AS b CROSS APPLY ( SELECT CASE WHEN b.Date1 >= b.Date2 THEN Date1 ELSE b.Date2 END AS GrtDt1) AS ca CROSS APPLY ( SELECT CASE WHEN ca.GrtDt1 >= b.Date3 THEN ca.GrtDt1 ELSE b.Date3 END AS GrtDt2) AS ca2 CROSS APPLY ( SELECT CASE WHEN ca2.GrtDt2 >= CASE b.RowNum %4 WHEN 1 THEN b.Date4_1 WHEN 2 THEN b.Date4_2 WHEN 3 THEN b.Date4_3 WHEN 0 THEN '19000101' END THEN ca2.GrtDt2 ELSE CASE b.RowNum %4 WHEN 1 THEN b.Date4_1 WHEN 2 THEN b.Date4_2 WHEN 3 THEN b.Date4_3 WHEN 0 THEN '19000101' END END AS GrtDt3 ) AS ca3INSERT INTO #results VALUES ( 'Cascade Test', DATEDIFF( ms, @StartTestTime, GETDATE()))SET @StartTestTime = GETDATE()-- CASE method GREATEST --INSERT INTO #dump3 SELECT RowNum, CASE WHEN Date1 >= Date2 THEN CASE WHEN Date1 >= Date3 THEN CASE WHEN Date1 >= CASE RowNum %4 WHEN 1 THEN Date4_1 WHEN 2 THEN Date4_2 WHEN 3 THEN Date4_3 WHEN 0 THEN '19000101' END THEN Date1 ELSE CASE RowNum %4 WHEN 1 THEN Date4_1 WHEN 2 THEN Date4_2 WHEN 3 THEN Date4_3 WHEN 0 THEN '19000101' END END ELSE CASE WHEN Date3 >= CASE RowNum %4 WHEN 1 THEN Date4_1 WHEN 2 THEN Date4_2 WHEN 3 THEN Date4_3 WHEN 0 THEN '19000101' END THEN Date3 ELSE CASE RowNum %4 WHEN 1 THEN Date4_1 WHEN 2 THEN Date4_2 WHEN 3 THEN Date4_3 WHEN 0 THEN '19000101' END END END ELSE CASE WHEN Date2 >= Date3 THEN CASE WHEN Date2 >= CASE RowNum %4 WHEN 1 THEN Date4_1 WHEN 2 THEN Date4_2 WHEN 3 THEN Date4_3 WHEN 0 THEN '19000101' END THEN Date2 ELSE CASE RowNum %4 WHEN 1 THEN Date4_1 WHEN 2 THEN Date4_2 WHEN 3 THEN Date4_3 WHEN 0 THEN '19000101' END END ELSE CASE WHEN Date3 >= CASE RowNum %4 WHEN 1 THEN Date4_1 WHEN 2 THEN Date4_2 WHEN 3 THEN Date4_3 WHEN 0 THEN '19000101' END THEN Date3 ELSE CASE RowNum %4 WHEN 1 THEN Date4_1 WHEN 2 THEN Date4_2 WHEN 3 THEN Date4_3 WHEN 0 THEN '19000101' END END END ENDFROM #blahINSERT INTO #results VALUES ( 'CASE Test', DATEDIFF( ms, @StartTestTime, GETDATE()))SET @StartTestTime = GETDATE()--SET STATISTICS TIME, IO OFFGO 50SELECT TestType, AVG( MillisecondLength) AS AvgLen, MAX( MillisecondLength) AS MaxLen, MIN( MillisecondLength) AS MinLenFROM #resultsGROUP BY TestTypeGO[/code]As I mentioned, I'd be appreciative if some of y'all would be willing to run this and check if you get similar times, and see if you can find exceptions to the timings due to some wildness I haven't even contemplated yet. |
| Stuck with a getdate time range? Posted: 29 Apr 2013 09:22 PM PDT Hi All, Sorry if this has been asked somewhere all ready but I've struggled to find the answer! I have a date time field which is a date time stamp on activity. i need to have a query that only brings back information for data that's between today's date and events between 08:00 and 10:00, but i need the date to be dynamic, so that it always returns today's date with that between those times.My brains says it should be straight forward but i cant suss it.Thanks |
| creating table error? Posted: 29 Apr 2013 06:52 PM PDT Hai Friends, Im creating one table for web application these is my table structure.after wrote the code ll show one waring messagecreate table onward_journey(onward_journey int identity,departuredate datetime,from_location varchar(100),to_location varchar(100),metro nvarchar(1100),trans_all nvarchar (1100),mode_of_travel nvarchar(1100),seat_type nvarchar(1100),no_of_days int,other_details varchar(100),status_id int foreign key references status(status_id))Warning: The table 'onward_journey' has been created but its maximum row size (9156) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.do the need full what can i do now? |
| How to pass where condition as parameter of stored procedure Posted: 29 Apr 2013 02:26 PM PDT How to pass where condition as parameter of stored procedure |
| How To Avoid The Duplicate Records.... Posted: 29 Apr 2013 04:39 AM PDT Hi Friends...I Having Table For The Following Structure..[code="sql"]create Table EmployeeDet (EmpID int,EmpName Varchar(100),Department varchar(100),Age int,salary int)insert into EmployeeDet values (1,'Arun','Accounts',25,25000),(1,'Arun','Accounts',23,21000),(1,'Arun','Accounts',26,22000),(1,'Arun','Accounts',29,27000)insert into EmployeeDet values (2,'kumar','Production',23,18000),(2,'kumar','Production',27,30000),(2,'kumar','Production',31,19000)insert into EmployeeDet values (3,'saran','Sales',22,38000)insert into EmployeeDet values (4,'Vidya','Purchase',18,20000),(4,'Vidya','Purchase',27,36000),(4,'Vidya','Purchase',23,35000)select * from EmployeeDetEmpID EmpName Department Age salary1 Arun Accounts 25 250001 Arun Accounts 23 210001 Arun Accounts 26 220001 Arun Accounts 29 270002 kumar Production 23 180002 kumar Production 27 300002 kumar Production 31 190003 saran Sales 22 380004 Vidya Purchase 18 200004 Vidya Purchase 27 360004 Vidya Purchase 23 35000Below I have mentioned The my Required Table Data EmpID EmpName Department Age salary1 Arun Accounts 25 250002 kumar Production 23 180003 saran Sales 22 380004 Vidya Purchase 18 20000[/code][u]My Requirement :[/u]1) EmpID,EmpName,Department Field Should not be Duplicate Record...2) You Take First Record or Last Record or Middle Record of the Particular EmpID but EmpID,EmpName,Department Should be Unique... ( Here I Mentioned First Record of The EmpID )...Thanks & Regards,Saravanan.D |
| No result set Posted: 29 Apr 2013 05:46 AM PDT I have this query:declare @controlnumber varchar(13) set @controlnumber = '0210300000001'select *from Cards where controlNum like ''''+SUBSTRING(@controlnumber,1,5)+'%'+''''it returned nothinghowever if I change the where clause to controlnum like '02103%' then it worked. Did I miss a quote or something?thanks for all the help and advice. |
| Stored Proc Novice Help Posted: 29 Apr 2013 02:58 AM PDT Hi all,I'd be really grateful if someone could help me (probably something very basic I'm missing!)I'm trying to create a stored procedure (code below) but it's failing. If I execute the create table, insert into, select and drop statements as a batch the code runs perfectly but when I put it in a create proc statement it fails!! Can anyone help please?[code="sql"]use tempdbgocreate proc dbo.DailyDBSizeCheckas create table #DatabaseSize ( fileid int, groupid int, size int, maxsize int, growth int, status int, perf int, name varchar(50), filename varchar(100) ) go insert into #DatabaseSize exec sp_msforeachdb @command1='select * from ?..sysfiles;' go select name as [DB File Name], filename as [DB File Path], size*8/1024 as [DB Size (MB)] from #DatabaseSize go drop table #DatabaseSize go[/code] |
