How to write If-Else Condition inside cursor

_{Monday, January 23, 2017 10:10 AM}

I am trying to make a procedure which have to add a new column for each row.
I have two columns - date nomenclature (refdate) and number of document.
Need to start from the oldest data. If the document from min(refdate) exists in min(refdate)+1 then add 1 in the new column, if not -0 .

DECLARE @VAR1 AS INT;
DECLARE @VAR2 AS INT;

DECLARE @CURSOR_REFDATE AS CURSOR;

SET @CURSOR_REFDATE = CURSOR FOR
SELECT REFDATE, DOC_NBR
  FROM TMP_TEST;

OPEN @CURSOR_REFDATE;

FETCH NEXT FROM @CURSOR_REFDATE INTO @VAR1,@VAR2;

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT CONCAT (@VAR1, ' ',@VAR2);
FETCH NEXT FROM @CURSOR_REFDATE INTO @VAR1,@VAR2;
END

CLOSE @CURSOR_REFDATE;
DEALLOCATE @CURSOR_REFDATE;

_{Monday, January 23, 2017 10:20 AM}

Can you post sample data+ desired result and it is possible we can provide a solution without a cursor?

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Monday, January 23, 2017 12:13 PM}

This is sample data :

And this is what the desired result: 0 means that this document is still open ,1 - means that the document don't exist any more , so its closed. The data is historical for 1 year back, so i need to put if the document is open or close on each row, for each day (refdate)

For example:
3241624 was created on 7685 (date nomenclature) .
On 7686 is still open , so we will put 0, on the next day is the same, etc.
On 7692 this document nbr doesn't exist so we have to create new line and put 1 in "Is doc closed".

_{Monday, January 23, 2017 12:19 PM}

It is unclear Windy. Where is a date here? What is logic behind the scene? I see all docs are the same

CREATE TABLE #t (REFDATE int,DOC_NBR int)
INSERT INTO #t VALUES (7691 ,3241624)
INSERT INTO #t VALUES (7690 ,3241624)
INSERT INTO #t VALUES (7689 ,3241624)
INSERT INTO #t VALUES (7686 ,3241624)
INSERT INTO #t VALUES (7685 ,3241624)

SELECT *, CASE WHEN EXISTS (SELECT * FROM #t t
WHERE t.DOC_NBR=#T.DOC_NBR AND t.REFDATE<=#T.REFDATE ) 
THEN 0 ELSE 1 END IsDocClosed FROM #t

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Monday, January 23, 2017 12:31 PM}

For example:
3241624 was created on 7685 (date nomenclature) . 
On 7686 is still open , so we will put 0, on the next day is the same, etc. 
On 7692 this document nbr doesn't exist so we have to create new line and put 1 in "Is doc closed".
For other documents should be the same. Check day by day and compare each day with the previous one for each document. If still exist - , if not - create line and put 1 in the new column.

_{Monday, January 23, 2017 12:49 PM}

I do not see 7692, who generates it?

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Monday, January 23, 2017 1:03 PM}

In this sample data, 7692 is data for tomorrow. There is a job which upload new data in a staging table. And then compare staging with historical.

On daily base is easy because you compare only two dates - Last 2 actually.

But when we have to reload all historical data for a year, we have to compare all dates from min to max (refdate,which is date nomenclature) for each document.

_{Monday, January 23, 2017 4:31 PM}

Thank you for attempting to post a narrative from which we could write our own DDL. Unfortunately, we do not use that flags in RDBMS; that was assembly language programming. Furthermore, why do you want to create a row for every day? Remember SQL is set oriented so we can have columns that model a range (in ANSI/ISO standard SQL. This is called an interval temporal temporal data type).

CREATE TABLE Documents
(document_nbr CHAR(7) NOT NULL,
 docu_open_date DATE NOT NULL,
 docu_close_date DATE,
 CHECK (docu_open_date <= docu_close_date),
 PRIMARY KEY (document_nbr, docu_open_date));

I am not sure from your narrative, if you need to have a "document_status" column, which includes open and closed as values. In SQL, most of the work is in the DDL; this is where the keys, constraints and referencing is done. But unfortunately your mindset is still thinking about using cursors, just like we did with filesystems. Quite literally, the cursor model in ANSI ISO standard SQL is based on the IBM magnetic tape system of the 1970 hardware.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

_{Tuesday, January 24, 2017 8:35 AM}

Hi windy_windy,

According to your description above, you may refer to script below. Besides, you could take a look at LEAD (Transact-SQL) using which you compare values in the current row with values in a following row.

SELECT a.*
    , CASE 
        WHEN EXISTS (SELECT 1 
                        FROM TMP_TEST b
                        WHERE b.[DOC_NBR] = a.[DOC_NBR]
                            AND b.[REFDATE] = a.[REFDATE] + 1) THEN 0
        ELSE 1
    END AS [Is Doc Closed]               
FROM TMP_TEST a

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.