Avatar of Raahaugen
Raahaugen
Flag for Denmark asked on

SQL Query --> Only records where...

Hey Experts,

I have the following query, where I would like to include only one instance for every customernumber, namely the one with the earliest "bookingTime".

How do I extend the query to include this requirement?

Best regards, Raahaugen

SQL Query:

SELECT
             M.Id
            ,M.RebookedMeetingId
            ,M.BookingTime
            ,M.StartTime
            ,M.EndTime
            ,M.Status
            ,M.MeetingPurpose
            ,MT.Name as MeetingType
            ,M.CreatedBy as Booker
            ,TA.Initials as TiedAgent
            ,C.Name as Center
            ,CUS.CustomerNumber
            ,ADR.ZipCode

FROM "MBW-DB"."dbo"."Meeting" M
            join "MBW-DB"."dbo"."TiedAgent" TA on M.TiedAgentId = TA.id
            join "MBW-DB"."dbo"."MeetingType" MT on M.meetingTypeId = MT.id
            join "MBW-DB"."dbo"."Center" C on TA.centerId = C.Id
            join "MBW-DB"."dbo"."Customer" CUS on M.customerId = CUS.Id
            join "MBW-DB"."dbo"."Address" ADR on M.addressId = ADR.Id
           
WHERE M.MeetingPurpose = 'SalesMeeting'

order by BookingTime asc
Microsoft ExcelSQL

Avatar of undefined
Last Comment
Raahaugen

8/22/2022 - Mon
Randy Downs

Try select distinct instead of select

SELECT DISTINCT
             M.Id
            ,M.RebookedMeetingId
            ,M.BookingTime
            ,M.StartTime
            ,M.EndTime
            ,M.Status
            ,M.MeetingPurpose
            ,MT.Name as MeetingType
            ,M.CreatedBy as Booker
            ,TA.Initials as TiedAgent
            ,C.Name as Center
            ,CUS.CustomerNumber
            ,ADR.ZipCode
Olaf Doschke

Don't know whther Execel support subqueries, but tht would solve it:

WHERE M.customerId,M.BookingTime IN
   (Select customerId, Min(BookingTime) FROM "MBW-DB"."dbo"."Meeting"
   Group By customerId WHERE MeetingPurpose = 'SalesMeeting')

Bye, Olaf.
Lowfatspread

you are connecting to an external database?
what is its dbms?

you either need to make use of the OVER windowing clause if your version of sql  supports it
or use a subquery/join to obtain the desired result....

however the row_number() Over technique is simplest if multiple rows could exist with the same latest "timestamp"
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Lowfatspread

what is the reationship between customerid and customer_number?

1:1
or 1:M ?
Lowfatspread

an example using row_number() over for Sql server (from sql 2005 onwards)

SELECT
             M.Id
            ,M.RebookedMeetingId
            ,M.BookingTime
            ,M.StartTime
            ,M.EndTime
            ,M.Status
            ,M.MeetingPurpose
            ,MT.Name as MeetingType
            ,M.CreatedBy as Booker
            ,TA.Initials as TiedAgent
            ,C.Name as Center
            ,CUS.CustomerNumber
            ,ADR.ZipCode
from (
SELECT
             M.Id
            ,M.RebookedMeetingId
            ,M.BookingTime
            ,M.StartTime
            ,M.EndTime
            ,M.Status
            ,M.MeetingPurpose
            ,MT.Name as MeetingType
            ,M.CreatedBy as Booker
            ,TA.Initials as TiedAgent
            ,C.Name as Center
            ,CUS.CustomerNumber
            ,ADR.ZipCode
   ,row_number() over (partition by cus.customernumber 
                        order by m.bookingtime asc) as rn

FROM "MBW-DB"."dbo"."Meeting" M 
            join "MBW-DB"."dbo"."TiedAgent" TA on M.TiedAgentId = TA.id
            join "MBW-DB"."dbo"."MeetingType" MT on M.meetingTypeId = MT.id
            join "MBW-DB"."dbo"."Center" C on TA.centerId = C.Id
            join "MBW-DB"."dbo"."Customer" CUS on M.customerId = CUS.Id
            join "MBW-DB"."dbo"."Address" ADR on M.addressId = ADR.Id
           
WHERE M.MeetingPurpose = 'SalesMeeting'
) as x
where x.rn=1
order by BookingTime asc

Open in new window

Lowfatspread

sorry forgot to resolve the aliases...

SELECT Id
            ,RebookedMeetingId
            ,BookingTime
            ,StartTime
            ,EndTime
            ,Status
            ,MeetingPurpose
            , MeetingType
            , Booker
            , TiedAgent
            , Center
            ,CustomerNumber
            ,ZipCode
            
from (
SELECT M.Id
            ,M.RebookedMeetingId
            ,M.BookingTime
            ,M.StartTime
            ,M.EndTime
            ,M.Status
            ,M.MeetingPurpose
            ,MT.Name as MeetingType
            ,M.CreatedBy as Booker
            ,TA.Initials as TiedAgent
            ,C.Name as Center
            ,CUS.CustomerNumber
            ,ADR.ZipCode
            
   ,row_number() over (partition by cus.customernumber 
                        order by m.bookingtime asc) as rn

FROM "MBW-DB"."dbo"."Meeting" M 
            join "MBW-DB"."dbo"."TiedAgent" TA on M.TiedAgentId = TA.id
            join "MBW-DB"."dbo"."MeetingType" MT on M.meetingTypeId = MT.id
            join "MBW-DB"."dbo"."Center" C on TA.centerId = C.Id
            join "MBW-DB"."dbo"."Customer" CUS on M.customerId = CUS.Id
            join "MBW-DB"."dbo"."Address" ADR on M.addressId = ADR.Id
           
WHERE M.MeetingPurpose = 'SalesMeeting'
) as x
where x.rn=1
order by BookingTime asc

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Lowfatspread

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Raahaugen

ASKER
Hey!

I asked our developer, who simply told me that I should go for every row in wich rebookedMeeting is 0. So I did - and you guys had no way to know that!!

Anyways your solutions inspired me to do some other queries, so you absolutely desserve some points.

I hope this will work out?

Best regards Raahaugen