November 13, 2011 at 7:24 pm
Pls find below table is input.
id todate bm sm
103 2011-05-10 00:00:00.000 ad xx
103 2011-05-10 00:00:00.000 yy xx
103 2011-07-10 00:00:00.000 ad xx
102 2011-03-10 00:00:00.000 nn kk
102 2011-05-10 00:00:00.000 rr mm
102 2011-07-10 00:00:00.000 nn kk
I want below menatation format form above table.
-----
id Fromdate todate bm sm
103 2011-04-01 00:00:00.000 2011-05-10 00:00:00.000 yy xx
103 2011-05-11 00:00:00.000 2011-07-10 00:00:00.000 ad xx
102 2011-04-01 00:00:00.000 2011-03-10 00:00:00.000 nn kk
102 2011-03-11 00:00:00.000 2011-05-10 00:00:00.000 rr mm
102 2011-05-11 00:00:00.000 2011-07-10 00:00:00.000 nn kk
November 13, 2011 at 7:48 pm
subbareddy542 (11/13/2011)
Pls find below table is input.id todate bm sm
103 2011-05-10 00:00:00.000 ad xx
103 2011-05-10 00:00:00.000 yy xx
103 2011-07-10 00:00:00.000 ad xx
102 2011-03-10 00:00:00.000 nn kk
102 2011-05-10 00:00:00.000 rr mm
102 2011-07-10 00:00:00.000 nn kk
I want below menatation format form above table.
-----
id Fromdate todate bm sm
103 2011-04-01 00:00:00.000 2011-05-10 00:00:00.000 yy xx
103 2011-05-11 00:00:00.000 2011-07-10 00:00:00.000 ad xx
102 2011-04-01 00:00:00.000 2011-03-10 00:00:00.000 nn kk
102 2011-03-11 00:00:00.000 2011-05-10 00:00:00.000 rr mm
102 2011-05-11 00:00:00.000 2011-07-10 00:00:00.000 nn kk
with no_ddl_provided as (select
id,
coalesce((select max(dateadd(dd,-1,todate))
from i_did_not_post_table_schema
idnpts where idnpts.id = i_did_not_post_table_schema.id
and idnpts.todate
< i_did_not_post_table_schema.todate), '2011-04-01') Fromdate,
todate,
bm,
sm
, row_number() over (partition by id, todate order by bm desc) rw
from i_did_not_post_table_schema )
select id, Fromdate, todate, bm, sm from no_ddl_provided where rw = 1
not tested.
November 13, 2011 at 9:35 pm
Thanks
It is wotking fine.
November 13, 2011 at 11:05 pm
when i am using above query in below in put but i am not getting excute out put
pls help me.
-------input---------
todate id SM BM
2011-05-25 00:00:00.000 R10321 Gaurav Mehta Mihir Vaidh
2011-06-20 00:00:00.000 R10321 Chintan Raval Arjun M Moorjani
2011-06-20 00:00:00.000 R10321 Gaurav Mehta Rajesh Parmar
2011-08-17 00:00:00.000 R10321 Chintan Raval Arjun M Moorjani
2011-10-10 00:00:00.000 R10321 Ravindran Ramachandran Arjun M Moorjani
2011-04-26 00:00:00.000 R15108 Ravindran Ramachandran Pankaj Kolhe
2011-04-26 00:00:00.000 R15108 Ravindran Ramachandran Ravi Kumar
2011-10-20 00:00:00.000 R15108 Ravindran Ramachandran Ravi Kumar
-------output
id Fromdate todate bm sm
R10321 2011-04-01 00:00:00.000 2011-05-25 00:00:00.000 Mihir Vaidh Gaurav Mehta
R10321 2011-05-26 00:00:00.000 2011-06-20 00:00:00.000 Rajesh Parmar Gaurav Mehta
R10321 2011-06-21 00:00:00.000 2011-08-17 00:00:00.000 Arjun M Moorjani Chintan Raval
R10321 2011-08-18 00:00:00.000 2011-10-10 00:00:00.000 Arjun M Moorjani Ravindran Ramachandran
R15108 2011-04-01 00:00:00.000 2011-04-26 00:00:00.000 Pankaj Kolhe Ravindran Ramachandran
R15108 2011-04-27 00:00:00.000 2011-10-20 00:00:00.000 Ravi Kumar Ravindran Ramachandran
November 14, 2011 at 7:59 am
You much more likely to get help from people if you post ddl, sample data (insert statements) and desired output based on your sample data. Take a look at the first link in my signature for best practices on posting questions to ensure you get tested code. Just remember we are all volunteers and would prefer to spend our time working on your solution instead of creating the environment to work on first.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 14, 2011 at 9:40 am
Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply