Database

Aliyu Usman wrote:

please helo me with this issue

SELECT
ADMISSION_NO,
FULL_NAME,
EXAMS,

CASE 

WHEN EXAMS BETWEEN 75 AND 100 THEN 'A'
WHEN EXAMS BETWEEN 70 AND 74 THEN 'AB'

WHEN EXAMS BETWEEN 65 AND 69 THEN 'B'
WHEN EXAMS BETWEEN 60 AND 64 THEN 'BC'

WHEN EXAMS BETWEEN 55 AND 59 THEN 'C'
WHEN EXAMS BETWEEN 50 AND 54 THEN 'CD'

WHEN EXAMS BETWEEN 45 AND 49 THEN 'D'
WHEN EXAMS BETWEEN 40 AND 44 THEN 'E'

WHEN EXAMS BETWEEN 40 AND 0 THEN 'F'

END AS GP

FROM  R_Upload_2

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