Dairy Project Part 3.Questions Answers

 -- Write SQL Query For 
1) Month wise Collection(Amount) for Year 2021 
2) Year 2021 Quantity by Milk Type
3) Detail Report - for Date 2 Feb 2021
FirstName LastName Quantity FATValue SNFValue Rate Amount MilkTime MilkType 
4) 2021 year data of customer code =5
5) Retrieve top 3 customer by Quantity-wise (cow)

--select * from Dailymilkcollection
--Ans 1) Month wise Collection(Amount) for Year 2021

select DateName(Month,Date_time) As Month_,Sum(Amount) As Amount from Dailymilkcollection 
where Year(Date_time)=2021 
Group By Datepart(Month,Date_time),DateName(Month,Date_time)
Order by Datepart(Month,Date_time)

--Ans 2)  Year 2021 Quantity by Milk Type

select M.Description As MilkType, Sum(D.Quantity) As QuantityInLtr from Dailymilkcollection D
Inner Join MilkTypemaster M
On D.MilkTypeId=M.id
where Year(Date_time)=2021 
Group by M.Description

--3) Detail Report - for Date 2 Feb 2021

--FirstName LastName FATValue SNFValue Quantity Rate Amount MilkTime MilkType 

SELECT R.FirstName, R.LastName, D.FATValue, D.SNFValue, D.Quantity, D.Rate, D.Amount, tp.Description AS MilkType, tm.Description AS MilkTime, D.Date, D.CustomerID
FROM     DailyMilkCollection AS D INNER JOIN
                  Registration AS R ON D.CustomerID = R.id INNER JOIN
                  MilkTypeMaster AS tp ON D.MilkTypeID = tp.Id INNER JOIN
                  MilkTimeMaster AS tm ON D.MilkTimeID = tm.Id
WHERE  (D.Date_Time = '2021-02-02')

4) 2021 year data of customer code =5
select * from Dailymilkcollection where CustomerID=5
SELECT R.FirstName, R.LastName, D.FATValue, D.SNFValue, D.Quantity, D.Rate, D.Amount, tp.Description AS MilkType, tm.Description AS MilkTime, D.Date_time, D.CustomerID
FROM     DailyMilkCollection AS D INNER JOIN
                  Registration AS R ON D.CustomerID = R.id INNER JOIN
                  MilkTypeMaster AS tp ON D.MilkTypeID = tp.Id INNER JOIN
                  MilkTimeMaster AS tm ON D.MilkTimeID = tm.Id
where CustomerID=5

5) Retrieve top 3 customer by Quantity-wise (cow)

SELECT TOP (3)  Registration.FirstName, Registration.LastName,SUM(D.Quantity) AS Quantity
FROM     DailyMilkCollection AS D INNER JOIN
                  MilkTypeMaster AS tp ON D.MilkTypeID = tp.Id INNER JOIN
                  Registration ON D.CustomerID = Registration.id
WHERE  (tp.Description = 'cow')
GROUP BY Registration.FirstName, Registration.LastName
ORDER BY Quantity DESC