select Original column value where column value changed

Question

Hi All, Please find the below image As of now my query result is like this. I want to print PO amount for first row of when PO number is changed.. Please refer below image. I want to print like below resultset from SQL query. If anyone knows the query please help me out to resolve this

Ruchi Sharavat · Answer

Can you DENSE_Rank() if want to work with CTE ; WITH Result as ( SELECT DENSE_Rank() OVER(Partition by SUPP_PO_NO order by QVLS_SUPP_PO_HED.IC) r, SUPP_PO_NO, SUPP_PO_DATE,PARTY_NAME, PART_NUMBER,PART_NAME, PART_RATE,ORDER_QTY,(PART_RATE*ORDER_QTY) PART_AMOUNT ,ORDER_AMT FROM QVLS_SUPP_PO_HED LEFT OUTER JOIN QVLS_SUPP_PO_DET ON QVLS_SUPP_PO_HED.IC = QVLS_SUPP_PO_DET.SUPP_PO_HED_IC LEFT OUTER JOIN QVLS_PARTY_MST ON QVLS_SUPP_PO_HED.PARTY_IC = QVLS_PARTY_MST.IC LEFT OUTER JOIN QVLS_PARTS_MST ON QVLS_SUPP_PO_DET.PART_IC = QVLS_PARTS_MST.IC ORDER BY FIN_YEAR,SUPP_PO_NO ASC ) SELECT SUPP_PO_NO, SUPP_PO_DATE,PARTY_NAME, PART_NUMBER,PART_NAME, PART_RATE,ORDER_QTY,(PART_RATE*ORDER_QTY) PART_AMOUNT , CASE r WHEN 1 THEN ORDER_AMT ELSE 0 END [PO Amount] FROM Result

Ruchi Sharavat · Answer

Wowwww....... I am glad that my answer was helpful to you.

Kanimozhi Shivakumar · Answer

Hi Ruchi I made few changes on your query...Now it is working perfectly the way I want it.. Thank you so much ..

snehal suryavanshi · Answer

private void dataGridView1_CurrentCellChanged(object sender, EventArgs e) Use this event It may help you Write query in this event Thank you

Kanimozhi Shivakumar · Answer

Hi Jignesh Kumar, Please read previous conversation.. Jeevanatham Subburaj already explained about LAG function and that is not applicable for SQL 2008 version. I think I have one option what suggested by Jefferson S. Motta Thank you all for your replies

Jignesh Kumar · Answer

Hi Kanimozhi, Please find this article it will help you. you can use LAG() function to solve your issue. https://www.codeproject.com/Tips/1138678/How-to-Format-Repeating-Values-in-SQL-Result-Set

Jefferson S. Motta · Answer

I understand the data, but not the procedure to organize data. Couldn't you create a temp table and fill as you wish? good luck, Jeff

Jeeva Subburaj · Answer

you can do that in SQL 2008 with self join but it may look little complex query. Look at this link for your reference and modify your query accordingly. https://blog.sqlauthority.com/2011/11/24/sql-server-solution-to-puzzle-simulate-lead-and-lag-without-using-sql-server-2012-analytic-function/

Kanimozhi Shivakumar · Answer

Is there any alternate solution for LAG function in MS SQL Server 2008 version?

Jeeva Subburaj · Answer

can you run this query and post the answer.. SELECT @@VERSION

Kanimozhi Shivakumar · Answer

I have MS SQL Server 2012 installed in my system.. But I am using server DB.I checked the version it is showing as 2008 as shown in above fig.

Kanimozhi Shivakumar · Answer

Sure . I am using MS SQL server 2012 only.

Jeeva Subburaj · Answer

Lag was introduced in sql 2012.. Are you sure you are using SQL server 2012?

Kanimozhi Shivakumar · Answer

I tried with LAG function. But i am getting below error..I am using MS SQL server 2012

Kanimozhi Shivakumar · Answer

SELECT SUPP_PO_NO, SUPP_PO_DATE,PARTY_NAME, PART_NUMBER,PART_NAME, PART_RATE,ORDER_QTY,(PART_RATE*ORDER_QTY) PART_AMOUNT ,ORDER_AMT FROM QVLS_SUPP_PO_HED LEFT OUTER JOIN QVLS_SUPP_PO_DET ON QVLS_SUPP_PO_HED.IC = QVLS_SUPP_PO_DET.SUPP_PO_HED_IC LEFT OUTER JOIN QVLS_PARTY_MST ON QVLS_SUPP_PO_HED.PARTY_IC = QVLS_PARTY_MST.IC LEFT OUTER JOIN QVLS_PARTS_MST ON QVLS_SUPP_PO_DET.PART_IC = QVLS_PARTS_MST.IC ORDER BY FIN_YEAR,SUPP_PO_NO ASC

Jeeva Subburaj · Answer

Here is the sample SQL that solve your issue using LAG statment in SQL. To understand more about LAG statment in sql, refer the following link https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2017 select first_name, gender, , case when age = lag(age,1,0) OVER ( ORDER BY age) then 0 else age end AS calc_age from person order by age

Jeeva Subburaj · Answer

Can you post your original SQL here.

Kanimozhi Shivakumar · Answer

I need to print in excel.. If I print cell by cell we can customize however we want.. But i am pushing entire resultset in one shot..So whatever formatting needs to be done I have to do it in query level..

Jeeva Subburaj · Answer

are you developing crystal reports based on this query. if so, you have the option not print the subsequent values in crystal reports instead of changing it in query. provide your full requirements and i will try to see if i can help you.