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 12»»

Adding text to Rank Expand / Collapse
Author
Message
Posted Wednesday, May 15, 2013 6:08 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:48 AM
Points: 125, Visits: 361
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
Post #1453036
Posted Wednesday, May 15, 2013 6:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:45 AM
Points: 1,694, Visits: 19,552
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))



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1453048
Posted Wednesday, May 15, 2013 6:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 2,854, Visits: 5,120
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1453049
Posted Wednesday, May 15, 2013 6:30 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:48 AM
Points: 125, Visits: 361
Thanks Mark, you have solved my problem.

Thanks So much

Tim
Post #1453052
Posted Wednesday, May 15, 2013 6:36 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:48 AM
Points: 125, Visits: 361
Thanks SSCrazy

It Works

Thanks

Tim
Post #1453056
Posted Wednesday, May 15, 2013 6:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 2,854, Visits: 5,120
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1453059
Posted Wednesday, May 15, 2013 9:08 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:48 AM
Points: 125, Visits: 361
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
Post #1453168
Posted Wednesday, May 15, 2013 9:10 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:48 AM
Points: 125, Visits: 361
Sorry Eugene, i mistook your name for SSCrazy. Lol

Thanks for the reply please check my new challenge above.

Thanks

Tim
Post #1453170
Posted Wednesday, May 15, 2013 9:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:45 AM
Points: 1,694, Visits: 19,552
Change

partition by fk_stationid 

to

partition by fk_stationid , AdDate



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1453177
Posted Wednesday, May 15, 2013 9:30 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 6:24 PM
Points: 23,397, Visits: 32,246
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?



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 #1453180
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse