How to get the list of all employees

Transact-SQL
1

I NEED HELP WITH MY SQL QUERY BELOW. I TRIED TO GET ALL THE LISTS OF EMPLOYEES INCLUDED WHO HAS DEDUCTION AMOUNT AND NOT DEDUCTION AMOUNT EVEN THE EMPLOYEE WHO DOESN'T HAVE ON THE SAME CHECK DATE. I NEED TO RUN BY CHECK DATE FOR 04-24-2020, BUT WANT TO INCLUDED EMPLOYEE 100. I AM NOT SURE HOW TO CREATE NEST QUERY TO INCLUDED EMPLOYEE 100 IN THE LIST EVEN I SELECT CHECK DATE = 04-24-2020. FOR EXAMPLE:
EMPLOYID, DED_AMOUNT, DED_CODE, CHECK_DATE
100 $75 FSME 01-31-2020
200 $100 FSME 04-24-2020
300 $60 FSME 04-24-2020
HERE'S MY SQL QUERY
SELECT a.EMPLOYEEID, a.START_DATE, a.STOP_DATE, b.DED_AMOUNT,
b.DED_CODE, b.CHECK_DATE
FROM BENEFIT a LEFT OUTER JOIN PAYDEDUCTION b
ON a.EMPLOYEEID = b.EMPLOYEEID
WHERE YEAR(a.START_DATE) = 2020 AND YEAR(a.STOP_DATE) = 2020
AND b.DED_CODE = 'FSME'
AND b.CHECK_DATE = '04-24-2020'

2

You really need to to provide DDL and sample data to get what you want. It looks like you want all employees and only include data from PayDeduction for CheckDate = 4/24/2020. I don't know where the start and stop dates come in. Here's a stab in the dark

create table #PayDeduction (EMPLOYID int,
DED_AMOUNT money,
DED_CODE varchar(10),
CHECK_DATE Date)

Create table #Employee (EmployeeID int,
EmpName varchar(20),
StartDate date,
EndDate date)

insert into #PayDeduction values
(100,$75 , 'FSME','01/31/2020'),
(200,$100, 'FSME','04/24/2020'),
(300,$60 , 'FSME','04/24/2020')

insert into #Employee values
(100, 'Emp1', '1/1/2020', null),
(200, 'Emp2','2/1/2020', '2/20/2020'),
(300, 'Emp3','3/3/2019', '3/30/2020')

SELECT a.EMPLOYEEID, a.EndDATE, a.EndDATE, b.DED_AMOUNT, b.DED_CODE, b.CHECK_DATE
FROM #Employee a
LEFT JOIN #PAYDEDUCTION b
ON a.EMPLOYEEID = b.EMPLOYID
and YEAR(a.STARTDATE) = 2020 AND YEAR(a.EndDATE) = 2020
AND b.DED_CODE = 'FSME'
AND b.CHECK_DATE = '04-24-2020'

1 Like
3

can you show me how to share dll

4

Mike here's my data

EMPLOYEE START_DATE STOP_DATE ANNUAL_AMT BOND_DED_AMT CYCLES_REMAIN DED_AMT CHECK_DATE
1169 1/1/2020 12/31/2020 1560 60 26 $60.00 4/24/2020
1340 1/1/2020 12/31/2020 500 19.24 26 $19.24 4/24/2020
1364 1/1/2020 12/31/2020 2700 103.85 26 $103.85 4/24/2020
1457 1/1/2020 12/31/2020 2600 100 26 $100.00 4/24/2020
1468 1/1/2020 12/31/2020 2550 98.08 26 $98.08 4/24/2020
1471 1/1/2020 12/31/2020 1950 75 26 $75.00 4/24/2020
1542 1/1/2020 12/31/2020 2002 77 26 $77.00 4/24/2020
1587 1/1/2020 12/31/2020 300 11.54 26 $11.54 4/24/2020
1655 1/1/2020 12/31/2020 650 25 26 $25.00 4/24/2020
1672 1/1/2020 12/31/2020 780 30 26 $30.00 4/24/2020
1752 1/1/2020 12/31/2020 2000 76.93 26 $76.93 4/24/2020
1757 1/1/2020 12/31/2020 2600 100 26 $100.00 4/24/2020
1758 1/1/2020 12/31/2020 1040 40 26 $40.00 4/24/2020
1777 1/1/2020 12/31/2020 2750 105.77 26 $105.77 4/24/2020
1789 1/1/2020 12/31/2020 2750 105.77 26 $105.77 4/24/2020
1811 1/1/2020 12/31/2020 2100 80.77 26 $80.77 4/24/2020
1847 1/1/2020 12/31/2020 1300 50 26 $50.00 4/24/2020
1872 1/1/2020 12/31/2020 1000 38.47 26 $38.47 4/24/2020
1890 1/1/2020 12/31/2020 1000 38.47 26 $38.47 4/24/2020
1914 1/1/2020 12/31/2020 2700 103.85 26 $103.85 4/24/2020
1939 1/1/2020 12/31/2020 1690 65 26 $65.00 4/24/2020
1993 1/1/2020 12/31/2020 2000 76.93 26 $76.93 4/24/2020
1996 1/1/2020 12/31/2020 1040 40 26 $40.00 4/24/2020
2013 1/1/2020 12/31/2020 2750 105.77 26 $105.77 4/24/2020
2015 1/1/2020 12/31/2020 806 31 26 $31.00 4/24/2020
5

Is there the way to do temp DB instead of physical temp table because I run dynamic every two week and don't want to maintain temp table.

6

There are 2 ways you can provide us data

  1. Direct access to your sql server
  2. Sample data by means of sql script

Sample script will help us help you bcs we do not have direct access to your sql sever

When you provide sample data it is usually in the form of

Create table #sample

Insert into #sample

So that we can emulate your data in our sql sever. So when people use #sample to answer your queationits so that we have sample of your data not because you have to it that way

1 Like