CODEWITHSUNDEEP
sqlsql-serverfor-loopwhere-clause
Amritha
Amritha

Reputation: 815

Recreating a For Loop in SQL Server?

I am trying to recreate a for loop in SQL. I have a table which has fields such as company ID, Company Name, Fiscal Year, Fiscal Quarter and various Data Items. For each Data Item ID, I need to grab the Data Item Value for all the companies and select the rows which have the greatest fiscal year and greatest fiscal quarter. For example, if this is my sample table:

Company ID | Fiscal Year | Fiscal Quarter | DataItemID | DataItem Value 
1          | 2018        | 1              | 100        | $200,000  
1          | 2018        | 3              | 100        | $250,000
1          | 2017        | 4              | 200        | 35.5 
2          | 2017        | 4              | 100        | $500,000 
2          | 2018        | 1              | 200        | 40.0
2          | 2016        | 2              | 100        | $400,000 
2          | 2017        | 3              | 200        | 50.5

My output should be:

Company ID | Fiscal Year | Fiscal Quarter | DataItemID | DataItem Value  
1          | 2018        | 3              | 100        | $250,000
1          | 2017        | 4              | 200        | 35.5 
2          | 2017        | 4              | 100        | $500,000 
2          | 2018        | 1              | 200        | 40.0

So for each dataitemID, loop through all companies and select the row with the greatest fiscalyear and fiscalquarter for each company along with the dataItem Value.

I have the below code that partially works. For one DataItemID it works perfectly. However for multiple dataitems, it's not looping through all the companies.

SELECT top (1) with ties companyid, COMPANYName, dataitemname, fiscalyear,fiscalquarter, periodtypeid, dataitemvalue As Data_Value

    FROM dbo.ciqCompany  
    -- List out dataitems to report  
  WHERE dataItemID IN (100,200)

    order by row_number() over (partition by companyId order by fiscalyear desc, fiscalquarter desc)

I think the issue is with the Where-Clause " WHERE dataItemID IN (100,200)". Since this is an "OR" condition, it does not pick up all the records for each ID. Any thoughts on this will be greatly appreciated! Thank you so much!

Upvotes: 1

Views: 83

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You also need DataItemID in partition clause 

. . .
row_number() over (partition by companyId, DataItemID order by fiscalyear desc, fiscalquarter desc)

Upvotes: 2

S3S
S3S

Reputation: 25152

You just need to add an additional partition by to include DataItemID

 order by row_number() over (partition by companyId, DataItemID order by fiscalyear desc, fiscalquarter desc)

Upvotes: 2

Related Questions