CUBE operator is used with Group by clause. It use to get the subtotal and grand total of all permutation of columns provided by the CUBE operator. ROLLUP operator is used with Group by clause. It use to get the subtotal and grand total of a set of columns provided by the ROLLUP operator. For …
Let me explain the case first, so you will get the idea when this is required to use. In the database, there is more than one tables which store the future values and will move on specific time to standard tables. For example, Table “UserList2” contains the future values consider as temporary table. Table “UserList1” …
BEGIN TRANSACTION BEGIN TRY — put SQL commands here INSERT/UPDATE/Delete — if successful – COMMIT the work COMMIT TRANSACTION END TRY BEGIN CATCH — handle the error case (here by displaying the error) SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage — in case …
SAMPLE RECORDS Query for getting top 10 records based on the company values with duplicate values select T.Country ‘Text’, T.Company’Value’ from ( select Country , Company, dense_rank() over(order by Companydesc) as rn from TableName ) T where T.rn <= 10
Sample data download: here Declare @tempAttendance table(EmpID int, EmpName varchar(50), edate int, ecode varchar(5)) insert into @tempAttendance SELECT empid, empname, code as ‘edate’, value as ‘ecode’ FROM [dbo].[hattendance] UNPIVOT ( value FOR code IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], …
Sample Data download: http://goo.gl/lcOf8z Generate excel report as below. Username, Address, City, State, Zip, Question 1, Question 2, Question 3, ….. Prakash, test, anand, gujarat, 388001, Answer 1, Answer 2, Answer 3,.. Chirag, test, anand, gujarat, 388001, Answer 1, Answer 2, Answer 3,.. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO — ============================================= — Author: …
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[unqSplit](@String nvarchar(4000), @Delimiter char(1)) RETURNS @Results TABLE (value nvarchar(4000)) AS –this function takes two parameters; the first is the delimited string, the second is the delimiter BEGIN DECLARE @INDEX INT DECLARE @SLICE nvarchar(4000) — HAVE TO SET TO 1 SO IT DOESNT EQUAL Z — …
Create store procedure. // Copyright © Microsoft Corporation. All Rights Reserved. // This code released under the terms of the // Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.) USE [master] GO /****** Object: StoredProcedure [dbo].[sp_BackupDatabases] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO — ============================================= — Author: Microsoft — Create date: 2010-02-06 — Description: Backup Databases …
To find records which exist in source table but not in target table: SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t2.Id = t1.Id) or SELECT * FROM t1 LEFT OUTER JOIN T2 on t1.Id = t2.Id WHERE t2.Id IS NULL If the primary key consists of more than one column, …
Declare @SearchText nvarchar(max) set @SearchText = ‘sah.CheckInCheckOutDateTime>=”2011-09-28 10:00:00.000”’ Declare @SchoolYearId uniqueidentifier set @SchoolYearId=’25511F7F-8F87-4085-A242-2004F4419D36′ Declare @Str as nvarchar(max) set @Str=’ select s.LastName +”, ” + s.FirstName as ”Name”,CheckInCheckOut,sah.CheckInCheckOutDateTime as ”ClockOut” from StaffAttendenceHistory sah join Staffschoolyear ssy on sah.staffschoolyearid = ssy.id join Staff s on ssy.staffid = s.id where ssy.SchoolYearId=@i’ If(LEN(ISNULL(@SearchText,0))>0) set @Str = @Str + ‘ …