SQL - Query not showing all results

I have a sql  query that returns daily results based on the current week (Sunday to Saturday), ie:
4/1/05     # of hours
4/2/05     # of hours
4/3/05     # of hours, etc
The problem is, if there are no records for Saturday it doesn't show up at all. I would like it to show up as null. Here is the script:

declare @d datetime
set @date = DATEADD(D, -DATEPART(DW, CAST(CONVERT(VARCHAR(10), '4/16/05', 101) AS DATETIME)) + 1, CAST(CONVERT(VARCHAR(10), '4/16/05', 101) AS DATETIME))
set @d = (DATEADD(D, -DATEPART(DW, CAST(CONVERT(VARCHAR(10), '4/16/05', 101) AS DATETIME)) + 1, CAST(CONVERT(VARCHAR(10), '4/16/05', 101) AS DATETIME)))+ 7
SELECT CAST(CONVERT(VARCHAR(10), ActivityBase.ActualEnd, 101) AS DATETIME) AS Date,
SUM(CAST (ActivityBase.ActualDurationMinutes/60.0 as decimal(10,2))) AS TotalHours  
FROM dbo.SystemUserBase,(dbo.AccountBase INNER JOIN dbo.IncidentBase ON dbo.AccountBase.AccountID = dbo.IncidentBase.AccountID) INNER JOIN dbo.ActivityBase ON dbo.ActivityBase.ObjectID = dbo.IncidentBase.IncidentID  
WHERE ((SystemUserBase.SystemUserID = ActivityBase.OwningUser)
and (ActivityBase.ActualEnd >= @date) and (ActivityBase.ActualEnd <= @d)
and (SystemUserBase.FullName like 'xxxxxx'))
GROUP BY CAST(CONVERT(VARCHAR(10), ActivityBase.ActualEnd, 101) AS DATETIME)
ORDER BY CAST(CONVERT(VARCHAR(10), ActivityBase.ActualEnd, 101) AS DATETIME)
GMJ29Asked:
Who is Participating?
 
rafranciscoConnect With a Mentor Commented:
If there is no data for Saturday, then it will really not show up.

I suggest creating a function that will return a table of the dates you want to process:

CREATE FUNCTION dbo.GetDateTable (@StartDate dATETIME, @EndDate DATETIME)
RETURNS @DateTable TABLE ( Date DATETIME )
AS
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO @DateTable (Date)
VALUES (@StartDate)

@StartDate = DATEADD(dd,1, @StartDate)
END
RETURN
GO

Then use this table in your query.
0
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
You will need to perform an OUTER JOIN with a table of dates.  Francisco suggestion of a tabular function is one way to obtain that table.


SELECT CAST(CONVERT(VARCHAR(10),
     ActivityBase.ActualEnd, 101) AS DATETIME) AS Date,
     SUM(CAST (ActivityBase.ActualDurationMinutes/60.0 as decimal(10,2))) AS TotalHours  
FROM dbo.AccountBase
INNER JOIN dbo.IncidentBase
     ON dbo.AccountBase.AccountID = dbo.IncidentBase.AccountID
INNER JOIN dbo.ActivityBase
     ON dbo.ActivityBase.ObjectID = dbo.IncidentBase.IncidentID
INNER JOIN dbo.SystemUserBase

WHERE ((SystemUserBase.SystemUserID = ActivityBase.OwningUser)
and (ActivityBase.ActualEnd >= @date) and (ActivityBase.ActualEnd <= @d)
and (SystemUserBase.FullName like 'xxxxxx'))
GROUP BY CAST(CONVERT(VARCHAR(10), ActivityBase.ActualEnd, 101) AS DATETIME)
ORDER BY CAST(CONVERT(VARCHAR(10), ActivityBase.ActualEnd, 101) AS DATETIME)
0
 
Brian CroweDatabase AdministratorCommented:
oops...premature entry...hate when that happens
0
 
cyberdevil67Commented:
Hi GMJ29,
 use IsNull(field,0) to make null values default to 0

Cheers!
0
 
rafranciscoCommented:
GMJ29, were you able to solve this problem?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.