July 27, 2014 at 2:46 pm
Hi all,
I'm hoping someone can help as I'm clueless 🙂
I'm looking for some TSQL script that filters the method field by A, except for the latest date which I need to filter by B. The script is run each month so the latest date varies each run.
Date method
01/05/2013 A
01/05/2013 B
01/05/2013 C
01/05/2013 D
02/05/2013 A
02/05/2013 B
02/05/2013 C
02/05/2013 D
03/05/2013 A
03/05/2013 B
03/05/2013 C
03/05/2013 D
07/08/2014 A
07/08/2014 B
05/09/2014 A
05/09/2014 B
Many thanks
July 27, 2014 at 3:00 pm
jonathan_blac15 (7/27/2014)
Hi all,I'm hoping someone can help as I'm clueless 🙂
I'm looking for some TSQL script that filters the method field by A, except for the latest date which I need to filter by B. The script is run each month so the latest date varies each run.
Date method
01/05/2013 A
01/05/2013 B
01/05/2013 C
01/05/2013 D
02/05/2013 A
02/05/2013 B
02/05/2013 C
02/05/2013 D
03/05/2013 A
03/05/2013 B
03/05/2013 C
03/05/2013 D
07/08/2014 A
07/08/2014 B
05/09/2014 A
05/09/2014 B
Many thanks
Based on the data above what is your expected results?
July 27, 2014 at 3:13 pm
Hi,
Thanks for the reply.
I'm hoping to give any date where the method field equals A, except for the latest date for which I want the date where the method field equals B.
Kind regards,
J
July 27, 2014 at 3:15 pm
The anticipated results would be...
Date method
01/05/2013 A
02/05/2013 A
03/05/2013 A
07/08/2014 A
05/09/2014 B
Kind regards,
J
July 27, 2014 at 4:23 pm
First, please pay attention to how I setup the test data. This is something you should do for us. I found it easier for this solution to use a table variable. You could use a temp table (#TestData for instance) or a permanent table (dbo.TestData for another example).
Second, notice when I wrote the insert statement for the test data I used the ISO standard yyyy-mm-dd for the dates. I also could have used yyyymmdd. Both of these are preferable as everyone should recognize this format.
Here is my shot at a solution:
/*
-- Format of dates is not stated based on anticipated results posted later
-- making the assumption that the following dates are in dd/mm/yyyy format.
Date method
01/05/2013 A
01/05/2013 B
01/05/2013 C
01/05/2013 D
02/05/2013 A
02/05/2013 B
02/05/2013 C
02/05/2013 D
03/05/2013 A
03/05/2013 B
03/05/2013 C
03/05/2013 D
07/08/2014 A
07/08/2014 B
05/09/2014 A
05/09/2014 B
*/
declare @TestData table (
MethodDate date,
Method char(1)
);
insert into @TestData
values ('2013-05-01','A'),
('2013-05-01','B'),
('2013-05-01','C'),
('2013-05-01','D'),
('2013-05-02','A'),
('2013-05-02','B'),
('2013-05-02','C'),
('2013-05-02','B'),
('2013-05-03','A'),
('2013-05-03','B'),
('2013-05-03','C'),
('2013-05-03','D'),
('2013-08-07','A'),
('2013-08-07','B'),
('2013-09-05','A'),
('2013-09-05','B');
select
td.MethodDate,
td.Method
from
@TestData td
cross apply (select max(MethodDate) MethodDate, Method from @TestData where Method = 'B' group by Method) ca
where
(td.Method = ca.Method and td.MethodDate = ca.MethodDate) or
(td.Method = 'A' and td.MethodDate < ca.MethodDate);
July 28, 2014 at 2:03 am
You can do this as below (note: Check for the performance issues before implementing)
declare @date datetime
select @date=MAX(date) from #mytable where method='B'
;with CTE
as
(
select [DATE],method from #mytable where method in ('A','B') )
select [DATE],method from cte where (method='A' and [DATE]<>@date) or (date=@date and method='B')
July 28, 2014 at 6:36 am
Sindhu Ravi (7/28/2014)
You can do this as below (note: Check for the performance issues before implementing)declare @date datetime
select @date=MAX(date) from #mytable where method='B'
;with CTE
as
(
select [DATE],method from #mytable where method in ('A','B') )
select [DATE],method from cte where (method='A' and [DATE]<>@date) or (date=@date and method='B')
This doesn't meet the requirements based on the expected results. If you add a record with Method = 'A' with a date greater the the max date for a record with Method = 'B' the 'A' record with the later date will be displayed.
A pet peeve, but the semicolon in front of the WITH belongs at the end of the preceding statement. Semicolons are statement terminators, not statement begininators.
You may want to start getting used to using them properly as eventually they may become mandatory. The MERGE statement already requires that a semicolon be used as a terminator.
July 28, 2014 at 3:38 pm
select
MethodDate,
Method
from
@TestData
where
Method = 'A'
UNION ALL
select top 1
MethodDate,
Method
from
@TestData
where
Method = 'B'
order by
MethodDate DESC
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply