金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 年数据统计

年数据统计

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-29 10:15

本文只是记录在品种中用到的总计的SQL语句,记一笔避防忘了

        /// <summary>
        /// 获取统计数据
        /// </summary>
        /// <param name="CKEY">店面ckey</param>
        /// <param name="type">统计类型(日、周、月、年)</param>
        /// <returns></returns>
        [WebMethod(true)]
        public static string GetData3(string CKEY, string type)
        {
            StringBuilder strSql = new StringBuilder();

            #region SQL语句

            if (type == "0")
            {
                #region 日
                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 日 ,DAY (CONVERT(CHAR(8), a.riqi, 112)) AS DDay, ");
                strSql.AppendFormat("           ISNULL(tbB.日成交量, 0) AS 日成交量 , ");
                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 日成交数量 , ");
                strSql.AppendFormat("           tbB.日实收金额 , ");
                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 日实收金额2 ");
                strSql.AppendFormat("   FROM    WeekDate a ");
                strSql.AppendFormat("           LEFT JOIN ( SELECT  ( 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("                               ) 日成交量 , ");
                strSql.AppendFormat("                               ISNULL(( SELECT SUM(Total) ");
                strSql.AppendFormat("                                        FROM   ( SELECT    SUM(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 日实收金额 , ");
                strSql.AppendFormat("                               TargetDate 日 ");
                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.日 ");
                #endregion
            }
            else if (type == "1")
            {
                #region 周
                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 日 , ");
                strSql.AppendFormat("               DATENAME(weekday,CONVERT(CHAR(8), a.riqi, 112)) DDay, ");
                strSql.AppendFormat("               ISNULL(tbB.日成交量, 0) AS 日成交量 , ");
                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 日成交数量 , ");
                strSql.AppendFormat("               tbB.日实收金额 , ");
                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 日实收金额2 ");
                strSql.AppendFormat("       FROM    WeekDate a ");
                strSql.AppendFormat("               LEFT JOIN ( SELECT  ( 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("                                   ) 日成交量 , ");
                strSql.AppendFormat("                                   ISNULL(( SELECT SUM(Total) ");
                strSql.AppendFormat("                                            FROM   ( SELECT    SUM(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 日实收金额 , ");
                strSql.AppendFormat("                                   TargetDate 日 ");
                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.日 ");
                #endregion
            }
            else if (type == "2")
            {
                #region 月

                strSql.AppendFormat("SELECT  YearMonth.月 , ");
                strSql.AppendFormat("       tb.月成交量 , ");
                strSql.AppendFormat("       CASE WHEN YearMonth.月 > MONTH(GETDATE()) THEN NULL ");
                strSql.AppendFormat("            WHEN YearMonth.月 <= MONTH(GETDATE()) THEN ISNULL(tb.月成交量, 0) ");
                strSql.AppendFormat("       END AS 月成交数量 , ");
                strSql.AppendFormat("       tb.月实收总金额 , ");
                strSql.AppendFormat("       CASE WHEN YearMonth.月 > MONTH(GETDATE()) THEN NULL ");
                strSql.AppendFormat("            WHEN YearMonth.月 <= MONTH(GETDATE()) THEN ISNULL(tb.月实收总金额, 0) ");
                strSql.AppendFormat("       END AS 月实收总金额2 ");
                strSql.AppendFormat(" FROM      ( SELECT 1 AS 月 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 10 UNION SELECT 11 UNION SELECT 12 ");
                strSql.AppendFormat("           ) AS YearMonth ");
                strSql.AppendFormat("       LEFT JOIN ( SELECT  ( 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("                           ) 月成交量 , ");
                strSql.AppendFormat("                           ISNULL(( SELECT SUM(Total) ");
                strSql.AppendFormat("                                    FROM   ( SELECT    SUM(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(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
                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(CONVERT(FLOAT, ISNULL(( wp1 + wp2 + wp3 + wp4 + wp5 ), 0))) AS Total ");
                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 月实收总金额 , ");
                strSql.AppendFormat("                           MONTH(TargetDate) 月 ");
                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.月 = tb.月 ");
                #endregion
            }
            else if (type == "3")
            {
                #region 年
                strSql.AppendFormat("SELECT  ( SELECT    COUNT(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("           ) 年成交量 , ");
                strSql.AppendFormat("           CONVERT(NVARCHAR(20),CONVERT(DECIMAL(18,2),ISNULL(( SELECT SUM(Total) ");
                strSql.AppendFormat("                    FROM   ( SELECT    SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
                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 年实收总金额 , ");
                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;
        }

 

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:年数据统计

关键词:

上一篇:没有了

下一篇:没有了