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),@Remind erMonth,12 0) AS 'Current month', @P1 AS 'Reminder 1', @P2 AS 'Reminder 2', @P3 AS 'Reminder 3', SUM(@P1+@P2+@P3) AS 'Curry month reminder Total',
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),@Remind
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, how does the whole query looks like? Sorry man Im abit tired...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
(@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.