February 18, 2012 at 1:47 am
I am doing a MIS Form.I select multiple table to get a data as single row.this Store procedure is correct
USE [smvmch]
GO
/****** Object: StoredProcedure [dbo].[spMISStudentFees] Script Date: 02/14/2012 17:55:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Tamil Selvan Selvaraj
-- Create date: 12-02-2012
-- Description: MIS Detail For Student Fees Detail
-- =============================================
CREATE PROCEDURE [dbo].[spMISStudentFees]
@FromDateDATETIME,
@ToDateDATETIME
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @FeesCollection INT,@FeesOutStanding INT,@Cancel INT,@Refund INT
DECLARE @ExcessFees INT,@NoOfStudent INT
SELECT @FeesCollection = SUM(SBD.Amount) FROM SStudentBilling SB
INNER JOIN SStudentBillingDetail SBD ON SB.BillNo = SBD.BillNo
WHERE BillDate BETWEEN CONVERT(Date,@FromDate) AND CONVERT(Date,@ToDate)
SELECT
@Cancel = SUM(Cancel)
,@Refund = SUM(Refund)
FROM
(SELECT CASE WHEN CR.BillType = 1 THEN SUM(CRD.Amount) ELSE 0 END AS Cancel,
CASE WHEN CR.BillType = 2 THEN SUM(CRD.Amount) ELSE 0 END AS Refund
FROM StudentCancelRefund CR
INNER JOIN StudentCancelRefundDetail CRD ON CR.BillCancelRefundNo = CRD.BillCancelRefundNo
WHERE CR.CancelRefundDate BETWEEN CONVERT(Date,@FromDate) AND CONVERT(Date,@ToDate)
GROUP BY CR.BillType) AS Can
SELECT @FeesOutStanding = SUM(Balance) FROM SStudentFees SF
INNER JOIN SStudent S ON SF.StudentID = S.StudentID
WHERE ParticularID <> 7 AND S.IsActive = 1 AND Balance>0
SELECT @ExcessFees = SUM(Balance) FROM SStudentFees SF
INNER JOIN SStudent S ON SF.StudentID = S.StudentID
WHERE ParticularID = 7 AND S.IsActive = 1
SELECT @NoOfStudent = COUNT(StudentID) FROM StudentFeesModification WHERE ReferenceDate BETWEEN CONVERT(Date,@FromDate) AND CONVERT(Date,@ToDate)
SELECT ISNULL(@FeesCollection,0) AS FeesCollection
,ISNULL(@FeesOutStanding,0) AS FeesOutStanding
,ISNULL(@Cancel,0) AS Cancel
,ISNULL(@Refund,0) AS Refund
,ISNULL(@ExcessFees,0) AS Excess
,ISNULL(@NoOfStudent,0) AS NoOfStudent
END
February 18, 2012 at 2:36 am
I can't tell whether it's correct without knowing what it's supposed to do. Does it run? Does it return the results you want? If yes to both, it's probably correct.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply