SQL Queries වගයක් මචං ල

[Thouson]

මචං ල table 3ක් තියෙනව මෙහෙම

A list of the most/least popular properties in a particular year (per property type)
A list of CSR who handle the most/least appointments in a particular month and year
A list of potential tenants who has never made an appointment to inspect properties
Display name,age and gender of the oldest female potential tenant(s) as well as the youngest male potential tenant(s)

මෙන්න මේ ප්‍රශ්න වලට queries ටික පුළුවන් නම් දාපල්ලකො ලොකු උදව්වක්

 

[aluthKolla0001]

Me table wala names tikai thiyanwanam er ekai danna

 

[NRTG]

BUMP BRO.......

 

[Thouson]

Me table wala names tikai thiyanwanam er ekai danna

1. 'Property'
2.'Appointment'
3.'CSR'
4.'Tenant'

 

[@media]

Table wala names tika dapan. Magic danne ne api.

 

[Thouson]

Table wala names tika dapan. Magic danne ne api.

1. 'Property'
2.'Appointment'
3.'CSR'
4.'Tenant'
Pics දාපු පිළිවෙලට මචං

 

[djmaxtor]

puluwannam DB dump ekak share karapan.

 

[MihiCherub]

kammali

 

[eCloud]

A list of CSR who handle the most/least appointments in a particular month and year


Most Appointments For particular Year


select X.*,Y.csrName
from (select max(apptcount) as totalcount ,csrid from (
select a.csrid,row_num () over (partition By b.csrid order by b.csrid) as apptcount
from csr a,appoinment b
where a.csrid = b.csrid
and b. Adate between 01-JAN-2020 and 31-DEC-2020) X , (select a.csrid,a.csrName,
row_num () over (partition By b.csrid order by b.csrid) as apptcount
from csr a,appoinment b
where a.csrid = b.csrid
and b. Adate between 01-JAN-2020 and 31-DEC-2020) Y
where X.csrid=y.csrid
and x.totalcount = y.apptcount ;




DB dumps නැති නිසා උඹේ DB eke execute කරලා බලපන්.මම SQL වැඩ්ඩෙක් නෙවෙයි.ආතල් එකට දැම්මේ .

 

[ThisaraMalintha]

run karala balanna hari yaida kiyala. mama wadiya sql danne na e hinda me queries wala quality eka, performance gana no idea

mewala max, min, years maru karanna ona widihata

meka diha balwuama mata therenneth na

// 1
select *
from property as p,
(
select max(counts.c) as maxCount, PType as PT
from Property as p
inner join (
            select count(*) as c, PropertyId as pId
            from appointment 
            group by PropertyId) as counts on p.PropertyId = counts.pId
group by PType) as groupCounts,
(select count(*) as c, PropertyId as pId
            from appointment 
            group by PropertyId) as propertyCounts
where p.PType = groupCounts.PT and p.PropertyId = propertyCounts.pId and propertyCounts.c = groupCounts.maxCount

// 2

DECLARE @MAXCOUNT int;
SET @MAXCOUNT = (select max(counts.c) as m
                from (select count(*) as c
                        from (select * from appointment where Year(Adate) = '2020') as ya
                group by ya.CSRId) as counts);
select *
from CSR as c
inner join
(select count(*) as c, CSRId
from (select * from Appointment where YEAR(ADate) = '2020') as ya
group by ya.CSRId) as counts on counts.CSRId = c.CSRID
where counts.c = @MAXCOUNT

// 3
select *
from Tenant
where TenantId not in (select TenantId from Appointment)

// 4
DECLARE @MAXAGE int;
SET @MaxAge = (select max(age) from (select * from Tenant where tenantId not in(select tenantId from appointment) and gender = 'F'));
                                   
select *
from tenant
where tenantId not in (select tenantId from appointment) and age = @MaxAge

 

[Thouson]

Ado thanks machan la eCLoud, ThisaraMalintha. Vade goda dagatta machan kohoma hari.