Declare @Subquery Table (Id uniqueidentifier,
						 TotalUnits int,
						 ManfName varchar(50),
						 SeriesName varchar(50),
						 UnitName varchar(50),
						 LensQuantity int,
						 PackageQuantity int,
						 DaysPerLens int)

Declare @AnnualSupplyOrders Table (ManfName varchar(50),
			           SeriesName varchar(50),
			           AnnualOrders int)

Declare @TotalOrders Table (ManfName varchar(50),
		             SeriesName varchar(50),
		             TotalOrders int)

Insert Into @Subquery (Id, TotalUnits, ManfName, SeriesName, UnitName, LensQuantity, PackageQuantity, DaysPerLens)
Select Clodorders.ClodOrderId,
           Sum (ClodOrderLineItems.Quantity) * Clodunits.LensQuantity as TotalUnits,
          Clodmanufacturers.name as manfname,
          Clodseries.name as seriesname,
          ClodUnits.Name,
          Clodunits.LensQuantity,
          ClodUnits.PackageQuantity,
          ClodSeries.DaysPerLens
From Clodorders
           join ClodOrderLineItems on ClodOrders.ClodOrderId = ClodOrderLineItems.ClodOrderId
           join ClodProducts on ClodOrderLineItems.ProductId = ClodProducts.ProductId
           join ClodProductFamilies on ClodProducts.ProductFamilyId = ClodProductFamilies.ProductFamilyId
           join ClodSeries on ClodSeries.SeriesId = ClodProductFamilies.SeriesId
           join ClodManufacturers on ClodManufacturers.ManufacturerId = ClodSeries.ManufacturerId
           join ClodUnitAvailability on ClodProductFamilies.ProductFamilyId = ClodUnitAvailability.ProductFamilyId
           join ClodUnits on ClodUnits.UnitId = ClodUnitAvailability.UnitId
Where ClodOrders.AccountId = @AccountId
and ClodProductFamilies.Trial = 0
and ClodOrders.LastUpdated between @StartDate and DATEADD(dd, 1, @EndDate)
          --and ClodUnits.LensQuantity >= 6
Group by Clodorders.ClodOrderId,
                 ClodOrderLineItems.Quantity,
                 Clodmanufacturers.name ,
                 Clodseries.name,
                 ClodUnits.Name,
                 Clodunits.LensQuantity,
                 ClodUnits.PackageQuantity,
                 ClodSeries.DaysPerLens

Insert Into @TotalOrders (ManfName,SeriesName,TotalOrders )
Select B.ManfName, B.SeriesName, count(*) as Orders
From @Subquery As B
Group by B.ManfName, B.SeriesName

Insert Into @AnnualSupplyOrders (ManfName,SeriesName, AnnualOrders )
Select A.ManfName, A.SeriesName, count(*) as Orders
From @Subquery As A
Where A.TotalUnits  >= (
		         Case
		         When A.DaysPerLens = 1 Then 720
		         When A.DaysPerLens = 7 Then 48
		         When A.DaysPerLens = 30 Then 24
		         When A.DaysPerLens = 90 Then 8
		         When A.DaysPerLens = 180 Then 4
		         When A.DaysPerLens = 365 Then 2
		         End
		      )
Group by A.ManfName, A.SeriesName


Select * from @TotalOrders As T
Where T.SeriesName in (Select Distinct SeriesName From @AnnualSupplyOrders)
End