SQL 2005 Query Assistance Needed.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL 2005 Query Assistance Needed.

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2010-07-23 : 12:16:31

First and foremost, the SQL is handled dynamically by the server, therefore items in my WHERE clause will look odd. Please ignore this as its not the issue.

I am trying to get the @FiscalYearStarts coded right in my report below, however every row is returned with a NULL. I think I goofed something. Any help is deeply appreciated.



SET NOCOUNT ON

/********* Determine the beginning of the fiscal year ***************************/

DECLARE @FiscalYearMonth INT
DECLARE @RequestedMonth INT
DECLARE @PatientVisitId INT
DECLARE @PatientVisit DATETIME
DECLARE @FiscalYearStarts DATETIME

SELECT
   @FiscalYearMonth = 1
SELECT
   @FiscalYearMonth = ISNULL(otherlong , 1)
FROM
   medlists
WHERE
   tablename = 'fiscalyear'
SELECT
   @RequestedMonth = MONTH(CONVERT(DATETIME , pv.Visit))
FROM
   PatientVisit pv
 WHERE 
 pv.PatientVisitId = @PatientVisitId  
					

IF @FiscalYearMonth <= @RequestedMonth 
   BEGIN
      SELECT
         @FiscalYearStarts = CONVERT(VARCHAR , @FiscalYearMonth) + '/1/' + CONVERT(VARCHAR , YEAR(CONVERT(DATETIME , @PatientVisit)))
   END
ELSE 
   BEGIN
      SELECT
         @FiscalYearStarts = CONVERT(DATETIME , CONVERT(VARCHAR , @FiscalYearMonth) + '/1/' + CONVERT(VARCHAR , YEAR(CONVERT(DATETIME , @PatientVisit)) - 1))
   END
   
/******************************************************************************/

SELECT
	pv.TicketNumber,
	pv.Visit, 
	'01' AS EntityCode, 
	@FiscalYearStarts AS [Fiscal Year],
	'  ' AS [Fiscal Period],
	'GL' AS [Subsystem],
	'JRNENT' AS [Source Code],
	LEFT(ISNULL(doc.Ledger,'')+ SPACE(10),10) AS [Dept No],
	LEFT(LEFT(isnull(fac.Ledger,''),4) + LEFT(isnull(fin.Ledger,''),4) + SPACE(10) , 10) AS [SubAccount No],
	SUM(pvp.totalfee) AS [Amount],
	'C' AS [Debit/Credit], 
	'Centricity Entry' + SPACE(34) AS [Description],
	SPACE(6)AS [Proj Id],
	SPACE(16)AS [Filler]
FROM
	PatientVisit pv 
	INNER JOIN DoctorFacility comp ON pv.CompanyId = comp.DoctorFacilityId 
	INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId 
	INNER JOIN DoctorFacility doc ON pv.DoctorId = doc.DoctorFacilityId 
	LEFT OUTER JOIN Medlists fin ON pv.FinancialClassMId = fin.MedListsId 
	INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
	INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId 
	INNER JOIN Batch b ON pvp.BatchID = b.BatchID
	INNER JOIN PatientVisitProcsAgg pvpa ON pvp.PatientVisitProcsID = pvpa.PatientVisitProcsID 
	LEFT OUTER JOIN Procedures p ON pvp.ProceduresID = p.ProceduresID 
	LEFT JOIN MedLists Procs ON p.DepartmentMId = procs.MedListsId 

