T-SQL Query

Question

T-SQL Query

john-902052

SSCommitted

Points: 1578
More actions
February 14, 2010 at 11:37 pm

#138838

All,
I am not very good with T-SQL, although I am learning. I have a query that I am trying to write but it is kickin my butt.
Process Participants looking for terminations. Terminations are defined by 3 consecutive absences
or exceeding MAX absences for Program.
An absence is recorded by adding a NoShow Fee record to the Ledger Table.
I know that there has to be a way to do most if not all of this in SQL and just return a table to the report. Any guidence would be GREATLY appreciated.
USE [ClientManager]
GO
/****** Object: StoredProcedure [dbo].[usp_GetAllNoShows] Script Date: 02/14/2010 22:05:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:--------------
-- Create date: 2/11/2010
-- Description:Get all No Show Records for the Participant
-- =============================================
ALTER PROCEDURE [dbo].[usp_GetAllNoShows]
AS
BEGIN
SET NOCOUNT ON;
SELECT TransDate,l.FeeID, p.PartFName + ' ' + p.PartLName as FullName, l.PartID
FROM ledger l
INNER JOIN fees f on f.feeid = l.feeid
INNER JOIN Participants p on l.PartID = p.PartID
WHERE l.feeid = '1'
Order by l.PartID, TransDate
END
-----DDL-----
USE [ClientManager]
GO
/****** Object: Table [dbo].[Ledger] Script Date: 02/14/2010 22:32:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ledger](
[TransID] [int] IDENTITY(1,1) NOT NULL,
[TransDate] [datetime] NULL,
[CR] [money] NULL,
[DB] [money] NULL,
[ReasonCodeID] [int] NULL,
[PartID] [int] NULL,
[CaseID] [int] NULL,
[ReceiptNumber] [nvarchar](15) NULL,
[Description] [nvarchar](50) NULL,
[FeeID] [int] NULL,
CONSTRAINT [PK_Ledger] PRIMARY KEY CLUSTERED
(
[TransID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
USE [ClientManager]
GO
/****** Object: Table [dbo].[Participants] Script Date: 02/14/2010 22:33:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Participants](
[PartID] [int] IDENTITY(1,1) NOT NULL,
[DateEntered] [datetime] NULL,
[PartDLNum] [nvarchar](15) NULL,
[PartLName] [nvarchar](30) NULL,
[PartFName] [nvarchar](30) NULL,
[PartAddr] [nvarchar](30) NULL,
[PartCity] [nvarchar](30) NULL,
[PartState] [nvarchar](2) NULL,
[PartZip] [nvarchar](5) NULL,
[PartPhone] [nvarchar](14) NULL,
[PartDOB] [datetime] NULL,
[Ethnicity] [int] NULL,
[AgeCategory] [varchar](10) NULL,
[CourtCountyID] [int] NULL,
[ClassTime] [nvarchar](50) NULL,
[Counselor] [int] NULL,
[Gender] [nvarchar](1) NULL,
[ClassDay] [int] NULL,
[PartEmail] [nvarchar](50) NULL,
[ClassID] [int] NULL,
[PaymentScheduleID] [int] NULL,
[MInitial] [nvarchar](1) NULL,
CONSTRAINT [PK_Participant] PRIMARY KEY CLUSTERED
(
[PartID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [ClientManager]
GO
/****** Object: Table [dbo].[Fees] Script Date: 02/14/2010 22:33:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Fees](
[FeeID] [int] IDENTITY(1,1) NOT NULL,
[FeeDesc] [nvarchar](50) NOT NULL,
[FeeAmount] [money] NOT NULL,
CONSTRAINT [PK_Fees] PRIMARY KEY CLUSTERED
(
[FeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Can you give us some sample data (as Insert statements, please), and expected output from that data? That would help a lot.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

john-902052 SSCommitted Points: 1578 More actions · Answer 2

All Fixed. Sorry and here you go.

INSERT INTO [ClientManager].[dbo].[Fees]

([FeeDesc],[FeeAmount])

VALUES

('No Show',15.00)

INSERT INTO [ClientManager].[dbo].[Ledger]