Count the first row data in SSRS / SQL Query

Question

In my SSRS report, there is a matrix showing following data... Every single of data in the matrix is dynamic.

Departure    Arravial     Bus name        Tour
01:51        02:01        07 ABY 04
02:02        02:12        07 AB 978
02:21        02:31        07 ABY 04
02:32        02:42        07 AB 978
03:01        03:11        07 ABY 04
03:02        03:12        07 AB 978
03:31        03:41        07 ABY 04
03:42        03:52        07 AB 978
04:01        04:11        07 ABY 04

What I want to do is count the first row bus name and put it next to it. Can this be achievable with expressions? or how can I come up with such query in SQL? How can I solve this puzzle?

Departure    Arravial     Bus name        Tour
01:51        02:01        07 ABY 04       1
02:02        02:12        07 AB 978       
02:21        02:31        07 ABY 04       2
02:32        02:42        07 AB 978       
03:01        03:11        07 ABY 04       3
03:02        03:12        07 AB 978       
03:31        03:41        07 ABY 04       4
03:42        03:52        07 AB 978       
04:01        04:11        07 ABY 04       5

This is my query by the way,

SELECT HCPD.DepartureTime, HCPD.ArrivalTime, V.BusName
FROM HAT_CALISMA_PLANI HCP WITH(NOLOCK)
INNER JOIN HAT_CALISMA_PLANI_DETAY HCPD WITH(NOLOCK) ON HCPD.HatCalismaPlaniKey = HCP.HatCalismaPlaniKey
INNER JOIN VALIDATOR V WITH(NOLOCK) ON V.ValidatorKey = HCPD.ValidatorKey
WHERE HCPD.DepartureTime = @Time AND HCP.HatKey = @HatKey
ORDER BY HCPD.DepartureTime

Edit: Bus names are dynamic.

Taryn · Accepted Answer · 2012-09-29 13:55:24Z

2

You might be able to do this by applying a row_number() to your query:

select DepartureTime, ArrivalTime, BusName,
  case when BusName ='07 ABY 04' 
        then cast(rn as varchar(10))
        else '' end Tour
from
(
  SELECT HCPD.DepartureTime, HCPD.ArrivalTime, V.BusName,
    row_number() over(partition by busname order by departure) rn
  FROM HAT_CALISMA_PLANI HCP WITH(NOLOCK)
  INNER JOIN HAT_CALISMA_PLANI_DETAY HCPD WITH(NOLOCK) 
    ON HCPD.HatCalismaPlaniKey = HCP.HatCalismaPlaniKey
  INNER JOIN VALIDATOR V WITH(NOLOCK) 
    ON V.ValidatorKey = HCPD.ValidatorKey
  WHERE HCPD.DepartureTime = @Time 
    AND HCP.HatKey = @HatKey
) x
order by DepartureTime;

Here is a sample query:

select departure, arrival, busname,
  case when busname ='07 ABY 04' 
        then cast(rn as varchar(10))
        else '' end Tour
from
(
  select departure, arrival, busname,
    row_number() over(partition by busname order by departure) rn
  from yourtable
) x
order by departure;

See SQL Fiddle with Demo

answered Sep 29, 2012 at 13:55

Taryn

249k57 gold badges375 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jake Feasel Over a year ago

I noticed you built up the schema manually, but this could have been done faster by just copying and pasting OP's data into the "Text to DDL" utility on SQL Fiddle. You probably knew about this feature, but I wanted to mention it because I've recently improved it a bit to better handle various input formats. Here's an example of the output it would produce: sqlfiddle.com/#!6/c4682

Taryn Over a year ago

@jake wow I actually did not know that. It will significantly reduce my need to type. Thanks for the info. :)

Collectives™ on Stack Overflow

Count the first row data in SSRS / SQL Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related