如何收集过去一个月的数据 [英] How to ger past one month data

问题描述

我正在编写查询以获取过去一个月的数据.即如果当前日期为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

这篇关于如何收集过去一个月的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！