Dear expert

Please help me with a query command you can find below, Now I got problem with variables @P4, @P5, @P6, problem is that I want to show result that minus the value of @P1, @P2, @P3, this R.DatetimeSentToReceiver < DATEADD(MONTH, 2, @ReminderMonth) adds 2 months value, what i want is @P4 = @P4 - @P1 now I tried add this in query, and it return with error ofc. How to do this in the correct way? There are many way doing this, I need a great solution for it, what Im after is this, data in current months output, data in last month output. @ReminderMonth will be every months day 1 so that will change too.

Any help would be great.

DECLARE

@ReminderMonth date = '2018--01-01';

DECLARE @P1 int =

(

SELECT

COUNT(*)

FROM Invoice.Reminder R

WHERE

R.DatetimeSentToReceiver >= @ReminderMonth

AND R.DatetimeSentToReceiver < DATEADD(MONTH, 1, @ReminderMonth)

AND R.ReminderNumber = 1

),

@P2 int =

(

SELECT

COUNT(*)

FROM Invoice.Reminder R

WHERE

R.DatetimeSentToReceiver >= @ReminderMonth

AND R.DatetimeSentToReceiver < DATEADD(MONTH, 1, @ReminderMonth)

AND R.ReminderNumber = 2

),

@P3 int =

(

SELECT

COUNT(*)

FROM Invoice.Reminder R

WHERE

R.DatetimeSentToReceiver >= @ReminderMonth

AND R.DatetimeSentToReceiver < DATEADD(MONTH, 1, @ReminderMonth)

AND R.ReminderNumber = 3

),

@P4 int =

(

SELECT

COUNT(*)

FROM Invoice.Reminder R

WHERE

R.DatetimeSentToReceiver >= @ReminderMonth

AND R.DatetimeSentToReceiver < DATEADD(MONTH, 2, @ReminderMonth)

AND R.ReminderNumber = 1

) ,

@P5 int =

(

SELECT

COUNT(*)

FROM Invoice.Reminder R

WHERE

R.DatetimeSentToReceiver >= @ReminderMonth

AND R.DatetimeSentToReceiver < DATEADD(MONTH, 2, @ReminderMonth)

AND R.ReminderNumber = 2

) ,

@P6 int =

(

SELECT

COUNT(*)

FROM Invoice.Reminder R

WHERE

R.DatetimeSentToReceiver >= @ReminderMonth

AND R.DatetimeSentToReceiver < DATEADD(MONTH, 2, @ReminderMonth)

AND R.ReminderNumber = 3

) ;

SELECT CONVERT(varchar(7),@ReminderMonth,120) AS 'Current month', @P1 AS 'Reminder 1', @P2 AS 'Reminder 2', @P3 AS 'Reminder 3', SUM(@P1+@P2+@P3) AS 'Curry month reminder Total',

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial