who to write query.

Question

who to write query.

subbu1

Hall of Fame

Points: 3281
More actions
November 13, 2011 at 7:24 pm

#262661

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 1

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.

subbu1 Hall of Fame Points: 3281 More actions · Answer 2

subbu1

Hall of Fame

Points: 3281

November 13, 2011 at 9:35 pm

#1406909

Thanks

It is wotking fine.

subbu1 Hall of Fame Points: 3281 More actions · Answer 3

when i am using above query in below in put but i am not getting excute out put

pls help me.

-------input---------

todateidSMBM

2011-05-25 00:00:00.000R10321Gaurav MehtaMihir Vaidh

2011-06-20 00:00:00.000R10321Chintan RavalArjun M Moorjani

2011-06-20 00:00:00.000R10321Gaurav MehtaRajesh Parmar

2011-08-17 00:00:00.000R10321Chintan RavalArjun M Moorjani

2011-10-10 00:00:00.000R10321Ravindran RamachandranArjun M Moorjani

2011-04-26 00:00:00.000R15108Ravindran RamachandranPankaj Kolhe

2011-04-26 00:00:00.000R15108Ravindran RamachandranRavi Kumar

2011-10-20 00:00:00.000R15108Ravindran RamachandranRavi Kumar

-------output

idFromdatetodatebmsm

R103212011-04-01 00:00:00.0002011-05-25 00:00:00.000Mihir VaidhGaurav Mehta

R103212011-05-26 00:00:00.0002011-06-20 00:00:00.000Rajesh ParmarGaurav Mehta

R103212011-06-21 00:00:00.0002011-08-17 00:00:00.000Arjun M MoorjaniChintan Raval

R103212011-08-18 00:00:00.0002011-10-10 00:00:00.000Arjun M MoorjaniRavindran Ramachandran

R151082011-04-01 00:00:00.0002011-04-26 00:00:00.000Pankaj KolheRavindran Ramachandran

R151082011-04-27 00:00:00.0002011-10-20 00:00:00.000Ravi KumarRavindran Ramachandran

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

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/

subbu1 Hall of Fame Points: 3281 More actions · Answer 5

subbu1

Hall of Fame

Points: 3281

November 14, 2011 at 9:40 am

#1407332

Thanks.