Sometimes you want to get some numbers and also percentages for quick analysis. This is a quick simple way to get quick overviews
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /****** Script for SelectTopNRows command from SSMS ******/ | |
| SELECT count(*) as [# Bookings], | |
| ROUND((cast(Count([Flight Booking ID])* 100 as FLOAT) / (Select Count(*) From [Antrak].[dbo].[FlightBookingsMain] where isCancelled <> 'false')), 2) as [% Booking], | |
| datename(dw, [Booking Date]) as [Most Popular Day] | |
| FROM [Antrak].[dbo].[FlightBookingsMain] | |
| where isCancelled <> 'false' | |
| group by datename(dw, [Booking Date]) | |
| order by [# Bookings] desc | |
| /** get number of bookings per week day **/ | |
| SELECT count(*) as [# Bookings], datename(dw, [Booking Date]) as [Most Popular Day] | |
| into #bookingbyday | |
| FROM [Antrak].[dbo].[FlightBookingsMain] | |
| where isCancelled <> 'false' | |
| group by datename(dw, [Booking Date]) | |
| order by 2 desc | |
| /** QUALITY CHECKS **/ | |
| -- check counts | |
| declare @mon int | |
| declare @tues int | |
| declare @wed int | |
| declare @thurs int | |
| declare @fri int | |
| declare @sat int | |
| declare @sun int | |
| set @mon = (select [# Bookings] from #bookingbyday where [Most Popular Day] = 'Monday'); | |
| set @tues = (select [# Bookings] from #bookingbyday where [Most Popular Day] = 'Tuesday'); | |
| set @wed = (select [# Bookings] from #bookingbyday where [Most Popular Day] = 'Wednesday'); | |
| set @thurs = (select [# Bookings] from #bookingbyday where [Most Popular Day] = 'Thursday'); | |
| set @fri = (select [# Bookings] from #bookingbyday where [Most Popular Day] = 'Friday'); | |
| set @sat = (select [# Bookings] from #bookingbyday where [Most Popular Day] = 'Saturday'); | |
| set @sun = (select [# Bookings] from #bookingbyday where [Most Popular Day] = 'Sunday'); | |
| /** get total of the bookings **/ | |
| SELECT count(*) totalBookings | |
| FROM [Antrak].[dbo].[FlightBookingsMain] | |
| where isCancelled <> 'false' | |
| select (@mon + @tues + @wed + @thurs + @fri + @sat + @sun ) as [sumofBookingsInDays], count(*)as totalBookings FROM [Antrak].[dbo].[FlightBookingsMain] | |
| where isCancelled <> 'false' | |
| /** QUALITY CHECKS **/ | |
| -- Check that all percentages add up to 100% | |
| SELECT | |
| ROUND((cast(Count([Flight Booking ID])* 100 as FLOAT) / (Select Count(*) From [Antrak].[dbo].[FlightBookingsMain] where isCancelled <> 'false')), 2) as PercentBooking | |
| into #PercentBookingttoal | |
| FROM [Antrak].[dbo].[FlightBookingsMain] | |
| where isCancelled <> 'false' | |
| select sum(PercentBooking) as ttlPercentage from #PercentBookingttoal | |
| --SELECT DATEADD(dd, 7-(DATEPART(dw, [Booking Date])), [Booking Date]) [WeekEnd] FROM [Antrak].[dbo].[FlightBookingsMain] | |
| /*refresh temporay tables */ | |
| drop table #bookingbyday | |
| drop table #PercentBookingttoal |
by People Who Read The Above Post Also Read This:
Populating Superset Caravel MySQL database tables with Data via Heroku
Rank a Place Visited By a Customer- Microsoft SQL Server – T-Transact
Splitting Values in one Column to Multiple Columns using CASE Expression Statement SQL Server
Superset Heroku Installation – Microsoft SQL Server Database Connection on Windows PC
