ahmed salah

ahmed salah

  • 1.6k
  • 530
  • 121.6k

How to get total cost per hotel and transfer per 8 days?

Jul 22 2017 3:01 PM
How to get total cost per hotel and transfer per 8 days ?
 
Details
 
8 days = 7 nights
 
meaning 8 days = 7 days accommodation per hotel
 
because last day he take flight not stay in hotel .
 
Result i need to get it
 
 
why null display in fiddle i need result in one line as above
 
the database and query found in the following fiddle
 
http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=0096a903948a93c1269e931328648be2
this sq query used
  1. ;with cte_HotelPrice  
  2. as  
  3. (  
  4. select   
  5. T6.HotelPrice,  
  6. T4.HotelID,  
  7. T5.HotelName,  
  8. T3.DetailsDurationID from package T   
  9. inner join StartPackage T1 on T.PackageId=T1.PackageId  
  10. inner join packageduration T2 on T.PackageId=T2.PackageId  
  11. inner join (SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY PackageDurationsId ORDER BY Days)   
  12.     FROM DurationDetails) T3 on T2.PackageDurationsId=T3.PackageDurationsID  
  13. inner join DayDetails T4 on T3.DetailsDurationID=T4.DetailsDurationID  
  14. left join Hotel T5 on T4.HotelID=T5.HotelID  
  15. cross apply (select HotelPrice from HotelPrice where HotelID=T4.HotelID and FromDate<=DATEADD(day, T3.RN - 1, T1.StartDate) and ToDate>=DATEADD(day, T3.RN - 1, T1.StartDate)) T6  
  16. )  
  17. ,TransferPrice as  
  18. (  
  19. select   
  20. ttd.Price,  
  21. dds.DetailsDurationID  
  22. from package p   
  23. inner join StartPackage s on p.PackageId=s.PackageId  
  24. inner join packageduration pd on p.PackageId=pd.PackageId  
  25. inner join (SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY PackageDurationsId ORDER BY Days)   
  26.     FROM DurationDetails) dd on pd.PackageDurationsId=dd.PackageDurationsID  
  27. inner join DayDetails dds on dd.DetailsDurationID=dds.DetailsDurationID  
  28. left join TransferType tt on dds.TransferTypeID=tt.TransferID  
  29. cross apply (select Price from TransferPeriod where TransferTypeID=dds.TransferTypeID and FromDate<=DATEADD(day, dd.RN - 1, s.StartDate) and Todate>=DATEADD(day, dd.RN - 1, s.StartDate)) ttd  
  30. )  
  31. select   
  32. S4.HotelID,S4.HotelName, S.PackageName, S1.StartDate, S1.EndDate,   
  33. sum(S4.HotelPrice) AS cost,  
  34. sum(S5.Price) as transfercost   
  35. from package S   
  36. inner join StartPackage S1 on S.PackageId=S1.PackageId  
  37. inner join packageduration S2 on S.PackageId=S2.PackageId  
  38. inner join DurationDetails S3 on S2.PackageDurationsId=S3.PackageDurationsID  
  39. left join  cte_HotelPrice S4 on S3.DetailsDurationID=S4.DetailsDurationID  
  40. left join  TransferPrice S5 on S3.DetailsDurationID=S5.DetailsDurationID  
  41. GROUP BY S4.HotelID, S4.HotelName,S.PackageName, S1.StartDate, S1.EndDate

Answers (1)