SQL shares _mssql with daily, weekly, monthly, and yearly statistical data

--By day
Select sum (Consume), day ([date]) from Consume_record where year ([date]) = ' 2006 ' GROUP By day ([date])

--By week quarter
Select sum (Consume), Datename (Week,[date]) from Consume_record where year ([date]) = ' 2006 ' GROUP by Datename (Week,[date])

--By month
Select sum (Consume), month ([Date]) from Consume_record where year ([date]) = ' 2006 ' GROUP by month ([date])

--by season
Select sum (Consume), Datename (Quarter,[date]) from Consume_record where year ([date]) = ' 2006 ' GROUP by Datename (quarter,[ Date])

--by year
Select sum (Consume), year ([date]) from Consume_record where group by year ([date])

Date_format

 select Date_format (create_time, '%y%u ') weeks,count (Caseid) count from Tc_case GROUP by We 
Eks 
Select Date_format (create_time, '%y%m%d ') days,count (Caseid) count from Tc_case Group by days; Select Date_format (create_time, '%y%m ') months,count (Caseid) count from Tc_case group by months; 

Date_format (Date,format)
Formats the date value based on the format string. The following modifiers can be used in the format string:
%m month name (January ...) December)
%w Week name (Sunday ...) Saturday)
%d The date of the month with English prefix (1st, 2nd, 3rd, and so on). ）
%Y years, numbers, 4-bit
%y years, numbers, 2-bit
%a name of the week (Sun ...) Sat)
Days in%d months, numbers (00 ...). 31)
%e the number of days in the month, numbers (0 ...). 31)
%m Month, number (01 ...) 12)
%c month, number (1 ...). 12)
%b abbreviated month name (...) DEC)
%j the number of days in a year (001 ...). 366)
%H hours (00 .....) 23)
%k hours (0 .....) 23)
%h hours (01 .....) 12)
%I Hours (01 .....) 12)
%l hours (1 .....) 12)
%i minutes, Number (00 .....) 59)
%r time, 12 hours (Hh:mm:ss [ap]m)
%T time, 24 hours (HH:MM:SS)
%s seconds (00 ...). 59)
%s seconds (00 ...). 59)
%p am or PM
%w the number of days in one weeks (0=sunday ...). 6=saturday)
%u weeks (0 .....) 52), here Sunday is the first day of the week
%u weeks (0 .....) 52), here Monday is the first day of the week
Percent% of a text "%".

This article is just an SQL statement that records the statistics used in the project, in case you forget

 <summary>///Get statistics///</summary>///<param name= "Ckey" > Store ckey</param>// /<param name= "type" > Statistic Type (day, week, month, year) </param>///<returns></returns> [WebMethod (True)] P
      
      Ublic static string GetData3 (string ckey, String type) {StringBuilder strSQL = new StringBuilder ();
        #region SQL statement if (type = = "0") {#region Day Strsql.appendformat ("with Weekdate");
        Strsql.appendformat ("as" (select DATEADD (D,-day (GETDATE ()) + 1, GETDATE ()) as Riqi ");
        Strsql.appendformat ("UNION all");
        Strsql.appendformat ("Select Riqi + 1 from weekdate");
        Strsql.appendformat ("WHERE Riqi + 1 <=" (select DATEADD (D,-day (GETDATE ()), DATEADD (M, 1, GETDATE ())));
        Strsql.appendformat (")");
  Strsql.appendformat ("Select Convert" (char (8), A.riqi, 112) as th, Day (CONVERT (char (8), A.riqi, 112)) as DDay, ");      Strsql.appendformat ("ISNULL (TbB. Daily turnover, 0) as daily turnover,");
        Strsql.appendformat ("Case When CONVERT" (char (8), A.riqi, 112) > CONVERT (char (8), GETDATE (), 112) ");
        Strsql.appendformat ("THEN NULL");
        Strsql.appendformat ("When CONVERT" (char (8), A.riqi, 112) <= CONVERT (char (8), GETDATE (), 112) ");
        Strsql.appendformat ("THEN ISNULL (volume, 0)");
        Strsql.appendformat ("End as day turnover quantity,");
        Strsql.appendformat ("TbB. Daily amount,");
        Strsql.appendformat ("Case When CONVERT" (char (8), A.riqi, 112) > CONVERT (char (8), GETDATE (), 112) ");
        Strsql.appendformat ("THEN NULL");
        Strsql.appendformat ("When CONVERT" (char (8), A.riqi, 112) <= CONVERT (char (8), GETDATE (), 112) ");
        Strsql.appendformat ("THEN ISNULL (TbB, 0)");
        Strsql.appendformat ("End as day paid-in Amount 2");
        Strsql.appendformat ("from Weekdate a"); Strsql.appendforMat ("Left JOIN" (select COUNT (1)); Strsql.appendformat ("from dbo.")
        Customerbase base ");
        Strsql.appendformat ("WHERE ckey = ' {0} '", Ckey); Strsql.appendformat ("and" + Impomo.)
        Totalconsumptionmon + "> 0"); Strsql.appendformat ("and targetdate = cus.")
        TargetDate ");
        Strsql.appendformat (") Daily turnover,");
        Strsql.appendformat ("ISNULL (select SUM)"; Strsql.appendformat ("From" (CONVERT (FLOAT, ISNULL (pc1 + PC2 + PC3 + pc4 + pc5), 0))
        S Total ");
        Strsql.appendformat ("From paymentcontent as pay"); Strsql.appendformat ("WHERE paydate = cus.")
        TargetDate "); Strsql.appendformat ("and pay.")
        Ckey = ' {0} ', ckey); Strsql.appendformat ("UNION All ");
        Strsql.appendformat ("Select SUM (CONVERT (FLOAT, ISNULL (Recmoney, 0)) as Total"); Strsql.appendformat ("from dbo.")
        CardRecharge8 as recharge "); Strsql.appendformat ("WHERE rechargdate = cus.")
        TargetDate "); Strsql.appendformat ("and recharge.")
        Ckey = ' {0} ', ckey);
        Strsql.appendformat ("UNION all"); Strsql.appendformat ("Select SUM" (ISNULL (PC1 + PC2 + PC3 + pc4 + pc5), 0)) as T
        Otal "); Strsql.appendformat ("from dbo.")
        Paymentswimming as Payswim "); Strsql.appendformat ("WHERE paydate = cus.")
        TargetDate "); Strsql.appendformat ("and Payswim.")
        Ckey = ' {0} ', ckey);
        Strsql.appendformat ("UNION all");     Strsql.appendformat ("                    SELECT SUM (CONVERT (FLOAT, ISNULL ((WP1 + wp2 + wp3 + WP4 + wp5), 0)) as total);
        Strsql.appendformat ("From Warepaymentcontent as Ware"); Strsql.appendformat ("WHERE paydate = cus.")
        TargetDate "); Strsql.appendformat ("and Ware.")
        Ckey = ' {0} ', ckey);
        Strsql.appendformat (") B");
        Strsql.appendformat ("), 0) as-day paid-in amount,");
        Strsql.appendformat ("TargetDate Day"); Strsql.appendformat ("from dbo.")
        Customerbase cus ");
        Strsql.appendformat ("WHERE year (targetdate) = year (GETDATE ())");
        Strsql.appendformat ("and MONTH (targetdate) = MONTH (GETDATE ())");
        Strsql.appendformat ("GROUP by TargetDate");
        Strsql.appendformat (") as TbB on CONVERT (CHAR (8), A.riqi, 112) = TbB. Day");
  #endregion    else if (type = = "1") {#region week Strsql.appendformat ("with Weekdate");
        Strsql.appendformat ("as" (select DATEADD (wk, DATEDIFF (wk, 0, GETDATE ()), 0) as Riqi);
        Strsql.appendformat ("UNION all");
        Strsql.appendformat ("Select Riqi + 1 from weekdate");
        Strsql.appendformat ("WHERE Riqi + 1 <=" (Select DATEADD (wk, DATEDIFF (wk, 0, GETDATE ()), 6));
        Strsql.appendformat (")");
        Strsql.appendformat ("Select CONVERT (CHAR (8), A.riqi, 112) as day,");
        Strsql.appendformat ("Datename (Weekday,convert (CHAR (8), A.riqi, 112)) DDay,");
        Strsql.appendformat ("ISNULL (TbB. Daily turnover, 0) as daily turnover,");
        Strsql.appendformat ("Case When CONVERT" (char (8), A.riqi, 112) > CONVERT (char (8), GETDATE (), 112) ");
        Strsql.appendformat ("THEN NULL"); Strsql.appendformat ("When CONVERT" (CHAR (8), A.riqi, 112) <= CONVERT (CHAR (8), GETDATE (), 112) ");
        Strsql.appendformat ("THEN ISNULL (volume, 0)");
        Strsql.appendformat ("End as day turnover quantity,");
        Strsql.appendformat ("TbB. Daily amount,");
        Strsql.appendformat ("Case When CONVERT" (char (8), A.riqi, 112) > CONVERT (char (8), GETDATE (), 112) ");
        Strsql.appendformat ("THEN NULL");
        Strsql.appendformat ("When CONVERT" (char (8), A.riqi, 112) <= CONVERT (char (8), GETDATE (), 112) ");
        Strsql.appendformat ("THEN ISNULL (TbB, 0)");
        Strsql.appendformat ("End as day paid-in Amount 2");
        Strsql.appendformat ("from Weekdate a");
        Strsql.appendformat ("Left JOIN" (select COUNT (1)); Strsql.appendformat ("from dbo.")
        Customerbase base ");
        Strsql.appendformat ("WHERE ckey = ' {0} '", Ckey); Strsql.appendformat ("and" + Impomo.) TotalconsumpTionmon + "> 0"); Strsql.appendformat ("and targetdate = cus.")
        TargetDate ");
        Strsql.appendformat (") Daily turnover,");
        Strsql.appendformat ("ISNULL (select SUM)";  Strsql.appendformat ("From" (CONVERT (FLOAT, ISNULL (pc1 + PC2 + PC3 + pc4 + pc5), 0))
        As Total ");
        Strsql.appendformat ("From paymentcontent as pay"); Strsql.appendformat ("WHERE paydate = cus.")
        TargetDate "); Strsql.appendformat ("and pay.")
        Ckey = ' {0} ', ckey);
        Strsql.appendformat ("UNION all");
        Strsql.appendformat ("Select SUM (CONVERT (FLOAT, ISNULL (Recmoney, 0)) as Total"); Strsql.appendformat ("from dbo.")
        CardRecharge8 as recharge ");                   Strsql.appendformat ("        WHERE rechargdate = cus.
        TargetDate "); Strsql.appendformat ("and recharge.")
        Ckey = ' {0} ', ckey);
        Strsql.appendformat ("UNION all");  Strsql.appendformat ("Select SUM" (CONVERT (FLOAT, ISNULL (pc1 + PC2 + PC3 + pc4 + pc5), 0)) as
        Total "); Strsql.appendformat ("from dbo.")
        Paymentswimming as Payswim "); Strsql.appendformat ("WHERE paydate = cus.")
        TargetDate "); Strsql.appendformat ("and Payswim.")
        Ckey = ' {0} ', ckey);
        Strsql.appendformat ("UNION all");  Strsql.appendformat ("Select SUM" (CONVERT (FLOAT, ISNULL (wp1 + wp2 + wp3 + WP4 + wp5), 0)) as
        Total ");
        Strsql.appendformat ("From Warepaymentcontent as Ware");                        Strsql.appendformat ("   WHERE paydate = cus.
        TargetDate "); Strsql.appendformat ("and Ware.")
        Ckey = ' {0} ', ckey);
        Strsql.appendformat (") B");
        Strsql.appendformat ("), 0) as-day paid-in amount,");
        Strsql.appendformat ("TargetDate Day"); Strsql.appendformat ("from dbo.")
        Customerbase cus ");
        Strsql.appendformat ("WHERE DATEPART (wk, targetdate) = DATEPART (wk, GETDATE ())");
        Strsql.appendformat ("and DATEPART (yy, targetdate) = DATEPART (yy, GETDATE ())");
        Strsql.appendformat ("GROUP by TargetDate");
        Strsql.appendformat (") as TbB on CONVERT (CHAR (8), A.riqi, 112) = TbB. Day"); #endregion} else if (type = = "2") {#region month Strsql.appendformat ("Select Yearmonth. Month")
        , ");
        Strsql.appendformat ("TB. Monthly turnover,"); Strsql.appendformat ("Case W")HEN yearmonth. Month > MONTH (GETDATE ()) THEN NULL ");
        Strsql.appendformat ("When Yearmonth. Month <= MONTH (GETDATE ()) THEN ISNULL (TB. Monthly turnover, 0)");
        Strsql.appendformat ("End as monthly turnover quantity,");
        Strsql.appendformat ("TB. Monthly total amount,");
        Strsql.appendformat ("Case when yearmonth. Month > MONTH (GETDATE ()) THEN NULL");
        Strsql.appendformat ("When Yearmonth. Month <= MONTH (GETDATE ()) THEN ISNULL (TB. Monthly total amount, 0)");
        Strsql.appendformat ("End as month paid-in Total amount 2"); Strsql.appendformat ("From" (Select 1 as month Union SELECT 2 Union SELECT 3 Union Select 4 UNION Select 5 Union Select 6)
        ; Strsql.appendformat ("Union Select 7 Union SELECT 8 Union SELECT 9 Union Select Union Select Union Select 12"
        );
        Strsql.appendformat (") as Yearmonth");
        Strsql.appendformat ("Left JOIN" (select COUNT (1)); Strsql.appendformat ("from dbo.")
        Customerbase base "); Strsql.appendformAt ("WHERE ckey = ' {0} '", Ckey); Strsql.appendformat ("and" + Impomo.)
        Totalconsumptionmon + "> 0"); Strsql.appendformat ("and MONTH (targetdate) = MONTH" (cus.
        targetdate) ");
        Strsql.appendformat (") monthly turnover,");
        Strsql.appendformat ("ISNULL (select SUM)"; Strsql.appendformat ("From" (CONVERT (FLOAT, ISNULL (pc1 + PC2 + PC3 + pc4 + pc5), 0)) as
        Total ");
        Strsql.appendformat ("From paymentcontent as pay"); Strsql.appendformat ("WHERE MONTH" (paydate) = MONTH (cus.
        targetdate) "); Strsql.appendformat ("and pay.")
        Ckey = ' {0} ', ckey);
        Strsql.appendformat ("UNION all");
        Strsql.appendformat ("Select SUM (CONVERT (FLOAT, ISNULL (Recmoney, 0)) as Total"); Strsql.appendformaT ("from dbo.")
        CardRecharge8 as recharge "); Strsql.appendformat ("WHERE MONTH" (rechargdate) = MONTH (cus.
        targetdate) "); Strsql.appendformat ("and recharge.")
        Ckey = ' {0} ', ckey);
        Strsql.appendformat ("UNION all"); Strsql.appendformat ("Select SUM" (ISNULL (PC1 + PC2 + PC3 + pc4 + pc5), 0)) as Tot
        Al "); Strsql.appendformat ("from dbo.")
        Paymentswimming as Payswim "); Strsql.appendformat ("WHERE MONTH" (paydate) = MONTH (cus.
        targetdate) "); Strsql.appendformat ("and Payswim.")
        Ckey = ' {0} ', ckey);
        Strsql.appendformat ("UNION all"); Strsql.appendformat ("Select SUM" (ISNULL (WP1 + wp2 + wp3 + WP4 + wp5), 0)) as Tot
        Al ");         Strsql.appendformat ("              From Warepaymentcontent as Ware "); Strsql.appendformat ("WHERE MONTH" (paydate) = MONTH (cus.
        targetdate) "); Strsql.appendformat ("and Ware.")
        Ckey = ' {0} ', ckey);
        Strsql.appendformat (") B");
        Strsql.appendformat ("), 0) as monthly total amount,");
        Strsql.appendformat ("MONTH (targetdate) month"); Strsql.appendformat ("from dbo.")
        Customerbase cus ");
        Strsql.appendformat ("WHERE year (targetdate) = year (GETDATE ())"); Strsql.appendformat ("GROUP by MONTH" (cus).
        targetdate) ");
        Strsql.appendformat (") as TB on yearmonth. Month = TB. Month"); #endregion} else if (type = = "3") {#region year Strsql.appendformat ("Select Cou
        NT (1) "); Strsql.appendformat ("from dbo.")
        Customerbase base "); Strsql.appendformat ("WHERE ckey = '{0} ' ", Ckey); Strsql.appendformat ("and" + Impomo.)
        Totalconsumptionmon + "> 0"); Strsql.appendformat ("and Year" (targetdate) = year (cus.
        targetdate) ");
        Strsql.appendformat (") annual turnover,");
        Strsql.appendformat ("Convert" (NVARCHAR), convert (DECIMAL (18,2), ISNULL (SELECT SUM); Strsql.appendformat ("From" (CONVERT (FLOAT, ISNULL ((PC1 + PC2 + PC3 + pc4 + pc5), 0))
        ;
        Strsql.appendformat ("From paymentcontent as pay"); Strsql.appendformat ("WHERE year (paydate) = year" (Cus.
        targetdate) "); Strsql.appendformat ("and pay.")
        Ckey = ' {0} ', ckey);
        Strsql.appendformat ("UNION all");
        Strsql.appendformat ("Select SUM (CONVERT (FLOAT, ISNULL (Recmoney, 0)) as Total"); Strsql.appendformat ("from dbo.")
        CardRecharge8 as recharge "); Strsql.apPendformat ("WHERE year (rechargdate) = year" (Cus.
        targetdate) "); Strsql.appendformat ("and recharge.")
        Ckey = ' {0} ', ckey);
        Strsql.appendformat ("UNION all");
        Strsql.appendformat ("Select SUM" (CONVERT (FLOAT, ISNULL ((PC1 + PC2 + PC3 + pc4 + pc5), 0)) as total); Strsql.appendformat ("from dbo.")
        Paymentswimming as Payswim "); Strsql.appendformat ("WHERE year (paydate) = year" (Cus.
        targetdate) "); Strsql.appendformat ("and Payswim.")
        Ckey = ' {0} ', ckey);
        Strsql.appendformat ("UNION all");
        Strsql.appendformat ("Select SUM" (CONVERT (FLOAT, ISNULL ((WP1 + wp2 + wp3 + WP4 + wp5), 0)) as total);
        Strsql.appendformat ("From Warepaymentcontent as Ware"); Strsql.appendformat ("WHERE year (paydate) = year" (Cus.
        targetdate) "); Strsql.appendfoRmat ("and Ware.")
        Ckey = ' {0} ', ckey);
        Strsql.appendformat (") B");
        Strsql.appendformat ("), 0))) as the total amount of the year,");
        Strsql.appendformat ("Year (targetdate)"); Strsql.appendformat ("from dbo.")
        Customerbase cus ");
        Strsql.appendformat ("GROUP by Year (targetdate)");
      #endregion} #endregion DataTable table = dbhelper.getdatetable (strsql.tostring ());
      String rs = Newtonsoft.Json.JsonConvert.SerializeObject (table);
    Return RS; }