Hi,
I am looking for some help with two queries using the following table/data. It would be great to see the most efficient way to achieve these.
Queries
1: Show all homes with rent above the average rent value for their Company
2: Show the number of homes registered 0-3 months, 4-6 months, 6-12 months, 1- 2 years, and 2+ years ago
Thank you for looking.
CREATE SCHEMA homes;
GO
CREATE TABLE homes.Companies (CompanyId INT, CompanyName NVARCHAR(50));
INSERT INTO homes.Companies VALUES
(1,'Company A')
,(2,'Company B')
,(3,'Company C')
,(4,'Company D')
CREATE TABLE homes.Branches(BranchId INT, CompanyId INT, BranchName NVARCHAR(20));
INSERT INTO homes.Branches VALUES
(1,1,'Liverpool')
,(2,1,'Blackpool')
,(3,1,'Stockport')
,(4,2,'Durham')
,(5,2,'Oxford')
,(6,2,'Cambridge')
,(7,2,'Manchester')
,(8,3,'Newcastle')
,(9,3,'Hull')
,(10,3,'York')
,(11,4,'Birmingham')
,(12,4,'Stoke on Trent')
-- DROP TABLE homes.Properties;
CREATE TABLE homes.Properties (PropertyId INT, BranchId INT, Address NVARCHAR(200), Rent INT, RegisteredOn DATETIME);
INSERT INTO homes.Properties VALUES
(1,1,'12a, Station Street, Liverpool',500,CAST('2019-12-31' AS DATETIME))
,(2,1,'12c, Station Street, Liverpool',475,CAST('2021-06-15' AS DATETIME))
,(3,3,'39, Main Road, Stockport',410,CAST('2022-09-30' AS DATETIME))
,(4,3,'12, Dovetrees, Manchester',700,CAST('2022-08-19' AS DATETIME))
,(5,5,'16, St Giles, Oxford',825,CAST('2023-02-28' AS DATETIME))
,(6,5,'32, George Street, Oxford',1100,CAST('2022-04-01' AS DATETIME))
,(7,5,'16, Main Road, Eynsham',680,CAST('2021-03-24' AS DATETIME))
,(8,6,'3b, University Road, Cambridge',960,CAST('2022-01-14' AS DATETIME))
,(9,7,'98, Heaton Road, Manchester',795,CAST('2022-09-07' AS DATETIME))
,(10,8,'64, St James Park, Newcastle',550,CAST('2022-11-26' AS DATETIME))
,(11,8,'47, St James Park, Newcastle',630,CAST('2023-01-16' AS DATETIME))
,(12,10,'6b, Station Lane, York',520,CAST('2022-07-19' AS DATETIME))
,(13,10,'24, Newcastle Road, York',510,CAST('2022-03-15' AS DATETIME))
,(14,11,'64, Bull Ring, Birmingham',1025,CAST('2022-12-03' AS DATETIME))
,(15,12,'Willowmead, Pottery Lane, Stoke on Trent',765,CAST('2022-05-12' AS DATETIME))
May 29, 2023 at 8:52 pm
Homework, school assignment, ... try chat gpt
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 30, 2023 at 12:52 pm
Here is a formatted version. Note that all of your
CAST ('YYYY-MM-DD' AS DATETIME)
statements can be simplified to 'YYYYMMDD'. As suggested above, this looks like homework to me.
CREATE SCHEMA homes;
GO
CREATE TABLE homes.Companies
(
CompanyId INT
,CompanyName NVARCHAR(50)
);
INSERT INTO homes.Companies
VALUES
(1, 'Company A')
,(2, 'Company B')
,(3, 'Company C')
,(4, 'Company D');
CREATE TABLE homes.Branches
(
BranchId INT
,CompanyId INT
,BranchName NVARCHAR(20)
);
INSERT INTO homes.Branches
VALUES
(1, 1, 'Liverpool')
,(2, 1, 'Blackpool')
,(3, 1, 'Stockport')
,(4, 2, 'Durham')
,(5, 2, 'Oxford')
,(6, 2, 'Cambridge')
,(7, 2, 'Manchester')
,(8, 3, 'Newcastle')
,(9, 3, 'Hull')
,(10, 3, 'York')
,(11, 4, 'Birmingham')
,(12, 4, 'Stoke on Trent');
-- DROP TABLE homes.Properties;
CREATE TABLE homes.Properties
(
PropertyId INT
,BranchId INT
,Address NVARCHAR(200)
,Rent INT
,RegisteredOn DATETIME
);
INSERT INTO homes.Properties
VALUES
(1, 1, '12a, Station Street, Liverpool', 500, CAST ('2019-12-31' AS DATETIME))
,(2, 1, '12c, Station Street, Liverpool', 475, CAST ('2021-06-15' AS DATETIME))
,(3, 3, '39, Main Road, Stockport', 410, CAST ('2022-09-30' AS DATETIME))
,(4, 3, '12, Dovetrees, Manchester', 700, CAST ('2022-08-19' AS DATETIME))
,(5, 5, '16, St Giles, Oxford', 825, CAST ('2023-02-28' AS DATETIME))
,(6, 5, '32, George Street, Oxford', 1100, CAST ('2022-04-01' AS DATETIME))
,(7, 5, '16, Main Road, Eynsham', 680, CAST ('2021-03-24' AS DATETIME))
,(8, 6, '3b, University Road, Cambridge', 960, CAST ('2022-01-14' AS DATETIME))
,(9, 7, '98, Heaton Road, Manchester', 795, CAST ('2022-09-07' AS DATETIME))
,(10, 8, '64, St James Park, Newcastle', 550, CAST ('2022-11-26' AS DATETIME))
,(11, 8, '47, St James Park, Newcastle', 630, CAST ('2023-01-16' AS DATETIME))
,(12, 10, '6b, Station Lane, York', 520, CAST ('2022-07-19' AS DATETIME))
,(13, 10, '24, Newcastle Road, York', 510, CAST ('2022-03-15' AS DATETIME))
,(14, 11, '64, Bull Ring, Birmingham', 1025, CAST ('2022-12-03' AS DATETIME))
,(15, 12, 'Willowmead, Pottery Lane, Stoke on Trent', 765, CAST ('2022-05-12' AS DATETIME));
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 30, 2023 at 3:05 pm
You may get better replies if you show what you have tried already, and what part is causing problems.
Expecting the entire solution from scratch seems a bit much.
May 30, 2023 at 3:10 pm
Do you need to convert the dates ? Isn't this OK ?
INSERT INTO homes.Properties
VALUES
(1, 1, '12a, Station Street, Liverpool', 500, '2019-12-31' )
,(2, 1, '12c, Station Street, Liverpool', 475, '2021-06-15' )
,(3, 3, '39, Main Road, Stockport', 410, '2022-09-30' )
,(4, 3, '12, Dovetrees, Manchester', 700, '2022-08-19' )
May 30, 2023 at 7:13 pm
Do you need to convert the dates ? Isn't this OK ?
INSERT INTO homes.Properties
VALUES
(1, 1, '12a, Station Street, Liverpool', 500, '2019-12-31' )
,(2, 1, '12c, Station Street, Liverpool', 475, '2021-06-15' )
,(3, 3, '39, Main Road, Stockport', 410, '2022-09-30' )
,(4, 3, '12, Dovetrees, Manchester', 700, '2022-08-19' )
Looking at the provided data - I assume they are using a British English system, and if so - then no you cannot use '2019-12-31' for a datetime data type. If the system is not using British English then it will work but it isn't safe. In fact, the original code isn't safe using CAST either.
Changing it to '20191231' would be safe - as then the format will be correctly converted as YYYYMMDD and is not language dependent.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
For the first query ChatGPT-4 generated
WITH CompanyAverageRent AS (
SELECT c.CompanyId, AVG(p.Rent) AS AverageRent
FROM #Companies c
JOIN #Branches b ON c.CompanyId = b.CompanyId
JOIN #Properties p ON b.BranchId = p.BranchId
GROUP BY c.CompanyId
)
SELECT p.PropertyId, p.Address, p.Rent, c.CompanyName
FROM #Properties p
JOIN #Branches b ON p.BranchId = b.BranchId
JOIN #Companies c ON b.CompanyId = c.CompanyId
JOIN CompanyAverageRent car ON c.CompanyId = car.CompanyId
WHERE p.Rent > car.AverageRent
ORDER BY c.CompanyName, p.Rent DESC;
Imo it's an ok query. The CTE is not really necessary imo
select c.CompanyName, calc.avg_rent, above.*
from #Companies c
cross apply (select avg(p.rent)
from #branches b
join #Properties p on b.BranchId = p.BranchId
where b.CompanyId=c.CompanyId) calc(avg_rent)
cross apply (select p.PropertyId, p.Address, p.Rent
from #branches b
join #Properties p on b.BranchId = p.BranchId
where b.CompanyId=c.CompanyId
and p.Rent>calc.avg_rent) above(PropertyId, Address, Rent)
order by c.CompanyName, above.Rent desc;
For the second query ChatGPT-4 generated
DECLARE @CurrentDate DATETIME = GETDATE();
WITH RegistrationPeriods AS (
SELECT PropertyId, DATEDIFF(MONTH, RegisteredOn, @CurrentDate) ddif,
CASE
WHEN DATEDIFF(MONTH, RegisteredOn, @CurrentDate) BETWEEN 0 AND 3 THEN '0-3 months'
WHEN DATEDIFF(MONTH, RegisteredOn, @CurrentDate) BETWEEN 4 AND 6 THEN '4-6 months'
WHEN DATEDIFF(MONTH, RegisteredOn, @CurrentDate) BETWEEN 7 AND 12 THEN '6-12 months'
WHEN DATEDIFF(YEAR, RegisteredOn, @CurrentDate) BETWEEN 1 AND 2 THEN '1-2 years'
ELSE '2+ years'
END AS RegistrationPeriod
FROM #Properties
)
SELECT RegistrationPeriod, COUNT(PropertyId) AS NumberOfHomes
FROM RegistrationPeriods
GROUP BY RegistrationPeriod
ORDER BY MIN(PropertyId);
The variable declaration seems unnecessary and the CTE is unnecessary. Mixing different date parts within DATEDIFF in the CASE expression and then using GROUP BY on the result seems potentially problematic imo. The ORDER BY MIN(PropertyId) is useless and possibly wrong.
Maybe something like this
select reg.reg_period, count(p.PropertyId) AS NumberOfHomes
from #Properties p
cross apply (values (datediff(month, p.RegisteredOn, getdate()))) v(mo_diff)
cross apply (values (case when v.mo_diff between 0 and 3 then '01 0-3 months'
when v.mo_diff between 4 and 6 then '02 4-6 months'
when v.mo_diff between 7 and 12 then '03 7-12 months'
when v.mo_diff between 13 and 24 then '04 13-24 months'
else '05 25+ months' end)) reg(reg_period)
group by reg.reg_period
order by reg.reg_period;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 31, 2023 at 9:00 am
Thanks Steve, That is a great reply. I haven't tried out ChatGPT. Really appreciate your help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply