Display Output according to it's date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Display Output according to it's date

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-05-07 : 06:02:25

Hello all,

i do have two TABLE's namely AS "Bro1" AND "Tra1"

Bro1 OUTPUT

ID Group_Code Group_Change_Date
2122 29 10/04/2006
2122 26 01/03/2008

Tra1 OUTPUT

TId Modify_Date
2122 11/06/2007
2122 18/06/2007
2122 03/12/2007
2122 14/12/2007
2122 04/01/2008
2122 24/01/2008
2122 10/04/2008
2122 14/04/2008
2122 24/04/2008

Mine Requirement IS....

TId Modify_Date Group_Code
2122 11/06/2007 29
2122 18/06/2007 29
2122 03/12/2007 29
2122 14/12/2007 29
2122 04/01/2008 26
2122 24/01/2008 26
2122 10/04/2008 26
2122 14/04/2008 26
2122 24/04/2008 26

it means IF Modify_Date IS BETWEEN '10/04/2006 ' AND '01/03/2008'
THEN i need TO show it's Group_Code [29] in my output

Same way the Modify_Date >= '01/03/2008' then i need to show it's Group_Code [26] in my OUTPUT

I hope i am clear WITH my question.

Please help me in writing query....

Thanks
Prashant Hirani

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-07 : 06:30:43

[code]select *, (select top 1 Group_Code from Bro1 where Group_Change_Date < Modify_Date order by Group_Change_Date desc) as Group_Code from Tra1 a[/code]

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-07 : 06:31:30

2122 04/01/2008 26
2122 24/01/2008 26

Why are these not 29?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-05-07 : 07:03:23

Thanks for the reply.

ya it's 29.

Above Query will not work in my case.
i need all the group_code

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-07 : 07:10:42

quote:
Originally posted by hirani_prashant

Above Query will not work in my case.

It works for me...

-- Preparation
set dateformat dmy

-- Structure and Data
declare @Bro1 table (ID int, Group_Code int, Group_Change_Date datetime)
insert @Bro1
          select 2122, 29, '10/04/2006'
union all select 2122, 26, '01/03/2008'

declare @Tra1 table (TId int, Modify_Date datetime)
insert @Tra1
          select 2122, '11/06/2007'
union all select 2122, '18/06/2007'
union all select 2122, '03/12/2007'
union all select 2122, '14/12/2007'
union all select 2122, '04/01/2008'
union all select 2122, '24/01/2008'
union all select 2122, '10/04/2008'
union all select 2122, '14/04/2008'
union all select 2122, '24/04/2008'

-- Query
select *, (select top 1 Group_Code from @Bro1 where Group_Change_Date < Modify_Date order by Group_Change_Date desc) as Group_Code from @Tra1 a

/* Results
TId         Modify_Date             Group_Code
----------- ----------------------- -----------
2122        2007-06-11 00:00:00.000 29
2122        2007-06-18 00:00:00.000 29
2122        2007-12-03 00:00:00.000 29
2122        2007-12-14 00:00:00.000 29
2122        2008-01-04 00:00:00.000 29
2122        2008-01-24 00:00:00.000 29
2122        2008-04-10 00:00:00.000 26
2122        2008-04-14 00:00:00.000 26
2122        2008-04-24 00:00:00.000 26
*/

quote:
i need all the group_code

I don't know what this means. Is your actual requirement different to your initial example?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-09 : 09:34:12

I nearly forgot about this thread. I wrote this code the other day while I was waiting for your response. It's just 4 different ways of including Group_Change_Date in the above output. I don't know if this is what you want?

-- Preparation
set dateformat dmy

-- Structure and Data
declare @Bro1 table (ID int, Group_Code int, Group_Change_Date datetime)
insert @Bro1
          select 2122, 29, '10/04/2006'
union all select 2122, 26, '01/03/2008'

declare @Tra1 table (TId int, Modify_Date datetime)
insert @Tra1
          select 2122, '11/06/2007'
union all select 2122, '18/06/2007'
union all select 2122, '03/12/2007'
union all select 2122, '14/12/2007'
union all select 2122, '04/01/2008'
union all select 2122, '24/01/2008'
union all select 2122, '10/04/2008'
union all select 2122, '14/04/2008'
union all select 2122, '24/04/2008'

-- Query 1
select * from (
    select *, row_number() over (partition by a.Modify_Date order by b.Group_Change_Date desc) as RowNumber
    from @Tra1 a inner join @Bro1 b on b.Group_Change_Date < a.Modify_Date) a
where RowNumber = 1

-- Query 2
select *,
    (select top 1 Group_Code from @Bro1 where Group_Change_Date < Modify_Date order by Group_Change_Date desc) as Group_Code,
    (select top 1 Group_Change_Date from @Bro1 where Group_Change_Date < Modify_Date order by Group_Change_Date desc) as Group_Change_Date
from @Tra1 a

-- Query 3
select a.*, b.Group_Change_Date from (
    select *,
        (select top 1 Group_Code from @Bro1 where Group_Change_Date < Modify_Date order by Group_Change_Date desc) as Group_Code
    from @Tra1 a) a inner join @Bro1 b on a.Group_Code = b.Group_Code

-- Query 4
select a.*, b.Group_Code from (
    select *,
        (select top 1 Group_Change_Date from @Bro1 where Group_Change_Date < Modify_Date order by Group_Change_Date desc) as Group_Change_Date
    from @Tra1 a) a inner join @Bro1 b on a.Group_Change_Date = b.Group_Change_Date

/* Results (generally)
TId         Modify_Date             Group_Code  Group_Change_Date
----------- ----------------------- ----------- -----------------------
2122        2007-06-11 00:00:00.000 29          2006-04-10 00:00:00.000
2122        2007-06-18 00:00:00.000 29          2006-04-10 00:00:00.000
2122        2007-12-03 00:00:00.000 29          2006-04-10 00:00:00.000
2122        2007-12-14 00:00:00.000 29          2006-04-10 00:00:00.000
2122        2008-01-04 00:00:00.000 29          2006-04-10 00:00:00.000
2122        2008-01-24 00:00:00.000 29          2006-04-10 00:00:00.000
2122        2008-04-10 00:00:00.000 26          2008-03-01 00:00:00.000
2122        2008-04-14 00:00:00.000 26          2008-03-01 00:00:00.000
2122        2008-04-24 00:00:00.000 26          2008-03-01 00:00:00.000
*/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 10:17:21

[code]SELECT t.*,tmp.Group_Code
FROM Tra1 t
CROSS APPLY (SELECT Group_Code
FROM Bro1
WHERE ID=t.ID
AND Group_Change_Date=(
SELECT MAX(Group_Change_Date)
FROM Bro1
WHERE ID=t.ID
AND Group_Change_Date<t.Modify_Date))tmp[/code]

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-09 : 11:06:32

Nice one visakh

If you add a top 1, the performance is better...

SELECT * FROM @Tra1 a CROSS APPLY (SELECT TOP 1 * FROM @Bro1 WHERE ID = a.TId AND Group_Change_Date =
    (SELECT MAX(Group_Change_Date) FROM @Bro1 WHERE ID = a.TId AND Group_Change_Date < a.Modify_Date)) b

I also (trivially) modified this so it's a 5th way with the previous code I posted. Thanks!

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-09 : 11:43:56

Hirani,

You need to post these in T-SQL forum.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 12:13:15

quote:
Originally posted by RyanRandall

Nice one visakh

If you add a top 1, the performance is better...

SELECT * FROM @Tra1 a CROSS APPLY (SELECT TOP 1 * FROM @Bro1 WHERE ID = a.TId AND Group_Change_Date =
(SELECT MAX(Group_Change_Date) FROM @Bro1 WHERE ID = a.TId AND Group_Change_Date < a.Modify_Date)) b
I also (trivially) modified this so it's a 5th way with the previous code I posted. Thanks!

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Thanks Ryan

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-05-10 : 04:27:22

Hello All,

Thanks to all for reply.

Regards
Prashant Hirani

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources