Adding text to Rank

• timotech

  SSCrazy

  Points: 2698

  More actions

  May 15, 2013 at 6:08 am

  #293352

  Hi All,

  Please i need help to complete this query, what it does is that it ranks some records which works well, but i want the ranking to be of this format, e.g 1 of 4 or 2 of 4 etc

  This is the query:

  SELECT AdDate, AdTime, FK_StationId, rank() over(partition by fk_stationid order by addate,adtime) as Position

  FROM dbo.tbl_Television

  GROUP BY AdDate, AdTime, FK_StationId

  HAVING (AdDate = CONVERT(DATETIME, '2013-04-01 00:00:00', 102))

  This is the sample data

  Insert into TestTable(AdDate, AdTime, Fk_StationId)

  Select 2013-04-01,07:52:00,A1,Union All

  Select 2013-04-01,07:52:43,A1,Union All

  Select 2013-04-01,08:05:06,A1,Union All

  Select 2013-04-01,08:20:45,A1,Union All

  Select 2013-04-01,06:00:00,A10,Union All

  Select 2013-04-01,06:03:12,A10,Union All

  Select 2013-04-01, 06:19:01,A10,Union All

  Select 2013-04-01,06:32:31,A10,Union All

  Select 2013-04-01, 06:43:36,A10

  I would want the position to be

  1 of 4

  2 of 4

  3 of 4

  4 of 4

  1 of 5

  2 of 5

  3 of 5

  4 of 5

  5 of 5

  Thanks

  Tim

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  May 15, 2013 at 6:27 am

  #1615379

  SELECT AdDate, AdTime, FK_StationId, CAST(rank() over(partition by fk_stationid order by addate,adtime) AS VARCHAR(10)) + ' of ' +

  CAST(count(*) over(partition by fk_stationid) AS VARCHAR(10)) as Position

  FROM dbo.TestTable

  GROUP BY AdDate, AdTime, FK_StationId

  HAVING (AdDate = CONVERT(DATETIME, '2013-04-01 00:00:00', 102))

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  May 15, 2013 at 6:29 am

  #1615380

  First of all, it will be more helpful if you would supply your sample data in a runable/executable way, something like this:

  Select '2013-04-01' AdDate,'07:52:00' AdTime ,'A1' Fk_StationId

  into #TestTable

  Union All Select '2013-04-01','07:52:43','A1'

  Union All Select '2013-04-01','08:05:06','A1'

  Union All Select '2013-04-01','08:20:45','A1'

  Union All Select '2013-04-01','06:00:00','A10'

  Union All Select '2013-04-01','06:03:12','A10'

  Union All Select '2013-04-01','06:19:01','A10'

  Union All Select '2013-04-01','06:32:31','A10'

  Union All Select '2013-04-01','06:43:36','A10'

  Here is one of the way how you can add the count you want:

  SELECT AdDate, AdTime, FK_StationId

  , CAST( rank() over(partition by fk_stationid order by addate,adtime) AS VARCHAR) +

  ' of ' + cc.cnt as Position

  FROM #TestTable t1

  CROSS APPLY (SELECT CAST(COUNT(*) AS VARCHAR) cnt FROM #TestTable t2 WHERE t2.Fk_StationId = T1.Fk_StationId) cc

  GROUP BY AdDate, AdTime, FK_StationId, cc.cnt

  HAVING (AdDate = CONVERT(DATETIME, '2013-04-01 00:00:00', 102))

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• timotech

  SSCrazy

  Points: 2698

  More actions

  May 15, 2013 at 6:30 am

  #1615382

  Thanks Mark, you have solved my problem.

  Thanks So much

  Tim

• timotech

  SSCrazy

  Points: 2698

  More actions

  May 15, 2013 at 6:36 am

  #1615386

  Thanks SSCrazy

  It Works

  Thanks

  Tim

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  May 15, 2013 at 6:40 am

  #1615389

  Something tells me that CROSS APPLY (with index seek on FK_StationId (I guess it's indexed in real life)) will be quite faster than using second windowed function which would cause table spools ...

  However, I guess, it's irrelevant for a smaller datasets.

  I'm not SSCrazy! 😉

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• timotech

  SSCrazy

  Points: 2698

  More actions

  May 15, 2013 at 9:08 am

  #1615483

  Hi guys, please i need another help. Please how can i use this particular data:

  Insert into TestTable(AdDate, AdTime, Fk_StationId)

  Select 2013-04-01,07:52:00,A1

  Union All Select 2013-04-01,07:52:43,A1

  Union All Select 2013-04-01,08:05:06,A1

  Union All Select 2013-04-01,08:20:45,A1

  Union All Select 2013-04-02,20:12:38,A1

  Union All Select 2013-04-02, 20:13:10,A1

  Union All Select 2013-04-02, 20:13:10,A1

  Union All Select 2013-04-02, 20:14:00,A1

  Union All Select 2013-04-01,06:00:00,A10

  Union All Select 2013-04-01,06:03:12,A10

  Union All Select 2013-04-01, 06:19:01,A10

  Union All Select 2013-04-01,06:32:31,A10

  Union All Select 2013-04-01, 06:43:36,A10

  Union All Select 2013-04-02, 08:08:54,A10

  Union All Select 2013-04-02, 08:08:54,A10

  Union All Select 2013-04-02, 08:58:46,A10

  Union All Select 2013-04-02, 08:58:46,A10

  To get something like this

  For the first day e.g 1 of 4, 2 of 4 for each Fk_stationId

  Then for the second day also 1 of 4, 2 of 4

  But on running it, its giving me 1 of 8, 2 of 8 since they all belong to the same Fk_StationId

  Please how can i resolve this

  Thanks

  Tim

• timotech

  SSCrazy

  Points: 2698

  More actions

  May 15, 2013 at 9:10 am

  #1615485

  Sorry Eugene, i mistook your name for SSCrazy. Lol

  Thanks for the reply please check my new challenge above.

  Thanks

  Tim

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  May 15, 2013 at 9:27 am

  #1615491

  Change

  partition by fk_stationid

  to

  partition by fk_stationid , AdDate

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 15, 2013 at 9:30 am

  #1615494

  timotech (5/15/2013)

  ---

  Hi guys, please i need another help. Please how can i use this particular data:

  Insert into TestTable(AdDate, AdTime, Fk_StationId)

  Select 2013-04-01,07:52:00,A1

  Union All Select 2013-04-01,07:52:43,A1

  Union All Select 2013-04-01,08:05:06,A1

  Union All Select 2013-04-01,08:20:45,A1

  Union All Select 2013-04-02,20:12:38,A1

  Union All Select 2013-04-02, 20:13:10,A1

  Union All Select 2013-04-02, 20:13:10,A1

  Union All Select 2013-04-02, 20:14:00,A1

  Union All Select 2013-04-01,06:00:00,A10

  Union All Select 2013-04-01,06:03:12,A10

  Union All Select 2013-04-01, 06:19:01,A10

  Union All Select 2013-04-01,06:32:31,A10

  Union All Select 2013-04-01, 06:43:36,A10

  Union All Select 2013-04-02, 08:08:54,A10

  Union All Select 2013-04-02, 08:08:54,A10

  Union All Select 2013-04-02, 08:58:46,A10

  Union All Select 2013-04-02, 08:58:46,A10

  To get something like this

  For the first day e.g 1 of 4, 2 of 4 for each Fk_stationId

  Then for the second day also 1 of 4, 2 of 4

  But on running it, its giving me 1 of 8, 2 of 8 since they all belong to the same Fk_StationId

  Please how can i resolve this

  Thanks

  Tim

  First, you really need to test any code you post. What you posted above won't work for several reasons, one of which is there is no CREATE TABLE statement preceeding the INSERT.

  Also, how do you want to handle duplicate entries as you have in your new dataset?

• timotech

  SSCrazy

  Points: 2698

  More actions

  May 15, 2013 at 9:33 am

  #1615496

  Ok Mark, Thanks, that works very fine.

  Thanks, i'm very grateful

  Tim

• timotech

  SSCrazy

  Points: 2698

  More actions

  May 15, 2013 at 9:34 am

  #1615497

  Hi Lynn,

  Thanks for your reply, but i think Mark has solved the problem, just follow his post

  Thanks

  Tim

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 15, 2013 at 9:36 am

  #1615498

  timotech (5/15/2013)

  ---

  Hi Lynn,

  Thanks for your reply, but i think Mark has solved the problem, just follow his post

  Thanks

  Tim

  Oh, I saw it and had come to the same conclusion.

• timotech

  SSCrazy

  Points: 2698

  More actions

  May 15, 2013 at 9:45 am

  #1615502

  Sorry Lynn,

  I meant to say that your corrections are noted, and i will make sure, i do it the right way next time.

  Thanks

  Tim

• sqldriver

  SSChampion

  Points: 10440

  More actions

  May 16, 2013 at 8:42 am

  #1615834

  Mark-101232 (5/15/2013)

  ---

  SELECT AdDate, AdTime, FK_StationId, CAST(rank() over(partition by fk_stationid order by addate,adtime) AS VARCHAR(10)) + ' of ' +

  CAST(count(*) over(partition by fk_stationid) AS VARCHAR(10)) as Position

  FROM dbo.TestTable

  GROUP BY AdDate, AdTime, FK_StationId

  HAVING (AdDate = CONVERT(DATETIME, '2013-04-01 00:00:00', 102))

  This is the coolest thing I've seen SQL do in weeks. Thanks.