How to get distinct record count from everyday log

Question

0.00/5 (No votes)

See more:

Hello there i have following data ... from this i want count of user activated on each day with its latest activated time...

ID Status Date Day Month Year
2962 Y 2015-08-21 14:16:25.113 21 8 2015
2963 Y 2015-08-21 15:15:26.013 21 8 2015
2962 Y 2015-08-21 15:15:28.113 21 8 2015
2964 Y 2015-08-21 15:16:58.117 21 8 2015
2909 Y 2015-08-21 15:16:59.820 21 8 2015
2964 Y 2015-08-24 12:46:55.850 24 8 2015
2908 Y 2015-08-24 12:46:57.877 24 8 2015
2963 Y 2015-08-24 12:46:59.600 24 8 2015
2841 Y 2015-08-24 13:11:13.513 24 8 2015
2840 Y 2015-08-24 13:11:15.323 24 8 2015
2839 Y 2015-08-24 13:11:17.297 24 8 2015
2838 Y 2015-08-24 13:11:19.270 24 8 2015
2778 Y 2015-08-24 13:11:21.283 24 8 2015
2778 Y 2015-08-24 13:12:49.293 24 8 2015
2838 Y 2015-08-24 13:12:51.353 24 8 2015
2839 Y 2015-08-24 13:12:53.030 24 8 2015
2840 Y 2015-08-24 13:12:55.220 24 8 2015
2841 Y 2015-08-24 13:12:57.093 24 8 2015
2908 Y 2015-08-24 13:12:59.110 24 8 2015
2909 Y 2015-08-24 13:13:01.187 24 8 2015

Result i want as

date Active users
2015-08-24 4
2015-08-21 2

Posted 24-Aug-15 1:32am

Devraj Kapdi

Add a Solution

1 solution

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)

Advay Pandya · Answer 1 · 2015-08-24T01:56:00

Solution 1

Hi Devraj,

Please try below query

SQL

SELECT Convert(date,[Date]),Count(Id) FROM [Table]
Where Status = 'Y' 
GROUP BY Convert(date,[Date]),Status

Here inside convert first date is keyword date and second [Date] is your field name.

The output will be shown, date wise active user count.

Please let me know if you have any concern or query on this or if I am missing something.

Thanks

Posted 24-Aug-15 1:56am

Advay Pandya

Comments

Maciej Los 24-Aug-15 8:07am

5ed!

Advay Pandya 24-Aug-15 8:08am

Thanks Maciej :)

Devraj Kapdi 24-Aug-15 8:32am

no it wont worked for me... As i have LOG of active and deactive users. the scenario is admin can active and deactive the users randomly in a day the log of that is inserted into the table.
i want the count of latest active users suppose admin can deactive the active user than the log is inserted ,.. again the admin activate that user the log is inserted again... the count for each user which are activated that i want to count for each day. for generating graph of active users per day...
Below is the query i used but it shows me only current date data as i used MAX function for date...

select CNTM.CNTM_CONTACT_ID,CNTM.CNTM_ISACTIVE,CNTM.CNTM_Active_Date as ActiveDate,
day(Convert(varchar, CNTM.CNTM_Active_Date ,110)) as ActiveDay,
month(Convert(varchar, CNTM.CNTM_Active_Date ,110)) as ActiveMonth,
year(Convert(varchar, CNTM.CNTM_Active_Date ,110)) as ActiveYear
from USER_MST inner join CONTACT_MST CNTM1 on USRM_CONTACTID=CNTM1.CNTM_CONTACT_ID
inner join USERROLEREL_DET on URRD_USER_ID=USRM_USER_ID inner join ROLE_MST on URRD_ROLE_ID=ROLM_ROLE_ID
inner join CONTACT_MST_LOG CNTM on CNTM1.CNTM_CONTACT_ID = CNTM.CNTM_CONTACT_ID
where USRM_COMPANY_ID= '544' and ROLM_ROLE_ID = 9 AND CNTM.CNTM_ISACTIVE = 'Y'
AND CNTM.CNTM_Active_Date = ( select max(CNTM_Active_Date) from CONTACT_MST_LOG b
where CNTM.CNTM_CONTACT_ID = b.CNTM_CONTACT_ID )
group by CNTM.CNTM_Active_Date,CNTM.CNTM_CONTACT_ID,CNTM.CNTM_ISACTIVE,
day(Convert(varchar, CNTM.CNTM_Active_Date ,110)),
month(Convert(varchar, CNTM.CNTM_Active_Date ,110)),
year(Convert(varchar, CNTM.CNTM_Active_Date ,110))
Order by year(Convert(varchar, CNTM.CNTM_Active_Date ,110)) ,month(Convert(varchar, CNTM.CNTM_Active_Date ,110))

below is my table structure

