quote:

---

Originally posted by Arunavally

Hai,

I used this trigger. When i insert Fees table after that this trigger will executed.

But first time only execute this trigger. I dnt knw what mistake i did?

This is my trigger.

ALTER trigger [dbo].[Fees_FeeBalance_insertion]
on [dbo].[Fees] for insert
as
begin
declare @id varchar(20),@total money,@Amt money,@SFee money, @Duedate varchar(20), @fee money,@bal money,@Paydate varchar(20)
select @id=Stid,@Amt=AmtPaid,@Paydate=PaidDate,@Duedate=DueDate from inserted
select @SFee=Fees from Batch
if not exists (select SId from FeeBalance )
begin
set @bal = @SFee-@Amt
insert into FeeBalance values(@id,@Amt,@bal,@Duedate)
end
else
begin
select @fee=Total from FeeBalance
set @bal = @SFee - (@fee +@Amt)
update FeeBalance set Total=@Amt+@fee,Balance=@bal,DueDate=@Duedate where Sid=@id
end
update StudReg set UpdationDate=@PayDate where StudReg.StuId = @id
end

If any reference field is need this case?

Kindly help me.

---

declare @x varchar(26) = 1234;

;with N(n) as (select 1 union all select n+1 from N where n < 26)
select char(ascii('a')+n-1), SUBSTRING(CAST(@x as varchar(26)),n,1)
from N
where n <= Len(@x);

quote:

---

Originally posted by Arunavally

Thank you.
I got error.
Invalid column name n.

If it need to declare n?

---

SELECT COUNT(*)
FROM YourTable
WHERE YEAR(CAST(@dt AS DATE)) = 2013;

SELECT COUNT(*)
FROM YourTable
WHERE @DT LIKE '%2013';

DECLARE @year INT = 2013;

;WITH Students AS 
(
	SELECT @year AS [Year],COUNT(*) AS Students
	FROM StudentReg
	WHERE YEAR(CAST(s.CreationDate AS DATE)) = @year

),
Finance AS 
(
	SELECT @year AS [Year],SUM (Payables) AS Expenses,SUM(Receivables) AS Income
	FROM Finance
	WHERE YEAR(CAST(s.CreationDate AS DATE)) = @year -- or whatever isthe date column
)
SELECT Students, Expenses, Income
FROM Students s INNER JOIN Finance f ON f.[Year]=s.[Year];

quote:

---

Originally posted by Arunavally

Thank you. Its work well.

Can i join any table

SELECT COUNT(*),fb.Sum(Payables) as Exp,Sum(Receivables) as Income FROM StudReg s join Finance fb on ...... WHERE YEAR(CAST(s.CreationDate AS DATE)) = 2013;

how it is work?

I wnt to display Total no of students from student table and Total of Payables and Total of Receivables from finance table in the year 2013

It is possible? But no link between two table

---

SELECT StudCnt,
Exp,
Income
FROM 
(
SELECT COUNT(*) AS StudCnt
FROM StudReg 
WHERE CreationDate > = '20130101'
AND CreationDate < '20140101'
)s
CROSS APPLY (SELECT Sum(Payables) as Exp,Sum(Receivables) as Income
 FROM Finance
 WHERE YEAR(DateField) = YEAR(s.CreationDate)
)f

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by Arunavally

Thank you. Its work well.

Can i join any table

SELECT COUNT(*),fb.Sum(Payables) as Exp,Sum(Receivables) as Income FROM StudReg s join Finance fb on ...... WHERE YEAR(CAST(s.CreationDate AS DATE)) = 2013;

how it is work?

I wnt to display Total no of students from student table and Total of Payables and Total of Receivables from finance table in the year 2013

It is possible? But no link between two table

---

if no link it should be this

SELECT StudCnt,
Exp,
Income
FROM 
(
SELECT COUNT(*) AS StudCnt
FROM StudReg 
WHERE CreationDate > = '20130101'
AND CreationDate < '20140101'
)s
CROSS APPLY (SELECT Sum(Payables) as Exp,Sum(Receivables) as Income
 FROM Finance
 WHERE YEAR(DateField) = YEAR(s.CreationDate)
)f

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

---

quote:

---

Originally posted by Arunavally

Hai, How to get last inserted record from table without id desc option.
Kindly help me.

---

DECLARE @LastID int

INSERT table
.... your actual insert code

SET @LastID = SCOPE_IDENTITY()

SELECT *
FROM Table
WHERE IDCol = @LastID