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())
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?
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())
)
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" ?
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())
)