分上下半月统计 SQL

最新推荐文章于 2024-06-27 08:00:00 发布

weixin_30536513

最新推荐文章于 2024-06-27 08:00:00 发布

阅读量207

点赞数

原文链接：http://www.cnblogs.com/Tonyyang/archive/2010/05/11/1732231.html

版权

代码

declare     @t     table    (Date    datetime ,   Name    varchar ( 30 ),    Money     money )
   insert     @t
   select     dateadd (dd, 10 , getdate ()), ' a ' , 10.0000     union     all
   select     dateadd (dd, 11 , getdate ()), ' a ' , 10.0000     union     all
   select     dateadd (dd, 12 , getdate ()), ' b ' , 10.0000     union     all
   select     dateadd (dd, 13 , getdate ()), ' b ' , 10.0000     union     all
   select     dateadd (dd, 6 , getdate ()), ' b ' , 10.0000     union     all
   select     dateadd (dd, - 1 , getdate ()), ' a ' , 10.0000     union     all
   select     dateadd (dd, - 2 , getdate ()), ' a ' , 10.0000     union     all
   select     dateadd (dd, - 3 , getdate ()), ' b ' , 10.0000     union     all
   select     dateadd (dd, - 4 , getdate ()), ' b ' , 10.0000     union     all
   select     dateadd (dd, - 40 , getdate ()), ' b ' , 10.0000     union     all
   select     dateadd (dd, - 20 , getdate ()), ' b ' , 10.0000     union     all
   select     dateadd (dd, 30 , getdate ()), ' b ' , 10.0000     union     all
   select     dateadd (dd, 60 , getdate ()), ' b ' , 10.0000
   SELECT     CONVERT ( VARCHAR ( 7 ),Date, 120 ) + ' - ' +
   case     when     day (date)    between     1     and     15     then     ' 上半月 '     else     ' 下半月 '     end ,
  Name,
   SUM ( Money )
   FROM     @t    t
   GROUP     BY     CONVERT ( VARCHAR ( 7 ),Date, 120 ) + ' - ' +
   case     when     day (date)    between     1     and     15     then     ' 上半月 '     else     ' 下半月 '     end ,
  Name

转载于:https://www.cnblogs.com/Tonyyang/archive/2010/05/11/1732231.html