Link to home
Start Free TrialLog in
Avatar of Jeff S
Jeff SFlag for United States of America

asked on

SQL Sequential Number

In my query below, I need to make "Journal Number" equal a sequential number, per my clients request. I tried decaring @Id as Int and then setting @Id = 0 and in the field telling it @Id +1, however that did not update as I would expect, it gave all my results a "1". Any assistance is appreciated.

SET NOCOUNT ON

;WITH    CTE
          AS (
--- Bring in the Charges
               SELECT
                	'Charge' AS [Type] ,
                	pv.TicketNumber ,
                	--pvp.CPTCode ,
                	LEFT('session',40) AS [Session ID] ,
                	NULL AS [Field 2] ,
                	LEFT('journal',40) AS [Field 3] ,
					CONVERT(VARCHAR(15), DATEADD(DAY , -( DAY(DATEADD(MONTH , 1 , GETDATE())) ) , DATEADD(MONTH , 1 , GETDATE())) ,101) AS [Journal Date] ,
                	NULL AS [Field 5] ,
                	0 AS [Journal Number] ,
                	NULL AS [Field 7] ,
                	NULL AS [Field 8] ,
                	'000' AS [Grant Code] ,
                	p.Ledger AS [GL Code] ,
                	ISNULL(fac.Ledger , '') AS [Location Ledger] ,
                	'05' AS [Program Code] ,
					ISNULL(doc.Ledger , '') AS [Discipline] ,
                	ISNULL(ic.Ledger , '00') AS [Payer Ledger Number] ,
					ISNULL(drspec.Ledger , '000') AS [Department] ,
                	'000' AS [Event Activity Code] ,
                	'1' AS [Restriction Code] ,
                	'000' AS [Field 18] ,
                	NULL AS [Field 19] ,
                	0.0 AS [Debit] ,
                	SUM(pvp.totalfee) AS [Credit]
               FROM
                	PatientVisit pv
                	INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
                	INNER JOIN DoctorFacility doc ON pv.DoctorId = doc.DoctorFacilityId
                	LEFT JOIN MedLists drspec ON doc.SpecialtyMID = drspec.MedListsID
                	INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
                	INNER JOIN Batch b ON pvp.BatchID = b.BatchID
                	LEFT JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId
                	LEFT JOIN Procedures p ON pvp.ProceduresId = p.ProceduresId
               WHERE
                	--b.Status <> 0
                	b.entry >= ISNULL('01/01/2012','1/1/1900') 
    				AND b.entry < DATEADD(DAY,1,ISNULL('11/09/2012','1/1/3000'))     
               GROUP BY
                	pv.TicketNumber ,
                	--pvp.CPTCode ,
                	b.[Entry] ,
                	doc.Ledger ,
                	drspec.Ledger ,
                	fac.Ledger ,
                	ic.Ledger ,
                	p.Ledger
               HAVING
                	SUM(pvp.TotalFee) <> 0),
        CTG
          AS (
-- Insert Adjustments
               SELECT
               		'Adjustment' AS [Type] ,
                	pv.TicketNumber ,
                	LEFT('session',40) AS [Session ID] ,
                	NULL AS [Field 2] ,
                	LEFT('journal',40) AS [Field 3] ,
                	CONVERT(VARCHAR(15), DATEADD(DAY , -( DAY(DATEADD(MONTH , 1 , GETDATE())) ) , DATEADD(MONTH , 1 , GETDATE())) ,101) AS [Journal Date] ,
                	NULL AS [Field 5] ,
                	0 AS [Journal Number] ,
                	NULL AS [Field 7] ,
                	NULL AS [Field 8] ,
                	'000' AS [Grant Code] ,
                	LEFT(ISNULL(at.Ledger , '') , 4) AS [GL Code] ,
                	ISNULL(fac.Ledger , '') AS [Location Ledger] ,
                	'05' AS [Program Code] ,
					ISNULL(doc.Ledger , '') AS [Discipline] ,
                	ISNULL(ic.Ledger , '00') AS [Payer Ledger Number] ,
					ISNULL(drspec.Ledger , '000') AS [Department] ,
                	'000' AS [Event Activity Code] ,
                	'1' AS [Restriction Code] ,
                	'000' AS [Field 18] ,
                	NULL AS [Field 19] ,
                	t.amount AS [Debit] ,
                	0.0 AS [Credit]
               FROM
                	PatientVisit pv
                	INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
                	INNER JOIN DoctorFacility doc ON pv.DoctorId = doc.DoctorFacilityId
                	LEFT JOIN MedLists drspec ON doc.SpecialtyMID = drspec.MedListsID
                	INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
                	INNER JOIN VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitid
                	INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId
                	INNER JOIN Batch b ON pm.BatchID = b.BatchID
                	INNER JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId
                	--LEFT JOIN TransactionDistributions td ON t.TransactionsId = td.TransactionsId
                	--LEFT JOIN PatientVisitProcs pvp ON td.PatientVisitProcsId = pvp.PatientVisitProcsId
                	LEFT OUTER JOIN MedLists at ON t.ActionTypeMId = at.MedListsId
                	LEFT JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId
               WHERE
                	t.Action = 'A'
                	--AND b.Status <> 0
                	AND NOT EXISTS ( SELECT
                	                    1
                	                 FROM
                	                    PaymentMethod pm1
                	                 WHERE
                	                    	vt.PaymentMethodId = pm1.PaymentMethodId
                	                    	AND pm1.InsuranceTransfer = 1 )
                	AND b.entry >= ISNULL('01/01/2012','1/1/1900') 
    				AND b.entry < DATEADD(DAY,1,ISNULL('11/09/2012','1/1/3000'))      
               GROUP BY
                	pv.TicketNumber ,
                	--pvp.CPTCode ,
                	t.Amount ,
                	doc.Ledger ,
                	drspec.Ledger ,
                	fac.Ledger ,
                	ic.Ledger ,
                	ic.ListName ,
                	fac.ListName ,
                	at.Ledger
               HAVING
               	 t.amount <> 0),
        CTE1
          AS ( SELECT
                *
               FROM
                CTE
               UNION ALL
               SELECT
                *
               FROM
                CTG )
    SELECT
        --[TicketNumber] ,
        --[CPTCode], 
        [Session ID] ,
        [Field 2] ,
        [Field 3] ,
        [Journal Date] ,
        [Field 5] ,
        [Journal Number] ,
        [Field 7] ,
        [Field 8] ,
        [Grant Code] ,
        [GL Code] ,
        [Location Ledger] ,
        [Program Code] ,
        [Discipline] ,
        [Payer Ledger Number] ,
        [Department] ,
        [Event Activity Code] ,
        [Restriction Code] ,
        [Field 18] ,
        [Field 19] ,
        [Debit] ,
        [Credit]
    FROM
        CTE1
    ORDER BY
    --    TicketNumber ,
        CASE [Type]
          WHEN 'Charge' THEN 1
          WHEN 'Adjustment' THEN 2
          ELSE 3
        END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of joshbula
joshbula

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jeff S

ASKER

thanks!