Min date

Transact-SQL
1

Hello - Need assistances with this small query, I am trying to run a query on a date field where I want to pull visit_id for only those customers which have minimum date > 2 days.

SELECT visit_id FROM visit_table
WHERE MIN(archivedate) > DATEADD(DD, -1, GETDATE())

Thank you

2 
SELECT visit_id
FROM visit_table
GROUP BY visit_id
HAVING MIN(archivedate) > DATEADD(DD, -1, GETDATE())
3 
SELECT visit_id FROM visit_table
GROUP BY visit_id
HAVING MIN(archivedate) > DATEADD(DD, -1, GETDATE())

Even though the query above may parse and run giving some results, I am not sure whether that will implement the logic that you actually want. E.g., is it one day or two days you are looking for? Do you want to consider time of the archivedate (if it has time portion) when doing the filtering? If this does not give you what you want, can you post some representative sample data along with the corresponding result that you are trying to get?

4

Thank you both -

So this is the query I am trying to run, it is giving me the results what I want, but it looks like it is not very efficient. It takes a lot of time when I am trying to run this query.

Is there a way to tweak this query and make it better?. basically -1 is just the start I want atleast -5 or -10. Based on the time it is taking right now, I can't use those dates it will impact the database performance.

SELECT visit_table.bill_no, visit_table.admission_date FROM visit_table
WHERE visit_table.visit_id IN (
SELECT document_table.visit_id
FROM visit_table LEFT JOIN service_table ON visit_table.visit_id=service_table._fk_visit
LEFT JOIN document_table ON visit_table.visit_id = document_table.visit_id
WHERE service_table.visit_admit_service IN ('WML', 'LSP', 'CIM', 'MLV', 'COS', 'KGO', 'APO')
group by document_table.visit_id
HAVING SUM(CASE WHEN document_table.doctype = 'CUSTDOC' THEN 1 ELSE 0 END) = 0 AND
MIN(document_table.archive_date) > DATEADD(DD, -1, GETDATE())
)

Thank you

5

Just to be crystal clear on the point that JamesK raised

SELECT	GetDate() AS CurrentTime,
	DATEADD(DD, -1, GETDATE()) AS Yesterday

gives me, at the moment in time when I ran it:

CurrentTime               Yesterday
-----------------------   ----------------------- 
2015-09-03 16:56:28.703   2015-09-02 16:56:28.703

so your selection on MIN(document_table.archive_date) would be compared to this-exact-time-yesterday - is that what you want? or do you want "since Yesterday first-thing-in-the-morning" ?

6 
SELECT visit_table.bill_no, visit_table.admission_date 
FROM visit_table
WHERE visit_table.visit_id IN (
    SELECT document_table.visit_id
    FROM document_table
    INNER JOIN (
        SELECT DISTINCT fkvisit
        FROM service_table
        WHERE service_table.visit_admit_service IN ('WML', 'LSP', 'CIM', 'MLV', 'COS', 'KGO', 'APO')
    ) AS service_table_matches ON
        service_table_matches.fkvisit = document_table.visit_id
    GROUP BY document_table.visit_id
    HAVING SUM(CASE WHEN document_table.doctype = 'CUSTDOC' THEN 1 ELSE 0 END) = 0 AND 
        MIN(document_table.archive_date) > DATEADD(DD, -1, GETDATE())
)