SQL SERVER : DATEDIFF การหาช่วงต่างระหว่างวันเวลา , การบวก ลบ วันที่ , บวกวันที่ ลบวันที่

Post by Goborijung at 2021-05-14 16:59:13 | ID: 1192

Select DATEDIFF(year, '1990-08-02', '2021-05-14') AS DateDiff;

ตัวอย่างการหาระยะเวลา ว่าห่างกันกี่นาที
Select DATEDIFF(MINUTE,'2022-07-25 11:50:44.787','2022-07-25 11:56:44.787') -- Out: 6 นาที

SQL SERVER : Dynamic SELECT TableName

Post by Goborijung at 2020-08-18 09:40:17 | ID: 729

DECLARE @tblName	nvarchar(20)	= 'Branch'
DECLARE @Query		nvarchar(200) = ''
SET @Query = 'SELECT TOP 1 * FROM ' + @tblName + ' WHERE OID = 1'
EXECUTE sp_executesql @Query

SQL SERVER : Get Columns Name , Show Columns Name

Post by Goborijung at 2023-07-11 17:13:07 | ID: 1948

Declare @tblName varchar(100) = 'PDK1_M_PAOUNDAM'

Select  ROW_NUMBER() over(Order by Column_Name) as No,Column_Name  
,case Data_Type when 'datetime' then Data_Type 
when 'bit' then Data_Type
when 'int' then Data_Type
when 'numeric' then Data_Type+'('+rtrim(cast(NUMERIC_PRECISION as char(5)))+','+rtrim(cast(NUMERIC_SCALE as char(5)))+')' 
else Data_Type+'('+cast(Lengths as varchar(5))+')'
end as Data_Type
,Descs
From 
(	
	Select name as Column_Name
	/*,iif(is_nullable = 0 , 'F','T') as is_nullable*/
	,(Select DATA_TYPE From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = @tblName And COLUMN_NAME = sc.name) as Data_Type 
	,(Select CHARACTER_MAXIMUM_LENGTH From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = @tblName And COLUMN_NAME = sc.name) as Lengths
	,(Select NUMERIC_PRECISION From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = @tblName And COLUMN_NAME = sc.name) as NUMERIC_PRECISION
	,(Select NUMERIC_SCALE From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = @tblName And COLUMN_NAME = sc.name) as NUMERIC_SCALE
	,(Select value From sys.extended_properties Where major_id = sc.object_id and minor_id = sc.column_id) as Descs

	From sys.columns sc
	Where object_id = (Select object_id From sys.tables Where name = @tblName)

) A
Order by Column_Name

SQL SERVER : Get MAX ID + 1 , maxid

Post by Goborijung at 2020-12-17 16:41:12 | ID: 901

SELECT CASE WHEN ISNULL(MAX(OIDSMPLMT), '') = '' THEN 1 ELSE MAX(OIDSMPLMT)+1 END AS newOIDMat FROM SMPLRequestMaterial

SQL SERVER : How to Create Function in SQL SERVER

Post by Goborijung at 2020-07-01 10:31:45 | ID: 645

How to Create Function in MSSQL

>> Scalar Functions (User-Defined-Functions) | scalar function (scalar UDF)

IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL  
    DROP FUNCTION ufnGetInventoryStock;  
GO  
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)  
RETURNS int   
AS   
-- Returns the stock level for the product.  
BEGIN  
    DECLARE @ret int;  
    SELECT @ret = SUM(p.Quantity)   
    FROM Production.ProductInventory p   
    WHERE p.ProductID = @ProductID   
        AND p.LocationID = '6';  
     IF (@ret IS NULL)   
        SET @ret = 0;  
    RETURN @ret;  
END; 

SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply  
FROM Production.Product  
WHERE ProductModelID BETWEEN 75 and 80;  

-----------------------------------------------------------------------------------------

>> Table-Valued Functions | inline table-valued function (TVF)

IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL  
    DROP FUNCTION Sales.ufn_SalesByStore;  
GO  
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)  
RETURNS TABLE  
AS  
RETURN   
(  
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'  
    FROM Production.Product AS P   
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID  
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID  
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID  
    WHERE C.StoreID = @storeid  
    GROUP BY P.ProductID, P.Name  
);

SELECT * FROM Sales.ufn_SalesByStore (602); 

-----------------------------------------------------------------------------------------

>> Multi-statement table-valued function (MSTVF)

IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL  
    DROP FUNCTION dbo.ufn_FindReports;  
GO  
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)  
RETURNS @retFindReports TABLE   
(  
    EmployeeID int primary key NOT NULL,  
    FirstName nvarchar(255) NOT NULL,  
    LastName nvarchar(255) NOT NULL,  
    JobTitle nvarchar(50) NOT NULL,  
    RecursionLevel int NOT NULL  
)  
--Returns a result set that lists all the employees who report to the   
--specific employee directly or indirectly.*/  
AS  
BEGIN  
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns  
    AS (  
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n  
        FROM HumanResources.Employee e   
INNER JOIN Person.Person p   
ON p.BusinessEntityID = e.BusinessEntityID  
        WHERE e.BusinessEntityID = @InEmpID  
        UNION ALL  
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor  
        FROM HumanResources.Employee e   
            INNER JOIN EMP_cte  
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode  
INNER JOIN Person.Person p   
ON p.BusinessEntityID = e.BusinessEntityID  
        )  
-- copy the required columns to the result of the function   
   INSERT @retFindReports  
   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
   FROM EMP_cte   
   RETURN  
END;  
GO  

SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
FROM dbo.ufn_FindReports(1);  

SQL SERVER : How to Create Store PROCEDURE

Post by Goborijung at 2021-01-22 16:41:31 | ID: 643

>> Stored Procedure Syntax
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

EX.
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

>> Execute a Stored Procedure
EXEC procedure_name;

EX.
EXEC SelectAllCustomers;

--------------------------------------------------------------------------

>> Stored Procedure With One Parameter

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

EXEC SelectAllCustomers @City = 'London';

--------------------------------------------------------------------------

>> Stored Procedure With Multiple Parameters

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';


>> ตัวอย่างการใช้งานจริง
USE [MDS]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE sp_Test
	@oidSmpl	int
	, @oidSize	int
	, @oidColor int
AS
BEGIN
	SET NOCOUNT ON;

    Select * From SMPLRequest smpl inner join SMPLQuantityRequired q on q.OIDSMPL = smpl.OIDSMPL

END
GO

EXEC sp_Test @oidSmpl = 1,  @oidSize = 1, @oidColor  = 1;

SQL SERVER : ISNULL

Post by Goborijung at 2020-06-29 16:03:38 | ID: 642

ISNULL(Column,'1') // ถ้ามีค่าเป็น Null ให้มีค่าเป็น 1

SQL SERVER : Select Right

Post by Goborijung at 2021-02-22 13:22:54 | ID: 1016

Select RIGHT('I Love You',3) as cutRight

// Output : You

SQL SERVER : SET Collate by Columns Name

Post by Goborijung at 2020-07-10 08:25:38 | ID: 661

Select top 1 
cusDB1.Code,cusDB1.Name 
,cusDB2.Code as navCusCode,cusDB2.ShortName,cusDB2.Name 
From GSSv2_Prod.dbo.Customer /*gss*/cusDB1 inner join ERP2020.dbo.Customer /*erp*/cusDB2 on cusDB1.Code = cusDB2.ShortName collate SQL_Latin1_General_CP1_CI_AS

/* เงื่อนไข */
GSSv2_Prod.dbo.Customer.Code = ERP2020.dbo.Customer.ShortName

SQL SERVER : Show Server List

Post by Goborijung at 2020-08-13 13:39:03 | ID: 719

SELECT * FROM [sys].[servers]