Column from another as parameter SQL SERVER

Question

I have 2 table like this:

Table_Work

Name      Date
===============
Andy      1 Jan
Andy      2 Jan
Andy      3 Jan
Ana       1 Jan
Ana       2 Jan
Ana       3 Jan
Ana       4 Jan

and Table_Salary

Name      Salary
=================
Andy       150
Ana        120

I want to use column Name and Salary from table_salary as a parameter. So, I can show a data like this:

Name     Salary_Got
===================
Andy        300
Ana         360

Table above show Salary_Got that calculate by (Salary x Number of Work). But I show only where work from 2 Jan. So, Andy only work for 2 days and Ana for 3 days.

Note: This is only illustrated problem. My problem more complex than this. The point is I cannot use JOIN because I need to calculate other row.

I hope anyone can help me to solve this problem. Thanks in advance.

This looks like an awful way to store your date information. Is the `Date` column actually text? Consider storing full date types instead of just text. If `Date` really is text, you will need some olympics to select a date range. — Tim Biegeleisen, Jan 18 '18 at 06:23
reading this may help: https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine — Md. Tazbir Ur Rahman Bhuiyan, Jan 18 '18 at 07:10

score 0 · Answer 1 · answered Jan 18 '18 at 06:24

0

Try something like that;

select ts.Name, (ts.Salary * ISNULL(tw.workcound,0)) from Table_Salary ts left join
(select Name, count(*) workcount from Table_Work where Date != '2017-01-02' group by Name) 
tw ON ts.Name = tw.Name

answered Jan 18 '18 at 06:24

lucky

12,734
4
24
46

score 0 · Answer 2 · answered Jan 18 '18 at 06:27

Try this:

select TW.Name, TW.DaysWorked * TS.Salary from (
    select [Name], count(*) [DaysWorked] from Table_Work TW
    where [Date] <> '1 Jan' --if you store date as text...
    -- where [Date] > '2018-01-01' --if you store Date as date
    group by [Name]
) TW join Table_Salary TS on TW.Name = TS.Name

score 0 · Answer 3 · answered Jan 18 '18 at 06:59

TRY THIS: You can use outer apply to find out the count of days for each employees then multiply it will salary of the respected employee

SELECT ts.Name, (ts.Salary*ISNULL(t.tot, 1)) Salary_Got 
FROM Table_Salary ts
OUTER APPLY(SELECT COUNT(*) tot 
            FROM Table_Work tw 
            WHERE tw.name = ts.name AND [Date] >= '2018-01-02') t

Column from another as parameter SQL SERVER

3 Answers3