Friday, August 30, 2013

[T-SQL] Cross Apply in Sql

[T-SQL] Cross Apply in Sql

Cross Apply in Sql

Posted: 09 Jun 2010 05:52 PM PDT

HiI have a very small question -- What is the difference between Cross Apply and Inner Join. what is it that can be achieved using Cross Apply that cannot be using Inner Join.Thanks in Advance.Azeem.

Help with this query please.

Posted: 29 Aug 2013 04:40 AM PDT

Hi All, i have this script:[code="sql"];WITH Estimated AS (SELECT nh.id,nh.parent_id,tproy.prefix,nh.name AS 'PLAN',Levelnodes=1, Jerarquia = CAST('\'+CAST(nh.name AS VARCHAR(200)) AS VARCHAR(MAX)) ,CONVERT(VARCHAR,CONVERT(INT,SUM(CONVERT(decimal(8,4),cdv.value))/60)) + ':' + RIGHT('0' + CAST (SUM(CONVERT(decimal(8,4),cdv.value))%60 AS INT),2) AS '[HH:mm]' ,CONVERT(INT,SUM(CONVERT(decimal(8,4),cdv.value))) AS 'TotalMinutos' FROM testprojects tproy (NOLOCK)INNER JOIN testplans tplan (NOLOCK) ON tproy.id=tplan.testproject_idINNER JOIN testplan_tcversions tcver (NOLOCK) ON tplan.id=tcver.testplan_idINNER JOIN cfield_design_values cdv (NOLOCK) ON tcver.tcversion_id=cdv.node_idINNER JOIN nodes_hierarchy nh (NOLOCK) ON tplan.id=nh.id WHERE tproy.id=2 AND cdv.value NOT like '%[A-Z]%'GROUP BY tproy.prefix,nh.name,nh.id,nh.parent_idUNION ALLSELECT nh.id,nh.parent_id,tproy.prefix,nh.name AS 'PLAN',Levelnodes=1, Jerarquia = CAST('\'+CAST(nh.name AS VARCHAR(200)) AS VARCHAR(MAX)) ,CONVERT(VARCHAR,CONVERT(INT,SUM(CONVERT(decimal(8,4),cdv.value))/60)) + ':' + RIGHT('0' + CAST (SUM(CONVERT(decimal(8,4),cdv.value))%60 AS INT),2) AS '[HH:mm]' ,CONVERT(INT,SUM(CONVERT(decimal(8,4),cdv.value))) AS 'TotalMinutos' FROM testprojects tproy (NOLOCK)INNER JOIN testplans tplan (NOLOCK) ON tproy.id=tplan.testproject_idINNER JOIN testplan_tcversions tcver (NOLOCK) ON tplan.id=tcver.testplan_idINNER JOIN cfield_design_values cdv (NOLOCK) ON tcver.tcversion_id=cdv.node_idINNER JOIN nodes_hierarchy nh (NOLOCK) ON tplan.id=nh.idINNER JOIN Estimated e ON nh.parent_id=e.id WHERE tproy.id=2 AND cdv.value NOT like '%[A-Z]%'GROUP BY tproy.prefix,nh.name,nh.id,nh.parent_id)SELECT id,parent_id,name = SPACE((nodes_level -1)*4) + name,nodes_level,jerarquia FROM EstimatedGROUP BY tproy.prefix,nh.name,nh.id,nh.parent_idORDER BY Jerarquia;[/code]give me this error:GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'Estimated'.Thanks

Killing all queries on all databases

Posted: 29 Aug 2013 10:20 PM PDT

I'm trying to kill all queries on all user databases prior to starting an overnight batch. Running this codeDECLARE @SQL VARCHAR(MAX)SET @SQL = ''SELECT @SQL = @SQL + 'USE master; ' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 'ALTER DATABASE [' + NAME +'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 'ALTER DATABASE [' + NAME +'] SET MULTI_USER; ' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) FROM sys.databases WHERE name not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')PRINT @SQLEXEC (@SQL)is giving errors (incorrect syntax near GO). However copying the printed sql and running that works. Can anyone suggest why this would be the case?Thanks

need help in a stored procedure

Posted: 29 Aug 2013 05:38 PM PDT

Hi friends, I have a table called op_registration where every visit of out patient(op) will be inserted.I need to check count of regular(up to 28 days from registration date) and irregular (more than 28 days) patients for each day in given month..[code="sql"]CREATE TABLE [dbo].[OP_Registration]( [Id] [int] IDENTITY(1,1) NOT NULL, [FacilityId] [int] NOT NULL,--branch id [MRNO] [nvarchar](20) NOT NULL,--patient unique number [OPNO] [nvarchar](20) NOT NULL,-- patient unique number per visit [RegistrationDate] [datetime] NOT NULL, [VisitNumber] [int] NULL, CONSTRAINT [PK_OP_Registration] PRIMARY KEY CLUSTERED ( [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO[/code]any help would be appreciated..Thanks in advance..

how to alter the existing id values to idetity(1,1)

Posted: 29 Aug 2013 01:36 AM PDT

hi,I have created the table likecreate table tblemployee(id int,name varchar(10),gender int)the table does not have any records..now how to add identity seed and increment values like identity(1,1) ..i have tried it is working from table design how to write T-sql query for that.Thanks,Giri

Float WTF?!

Posted: 29 Aug 2013 09:18 AM PDT

Ok, so this has no real bearing on my application but I'm curious. Given the following:[code="sql"]select len(0.28999999165535)select len(cast(0.28999999165535 as float))select 0.28999999165535 * 2select cast(0.28999999165535 as float) * 2[/code]The results are:1640.579999983310700.5799999833107so..... WHY IS THE LEN OF THE FLOAT 4?! The reason I ask is that it's stored in the database (erroneously) as 0.28999999165535 instead of 0.29 but if I try to find it using a LEN() filter it doesn't filter as expected.[quote][/quote]

Selecting data from 2 tables

Posted: 29 Aug 2013 01:43 AM PDT

SELECT * FROM DocketTB,WorkshopTB WHERE DocketTB.Docket_Date = WorkshopTB.Date_Raised order by Docket_Date descusing the above works to a fashion but it adds them in 1 row!, how is the correct way to do the data row by row in date order. Hope this makes sense
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog