SQL query to extract data for claim against Royal Mail
Stuart Cameron
said
over 6 years ago
This is awesome, I am writing a data mine to lookup ALL tracked items against the RM website, including the new 2d ones. It will then compile and highlight all late deliveries in the Christmas period I will then be emailing this to the Royal Mai Chief Exec "moya.greene@royalmail.com"
how do you implement this in excel?
thanks in advance :)
Sanyam Kaushik
Hi Guys
a few of my items were lost and i found quite daunting to populate royal mail multi claims excel sheet quite daunting.
so, I created a sql to extract the data, which can be pasted into excel sheet as it is and sharing it for everyone now:
If you have order numbers handy:
select
c.cCountry,
o.Source,
o.pkOrderID,
o.ReferenceNum,
o.nOrderId,
p.PostalServiceName,
o.PostalTrackingNumber,
'Loss' as ClaimType,
' ' as Title,
Substring(o.cFullName, 1,Charindex(' ', o.cFullName,-1)) as FirstName,
Substring(o.cFullName, Charindex(' ', o.cFullName)+1, LEN(o.cFullName)) as Surname,
'' as Company,
o.cEmailAddress,
o.Address1,
o.Address2,
o.Address3,
o.Town,
o.cPostCode,
o.dDispatchBy,
cast(oi.nqty as varchar(3)) + ' x ' + oi.ChannelSKU as contentDescription,
' ' as postagepaid,
o.fTotalCharge
from [Order] o, ListCountries c, PostalServices p, OrderItem oi
where o.fkCountryId=c.pkCountryId
and p.pkPostalServiceId = o.fkPostalServiceId
and o.pkOrderID = oi.fkOrderID
and o.nOrderId in (enterordernumbersseperatedbycommas)
if you have tracking numbers handy:
select
c.cCountry,
o.Source,
o.pkOrderID,
o.ReferenceNum,
o.nOrderId,
p.PostalServiceName,
o.PostalTrackingNumber,
'Loss' as ClaimType,
' ' as Title,
Substring(o.cFullName, 1,Charindex(' ', o.cFullName,-1)) as FirstName,
Substring(o.cFullName, Charindex(' ', o.cFullName)+1, LEN(o.cFullName)) as Surname,
'' as Company,
o.cEmailAddress,
o.Address1,
o.Address2,
o.Address3,
o.Town,
o.cPostCode,
o.dDispatchBy,
cast(oi.nqty as varchar(3)) + ' x ' + oi.ChannelSKU as contentDescription,
' ' as postagepaid,
o.fTotalCharge
from [Order] o, ListCountries c, PostalServices p, OrderItem oi
where o.fkCountryId=c.pkCountryId
and p.pkPostalServiceId = o.fkPostalServiceId
and o.pkOrderID = oi.fkOrderID
and o.PostalTrackingNumber in ('trackingnoseperatedbycommas','2','etc')
paste column 5 onwards directly into excel sheet and im sure it will save your effort :)