CODEWITHSUNDEEP
sql-servert-sql
JadedEric
JadedEric

Reputation: 2083

Multiple result sets excluding column

I have a collection of entries in a table, table is joined with another table and together I need to return a resultset excluding entries by a particular date value.

Table 1

lookup table

I need to return a collection of entries based on a query and find the value, along with a collection of other items where the date as per the screenshot is <= GETDATE()

Results should be

results

As you can see, the resultset returns all three of the General Worker items but should only return where the date time is <= GetDate().

I have tried various approaches, from the (SELECT .. (PARTITION)) approach to sub-value table results and none of them return the resultset I need.

I need all other rows intact with only the General Worker where date <= GETDATE() and I'm stuck.

UPDATE

My T-SQL statement before modifications:

SELECT
    T0.nContractID,
    T1.sJobCatNo,
    T1.nJobCatID,
    T1.sJobCatDesc,
    T1.nDeleted,
    T1.nAdminLocked,
    T1.nClientDefault,
    T1.nRateNT,
    CASE
        WHEN (T0.sDistributionCode IN ('Nails', 'Board'))
        THEN 1
        ELSE 0
    END AS 'ShowRate'
FROM
    [dbo].[Contract] AS T0
    INNER JOIN [dbo].[JobCategoryRates] AS T1 ON T1.nContractID = T0.nContractID
WHERE
    T1.nContractID = 200198
    AND T1.nDeleted = 0
ORDER BY
    T1.sJobCatDesc

UPDATE 2

I need the results to look like this:

enter image description here

UPDATE 3

Maybe this might help?

Table 1, for nContractID returns 19 results (3 of which are the same), the only distinct value is the dEndDate column should should be <= GETDATE(). I need to extract all values where dEndDate is null and dEndDate <= GETDATE(). Everything I've tried thus far brings back only one result, but logic in my head says I should have 17 results, if the dEndDate items >= GETDATE() is removed?

Upvotes: 1

Views: 140

Answers (1)

paparazzo
paparazzo

Reputation: 45096

Need to clean up the query and your thought process

If you want to debug dEndDate then include it in the output

All values where dEndDate is null and dEndDate <= GETDATE() is always false.
A value cannot be null and have a value.

In the default configuration a comparison to null is always false.
null <= 1/1/2000 is false
null >= 1/1/2000 is false
null = null is false

If you want null OR dEndDate <= GETDATE() then:

where dEndDate is null or dEndDate <= GETDATE()

Why would you expect this not to return one row?

dEndDate <= GETDATE()

Upvotes: 2

Related Questions