I wondered whether something is possible and how I'd go about it.
I have a reference number on one table, and then in an audit trail I will have a description (something like 'Reference 12345 is complete') and a date.
I'm trying to link the date of the audit trail post [where it states complete] to the reference number of the other table (because this tables holds loads of other info I need).
I'm much better on Excel and I would simply do
Index(AuditTrailDate, match ("Reference "&ReferenceNumber&" is complete", AuditTrailDescription ,0))
to give me:
Col 1 Col 2
Reference Number AuditTrailDate
Is there a way of doing this in the query itself? I know I can do it as an expression in the table but for other reasons it'd be better to have it this way round instead.
e.g.
SELECT
Lookup("Reference "&ReferenceNumebr&" is Complete",AuditTrail.Description, AuditTrail.DateAdded)
If this doesn't make sense let me know and I'll try and send some more info.
So the audit trail table does not have a column that references back to the the table that has the reference number?
create table #sample_refs(referencenumber int)
create table #sample_audits(trailmix varchar(150))
insert into #sample_refs
select distinct column_id from sys.columns where column_id between 1 and 12
insert into #sample_audits
select 'Reference ' + cast(referencenumber as varchar(150)) +
' is Complete '
+ convert(varchar(10), dateadd(d,referencenumber, getdate()),101)
from #sample_refs
;with cte
as
(
select * ,
cast(REPLACE('<r>' + trailmix + '</r>', SPACE(1), '</r><r>') as xml) as buju
From #sample_audits
),
cte2 as
(
select buju.value('(/r)[2]', 'int') as referencenumber,
buju.value('(/r)[5]', 'date') as audit_Date
FROM cte
)
select *
from cte2 da
join #sample_refs sr on da.referencenumber = sr.referencenumber
drop table #sample_audits
drop table #sample_refs
I would not recommend the way you are tracking your audits. make a clean table with FK references and lookup table for statuses etc.
Annoyingly it does but it's at a higher level. So the reference the audit trail has is the overall order number, however the description then states individual product references.
i.e.
Order # Description Date/Time
123 Product 912 is packed 01/01/2018 01:00:00
123 Product 633 is packed 01/01/2018 01:01:00
123 Product 912 is sent 01/01/2018 05:00:00
etc.
I want to be able to link the product reference with the product table so I can say, if I really wanted to, that we send out the majority of our blue coloured products at 1am for example.Thanks for your help, what you've sent is not something I've done before but looks like it would do the job. Will let you know, when I get a chance to give it a go, how I get on. Thanks again
--sample tables
create table #audits(orderNumber int, Description varchar(100), OrderDate datetime)
create table #products(productNumber int, Description varchar(100))
--sample data
insert into #audits
select 123, 'Product 912 is packed', '01/01/2018 01:00:00' union
select 123, 'Product 633 is packed', '01/01/2018 01:01:00' union
select 123, 'Product 912 is sent', '01/01/2018 05:00:00'
insert into #products
select 912, 'Waffles with blueberries' union
select 633, 'Sausages'
--This section is what you can use to get what you need
;with cte
as
(
select * ,
cast(REPLACE('<r>' + Description + '</r>', SPACE(1), '</r><r>') as xml) as buju
From #audits
),
cte2 as
(
select buju.value('(/r)[2]', 'int') as productNumber,
OrderDate
FROM cte
)
select *
from cte2 da
join #products sr on da.productNumber = sr.productNumber
--clean up
drop table #audits
drop table #products