用于从日期到10个日期之前检索数据的SQL查询。 [英] sql query for retrieving data from date to before 10 dates .

问题描述

这是我的桌子数据

This is My Table Data

a    b     c      YY   MM  DD  Hr  Min Sec
1080 20006  24122  2013  4   6   17  8   1
1080 20006  24123  2013  4   2   17  16  28
1080 20006  24124  2013  4   9   17  21  21
1080 20006  24125  2013  4   9   17  25  12
1080 20007  24126  2013  5   13  17  30  13
1080 20007  24127  2013  5   29  17  40  2
1080 20007  24128  2013  5   30  17  49  35
1080 20007  24129  2013  5   31  18  0   10
1080 20008  24130  2013  6   9   18  6   27
1080 20008  24131  2013  6   1   18  12  55
1080 20009  24132  2013  6   2   18  19  48
1080 20009  24133  2013  6   3   18  28  36
1080 20009  24132  2013  6   4   18  19  48
1080 20009  24133  2013  6   5   18  28  36

我想要结果格式化。（基于数据（DD）和月份（MM）的结果）

I want result bellow format.(Result based on data(DD) and month(MM))

    b     YY   MM       PacketsCountbetweenDD              PacketsCountbetweenDD
                  (yesterdaydate to 10daysbackDate)  (yesterdaydate to 15daysbackDate)
20006  2013  04  	0                                          0                                     
20006  2013  05  	3                                          3                                  
20006  2013  06  	6                                          6

推荐答案

您好，



您需要在10天之前获取数据然后使用DATEADD函数，例如

Hi,

You need to get before 10 days data then use DATEADD Function like

SELECT ColumnList FROM tableName WHERE your_date_column BETWEEN GETDATE() AND DATEADD(DAY,-10,GETDATE)

-- Note : If you dont have Single column for DATETIME, then do like this CAST(yearcolumn AS VARCHAR(4))+CAST(monthColum AS VARCAHR(2))+CAST(daycolumn AS VARCHAR(2)).Then use in BETWEEN Condition....

-- Check this...
SELECT M.b, M.YY, M.MM ,
	(SELECT COUNT(b) FROM tableName
	 WHERE b=M.b
	 AND CAST(YY AS VARCHAR(4)) + CAST(MM AS VARCHAR(2)) + CAST(DD AS VARCHAR(2)) 
		BETWEEN CONVERT(VARCHAR(10),(DATEADD(DAY,-10,GETDATE())),112) AND CONVERT(VARCHAR(10),GETDATE(),112)
        )'Before_10_Days',
	(SELECT COUNT(b) FROM tableName
	 WHERE b=M.b
	 AND CAST(YY AS VARCHAR(4))+CAST(MM AS VARCHAR(2)) +CAST(DD AS VARCHAR(2)) 
		BETWEEN CONVERT(VARCHAR(10),(DATEADD(DAY,-15,GETDATE())),112) AND CONVERT(VARCHAR(10),GETDATE(),112)
        ) 'Before_15_Days'
 
FROM tableName M
GROUP BY M.b, M.YY, M.MM 

问候，

GVPrabu

Regards,
GVPrabu

我将为您提供示例代码....使用此根据您的要求







select * from YourtableName WHERE date from fromdate-1 and fromdate-16



例如

fromdate是2013年6月6日意味着



范围之间的查询是2013年6月5日至2013年6月20日



任何疑问。请告诉我。

I will give you the sample code.... use this for your requirement



select * from YourtableName WHERE date between fromdate-1 and fromdate-16

for example
fromdate is 06 june 2013 means

query between range is 05june2013 to 20june2013

any doubts. pls let me know.

在这个帖子中：用于检索的sql查询基于两个表格中的日期和月份。 [ ^ ]我已经告诉你如何将你的数据[yy] - [MM] - [DD]转换成日期。拜托，不要偷懒！尝试自己做点什么！

In this thread: sql query for Retrieving based on date and month from two tables.[^] i had show you how to "convert" your data [yy]-[MM]-[DD] into date. Please, don't be lazy! Try to do something yourself!

这篇关于用于从日期到10个日期之前检索数据的SQL查询。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！