[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL - Query not showing all results

Posted on 2005-04-18
8
Medium Priority
?
233 Views
Last Modified: 2010-03-19
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)
0
Comment
Question by:GMJ29
  • 2
  • 2
5 Comments
 
LVL 28

Accepted Solution

by:
rafrancisco earned 1000 total points
ID: 13807359
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
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 1000 total points
ID: 13807463
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
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13807484
oops...premature entry...hate when that happens
0
 
LVL 9

Expert Comment

by:cyberdevil67
ID: 13808237
Hi GMJ29,
 use IsNull(field,0) to make null values default to 0

Cheers!
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 14030324
GMJ29, were you able to solve this problem?
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question