Link to home
Start Free TrialLog in
Avatar of WeTi
WeTi

asked on

More SQL Query questions

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',
Avatar of WeTi
WeTi

ASKER

Now i did resolve this by doing this:
(@P4-@P1) AS 'Last 2 months Reminder 1'
(@P5-@P2) AS 'Last 2 months Reminder 2'
(@P6-@P3) AS 'Last 2 months Reminder 3'

As I said this took time from SQL query to show, too much processes, now I need some better solution.
SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of WeTi

ASKER

Thanks, how does the whole query looks like? Sorry man Im abit tired...
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of WeTi

ASKER

Now I realized how much you can do with sql query... thanks all for your kindly help, I will use Vitors solution. Thanks again.