Database

Aliyu Usman wrote:

WHEN EXAMS BETWEEN 40 AND 0 THEN 'F'

BETWEEN 0 AND 40

CREATE TABLE tblTest (ID INT, NAME VARCHAR(20),AGE INT)
INSERT INTO tblTest VALUES(1,'AAA',22),(1,'AAA',22),(2,'BBB',33),(2,'BBB',33),(2,'BBB',33),(3,'CCC',44),(4,'DDD',55)

--create an alternate table to store the duplicate records
CREATE TABLE tblTestDuplicate (ID INT, NAME VARCHAR(20),AGE INT)

-- insert those duplicate records to this new table
INSERT INTO tblTestDuplicate
SELECT *
FROM tblTest
GROUP BY ID,NAME,AGE
HAVING COUNT(ID) > 1

--delete the duplicate records from the original table
DELETE FROM
tblTest 
WHERE  ID IN (SELECT ID FROM tblTestDuplicate)

--insert all the records into the original table
INSERT INTO tblTest
SELECT * 
FROM tblTestDuplicate

--Project the new records
SELECT *
FROM tblTest
ORDER BY ID


--clean up
DROP TABLE tblTestDuplicate
DROP TABLE tblTest

ID	NAME	AGE
1	AAA	    22
2	BBB	    33
3	CCC	    44
4	DDD	    55

total records belonging to each division

;WITH CTE AS(
SELECT  
	1 AS EMPNUM
	,'File1'AS FILENAME
	,'Name1' AS EMPNAME
	,'Division1' AS Division UNION ALL
SELECT  
	2 AS EMPNUM
	,'File2'AS FILENAME
	,'Name2' AS EMPNAME
	,'Division2' AS Division UNION ALL
SELECT  
	3 AS EMPNUM
	,'File12'AS FILENAME
	,'Name12' AS EMPNAME
	,'Division1' AS Division UNION ALL
SELECT  
	4 AS EMPNUM
	,'File14'AS FILENAME
	,'Name14' AS EMPNAME
	,'Division1' AS Division UNION ALL
SELECT  
	5 AS EMPNUM
	,'File21'AS FILENAME
	,'Name21' AS EMPNAME
	,'Division2' AS Division UNION ALL
SELECT  
	6 AS EMPNUM
	,'File3'AS FILENAME
	,'Name3' AS EMPNAME
	,'Division3' AS Division) -- table create and data insertion part

-- Query starts
SELECT
	X.*	
FROM(
	SELECT 
		EMPNUM = ISNULL(CAST(t.EMPNUM AS VARCHAR(10)), ' ')	
		,FILENAME = ISNULL(CAST(t.FILENAME AS VARCHAR(10)), ' ')		
		,EMPNAME = ISNULL(CAST(t.EMPNAME AS VARCHAR(10)), ' ')			
		,Division = CASE 
						WHEN t.Division IS NULL AND t.EMPNUM IS NULL THEN 'Total :-'
						WHEN t.EMPNUM IS NULL THEN 'Total ' + t.Division + ' Count :-'
						ELSE t.Division END	
		,DivisionCount = COUNT(t.Division)
	FROM   CTE t	
	GROUP BY ROLLUP(t.Division,t.EMPNUM,t.EMPNAME,t.FILENAME))X
WHERE (LEN(X.FILENAME) > 0 AND LEN(X.EMPNAME) > 0)
OR	(LEN(X.FILENAME) = 0 AND LEN(X.EMPNAME) = 0 AND LEN(X.EMPNUM) = 0)

EMPNUM	FILENAME	EMPNAME	Division         	     DivisionCount
1	       File1	Name1	Division1	                1
3	       File12	Name12	Division1	                1
4	       File14	Name14	Division1	                 1
 	 	 	                Total Division1 Count :-	3
2	       File2	Name2	Division2	                 1
5	       File21	Name21	Division2	                 1
 	 	 	                 Total Division2 Count :-	2
6	       File3	Name3	Division3	                1
 	 	 	                 Total Division3 Count :-	1
 	 	 	                 Total :-	                 6

Dim orders = _
(
    From oc In context.Order_Completed
    Group Join srs In context.Shipping_Realtime On oc.RateAPICode Equals srs.RateAPICode Into rRates = Group From srs In rRates.DefaultIfEmpty()
    Group Join fsm In Context.Shipping_Free_Matrix On oc.RateAPICode Equals fsm.RateAPICode Into fRates = Group From fsm In fRates.DefaultIfEmpty()
    Where oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
    Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
    Or oc.OrderStatus = "ORDER_CANCELED" _
    And oc.OrderDate >= DbFunctions.AddDays(Today, -7)
    Order By oc.OrderNumber
    Select New With
    {
        .OrderID = oc.CompletedOrderID,
        .OrderDate = oc.OrderDate,
        .OrderNumber = oc.OrderNumber,
        .OrderStatus = oc.OrderStatus,
        .LoginID = oc.LoginID,
        .GrandTotal = oc.GrandTotal,
        .RateAPICode = oc.RateAPICode,
        .RateCharge = oc.TotalNetCharge,
        .RateAPIName = If(Not srs.RateName is Nothing, srs.RateName, fsm.RateName) 
    }
).AsEnumerable()

SELECT SUSER_SNAME([Transaction SID])
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'

USE [TestDB]
GO

--Create table tblTestEmployee
CREATE TABLE [dbo].[tblTestEmployee]
(
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[EmpName] [varchar](100) NOT NULL,
	[Address] [varchar](100) NOT NULL
)

--Populate some records to the tblTestEmployee
INSERT INTO [dbo].[tblTestEmployee] 
VALUES('Emp1','Address1'),('Emp2','Address2'),('Emp3','Address3'),('Emp4','Address4')

--Drop the column Address
ALTER TABLE [dbo].[tblTestEmployee] DROP COLUMN [Address]

--Find who has done that
GO
SELECT SUSER_SNAME([Transaction SID])
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
GO

DECLARE @T TABLE(SName VARCHAR(20), Subject VARCHAR(20), Marks INT, ExamDate  DATE)
INSERT INTO @T
SELECT 'A', 'Subject1', 77, '2011-01-01' UNION ALL
SELECT 'A', 'Subject2', 97, '2011-01-01' UNION ALL
SELECT 'B', 'Subject1', 80 ,'2012-04-01' UNION ALL
SELECT 'B', 'Subject2', 70, '2012-03-01' UNION ALL
SELECT 'C', 'Subject1', 44, '2011-01-01' UNION ALL
SELECT 'C', 'Subject2', 90, '2011-01-01' UNION ALL
SELECT 'D', 'Subject1', 79 ,'2012-04-01' UNION ALL
SELECT 'D', 'Subject2', 66, '2012-03-01'

SELECT X.*
FROM ( SELECT
        t.*
        ,Rn = DENSE_RANK() OVER(PARTITION BY t.Subject ORDER BY t.Marks DESC)
FROM @T t) X WHERE X.Rn = 3

SName	Subject	   Marks	ExamDate	Rn
A	     Subject1	77	    2011-01-01	3
B	     Subject2	70	    2012-03-01	3