WHERE 
	b.entry >= ISNULL(NULL,'1/1/1900') 
	AND b.entry < DATEADD(DAY,1,ISNULL(NULL,'1/1/3000')
	)	
	AND  --Filter on Facility
	(
	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on Company
	(
	(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on Financial Class
	(
	(NULL IS NOT NULL AND pv.FinancialClassMID IN (NULL)) OR
	(NULL IS NULL)
	)
	
GROUP BY 
	pv.TicketNumber,
	pv.Visit,
	doc.Ledger,
	fac.Ledger,
	fin.Ledger
	
HAVING SUM(pvp.TotalFee)<> 0 
	
ORDER BY pv.TicketNumber

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-23 : 12:26:25

There is a LEFT OUTER JOIN so it is very possible that you could get some NULL values. I couldn't tell from your description, but I assume only some of the columns are NULL and not every column (possibly those from the Medlists table)?

JeffS23
Posting Yak Master

212 Posts

Posted - 2010-07-23 : 12:30:39

I should not have any NULL values. I get a NULL back in every row in my dataset, which tells me something is not right in my SQL.

JeffS23
Posting Yak Master

212 Posts

Posted - 2010-07-23 : 12:39:26

Lamprey -

I see an error I missed. I never set @PatientVisit. I added in that logic and its giving me results other then NULL now, but not what I had expected. In my dataset, I have one with a Visit of '2006-02-20 11:14:00.000' and now a Fiscal Year equal to '2009-07-01 00:00:00.000' I would like to see this as '2006' not sure why the 2009 date?

The new full query


SET NOCOUNT ON

/********* Determine the beginning of the fiscal year ***************************/

DECLARE @FiscalYearMonth INT
DECLARE @RequestedMonth INT
DECLARE @PatientVisitId INT
DECLARE @PatientVisit DATETIME
DECLARE @FiscalYearStarts DATETIME

SELECT
   @FiscalYearMonth = 1
SELECT
   @FiscalYearMonth = ISNULL(otherlong , 1)
FROM
   medlists
WHERE
   tablename = 'fiscalyear'
   
SELECT
   @PatientVisit = pv.Visit
FROM
   PatientVisit pv 
   
SELECT
   @RequestedMonth = MONTH(CONVERT(DATETIME , pv.Visit))
FROM
   PatientVisit pv
WHERE
   pv.PatientVisitId = @PatientVisitId  
					

IF @FiscalYearMonth <= @RequestedMonth 
   BEGIN
      SELECT
         @FiscalYearStarts = CONVERT(VARCHAR , @FiscalYearMonth) + '/1/' + CONVERT(VARCHAR , YEAR(CONVERT(DATETIME , @PatientVisit)))
   END
ELSE 
   BEGIN
      SELECT
         @FiscalYearStarts = CONVERT(DATETIME , CONVERT(VARCHAR , @FiscalYearMonth) + '/1/' + CONVERT(VARCHAR , YEAR(CONVERT(DATETIME , @PatientVisit)) - 1))
   END
   
/******************************************************************************/

SELECT
   pv.TicketNumber,
   pv.Visit,
   '01' AS EntityCode,
   @FiscalYearStarts AS [Fiscal Year],
   '  ' AS [Fiscal Period],
   'GL' AS [Subsystem],
   'JRNENT' AS [Source Code],
   LEFT(ISNULL(doc.Ledger , '') + SPACE(10) , 10) AS [Dept No],
   LEFT(LEFT(ISNULL(fac.Ledger , '') , 4) + LEFT(ISNULL(fin.Ledger , '') , 4) + SPACE(10) , 10) AS [SubAccount No],
   SUM(pvp.totalfee) AS [Amount],
   'C' AS [Debit/Credit],
   'Centricity Entry' + SPACE(34) AS [Description],
   SPACE(6) AS [Proj Id],
   SPACE(16) AS [Filler]
FROM
   PatientVisit pv 
INNER JOIN DoctorFacility comp ON pv.CompanyId = comp.DoctorFacilityId 
INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId 
INNER JOIN DoctorFacility doc ON pv.DoctorId = doc.DoctorFacilityId 
LEFT OUTER JOIN Medlists fin ON pv.FinancialClassMId = fin.MedListsId 
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId 
INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId 
INNER JOIN Batch b ON pvp.BatchID = b.BatchID 
INNER JOIN PatientVisitProcsAgg pvpa ON pvp.PatientVisitProcsID = pvpa.PatientVisitProcsID 
LEFT OUTER JOIN Procedures p ON pvp.ProceduresID = p.ProceduresID 
LEFT JOIN MedLists Procs ON p.DepartmentMId = procs.MedListsId
WHERE
   b.entry >= ISNULL(NULL , '1/1/1900') AND
   b.entry < DATEADD(DAY , 1 , ISNULL(NULL , '1/1/3000')) AND
   --Filter on Facility
	(
        (
          NULL IS NOT NULL AND
          pv.FacilityID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
   --Filter on Company
	(
        (
          NULL IS NOT NULL AND
          pv.CompanyID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
   --Filter on Financial Class
	(
        (
          NULL IS NOT NULL AND
          pv.FinancialClassMID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      )
GROUP BY
   pv.TicketNumber,
   pv.Visit,
   doc.Ledger,
   fac.Ledger,
   fin.Ledger
HAVING
   SUM(pvp.TotalFee) <> 0
ORDER BY
   pv.TicketNumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources