select Original column value where column value changed

Kanimozhi Shivakumar

8y1.2k

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

19 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ruchi Sharavat8y ago
Accepted 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 Sharavat8y ago
Wowwww.......

I am glad that my answer was helpful to you.
Kanimozhi Shivakumar8y ago
Hi Ruchi

I made few changes on your query...Now it is working perfectly the way I want it..

Thank you so much ..
snehal suryavanshi8y ago
private void dataGridView1_CurrentCellChanged(object sender, EventArgs e) Use this event It may help you Write query in this event Thank you
Kanimozhi Shivakumar8y ago
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 Kumar8y ago
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. Motta8y ago
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 Subburaj8y ago
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 Shivakumar8y ago
Is there any alternate solution for LAG function in MS SQL Server 2008 version?
Jeeva Subburaj8y ago
can you run this query and post the answer.. SELECT @@VERSION
Kanimozhi Shivakumar8y ago

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 Shivakumar8y ago
Sure . I am using MS SQL server 2012 only.
Jeeva Subburaj8y ago
Lag was introduced in sql 2012.. Are you sure you are using SQL server 2012?
Kanimozhi Shivakumar8y ago
I tried with LAG function. But i am getting below error..I am using MS SQL server 2012
Kanimozhi Shivakumar8y ago
1. 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
2. FROM QVLS_SUPP_PO_HED
3. LEFT OUTER JOIN QVLS_SUPP_PO_DET ON QVLS_SUPP_PO_HED.IC = QVLS_SUPP_PO_DET.SUPP_PO_HED_IC
4. LEFT OUTER JOIN QVLS_PARTY_MST ON QVLS_SUPP_PO_HED.PARTY_IC = QVLS_PARTY_MST.IC
5. LEFT OUTER JOIN QVLS_PARTS_MST ON QVLS_SUPP_PO_DET.PART_IC = QVLS_PARTS_MST.IC
6. ORDER BY FIN_YEAR,SUPP_PO_NO ASC

Jeeva Subburaj8y ago

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 Subburaj8y ago

Can you post your original SQL here.

Kanimozhi Shivakumar8y ago

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 Subburaj8y ago

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.