Select Generate daily report, monthly Report, Annual report

Incoming parameters:

@Type: Type, what kind of report, there are three kinds of year,month,day

@Time: Time

Gets the month data of time, respectively, according to the type parameter, date data

declare @Type nvarchar ( -) ='Month'; DECLARE @Time DateTime=getdate (); SELECT distinct case @Type='Quarter'Then CONCAT (DATEPART (Qq,dateadd (Qq,number, @Time)),'Quarterly') when @Type='Month'Then FORMAT (DATEADD (Mm,number, @Time),'mm Month') ELSE FORMAT (DATEADD (Dd,number, @Time),'DD Day') END DT from master. Spt_values WHERE Type='P' 

Find this first, and then the table in the database left join

declare @Type nvarchar ( -) ='Month'; DECLARE @Time DateTime=getdate (); Select         Case@Type when' Year'Then format (Createtime,'mm Month') when'Month'Then format (Createtime,'DD Day')        ElseFormat (Createtime,'DD Day') End Dt2,sum (IsNull (Electricallaborhour,0)+IsNull (Electricalparts,0)+IsNull (Sheetspraylaborhour,0)+IsNull (Sheetsprayparts,0)+IsNull (Sheetspraytranslaborhour,0)+IsNull (Oilchangelaborhour,0)+IsNull (Oilchangeparts,0)+IsNull (Warrantylaborhour,0)+IsNull (Warrantyparts,0)+IsNull (Warrantytranslaborhour,0)+IsNull (Internalelectricallaborhour,0)+IsNull (Internalparts,0)+IsNull (Internalsheetspraylaborhour,0)) asTotal fromT_dmsmaintenancewhereIsdelete=0and ((@Type=' Year'and datepart (yyyy,createtime) =datepart (yyyy, @Time)) or (@Type='Month'and Format (Createtime,'yyyy year mm month') =format (@Time,'yyyy year mm month') or (@Type=' Day'and Format (Createtime,'YYYY-MM-DD') =format (@Time,'YYYY-MM-DD'))) group by Case@Type when' Year'Then format (Createtime,'mm Month') when'Month'Then format (Createtime,'DD Day')        ElseFormat (Createtime,'DD Day') End

Look at the above query criteria

Or,and combined and grouped according to specific type parameters

Then connect the two tables

declare @Type nvarchar ( -) ='Month'; DECLARE @Time DateTime=getdate (); SelectDt,total from(SELECT distinct Case@Type when' Year'Then Format (DATEADD (Mm,number, @Time),'mm Month') when'Month'Then Format (DATEADD (Dd,number, @Time),'DD Day')        ElseFormat (DATEADD (Dd,number, @Time),'DD Day') end DT from master. Spt_values WHERE Type='P') asT1 left JOIN (Select         Case@Type when' Year'Then format (Createtime,'mm Month') when'Month'Then format (Createtime,'DD Day')        ElseFormat (Createtime,'DD Day') End Dt2,sum (IsNull (Electricallaborhour,0)+IsNull (Electricalparts,0)+IsNull (Sheetspraylaborhour,0)+IsNull (Sheetsprayparts,0)+IsNull (Sheetspraytranslaborhour,0)+IsNull (Oilchangelaborhour,0)+IsNull (Oilchangeparts,0)+IsNull (Warrantylaborhour,0)+IsNull (Warrantyparts,0)+IsNull (Warrantytranslaborhour,0)+IsNull (Internalelectricallaborhour,0)+IsNull (Internalparts,0)+IsNull (Internalsheetspraylaborhour,0)) asTotal fromT_dmsmaintenancewhereIsdelete=0and ((@Type=' Year'and datepart (yyyy,createtime) =datepart (yyyy, @Time)) or (@Type='Month'and Format (Createtime,'yyyy year mm month') =format (@Time,'yyyy year mm month') or (@Type=' Day'and Format (Createtime,'YYYY-MM-DD') =format (@Time,'YYYY-MM-DD'))) group by Case@Type when' Year'Then format (Createtime,'mm Month') when'Month'Then format (Createtime,'DD Day')        ElseFormat (Createtime,'DD Day') end) asT2 on T1. Dt=t2. DT2

Select Generate daily report, monthly Report, Annual report