May 7, 2010 at 7:40 am
I use this query below that collates all records for that current day, I need to be able to use the same select criteria and run a query on any given day and find all booking_dates for the current month. So if I run this query on the 1st or 10th or 31st it shows me all records for everyday in that month that has past.
How can I do this?
SELECT ASV.order_no AS OrderNo
,P.prop_ref AS UPRN
,LTRIM(ISNULL(P.suffix, '') + ' ' + ISNULL(P.number, '') + ' ' + REPLACE(P.address_1, ',', ' ')) AS Address
,ISNULL(P.postcode, '') AS Postcode
,ASV.receive_date AS ASVReceivedDate
,ASV.booking_date AS ApptDate
,ASV.completion_date AS CompletedDate
,ASV.status AS ASVStatus
,QLSS.status AS ASVJobStatus
,ASV.access_details AS AccessDetails
,ASV.comments AS Comments
,E.engineer_displayname AS Engineer
FROM dbo.property P
INNER JOIN dbo.servicing_jobs ASV ON P.prop_seql = ASV.prop_seql
INNER JOIN dbo.contracts C ON P.contract_id = C.id
INNER JOIN dbo.engineers E ON ASV.engineer = E.id
LEFT OUTER JOIN dbo.quicklist_service_status_sub QLSSS ON ASV.status_sub = QLSSS.id
LEFT OUTER JOIN dbo.quicklist_service_status QLSS ON ASV.status = QLSS.id
WHERE C.id NOT IN (74, 75)
AND (DATEADD(DD, DATEDIFF(DD, 0, ASV.booking_date), 0) = DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0))
AND ASV.completion_date IS NOT NULL
May 7, 2010 at 8:00 am
jez.lisle (5/7/2010)
WHERE C.id NOT IN (74, 75)
AND (DATEADD(DD, DATEDIFF(DD, 0, ASV.booking_date), 0) = DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0))
AND ASV.completion_date IS NOT NULL
AND ASV.booking_date > DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 0)) -- For current Day
AND ASV.booking_date > DATEADD(Month, DATEDIFF(Month, 0, GETDATE()), 0)) -- For current Month
-- Cory
May 8, 2010 at 4:11 am
Excellent thanks for that 🙂
Its a bit simple really, was having a bad day yesterday 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply