0

I have 2 lists, one is a list of master list assets, and the second is a list of bookings for those assets on discrete dates.

a simplified version of the asset table is shown below. this has a list of all asset id's and the type/model of asset they are

AssetId AssetType
1 model1
2 model1
3 model2
4 model1
5 model3

and the bookings table is like this

Date AssetId
01/01/22 1
01/01/22 5
03/01/22 3
03/01/22 4
03/01/22 2

and I want to produce an output that shows count of all assets grouped by their model type in use every day in specific like this

Date Type Count
01/01/22 Model1 1
01/01/22 Model2 0
01/01/22 Model3 1
02/01/22 Model1 0
02/01/22 Model2 0
02/01/22 Model3 0
03/01/22 Model1 2
03/01/22 Model2 1
03/01/22 Model3 0

I can easily generate an SQL query to produce a list that shows me which models are in use on specific day but not include the ones that aren't being used or entries for dates when no assets are in use.

augeri
  • 1
  • 1
    You need to outer-join to a distinct list of models and dates, search Google for "calendar Table" – Stu Jul 13 '22 at 11:12
  • Does it answer your question https://stackoverflow.com/questions/54825778/how-to-get-missing-dates-with-0-value-in-sql-server ? – Serg Jul 13 '22 at 11:13
  • I have tried a calendar table but cannot get the joins correct. this is the code I am running – augeri Jul 13 '22 at 13:06
  • SELECT CalendarDate as bookingdate, r.ResModel, count(r.ResModel) as Count FROM [AssetMaster].dbo.[Calendar] c left join [AssetMaster].dbo.[uk-assets-new] a on c.CalendarDate = convert(datetime, a.ResDate, 103) right join [Resource-Master] r on a.ResCode = r.ResCODE where r.ResType = 'Camera' group by c.calendardate, r.ResModel – augeri Jul 13 '22 at 13:07

0 Answers0