November 4, 2009 at 9:08 am
I have a query that I've written to find me all the properties on contract and then I want to find all the service's in the last year for this property, but I only want to bring the latest service in the last year.
This below is my SQL
How can I only bring back 1 completed service on each property?
SELECT
C.[name_short] AS [Area],
(SELECT COUNT(PROP.[prop_seql]) FROM dbo.contracts CON
INNER JOIN dbo.property PROP ON CON.[id] = PROP.[contract_id]
WHERE PROP.[status] = 1 AND CON.[name_short] = C.[name_short]
GROUP BY CON.[name_short]) AS [PropertyCount],
SUM(CASE WHEN ASV.[completion_date] IS NOT NULL
AND ASV.[completion_date] >= CONVERT(DATETIME,DATEDIFF(DAY, 365, GETDATE())) THEN
1 ELSE 0
END) AS [ValidCP12]
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]
WHERE C.[name_short] NOT LIKE 'Test'+'%'
AND P.[status] = 1
GROUP BY C.[name], C.[name_short], C.[id]
ORDER BY C.[name_short], C.[id]
November 4, 2009 at 9:13 am
It would help if you could provide the DDL (CREATE TABLE statements) for the tables, sample data (INSERT INTO <tablename> SELECT <values> UNION ALL ... ), expected results based on the sample data.
If you provide this info you will get much better assistance as well as tested code in return.
For assistance in this, please read the first article I reference in my signature block below regarding "Asking for help".
November 4, 2009 at 9:17 am
Lynn is right, with the DDL of the tables, sample data, and the expected output, people will jump in to try to answer you. It does not sound like a real tough problem, so you will most probably get a quick answer.
Cheers,
J-F
November 4, 2009 at 9:40 am
Attached is a copy of the tables, I will get some sample data shortly
November 4, 2009 at 9:50 am
Find attached sample data using this query
If you look at the prop_seql 60571 as an example you will see the dates of
2009-09-15 14:34:00 and 2008-11-18 14:23:00
In my original SQL I'm looking to return only the latest date so in this case I would only want to see 2009-09-15 14:34:00
SELECT DISTINCT P.contract_id, db_serv_job_no,
ASV.prop_seql, P.status, ASV.completion_date,
C.name_short
FROM dbo.contracts C INNER JOIN
dbo.property P ON C.id = P.contract_id INNER JOIN
dbo.servicing_jobs ASV ON P.prop_seql = ASV.prop_seql
GROUP BY P.contract_id, ASV.db_serv_job_no, ASV.prop_seql, P.status, ASV.booking_date,
C.name_short, ASV.completion_date
HAVING P.contract_id = 13
AND P.status = 1
AND MAX(ASV.completion_date) >= CONVERT(DATETIME,DATEDIFF(day, 365, GETDATE()))
ORDER BY ASV.prop_seql
November 4, 2009 at 11:12 am
You have provided sample data for only 1 table, and it is not in the ""insert into table_name select values"".
Can you repost the sample data, so it's usable by anyone who runs the script? It's easy for you by following the steps in the link Lynn has given you. It's a lot harder for us to just retype the entire data.
Cheers,
J-F
November 4, 2009 at 11:58 am
Since I have a great text editor at hand I partially fixed your data. I wasn't sure which table the data was for, so you will need to fix that part. But we also need data for the other table as well.
November 4, 2009 at 12:00 pm
Nice job Lynn, curious to know what you have used to fix the data?
Cheers,
J-F
November 4, 2009 at 12:05 pm
UltraEdit. I love the column mode. I can also do syntax highlighting in a variety of languages using it.
I actually use UltraEdit Studio, but UltraEdit is still at the heart of it.
November 4, 2009 at 12:07 pm
Nice, I heard it a few times, but never had the opportunity to use it. I will someday! 🙂
Cheers,
J-F
November 4, 2009 at 12:14 pm
A co-worker of mine at a previous employer is so hooked on it the UltraEdit is the first thing he installs on a new computer. He then hacks the system so that UltraEdit is called for everything the Windows would normally call NotePad.
I have to agree, it is an excellent product. You should look into it. It also has an interesting story behind it, IMHO.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply