Saving a select count(*) value to a variable in SQL Server

Question

I have Leave Entries table like this:

emp_id   LeaveTypeSName    LeaveStatus   FromDate            ToDate
----------------------------------------------------------------------
 1            CL             Full Day      21/07/2014       21/07/2014
 1            SL             Half Day      21/07/2014       21/07/2014
 1            CL             Full Day      21/07/2014       21/07/2014
 1            SL             Full Day      21/07/2014       21/07/2014
 1            CL             Half Day      21/07/2014       21/07/2014
 1            CL             Full Day      21/07/2014       24/07/2014

I am trying get result like this...

 CL_count      SL_count
 -----------------------
    6.5           1.5

My SQL query is

SELECT     
   no_dyas = SUM( DATEDIFF(d, FromDate, ToDate)),
   CL_COUNT = SUM(CASE WHEN LeaveTypeSName='CL' AND LeaveStatus='FullDay'  THEN 1 WHEN LeaveTypeSName='CL' AND LeaveStatus='HalfDay' THEN 0.5  END),
   SL_COUNT = SUM(CASE WHEN LeaveTypeSName='SL' AND LeaveStatus='FullDay' THEN 1 WHEN LeaveTypeSName='SL' AND LeaveStatus='HalfDay' THEN 0.5  END),
   FH_COUNT = SUM(CASE WHEN LeaveTypeSName='FH' AND LeaveStatus='FullDay' THEN 1 WHEN  LeaveTypeSName='FH' AND LeaveStatus='HalfDay' THEN 0.5  END),
   LOP_COUNT = SUM(CASE WHEN LeaveTypeSName='LOP' AND LeaveStatus='FullDay' THEN 1 WHEN LeaveTypeSName='LOP' AND LeaveStatus='HalfDay' THEN 0.5  END)   
FROM 
   LeaveEntries 
WHERE
   EmployeeCode = '1'

Please help me.....

Are you getting CL=4 or CL=6.5? What is the actual business logic to calculate these values? — shree.pat18
– shree.pat18, Commented Jul 23, 2014 at 5:10

TechDo · Accepted Answer · 2014-07-23 05:19:42Z

2

Please try:

select 
   LeaveTypeSName,
   SUM(case when LeaveStatus='Full Day' then 1 else 0.5 end)
From 
   LeaveEntries 
where emp_id=1
group by LeaveTypeSName

To get the sum of leave days, try:

select LeaveTypeSName,
  SUM(case when LeaveStatus='Full Day' then DATEDIFF(day, FromDate, ToDate)+1 else 0.5 end)
From 
  LeaveEntries 
where emp_id=1
group by LeaveTypeSName

edited Jul 23, 2014 at 5:19

answered Jul 23, 2014 at 5:05

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dileep Kumar Over a year ago

it should also include the Leaves No ..for a eg...in last row in theleave entries it shows that number of CL is 4

TechDo Over a year ago

@DileepKumar Please add the same in expected output.

Dileep Kumar Over a year ago

i want LeaveTypesName count.

schlonzo Over a year ago

If you want to add LeaveTypesName count then add COUNT(*) to the select statement

Farrokh · Accepted Answer · 2014-07-23 06:31:10Z

0

this worked for me!!!

SELECT     
  no_dyas = SUM( DATEDIFF(d, FromDate, ToDate)),
  CL_COUNT = SUM(CASE WHEN LeaveTypeSName='CL' AND LeaveStatus='Full Day'  THEN 1 WHEN LeaveTypeSName='CL' AND LeaveStatus='Half Day' THEN 0.5  END),
  SL_COUNT = SUM(CASE WHEN LeaveTypeSName='SL' AND LeaveStatus='Full Day' THEN 1 WHEN LeaveTypeSName='SL' AND LeaveStatus='Half Day' THEN 0.5  END),
  FH_COUNT =SUM(CASE WHEN LeaveTypeSName='FH' AND LeaveStatus='Full Day' THEN 1 WHEN  LeaveTypeSName='FH' AND LeaveStatus='Half Day' THEN 0.5  END),
  LOP_COUNT =SUM(CASE WHEN LeaveTypeSName='LOP' AND LeaveStatus='Full Day' THEN 1 WHEN LeaveTypeSName='LOP' AND LeaveStatus='Half Day' THEN 0.5  END)   
FROM 
  Leave_Entries 
WHERE    emp_id = 1

it's result is true... 3.5 and 1.5
I just changed the 'HalfDay' to 'Half Day' And 'FullDay' to 'Full Day' according to your data in your query and done...

edited Jul 23, 2014 at 6:31

answered Jul 23, 2014 at 5:23

Farrokh

1,1771 gold badge7 silver badges18 bronze badges

3 Comments

schlonzo Over a year ago

This doen't regard the timespan (FromDate, ToDate)

Farrokh Over a year ago

yes but isn't important, you can add it and it will work, it worked for me correctly without any problem, (my post edited)

schlonzo Over a year ago

But the result should be 6.5 and 1.5, like described by Dileep.

Collectives™ on Stack Overflow

Saving a select count(*) value to a variable in SQL Server

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related