create function on microsoft sql server

$.md

https://www.perplexity.ai/search/7b10bf92-6ee6-47b4-8e1f-b39ace6ee873?s=u

---

https://www.phind.com/search?cache=b34651e1-f4e9-40ce-81da-411bd476d71f

avg_sum (2).sql

SELECT student_code, AVG(lesson_rate) AS avg_lesson_rate , SUM(lesson_rate) AS sum_lesson_rate 
FROM Unit
GROUP BY student_code

SELECT StudentId, AVG(NomreDars) as miangin,SUM(NomreDars) as jameNomre
FROM EntekhabVahed
GROUP BY StudentId

-- trigger 
CREATE TRIGGER update_total_vaheds
ON EntekhabVahed
AFTER UPDATE,INSERT
AS
BEGIN
   DECLARE @StudentId INT;
   DECLARE @tedadKolVahedHa INT;
   SELECT @StudentId= StudentId FROM inserted
   SELECT @tedadKolVahedHa= SUM(dbo.getTedadVahed(DarsId))
   FROM EntekhabVahed
   WHERE StudentId = @StudentId
   GROUP BY StudentId
   UPDATE Students
   SET [tedadKolVahed] = @tedadKolVahedHa
   WHERE StudentId = @StudentId
END

-- create function 
create FUNCTION getTedadVahed2 (@darsId INT)
RETURNS INT
AS
BEGIN
	DECLARE @tedadVehd INT;
	SELECT @tedadVehd = TedadVahed
    FROM [dbo].[Dars]
    WHERE DarsId = @darsId

    RETURN @tedadVehd
END

func.sql

create function tarakomMaadares(@nahie nvarchar) returns numeric
as
begin
declare @tarakom numeric
SELECT @tarakom = sum([totalStudents]/ [totlaClasses])
FROM   madarestext231
WHERE [nahie] = @nahie
return @tarakom
end


create function tarakomMaadares5(@nahie nvarchar) returns numeric
as
begin
declare @tarakom numeric
SELECT @tarakom = sum([totalStudents]/ [totlaClasses])
FROM   madarestext231
-- WHERE [codeMadrese] = @codeMadrese
WHERE not [totlaClasses] =0 and [nahie] = @nahie
return @tarakom
end

create function tarakomMaadares2(@codeMadrese numeric) returns numeric
as
begin
declare @tarakom numeric
SELECT @tarakom = [totalStudents]/ [totlaClasses]
FROM   madarestext231
-- WHERE [codeMadrese] = @codeMadrese
return @tarakom
end

gistfile1.txt

https://chrome.google.com/webstore/detail/majdfhpaihoncoakbjgbdhglocklcgno

sen.sql

-- sql server query calculate student age with birthday
SELECT [StudentId] , [FirstName], DATEDIFF(YEAR,[Birthday],GETDATE())as age
FROM [dbo].[Students]

تابع.txt

CREATE FUNCTION CalculateLoanTime(@date_in DATE, @date_out DATE)
RETURNS NVARCHAR(50)
AS
BEGIN
  DECLARE @loan_time NVARCHAR(50)
  SET @loan_time = DATEDIFF(day, @date_in, @date_out)
  RETURN @loan_time
END

تریگر آپدیت بعد پس از پس دادن کتاب.txt

CREATE TRIGGER update_book_status_after_update
ON Amanat
AFTER UPDATE
AS
BEGIN
  UPDATE book
  SET book_status = 'in'
  FROM book b
  INNER JOIN inserted i ON b.book_id = i.book_id
  WHERE i.status = 'returned'
END

تریگر.txt

use emtehan
go
CREATE TRIGGER update_book_status ON [dbo].[Amanat]
AFTER INSERT
AS
BEGIN
    DECLARE @book_id INT;
    SELECT @book_id = book_id FROM inserted;
    UPDATE [dbo].[Book]
    SET book_status = 'out'
    WHERE Book.book_id = @book_id;
END;
go

فراخوانی تابع.txt

SELECT * , dbo.CalculateLoanTime(date_in, date_out) AS 'زمان امانت گرفته شده'
FROM Amanat