How to get continuous leave taken staff for the current date in mssql?

Question

1.00/5 (2 votes)

See more:

My Table: (For Current Month)
EMPCODE DATE ATTENDANCEMODE
100 01/01/2020 A
100 02/01/2020 A
100 03/01/2020 A
101 03/01/2020 A
102 02/01/2020 A
102 03/01/2020 A

What i need?

EMPCODE CONTINUOUSLEAVEFORHOWMUCHDAYS:
100 3
101 1
102 2

Note: 03/01/2020 is the current date

What I have tried:

select COUNT(*) from mytable where date='2020/01/03'

Posted 20-Feb-20 3:44am

Sissy Ram

Updated 20-Feb-20 5:15am

v2

Add a Solution

Comments

[no name] 20-Feb-20 10:36am

You need the "days present" too, if you don't want to go crazy.

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)

RAHUL(10217975) · Answer 1 · 2020-02-20T05:16:00

Solution 1

select code EMPCODE,count(date) as 'CONTINUOUSLEAVEFORHOWMUCHDAYS' from employee
where date = '01/02/2020'
group by Code

Accept solution if you are happy with the solution

Posted 20-Feb-20 5:16am

RAHUL(10217975)

Updated 20-Feb-20 5:30am

v3

Comments

MadMyche 20-Feb-20 12:29pm

Did you run this against the data given AND get the desired result set?

RAHUL(10217975) 22-Feb-20 7:08am

Yes. Query is working fine against the data given and got desired result set

Sissy Ram 14-Mar-20 4:13am

@Rahul(10217975) Do you think this is right query

IF
My Table: (For Current Month)
EMPCODE DATE ATTENDANCEMODE
100 01/01/2020 P
100 02/01/2020 A
100 03/01/2020 A
100 04/01/2020 P
100 05/01/2020 A
100 06/01/2020 A
100 07/01/2020 A
Current date 07-01-2020

What I need?
EMPCODE CONTINUOUSLEAVEFORHOWMUCHDAYS:
100 3

Will the above query works for this?
NO..