none
Max value or null values

    Question

  • Hi

    I have the following tables -

    LOCATION - Location ID, Address

    TENANCY - Tenancy ID, Location ID, Status (Former, current or terminating), Start date, end date, Tenant name

    I would like to pull up all locations and their latest tenancies. I have done this using a left outer join and specifying MAX tenancy. However, I do not want any locations if the Max tenancy has a status of current. 

    As locations are only matched when a tenancy is started, I get as far as getting the latest tenancies with a blank if there is no current tenancy but then when I try and get rid of the locations that have a current tenancy attached, the blank ones do not show.

    Monday, October 14, 2013 9:03 PM

Answers

  • Hi dpsl06,

    From your description, you want to query all LOCATIONS and their latest TENANCIES, except the tenancy which Status is current. And if there is a Location ID in LOCATION, while it have no corresponding data in TENANCY, then the result should include the blank one. In Reporting Services, we can add a filter or just use T-SQL statement to get rid of the current Status of tenancy. Besides, we can query the blank one by using correct T-SQL statement(method 1 or method 2).

    After testing it in my own environment, we can refer to the following steps to achieve your requirement:

    1. Method 1:
        1) Create a DataSet with the following query statement:
            SELECT   l.Address, t.[Tenancy ID], t.Status, t.[Start date], t.[end date], t.[Tenant name], l.[Location ID]
            FROM      LOCATION AS l LEFT JOIN  TENANCY AS t ON l.[Location ID] = t.[Location ID]
        2) Right-click the DataSet to open DataSet Properties dialog box, click Filter in the left pane.
        3) Add a filter as follows:
            Expression: =IIF(Fields!Status.Value="current",0,1)           Operator: >          Value: =0

       Method 2:
       1) Create a DataSet with the following query statement:
           SELECT    l.Address, t.[Tenancy ID], t.Status, t.[Start date], t.[end date], t.[Tenant name], l.[Location ID]
           FROM       LOCATION AS l LEFT OUTER JOIN  TENANCY AS t ON l.[Location ID] = t.[Location ID]
           WHERE       (t.Status <> 'current') OR  (t.Status IS NULL)
    2. Drag a Matrix from Toolbox to design surface, add a Row Group grouped by [Location_ID].
    3. Insert Address field into the second row of the second column, add five columns out of the group.
    4. Modify the expression of the following textboxes in the right order as below:

    =lookup(max(Fields!Start_date.Value,"Location_ID"),Fields!Start_date.Value,Fields!Tenancy_ID.Value,"DataSet1")
    =lookup(max(Fields!Start_date.Value,"Location_ID"),Fields!Start_date.Value,Fields!Status.Value,"DataSet1")
    =max(Fields!Start_date.Value,"Location_ID")
    =lookup(max(Fields!Start_date.Value,"Location_ID"),Fields!Start_date.Value,Fields!end_date.Value,"DataSet1")
    =lookup(max(Fields!Start_date.Value,"Location_ID"),Fields!Start_date.Value,Fields!Tenant_name.Value,"DataSet1")


    The following screenshot is for your reference:

    If you have any more questions, please feel free to ask.

    Thanks,
    Katherine Xiong


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Thursday, October 17, 2013 2:09 AM
  • You need to use a query like below

    This will return both cases where it doesnt have a tenancy as well as cases where latest tenancy status is not current

    SELECT LocationID,
    Address,
    Status,
    Startdate,
    Enddate,
    TenancyName
    FROM
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY LocationID ORDER BY TenancyID DESC) AS Rn,l.LocationID,l.Address,t.Status,t.Startdate,t.Enddate,t.TenancyName
    FROM LOCATION l
    LEFT JOIN TENANCY t
    ON t.LOCATIONID = l.LOCATIONID
    )t
    WHERE Rn=1
    AND COALESCE(Status,'') <> 'Current'

    Please Mark as Answer if this post helps you to solve the problem

    Thursday, October 17, 2013 3:22 AM

All replies

  • Can you please post some data examples and your T-SQL request ?

    ++


    Business Intelligence Consultant : Microsoft Certified MCITP, MCTS and MCSA SQL Server 2008.

    Tuesday, October 15, 2013 8:58 AM
  • Please post some data samples.

    It would be great if you could copy the script for table and data as well.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, October 15, 2013 9:21 AM
  • Hi dpsl06,

    From your description, you want to query all LOCATIONS and their latest TENANCIES, except the tenancy which Status is current. And if there is a Location ID in LOCATION, while it have no corresponding data in TENANCY, then the result should include the blank one. In Reporting Services, we can add a filter or just use T-SQL statement to get rid of the current Status of tenancy. Besides, we can query the blank one by using correct T-SQL statement(method 1 or method 2).

    After testing it in my own environment, we can refer to the following steps to achieve your requirement:

    1. Method 1:
        1) Create a DataSet with the following query statement:
            SELECT   l.Address, t.[Tenancy ID], t.Status, t.[Start date], t.[end date], t.[Tenant name], l.[Location ID]
            FROM      LOCATION AS l LEFT JOIN  TENANCY AS t ON l.[Location ID] = t.[Location ID]
        2) Right-click the DataSet to open DataSet Properties dialog box, click Filter in the left pane.
        3) Add a filter as follows:
            Expression: =IIF(Fields!Status.Value="current",0,1)           Operator: >          Value: =0

       Method 2:
       1) Create a DataSet with the following query statement:
           SELECT    l.Address, t.[Tenancy ID], t.Status, t.[Start date], t.[end date], t.[Tenant name], l.[Location ID]
           FROM       LOCATION AS l LEFT OUTER JOIN  TENANCY AS t ON l.[Location ID] = t.[Location ID]
           WHERE       (t.Status <> 'current') OR  (t.Status IS NULL)
    2. Drag a Matrix from Toolbox to design surface, add a Row Group grouped by [Location_ID].
    3. Insert Address field into the second row of the second column, add five columns out of the group.
    4. Modify the expression of the following textboxes in the right order as below:

    =lookup(max(Fields!Start_date.Value,"Location_ID"),Fields!Start_date.Value,Fields!Tenancy_ID.Value,"DataSet1")
    =lookup(max(Fields!Start_date.Value,"Location_ID"),Fields!Start_date.Value,Fields!Status.Value,"DataSet1")
    =max(Fields!Start_date.Value,"Location_ID")
    =lookup(max(Fields!Start_date.Value,"Location_ID"),Fields!Start_date.Value,Fields!end_date.Value,"DataSet1")
    =lookup(max(Fields!Start_date.Value,"Location_ID"),Fields!Start_date.Value,Fields!Tenant_name.Value,"DataSet1")


    The following screenshot is for your reference:

    If you have any more questions, please feel free to ask.

    Thanks,
    Katherine Xiong


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Thursday, October 17, 2013 2:09 AM
  • You need to use a query like below

    This will return both cases where it doesnt have a tenancy as well as cases where latest tenancy status is not current

    SELECT LocationID,
    Address,
    Status,
    Startdate,
    Enddate,
    TenancyName
    FROM
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY LocationID ORDER BY TenancyID DESC) AS Rn,l.LocationID,l.Address,t.Status,t.Startdate,t.Enddate,t.TenancyName
    FROM LOCATION l
    LEFT JOIN TENANCY t
    ON t.LOCATIONID = l.LOCATIONID
    )t
    WHERE Rn=1
    AND COALESCE(Status,'') <> 'Current'

    Please Mark as Answer if this post helps you to solve the problem

    Thursday, October 17, 2013 3:22 AM