sql query for retrieving data from date to before 10 dates .

Question

4.00/5 (1 vote)

See more:

This is My Table Data

SQL

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

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

SQL

    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

Posted 6-Jun-13 0:14am

subbu a

Updated 6-Jun-13 0:32am

gvprabu

v2

Add a Solution

Comments

gvprabu 6-Jun-13 6:25am

give Some More Information about our Request....

subbu a 6-Jun-13 6:49am

in the above table.
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
in the above 3 records from yesterday to 10days back there is no data. packet count is 0.
i want displaying records count between dates.

Maciej Los 6-Jun-13 7:23am

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!

Mark all useful solutions as "answer" (green button) in all your threads (questions).

gvprabu 6-Jun-13 7:05am

with one more column, for that year and Month

gvprabu 6-Jun-13 7:14am

Hi,
I Updated my solution, check if any syntax error is there correct it, because I don't have SQL Server in my machine :-)

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Answer 1 · 2013-06-06T00:30:00

Hi,

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

SQL

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

Regards,
GVPrabu

Arun Vasu · Answer 2 · 2013-06-06T00:26:00

Solution 1

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.

Posted 6-Jun-13 0:26am

Arun Vasu

Updated 7-Jun-13 19:30pm

v2

Comments

gvprabu 6-Jun-13 6:32am

While doing any operation in Datetime column use DATEADD function. its good practice

subbu a 6-Jun-13 6:57am

I want count not list

gvprabu 6-Jun-13 7:05am

so use same kind of select statement and Use COUNT Function.

subbu a 6-Jun-13 7:15am

SELECT DISTINCT a, COUNT (a) AS count1
FROM data
WHERE date1 BETWEEN '27/05/2013' AND '06/06/2013'
GROUP BY a
ORDER BY a

i am using above query
it displays fromdate to before 10 days count.

i want from date to before 20 days count how can i write query.

gvprabu 6-Jun-13 8:30am

Try like this....

SELECT DISTINCT a, COUNT (a) AS count1
FROM data
WHERE date1 BETWEEN GETDATE() AND DATEADD(DAY,-20,GETDATE())
GROUP BY a
ORDER BY a

Maciej Los · Answer 3 · 2013-06-06T01:25:00

Solution 3

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!

Posted 6-Jun-13 1:25am

Maciej Los