CREATE TABLE [dbo].[CONTACT_MST_LOG](
[CNTM_CONTACT_ID] [numeric](18, 0) NOT NULL,
[CNTM_CREATED_BY] [numeric](18, 0) NULL,
[CNTM_CREATED_DATE] [datetime] NULL,
[CNTM_MODIFIED_BY] [numeric](18, 0) NULL,
[CNTM_MODIFIED_DATE] [datetime] NULL,
[CNTM_ISACTIVE] [varchar](1) NULL,
[CNTM_ISBLOCKED] [varchar](1) NULL,
[CNTM_BLOCKED_BY] [numeric](18, 0) NULL,
[CNTM_BLOACKED_DATE] [datetime] NULL,
[CNTM_Active_By] [numeric](18, 0) NULL,
[CNTM_Active_Date] [datetime] NULL
) ON [PRIMARY]

Advay Pandya 24-Aug-15 8:49am

I am still not clear about the latest active users. Does your query returns correct result for the today's date ? If yes then I can help you to make query result for all the days instead of single date.

Devraj Kapdi 24-Aug-15 8:59am

yes its working correct... u can proceed

Advay Pandya 24-Aug-15 9:08am

I have modified your query as per my understanding. I have removed the where condition of date and put a separate join. The reason of joining is to have multiple where condition with the table.
Please try below and please let me know if it will still not a working solution for you:

select CNTM.CNTM_CONTACT_ID,CNTM.CNTM_ISACTIVE,CNTM.CNTM_Active_Date as ActiveDate,
day(Convert(varchar, CNTM.CNTM_Active_Date ,110)) as ActiveDay,
month(Convert(varchar, CNTM.CNTM_Active_Date ,110)) as ActiveMonth,
year(Convert(varchar, CNTM.CNTM_Active_Date ,110)) as ActiveYear
from USER_MST inner join CONTACT_MST CNTM1 on USRM_CONTACTID=CNTM1.CNTM_CONTACT_ID
inner join USERROLEREL_DET on URRD_USER_ID=USRM_USER_ID inner join ROLE_MST on URRD_ROLE_ID=ROLM_ROLE_ID
inner join CONTACT_MST_LOG CNTM on CNTM1.CNTM_CONTACT_ID = CNTM.CNTM_CONTACT_ID
INNER JOIN ( select max(CNTM_Active_Date) AS CNTM_Active_Date from CONTACT_MST_LOG b
where CNTM.CNTM_CONTACT_ID = b.CNTM_CONTACT_ID ) T ON CNTM.CNTM_Active_Date = T.CNTM_Active_Date
where USRM_COMPANY_ID= '544' and ROLM_ROLE_ID = 9 AND CNTM.CNTM_ISACTIVE = 'Y'
group by CNTM.CNTM_Active_Date,CNTM.CNTM_CONTACT_ID,CNTM.CNTM_ISACTIVE,
day(Convert(varchar, CNTM.CNTM_Active_Date ,110)),
month(Convert(varchar, CNTM.CNTM_Active_Date ,110)),
year(Convert(varchar, CNTM.CNTM_Active_Date ,110))
Order by year(Convert(varchar, CNTM.CNTM_Active_Date ,110)) ,month(Convert(varchar, CNTM.CNTM_Active_Date ,110))

Devraj Kapdi 25-Aug-15 1:41am

Im getting this error

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "CNTM.CNTM_CONTACT_ID" could not be bound.

Advay Pandya 25-Aug-15 2:20am

Okay, You might get error on 1 or 2 attempts because I dont have DB structure so I am not able to run the query.
Please replace inner join part from below lines. I think now it will work.

INNER JOIN ( select CNTM_CONTACT_ID,max(CNTM_Active_Date) AS CNTM_Active_Date from CONTACT_MST_LOG b
) T ON CNTM.CNTM_Active_Date = T.CNTM_Active_Date AND CNTM.CNTM_CONTACT_ID = T.CNTM_CONTACT_ID

U can exclude the CNTM_CONTACT_ID condition if you don't need.
Please let me know if you are still facing issue.

Devraj Kapdi 27-Aug-15 8:35am

ID Status Date
-------------------------------------------

2706 Y 2015-08-27 14:26:25.103
2706 N 2015-08-27 14:33:42.053
2707 Y 2015-08-27 14:26:22.227
2707 N 2015-08-27 14:27:11.000
2710 Y 2015-08-25 17:07:01.937
2710 N 2015-08-27 14:16:42.420
2710 Y 2015-08-27 14:18:56.347
2710 N 2015-08-27 14:27:07.753
2755 Y 2015-08-25 17:06:59.210
2755 N 2015-08-26 11:27:28.643
2755 Y 2015-08-26 11:27:35.143
2755 N 2015-08-27 14:16:39.917
2755 Y 2015-08-27 14:18:53.477
2755 N 2015-08-27 14:26:49.217

Hello, Here is my DataTable above ... from this i want in c# as the max date record by ID and its status

2706 N 2015-08-27 14:33:42.053
2707 N 2015-08-27 14:27:11.000
2710 N 2015-08-27 14:27:07.753
2755 N 2015-08-27 14:26:49.217
2755 Y 2015-08-26 11:27:35.143
2710 Y 2015-08-25 17:07:01.937
2755 Y 2015-08-25 17:06:59.210