Sql-server – Incorrect syntax near the keyword ‘is’ in SQL server vb6

sql serversql-server-2012t-sql

Set rs = con.Execute("select IIF(MAX(bill_bno) IS NULL,1,MAX(bill_bno)+1) from rec_all      where rw=0 and iif(month(bill_date)&gt;=1 AND month(bill_date)&lt;=3,year(bill_date)-1,year(bill_date))=" &amp; IIf(Month(Date) &gt;= 1 And Month(Date) &lt;= 3, Year(Date) - 1, Year(Date)))

how to change vb6 code (MS access) database to SQL Server 2012?

Best Answer

Since you only gave us the line that generates your recordset, we'll assume that you are able to establish your connection successfully.

First: the following statement should be valid in SQL Server 2012:

select IIF(MAX(bill_bno) IS NULL,1,MAX(bill_bno)+1) from rec_all
 where rw=0
   and  iif(month(bill_date)>=1 AND month(bill_date)<=3,year(bill_date)-1,year(bill_date))
       =iif(Month(GETDATE())>= 1 And Month(GETDATE()) <= 3, Year(GETDATE()) - 1, Year(GETDATE()))

As far as I can tell, this is what you listed above, with the HTML-ification removed, and the SQL Server current date and time function GETDATE() replacing what I believe is the MS Access current date function Date.

I've displayed the query on multiple lines with indenting for human readability; you can put the full query on one line, if that's what you're language requires.

Not knowing the language you're using, I can't tell you how to properly format your actual con.execute command; change > and < to > and < if required.

As the other answers showed, there are other commands in SQL more commonly used to do the things you're doing (I had to check to be sure iif was available in T-SQL), but the major problem query-wise seems to have been how to get the current system date.

Related Solutions

Sql-server – Nested with – Incorrect syntax near the keyword ‘WITH’

From documentation

Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

Thus,

with PS as (
    WITH CS AS (
      SELECT      'ab' as a
    )
    SELECT * from cs
    ) select * from PS

gives you error. You can define multiple CTEs though :

WITH CS AS (SELECT 'ab' as a),
PS as (SELECT * from cs) 
select * from PS

Sql-server – How to return data from a table where the data is the max date of the previous month’s data submissions (-1 of current date)

Expanding on James' answer

Try this code on SqlFiddle.

Also check out @SqlKiwi Paul White's post regarding Delete vs Truncate

create table tracker_live (
  client_nbr int not null, tracker_date date not null, submit_date date not null, live float not null, retainer float not null, bonus float not null
  , constraint pk_tracker_live primary key (client_nbr, tracker_date, submit_date));

insert into tracker_live (client_nbr, tracker_date, submit_date, live, retainer, bonus) values
   (9,'2014-10-01','2014-10-08',537841.5657,1,1)
  ,(9,'2014-11-01','2014-10-08',890293.714,2,2)
  ,(9,'2014-12-01','2014-10-08',45585.4029,3,3)
  ,(9,'2014-10-01','2014-10-17',537841.5657,1,1)
  ,(9,'2014-11-01','2014-10-17',890293.714,2,2)
  ,(9,'2014-12-01','2014-10-17',45585.4029,3,3)
  ,(9,'2014-10-01','2014-11-13',2321938.59,1,1)
  ,(9,'2014-11-01','2014-11-13',907925.1072,2,2)
  ,(9,'2014-12-01','2014-11-13',392917.5237,3,3)
  ,(9,'2014-10-01','2014-11-18',2321938.59,1,1)
  ,(9,'2014-11-01','2014-11-18',795725.1072,2,2)
  ,(9,'2014-12-01','2014-11-18',725835.0474,3,3)

create table tracker_live_last_month (
  client_nbr int not null, tracker_date date not null, submit_date date not null, live float not null, retainer float not null, bonus float not null
  , constraint pk_tracker_live_last_month primary key (client_nbr, tracker_date));  

/* example query for procedure */


declare @test date;
  set @test = '2014-11-18';
  --set @test = convert(date,getdate());
declare @LastDayOfLastMonth date;
  set @LastDayOfLastMonth  = dateadd(day,-datepart(day,@test),@test);
declare @FirstDayOfLastMonth date;  
  set @FirstDayOfLastMonth = dateadd(day,1-datepart(day,@LastDayOfLastMonth),@LastDayOfLastMonth);

  truncate table tracker_live_last_month;

  /* inserting the data with this combined query should be quicker than creating the table and then updating values */
  insert into tracker_live_last_month (client_nbr, tracker_date, submit_date, live, retainer, bonus)
    select tl.client_nbr, tl.tracker_date, ca.submit_date, ca.live, ca.retainer, ca.bonus
      from tracker_live tl
        cross apply (select top 1 i.submit_date, i.live, i.retainer, i.bonus 
          from tracker_live i 
          where i.client_nbr=tl.client_nbr 
            and i.tracker_date=tl.tracker_date
          order by i.submit_date desc) as ca
      where tl.submit_date between @FirstDayOfLastMonth and @LastDayofLastMonth
      group by tl.client_nbr, tl.tracker_date, ca.submit_date, ca.retainer, ca.live, ca.bonus
      order by tl.client_nbr, tl.tracker_date, ca.submit_date, ca.retainer, ca.live, ca.bonus;

Best Answer

Related Solutions

Sql-server – Nested with – Incorrect syntax near the keyword ‘WITH’

Sql-server – How to return data from a table where the data is the max date of the previous month’s data submissions (-1 of current date)

Related Question