Rank a Place Visited By a Customer- Microsoft SQL Server – T-Transact
This is a short guide to getting ranking or rating from Microsoft Sql Server customers. Ranking is taken by checking how many times a customer has visited a particular country divided by the total number of travels the customer has made within the database
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
/*make a back up of the recommender rankings table*/ truncate table antrak.dbo.recommender_rankings_copy insert into antrak.dbo.recommender_rankings_copy select * from antrak.dbo.recommender_rankings /*select total number of times a customer has travelled to a specific country*/ select [First Name] ,Country , count(*) as [# times visited] into #travelpercountry FROM [Antrak].[dbo].[FlightBookingsMain] group by [First Name] ,Country order by [First Name] /*total number of times each customer has travelled to all destinations*/ select [First Name] , count(*) as [# times visited] into #totaltravel FROM [Antrak].[dbo].[FlightBookingsMain] group by [First Name] ----,Country order by [First Name] -- lets view by the total bookings select * from #totaltravel order by [First Name] -- lets view bookings per country select * from #travelpercountry order by [First Name] /*get rating*/ drop table antrak.dbo.recommender_rankings select tt.[First Name] ,Country ,cast(round((cast(tpc.[# times visited] as decimal(3,0)) / cast(tt.[# times visited] as decimal(3,0)) * 100), 1) as float) as rating into antrak.dbo.recommender_rankings from #totaltravel as tt inner join #travelpercountry as tpc on tt.[First Name] = tpc.[First Name] order by [First Name] select * from antrak.dbo.recommender_rankings /*lets refresh the temporary tables*/ drop table #totaltravel drop table #travelpercountry |