如何收集过去一个月的数据

本文介绍了如何收集过去一个月的数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在编写查询以获取过去一个月的数据.即如果当前日期为2012-08-27，则结果表数据为2012-08-27至2012-07-27. 为此，我写了一个查询，如

Hi, I am writing a query to get past one month data. i.e if current date is 2012-08-27 then the result table data is 2012-08-27 to 2012-07-27. for this i have written a query like

Declare @Days Table (Days int); Declare @i as int; Declare @CurrentDate nvarchar(10) BEGIN Set @CurrentDate= @DateFrom ;-- '2012-07-27' Set @CurrentDate=(select REPLACE(@CurrentDate,'-','/')); Set @i = 1 While @i < 30 Begin Insert into @Days Values(@i); Set @i = @i + 1; End; Select [Month of Day], Count([TotalShipOrders]) as Total From ( SELECT DATEpart(DAY, convert(Datetime,CreatedDateTime,13)) AS [Month of Day] , Id AS [TotalShipOrders] FROM ShipmentDetail where CONVERT(VARCHAR(10),CreatedDateTime,111)>= dateadd(DAY,-30,@CurrentDate) Union ALL SELECT Days, NULL From @Days ) as Data GROUP BY [Month of Day] ORDER BY [Month of Day] END

结果如下:

It gives the result like :

Month of Day Total 1 0 2 0 3 0 4 0 5 0 6 0 7 0 8 0 9 0 10 0 11 0 12 0 13 0 14 0 15 0 16 0 17 0 18 0 19 0 20 0 21 0 22 0 23 0 24 0 25 353 26 726 27 327 28 0 29 0 30 35

在这里我想将1,2，，3,4,5，..... 30更改为1Aug.2Aug，3Aug，........... 上面的查询写了while循环而不是while循环，这样就可以获得一个月的数据. 在这里，我写了一段时间，如果在某一天，如果没有计数，则应该写零(0). 请给我确切的查询. 谢谢与问候， Raghu.

Here i want to change 1,2,,3,4,5,.....30 to 1Aug.2Aug,3Aug,........... The above query has written with while loop instead of while loop is it possible to get one month data. Here i have written while for if on a particular day if there is no count it should written Zero(0). Please give me exact query. Thanks&Regards, Raghu.

推荐答案

这可以解决您的问题...使用日期名称和日期创建字符串 DATENAME(month，CreatedDateTime)+ CAST(DATEPART(DAY，CreatedDateTime)as varchar(2))datewithmonth Hi this could solve your problem... make a string using datename and day DATENAME(month, CreatedDateTime) + CAST(DATEPART(DAY,CreatedDateTime) as varchar(2)) datewithmonth