CODEWITHSUNDEEP
sqlsql-serverdatesql-update
BensonMN
BensonMN

Reputation: 29

SQL UPDATE Function Date Columns using MS-SQLSERVER

I have a table which consists of 2 date columns. One column is a transaction date, and the next column is a date I've used as batch date where I group the dates based on what day of the date of transaction. My Table for demo purposes is was built on this sql script

CREATE TABLE [DateDemo1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [mydate] [datetime] NULL,
    [batchdate] [datetime] NULL
)

The present data I have inserted is (date format is dd/mm/yyyy)

INSERT INTO [DateDemo]([mydate],[batchdate])  VALUES ('01/03/2017', '01/03/2017')
INSERT INTO [DateDemo]([mydate],[batchdate])  VALUES ('02/03/2017', '01/03/2017')
INSERT INTO [DateDemo]([mydate],[batchdate])  VALUES ('03/03/2017', '01/03/2017')
INSERT INTO [DateDemo]([mydate],[batchdate])  VALUES ('06/03/2017', '01/03/2017')
INSERT INTO [DateDemo]([mydate],[batchdate])  VALUES ('01/04/2017', '01/04/2017')
INSERT INTO [DateDemo]([mydate],[batchdate])  VALUES ('02/04/2017', '01/04/2017')
INSERT INTO [DateDemo]([mydate],[batchdate])  VALUES ('03/04/2017', '01/04/2017')
INSERT INTO [DateDemo]([mydate],[batchdate])  VALUES ('04/04/2017', '01/04/2017')
INSERT INTO [DateDemo]([mydate],[batchdate])  VALUES ('05/04/2017', '01/04/2017')
INSERT INTO [DateDemo]([mydate],[batchdate])  VALUES ('06/04/2017', '01/04/2017')
INSERT INTO [DateDemo]([mydate],[batchdate])  VALUES ('07/04/2017', '01/04/2017')

I intend to update the batchdate of each row into groups(1st of the month or 2nd of the month). using the 3rd day of the month which determines the batchdate each row entry will be categorised in. An Example will be like the row of the 2nd of March 2017 should fall under the 2nd Feb 2017 batchdate. the 3rd of March 2017 will be on the 1st March 2017 batchdate, the 4th of March 2017 will be in the 2nd March 2017 batch date and for arguments sake the 7th of April will be in the 2nd of April 2017 batchdate. My desired result set I would like it to be as follows

id,    mydate,      batchdate
    1, 01/03/2017 ,  02/02/2017 
    2, 02/03/2017 ,  02/02/2017 
    3, 03/03/2017 ,  01/03/2017 
    4, 06/03/2017 ,  02/03/2017 
    5, 01/04/2017 ,  02/03/2017 
    6, 02/04/2017 ,  02/03/2017 
    7, 03/04/2017 ,  01/04/2017 
    8, 04/04/2017 ,  02/04/2017 
    9, 05/04/2017 ,  02/04/2017 
    10, 06/04/2017 ,  02/04/2017 
    11, 07/04/2017 ,  02/04/2017

I require an SQL UPDATE function that will give me the result I'm looking for. pseudo code will be welcome as well.

Thanks in advance.

Upvotes: 0

Views: 53

Answers (1)

SqlZim
SqlZim

Reputation: 38063

using two case expressions which modify the truncation of the date to month.

update DateDemo
  set batchdate = dateadd(day
                   , case when day(mydate) = (3) then 0 else 1 end
                   , dateadd(month, datediff(month, 0, mydate ) 
                       + case when day(mydate) in(1,2) then -1 else 0 end
                   , 0)
                  );
select 
    mydate
  , batchdate
from DateDemo;

rextester demo: http://rextester.com/CKGNS20776

returns:

+------------+------------+
|   mydate   | batchdate  |
+------------+------------+
| 2017-03-01 | 2017-02-02 |
| 2017-03-02 | 2017-02-02 |
| 2017-03-03 | 2017-03-01 |
| 2017-03-06 | 2017-03-02 |
| 2017-04-01 | 2017-03-02 |
| 2017-04-02 | 2017-03-02 |
| 2017-04-03 | 2017-04-01 |
| 2017-04-04 | 2017-04-02 |
| 2017-04-05 | 2017-04-02 |
| 2017-04-06 | 2017-04-02 |
| 2017-04-07 | 2017-04-02 |
+------------+------------+

Upvotes: 1

Related Questions