SQL Query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL Query

Author

Topic

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2010-04-02 : 13:54:27

For BG entry count whenever patient did't enter count it should display null. Can you guide me, below is the query...

DECLARE @accountID INT
DECLARE @BeginDate DATETIME
DECLARE @EndDate DATETIME

SET @accountID = 4 -- DC connect

SET @BeginDate = DateAdd(dd,-7,getdate()) -- last week
SET @EndDate = getdate() -- currentdate

SELECT T1.LoginID, T2.AccountPatientDisplayID, T1.LastLoginDateTime into #temp_patients FROM [Login] T1
INNER JOIN [Patient] T2 ON T1.LoginID = T2.PatientID AND T1.StatusID IN (1,2) -- Active, Idle
INNER JOIN [PatientAccount] T3 ON T2.PatientID = T3.PatientID AND T3.ACCOUNTID = @accountID

Select AccountPatientDisplayID, LastLoginDateTime AS 'Last Login DateTime' from #temp_patients

--BG ENTRY COUNT
SELECT Max(T2.AccountPatientDisplayID) AS AccountPatientDisplayID, count(*) AS 'Sugar Entry Count' FROM ELogBGEntry T1
INNER JOIN #temp_patients T2 ON T1.PatientID = T2.LoginID And T1.BGEntryCreatedDate BETWEEN @BeginDate AND @EndDate
GROUP BY T1.PatientID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 13:58:37

count(*) will never return NULL. can i ask why you need to return count as null?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2010-04-02 : 14:04:53

The above script was prepared by previous developer for a report. Now I was asked to update.

PatientdisplayID7 is in lastlogindatetime where as 7 is not in BG entrycount, that patient7 did't enter any data for BG Entry count. So I need to display patient7 in BG Entry count with NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 14:14:42

perhaps this is what you're asking for

SELECT Max(T2.AccountPatientDisplayID) AS AccountPatientDisplayID, count(*) AS 'Sugar Entry Count' FROM #temp_patients T2 
LEFT JOIN ELogBGEntry T1 ON T1.PatientID = T2.LoginID And T1.BGEntryCreatedDate BETWEEN @BeginDate AND @EndDate
GROUP BY T2.LoginID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources