Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSQL Script help for new user Expand / Collapse
Author
Message
Posted Sunday, July 27, 2014 2:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 27, 2014 3:24 PM
Points: 6, Visits: 10
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


  Post Attachments 
TSQL_Filter.txt (4 views, 1.15 KB)
Post #1596657
Posted Sunday, July 27, 2014 3:00 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:17 AM
Points: 20,739, Visits: 32,524
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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1596658
Posted Sunday, July 27, 2014 3:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 27, 2014 3:24 PM
Points: 6, Visits: 10
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

Post #1596661
Posted Sunday, July 27, 2014 3:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 27, 2014 3:24 PM
Points: 6, Visits: 10
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
Post #1596662
Posted Sunday, July 27, 2014 4:23 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:17 AM
Points: 20,739, Visits: 32,524
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);





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1596665
Posted Monday, July 28, 2014 2:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 4, 2014 1:04 AM
Points: 20, Visits: 51
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')
Post #1596717
Posted Monday, July 28, 2014 6:36 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:17 AM
Points: 20,739, Visits: 32,524
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1596808
Posted Monday, July 28, 2014 3:38 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:23 AM
Points: 156, Visits: 599
select
MethodDate,
Method
from
@TestData
where
Method = 'A'

UNION ALL

select top 1
MethodDate,
Method
from
@TestData
where
Method = 'B'
order by
MethodDate DESC




"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1597092
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse