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.

  • 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


    Madhivanan

    Failing 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

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

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