How to show results without any rows? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to show results without any rows?

Author

Topic

Roxxor
Starting Member

7 Posts

Posted - 2008-09-15 : 11:02:37

Hi!

I have a table called bookings and I would like to create a view of the bookings for each day of the last week of a month. The days with no bookings should also be presented as "No bookings".

I have tried myself but I cannot get the days with no bookings to be showed up and I have really tried everything I know.

ALTER VIEW list_bookings
AS
SELECT  TOP (100) PERCENT booking_time AS [Date], 
(SELECT CASE WHEN COUNT(booking_id) = 0 THEN 'No bookings' 
  ELSE COUNT(booking_id) END AS Expr1
FROM         bookings) AS [Number of bookings]
FROM bookings
WHERE     (booking_time>= '2008-08-24') AND (booking_time<= '2008-08-31')
GROUP BY booking_time
ORDER BY [Date]

The ouput of the above statement are just the days with any bookings and the days without bookings aren´t showed. How can I make them to be showed up?

The booking table have these columns:
booking_id
service_id
booking_time

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-15 : 11:07:32

[code]ALTER VIEW list_bookings
AS

;WITH Yak (theDate)
AS (
SELECT CAST('20080824' AS DATETIME)

UNION ALL

SELECT DATEADD(DAY, 1, theDate)
FROM Yak
WHERE theDate < '20080831'
)

SELECT Yak.theDate AS [Date],
CASE COUNT(b.booking_id)
WHEN 0 THEN 'No bookings'
ELSE CAST(COUNT(b.booking_id) AS VARCHAR(11))
END AS Expr1
FROM Yak AS y
LEFT JOIN bookings AS b ON DATEDIFF(DAY, 0, booking_time) = y.theDate
GROUP BY y.theDate[/code]

E 12°55'05.63"
N 56°04'39.26"

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-15 : 11:31:16

I saw peso already posted a similiar solution, but I figured I'd post this method as well.


declare @startdate datetime,@EndDate datetime
set @startdate = '08/24/2008'
set @enddate = '8/31/2008'
;with t1 (d)
		as
		( 
		--SELECT max(a.BillStartDate),a.BillingCycleID,a.BenefitTypeID
		SELECT @Startdate
		Union all
		SELECT dateadd(day,1,d)
		From t1 a1 
		where d < @Enddate
		)
SElect
a.d AS [Date], 
count(*) as Bookings
FROM 
	t1 a
left join
	bookings b
on a.d = b.BookingTime
GROUP BY a.d
ORDER BY a.d

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Roxxor
Starting Member

7 Posts

Posted - 2008-09-15 : 12:14:42

Vinny 881:
When I run your code I get the booking dates in the column "Number of bookings". I would like to have the number of dates there and for those without any bookings it should print "No bookings" or something like that.

Peso:
When I try to run your code I get "Incorrect syntax near ';'." on line 3. What´s wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-15 : 13:37:47

Set Compatibility Level to 90 or more for your database.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 13:40:14

quote:
Originally posted by Peso

Set Compatibility Level to 90 or more for your database.

E 12°55'05.63"
N 56°04'39.26"

i dont think that was problem with OP as he commented Vinnie's soln worked for him which still uses a CTE.

Roxxor
Starting Member

7 Posts

Posted - 2008-09-15 : 13:42:00

quote:
Originally posted by Peso

Set Compatibility Level to 90 or more for your database.

E 12°55'05.63"
N 56°04'39.26"

Then I got

"Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "Yak.theDate" could not be bound."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 13:48:53

quote:
Originally posted by Roxxor

quote:
Originally posted by Peso

Set Compatibility Level to 90 or more for your database.

E 12°55'05.63"
N 56°04'39.26"

Then I got

"Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "Yak.theDate" could not be bound."

ALTER VIEW list_bookings
AS

;WITH Yak (theDate)
AS (
	SELECT CAST('20080824' AS DATETIME)
	
	UNION ALL
	
	SELECT	DATEADD(DAY, 1, theDate)
	FROM	Yak
	WHERE	theDate < '20080831'
)

SELECT		Yak y.theDate AS [Date],
		CASE COUNT(b.booking_id)
			WHEN 0 THEN 'No bookings' 
			ELSE CAST(COUNT(b.booking_id) AS VARCHAR(11))
		END AS Expr1
FROM		Yak AS y
LEFT JOIN	bookings AS b ON DATEDIFF(DAY, 0, booking_time) = y.theDate
GROUP BY	y.theDate

Roxxor
Starting Member

7 Posts

Posted - 2008-09-15 : 13:58:28

visakh16: Thanks! Just a problem, it works when I run it as a sql question, but when I try to alter the view I still get
"Incorrect syntax near ';'." at line 3 which is the
;WITH Yak (theDate).

What does it mean?

Roxxor
Starting Member

7 Posts

Posted - 2008-09-15 : 14:00:17

It seemed to work if I removed the ;. Strange? Or not?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 14:09:31

quote:
Originally posted by Roxxor

It seemed to work if I removed the ;. Strange? Or not?

the ; is reqd only if CTE is not in beginning of batch. so you may remove it

Roxxor
Starting Member

7 Posts

Posted - 2008-09-15 : 14:11:20

Thanks!

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-15 : 16:34:43

Also there was a typo in my post.


count(*) as Bookings

should be


count(b.Booking_ID)

It should show you a 0 when does not exist, but the count of the dates when it does. you can use a case statment to change the text to whatever.

i.e


declare @startdate datetime,@EndDate datetime
set @startdate = '08/24/2008'
set @enddate = '8/31/2008'
;with t1 (d)
		as
		( 
		--SELECT max(a.BillStartDate),a.BillingCycleID,a.BenefitTypeID
		SELECT @Startdate
		Union all
		SELECT dateadd(day,1,d)
		From t1 a1 
		where d < @Enddate
		)
SElect
a.d AS [Date], 
case 
when count(b.Booking_ID) = 0 then 'No Bookings'
else convert(varchar(5),count(b.Booking_ID))
end  as Bookings
FROM 
	t1 a
left join
	bookings b
on a.d = b.BookingTime
GROUP BY a.d
ORDER BY a.d

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-09-16 : 03:25:05

Hi Peso,

iam able to understand recursive CTE but i cant figure out this
DATEDIFF(DAY, 0, booking_time) = y.theDate. what does this actually mean? pls

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 03:31:20

quote:
Originally posted by dineshrajan_it

Hi Peso,

iam able to understand recursive CTE but i cant figure out this
DATEDIFF(DAY, 0, booking_time) = y.theDate. what does this actually mean? pls

you've only date value available inside CTE but your actual table column may contain time part.DATEDIFF(DAY, 0, booking_time) strips time part off your column value and then you compare it with your datevalue. if you dont strip time part it will never be equal.

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-09-16 : 03:46:43

Thanks Vishak,

now i got the answer.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 04:04:37

quote:
Originally posted by dineshrajan_it

Thanks Vishak,

now i got the answer.

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources