retraiving data from sql server between specific dates

Question

0.00/5 (No votes)

See more:

I want to show that how much user loggedin my site between two date like 01/02/2015 to 22/03/2015 and my query is:
"select LoggedIn,count(LoggedInFrom) AS Device from User_log_Info where LoggedIn between '03/20/2015 00:00:00' And '03/22/2015 23:59:59' And LoggedInFrom = 0 GROUP BY LoggedIn order by LoggedIn asc "

the result is :

HTML

id          LoggedIn        Device

1   2015-03-20 23:14:00     1
2   2015-03-21 07:13:00     1
3   2015-03-21 11:22:00     1
4   2015-03-21 11:24:00     1
5   2015-03-21 11:35:00     1
6   2015-03-22 14:07:00     1

but i want to show like this:

HTML

id  	LoggedIn  Device

1   2015-03-20 	     1		
2	2015-03-21 	     4
3	2015-03-22 	     1

how can i do this?

Posted 22-Mar-15 19:24pm

Member 9361273

Updated 22-Mar-15 19:29pm

v2

Add a Solution

Comments

PIEBALDconsult 23-Mar-15 1:50am

GROUP BY and COUNT
And I hope you are not storing dates as strings.

2 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)

Saral S Stalin · Answer 1 · 2015-03-22T21:14:00

Solution 2

Abhinav gave correct solution, but there are minor syntax errors in it.

SQL

SELECT CAST(LoggedIn AS DATE)  LoggedIn
       ,COUNT(LoggedInFrom) AS Device
FROM User_log_Info
WHERE LoggedIn BETWEEN '03/20/2015 00:00:00' AND '03/22/2015 23:59:59'
      AND LoggedInFrom = 1
GROUP BY CAST(LoggedIn AS DATE)
ORDER BY LoggedIn ASC

Note that I changed one condition LoggedInFrom = 1 to get data as per the sample provided.

Posted 22-Mar-15 21:14pm

Saral S Stalin

Comments

Member 9361273 23-Mar-15 5:37am

Yes its working well but it also show the data of 03/23/2015 though i want to show data between '03/20/2015 00:00:00' AND '03/22/2015 23:59:59'

Saral S Stalin 23-Mar-15 6:01am

That's a surprise. Plesae post table schema and sample data and the query you are running. Let me check.

Member 9361273 24-Mar-15 2:54am

Column Name datatype Allow nulls
ID int no
LoggedIn smalldatetime no
LoggedInFrom tinyint no

this is the table design
and sample data is shown below:

ID LoggedIn LoggedInFrom
1 2015-02-20 11:48:00 1
2 2013-02-20 11:48:00 2
3 2013-02-21 11:48:00 2

and my my query is:
SELECT CAST(LoggedIn AS DATE) LoggedIn
,COUNT(LoggedInFrom) AS Device
FROM User_log_Info
WHERE LoggedIn BETWEEN '03/20/2015 00:00:00' AND '03/22/2015 23:59:59'
AND LoggedInFrom = 1
GROUP BY CAST(LoggedIn AS DATE)
ORDER BY LoggedIn ASC

Abhinav S · Answer 2 · 2015-03-22T19:50:00

Solution 1

Cast LoggedIn to date ->

select LoggedIn,count(LoggedInFrom) AS Device from User_log_Info where LoggedIn between '03/20/2015 00:00:00' And '03/22/2015 23:59:59' And LoggedInFrom = 0 GROUP BY CAST(LoggedIn) AS DATE) order by LoggedIn asc

Posted 22-Mar-15 19:50pm

Abhinav S

Updated 22-Mar-15 19:51pm

v2