October 31, 2008 at 11:12 am
***************************************************************************
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
*****************************************************************************
October 31, 2008 at 11:42 am
Okay, what is the question?
October 31, 2008 at 11:48 am
How can I merge the rows with the duplicate or common custnumbers, amounts and semesters?
e.g. row 4 and 5 have a common custnumber, amount and semester so i want to display just one row for that customer displaying all the values in the original two rows.
Hope this helps. 🙁
Thanks
Sam
October 31, 2008 at 12:40 pm
Please do not double post. It clutters things up and is generally confusing.
The other thread... http://www.sqlservercentral.com/Forums/Topic594962-338-1.aspx
Maybe I'm missing something... but I don't see where that script generates that output.
The problem with displaying that data on one line is your Y/N on Ind3. You need to decide what you want to do about that.
Let's use this thread to continue the discussion.
October 31, 2008 at 7:18 pm
sam (10/31/2008)
How can I merge the rows with the duplicate or common custnumbers, amounts and semesters?e.g. row 4 and 5 have a common custnumber, amount and semester so i want to display just one row for that customer displaying all the values in the original two rows.
Hope this helps. 🙁
Thanks
Sam
You are trying to do what is known as a "Cross-Tab" or "Pivot"... please see the following article for the proper way...
http://www.sqlservercentral.com/articles/T-SQL/63681/
More specifically, you cannot use DISTINCT to do this... you must use GROUP BY instead.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2008 at 12:49 pm
Hi Jeff,
Thanks for the direction. I still am finding it difficult to understand how to do this. The deadline for this is today, is there anyway you can help me with this?
Thanks,
Sam
November 4, 2008 at 1:26 pm
Maybe I'm missing something... but I don't see where that script generates that output.
The problem with displaying that data on one line is your Y/N on Ind3. You need to decide what you want to do about that.
That still applies. And I still don't see how that script generates that output.
November 4, 2008 at 5:22 pm
sam (11/4/2008)
Hi Jeff,Thanks for the direction. I still am finding it difficult to understand how to do this. The deadline for this is today, is there anyway you can help me with this?
Thanks,
Sam
Sorry I missed this this morning, Sam... I don't normally look at forums or even my personal email during the day... ethics and all that. :hehe:
Anyway, let's keep things really simple... What I would do is NOT mess with the query that produces the output you currently have. Rather, I would use that monster as a "Derived Table" (sub-query in the from clause that is given a table alias and used as if it were a table) or dump the results into a temp table where you can work on it. With that in mind, understand that the SELECT/UNION ALL's in the following query represent that derived table or temp table... I'm also assuming that, logically speaking, "Y" is like a "1" and "N" is like a "0" and that what you really want is an "OR'd Add" between similar rows for each IndX column...
SELECT CustNumber,
Amount,
Semester,
MAX(Ind1) AS Ind1,
MAX(Ind2) AS Ind2,
MAX(Ind3) AS Ind3,
MAX(Ind4) AS Ind4
FROM
(--==== Derived table "d" below would be replaced by your entire query or a temp table result
SELECT 1005401 CustNumber, -282.77 Amount,'2008 June Semester' Semester,'N' Ind1,'N' Ind2,'Y' Ind3,'N' Ind4 UNION ALL
SELECT 1008007,-1687.12,'2008 September Semester','N','N','Y','N' UNION ALL
SELECT 1008615, -750.00,'2008 August Semester' ,'N','N','Y','N' UNION ALL
SELECT 1009089, 3125.00,'2007 December Semester' ,'N','N','Y','N' UNION ALL
SELECT 1009089, 3125.00,'2007 December Semester' ,'N','Y','N','N' UNION ALL
SELECT 1009288, -825.00,'2008 August Semester' ,'N','N','Y','N' UNION ALL
SELECT 1012631, 1500.00,'2008 September Semester','N','N','Y','N' UNION ALL
SELECT 1012631, 1500.00,'2008 September Semester','Y','N','N','N' UNION ALL
SELECT 1013152,-2132.64,'2008 September Semester','N','N','Y','N' UNION ALL
SELECT 1014263, 1225.00,'2008 July Semester' ,'N','N','Y','N'
)d
GROUP BY CustNumber, Amount, Semester
... and that yields the following results...
[font="Courier New"]
CustNumber Amount Semester Ind1 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 Y Y N
1009288 -825.00 2008 August Semester N N Y N
1012631 1500.00 2008 September Semester Y N Y N
1013152 -2132.64 2008 September Semester N N Y N
1014263 1225.00 2008 July Semester N N Y N
(8 row(s) affected)[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2008 at 6:48 am
Hi Jeff,
I really do not know how to express my sincere appreciation for all your help. Thank you so much. I did not even have to do the second part of it. All I did was add the max function to the indicator fields and do a group by on custnumber, amount, and semester and that fixed it.
Thank you very much and God bless you.
Sam
November 5, 2008 at 5:42 pm
that's good news, Sam. Thank you very much for the feedback. Very happy I could help.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply