Getting rowcount for a column based on values from

Question

I have two tables, T1 AND T2.

T1 with following columns: Id, TypeofValue, Year, value

Typeofvalue can have 2 values

1 - indicates Actual

2 - indicates Target

T2 With following column: NoOfRecordsToDisplay

I need to fetch the number of records (if existing) for Target corresponding to an Id.

However, the catches are:

1. Sometimes Target value might not be present for a year
2. I need to get only last records for targets on the basis of NoOfRecordsToDisplay (The number of records to display comes from T2) for actual

Example1: NoOfRecordsToDisplay =3, ID =123 The data below should return 3 as we have 3 non null values for target for last 3 years -2015, 2014,2013 in this case

Id  TypeofValue     Year    Value
123     1           2015    55
123     1           2014    56
123     1           2013    57
123     1           2012    58
123     2           2015    50
123     2           2014    50
123     2           2013    50
123     2           2012    50
124     1           2015    55
124     1           2014    56
124     1           2013    57
124     1           2012    58
124     2           2015    50
124     2           2014    50
124     2           2013    50
124     2           2012    50

Another dataset - NoOfRecordsToDisplay =3, ID =123 The data below should return 0, as we have no values for target for last 3 years -2015, 2014,2013

Id  TypeofValue     Year    Value
123     1           2015    55
123     1           2014    56
123     1           2013    57
123     1           2012    58
123     2           2012    50
124     1           2015    55
124     1           2014    56
124     1           2013    57
124     1           2012    58
124     2           2012    50

Answer 1

OK so if I understand correctly, you want a count of rows where the TypeOfValue = 2, and the Year is in the top n values where TypeOfValue = 1, for a given Id.

This should be:

DECLARE @Id int, @NoOfRecordsToDisplay int

SET @Id = 123
SET @NoOfRecordsToDisplay = 3

SELECT COUNT(*) FROM myTable
WHERE
  TypeofValue = 2
  AND Id = @Id
  AND [Year] IN ( SELECT TOP(@NoOfRecordsToDisplay) [Year] FROM myTable
              WHERE TypeofValue = 1 AND Id = @Id
              ORDER BY [Year] DESC)

In practice, you would probably want to create this as a stored proc with @Id as an input parameter. @NoOfRecordsToDisplay could either be a parameter too, or selected from some other table - I'm still not 100% clear on this from your question.

Updated SQL Fiddle here: http://sqlfiddle.com/#!3/87b0c/2

Edit: Forgot the ORDER BY on the subquery!

Edit 2: Updated query and SQL fiddle based on updated question.

Answer 2

You can select the last three actual items and join in the corresponding target items, then count the non-null values:

select
  count(Id)
from (
  select top 3
    t2.Id
  from
    TheTable t1
    left join TheTable t2 on t2.Year = t1.Year and t2.TypeofValue = 2
  where
    t1.TypeofValue = 1
  order by
    t1.Year desc
) x

Answer 3

With SQL and such these queries, understanding the problem and imagination of an algorithm or method for solving the problem is too much important, on base of what you want:

I need to get only last 3 records for targets on the basis of latest 3 values for actual

you need to have tow steps:

1.determine the last 3 years of actual values:

SELECT TOP 3 [Year]
FROM Your_Table
WHERE Typeofvalue = 1 
ORDER BY [Year] DESC

2.count the records of target values which their years are in above query:

SELECT COUNT(*) FROM Your_Table
WHERE
  Typeofvalue = 2
  AND 
  [Year] IN (
          SELECT TOP 3 [Year]
          FROM Your_Table
          WHERE Typeofvalue = 1 
          ORDER BY [Year] DESC)

---

You can do it with join too instead of subquery:

SELECT COUNT(*) FROM Your_Table t
JOIN
(
  SELECT TOP 3 [Year]
  FROM Your_Table
  WHERE Typeofvalue = 1 
  ORDER BY [Year] DESC
)q
ON T.[Year]=q.[Year]
WHERE t.Typeofvalue=2