Group Records on a single row
-
***************************************************************************
declare @T4CreditBalanceWithdrawalDesc varchar(50)
DECLARE @DateStart DateTime
,@DateEnd DateTime
SET @DateStart = '01-01-1900'
SET @DateEnd = '12-31-2099'
SET @T4CreditBalanceWithdrawalDesc = 'No Data Received'
Declare @data as varchar(300)
Declare @SplitOn as char(1)
Declare @Cnt int
Declare @tblT4CreditBalanceWithdrawalDesc Table( T4CB varchar(300) )
Set @data = @T4CreditBalanceWithdrawalDesc
Set @SplitOn = ','
Set @Cnt = 1
Set @data= 'No Data Received,Keep-Lender,Keep-Student'
While (Charindex(@SplitOn,@Data)>0)
Begin
Insert Into @tblT4CreditBalanceWithdrawalDesc (T4CB)
Select ltrim(rtrim(Substring(@Data, 0,Charindex(@SplitOn,@Data))))
Set @data = LTRIM(RTRIM(Substring(@Data, Charindex(@SplitOn,@Data) + 1, Len(@Data))))
End
Insert Into @tblT4CreditBalanceWithdrawalDesc (T4CB)
Select @data
Select * from @tblT4CreditBalanceWithdrawalDesc
DECLARE @tblCreditBalance TABLE
(
StudentID varchar(20)
,CreditBalance varchar(25)
,Semester varchar(50)
)
DECLARE @tblLedger TABLE
(
CUSTNMBR varchar(15)
,NEWDOCNUM varchar(22)
,Semester varchar(31)
)
DECLARE @MostRecentAY TABLE
(
StudentID int
,SemesterID int
,AYDesc varchar(100)
)
DECLARE @tblFSARefunds TABLE
(
StudentID char(20) null
,RefundAmount Numeric(11,2) null
,Semester varchar(30) null
,NonFSAIndicator varchar(1) null
,PELLIndicator varchar(1) null
,FFELPIndicator varchar(1) null
,DirectLoan varchar(1) null
)
INSERT INTO @tblCreditBalance
--Select * from tmpBalance
SELECT *
FROM dbo.fn_CreditBalance(@DateStart, @DateEnd)
INSERT INTO @tblLedger (CUSTNMBR, NEWDOCNUM, Semester)
SELECT DISTINCT
r.CUSTNMBR
,CASE WHEN LEFT(RTRIM(r.DOCNUMBR),2) in ('DP','WD')
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
ELSE RTRIM(r.DOCNUMBR)
END
,hm2503.DSCRIPTN AS Semester
FROM dbo.SOP10106 s WITH (NOLOCK)
RIGHT OUTER JOIN dbo.SOP30300 sh WITH (NOLOCK)
RIGHT OUTER JOIN dbo.HM02503 hm2503 WITH (NOLOCK)
INNER JOIN dbo.HM02500 hm25 WITH (NOLOCK) ON hm2503.HM_Key_3 = hm25.HM_Key_3
RIGHT OUTER JOIN dbo.RM20101 r WITH (NOLOCK) ON hm25.DOCNUMBR = r.DOCNUMBR
ON sh.SOPNUMBE = r.DOCNUMBR
ON s.SOPNUMBE = r.DOCNUMBR
WHERE (hm25.RMDTYPAL <> 99 OR hm25.RMDTYPAL IS NULL)
AND r.RMDTYPAL <> 3
AND r.CUSTNMBR IN ( SELECT convert(varchar, StudentID, 15)
FROM APUS_PAD_LINKED.apus_pad.dbo.tblAcademicYear )
-- AND r.CUSTNMBR = @StudentID
--Payables
INSERT INTO @tblLedger (CUSTNMBR, NEWDOCNUM, Semester)
SELECT b.CUSTNMBR
,CASE WHEN LEFT(RTRIM(b.APTODCNM),2) in ('DP','WD')
THEN RIGHT(RTRIM(b.APTODCNM),7)
WHEN LEFT(RTRIM(b.APTODCNM),5) = 'DEBIT'
THEN RIGHT(RTRIM(b.APFRDCNM),7)
ELSE RTRIM(b.APTODCNM)
END
,f.DSCRIPTN AS HM2503Desc
FROM dbo.HM02500 a WITH (NOLOCK)
INNER JOIN dbo.RM20201 b WITH (NOLOCK) ON a.DOCNUMBR = b.APFRDCNM
INNER JOIN dbo.PM30200 c WITH (NOLOCK) ON b.APTODCNM = RIGHT(RTRIM(c.TRXDSCRN), 13)
INNER JOIN dbo.PM30300 d WITH (NOLOCK) ON c.DOCNUMBR = d.APTODCNM AND c.VENDORID = d.VENDORID
INNER JOIN dbo.HM02500A E WITH (NOLOCK) ON a.DOCNUMBR = E.DOCNUMBR AND b.APTODCNM = E.hm_string_10
INNER JOIN dbo.HM02503 f WITH (NOLOCK) ON a.HM_Key_3 = f.HM_Key_3
WHERE a.RMDTYPAL = 9 and b.CUSTNMBR in (SELECT convert(varchar, StudentID, 15)
FROM APUS_PAD_LINKED.apus_pad.dbo.tblacademicyear)
-- AND b.CUSTNMBR = @StudentID
--Select * from @tblLedger
INSERT INTO @tblFSARefunds (StudentID, RefundAmount, Semester,
NonFSAIndicator, PELLIndicator, FFELPIndicator, DirectLoan)
SELECT DISTINCT RM_TRX.CUSTNMBR
,Credit.CreditBalance
,Credit.Semester
,CASE
When UPPER(RM_TRX.BACHNUMB) like '%FSS%' then 'N'
When UPPER(RM_TRX.BACHNUMB) like '%FFU%' then 'N'
When UPPER(RM_TRX.BACHNUMB) like '%FFP%' then 'N'
When UPPER(RM_TRX.BACHNUMB) like '%PELL%' then 'N'
When UPPER(RM_TRX.BACHNUMB) like '%FDS%' then 'N'
When UPPER(RM_TRX.BACHNUMB) like '%FDU%' then 'N'
When UPPER(RM_TRX.BACHNUMB) like '%FDP%' then 'N'
When UPPER(RM_TRX.BACHNUMB) like '%UNSUB%' then 'N' -- Alias to FFU
When UPPER(RM_TRX.BACHNUMB) like '%SUB%' then 'N' -- Alias to FDS
When UPPER(RM_TRX.BACHNUMB) like '%PLUS%' then 'N' -- Alias to FDP
Else 'Y'
END AS NonFSAIndicator
,CASE UPPER(HM.HM_Key_2)WHEN 'PELL' THEN 'Y'
ELSE 'N'
END AS PELLIndicator
,CASE UPPER(HM.HM_Key_2)WHEN 'SUB' THEN 'Y'
WHEN 'UNSUB' THEN 'Y'
WHEN 'PLUS' THEN 'Y'
ELSE 'N'
END AS FFELPIndicator
,CASE UPPER(HM.HM_Key_2)WHEN 'DSUB' THEN 'Y'
WHEN 'DUNSUB' THEN 'Y'
WHEN 'DPLUS' THEN 'Y'
ELSE 'N'
END AS DirectLoan
FROM RM20101 AS RM_TRX (NOLOCK)
INNER JOIN @tblLedger AS Ledger ON RM_TRX.CUSTNMBR = Ledger.CUSTNMBR
AND RM_TRX.DOCNUMBR = NEWDOCNUM
INNER JOIN @tblCreditBalance AS Credit ON RM_TRX.CUSTNMBR = Credit.StudentID
AND Ledger.Semester = Credit.Semester
INNER JOIN HM02500 AS HM (NOLOCK) ON HM.DOCNUMBR = RM_TRX.DOCNUMBR
*****************************************************************************
The script returns the results below
*****************************************************************************
CustNumber Amount Semester Ind 1 Ind2 Ind3 Ind4
1005401 -282.77 2008 June Semester N N Y N
1008007 -1687.12 2008 September Semester N N Y N
1008615 -750.00 2008 August Semester N N Y N
1009089 3125.00 2007 December Semester N N Y N
1009089 3125.00 2007 December Semester N Y N N
1009288 -825.00 2008 August Semester N N Y N
1012631 1500.00 2008 September Semester N N Y N
1012631 1500.00 2008 September Semester Y N N N
1013152 -2132.64 2008 September Semester N N Y N
1014263 1225.00 2008 July Semester N N Y N
*****************************************************************************
-
No rows are highlighted.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
If it were mine, I would do the following:
1) one table with the Customer info
2) one table that has Customer number & the four Ind fields combined together per Customer (maybe a group by)
3) Then I would join those two tables together
-
budbeth (10/31/2008)
If it were mine, I would do the following:1) one table with the Customer info
2) one table that has Customer number & the four Ind fields combined together per Customer (maybe a group by)
3) Then I would join those two tables together
Do you need something like this?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
MadhivananFailing to plan is Planning to fail
-
I used the highlight tool but for some reason it did not work...i just tried the other methods in the link you provided but neither of them worked either. The rows I am trying to combine are row 4 and 5 and row 7 and 8. Please let me know if my posting is still not clear.
-
This is a duplicate thread. Please direct future replies to:
http://www.sqlservercentral.com/Forums/Topic595159-338-1.aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply