SQL Query between dates

eponcede · January 16, 2019, 9:51pm

I have 2 tables MailOutDate and InvoiceDetail what I need to do is combine those tables to get the sum of Sales per state between 2 dates. The dates will be given by the MailOutDate column.

I want to know the sum of the next 30 days after and before a MailoutDate Date per state, so that I end up with a table that looks like Book 1 with the values in columns E,F.

mannesravya · January 16, 2019, 9:56pm

Whats the Sales column in the table. Image is not clear i cant find that column.

eponcede · January 16, 2019, 10:00pm

Well that is the thing, i am not sure how to add 30 days to a value in SQL.

mannesravya · January 16, 2019, 10:02pm

select DATEADD(DAY , -30, Mailoutdate) to get before 30 days
select DATEADD(DAY , 30,Mailoutdate) to get after 30 days

eponcede · January 16, 2019, 10:21pm

OK, thanks. But then how do I combine it with the invoice detail table to ge the sum of the 2 dates.

eponcede · January 16, 2019, 10:30pm

Colum K in the InvoiceDetail file

bitsmed · January 16, 2019, 11:25pm

Something like this just might work for you:

select a.mailoutstate
      ,a.mailoutdate
      ,dateadd(day,30,a.mailoutdate) as next30days
      ,sum(case
              when b.invoicedate>=a.mailoutdate
              then isnull(b.salesprice,0)
              else 0
           end
          )
       as next30sum
      ,dateadd(day,-30,a.mailoutdate) as prior30days
      ,sum(case
              when b.invoicedate>=a.mailoutdate
              then 0
              else isnull(b.salesprice)
           end
          )
       as prior30sum
  from mailoutdate as a
       left outer join invoicedetail as b
                    on b.invoicestate=a.mailoutstate
                   and b.invoicedate>=dateadd(day,-30,a.mailoutdate)
                   and b.invoicedate<dateadd(day,31,a.mailoutdate)
 group by a.mailoutstate
         ,a.mailoutdate
;

mannesravya · January 17, 2019, 5:27pm

I think some changes may be required in this code.
select a.mailoutstate
,a.mailoutdate
,dateadd(day,30,a.mailoutdate) as next30days
,sum(case
when b.invoicedate<dateadd(day,30,a.mailoutdate)
then isnull(b.salesprice,0)
else 0
end
)
as next30sum
,dateadd(day,-30,a.mailoutdate) as prior30days
,sum(case
when b.invoicedate>=dateadd(day,-30,a.mailoutdate)
then 0
else isnull(b.salesprice,0)
end
)
as prior30sum
from mailoutdate as a
left outer join invoicedetail as b
on b.invoicestate=a.mailoutstate
and b.invoicedate>=dateadd(day,-30,a.mailoutdate)
and b.invoicedate<dateadd(day,31,a.mailoutdate)
group by a.mailoutstate
,a.mailoutdate

eponcede · January 17, 2019, 5:49pm

thank you all for your help. I am getting results but not the desired ones, not sure what is wrong.

SELECT
a.MailOutState,
a.MailOutDate,
AddDays(a.MailOutDate,30) as Next30Days,
AddDays(a.MailOutDate,-30) as Prior30Days,
SUM(CASE
WHEN b.InvoiceDate >= a.MailOutDate THEN b.InvoiceSalesPrice
ELSE 0 END ) as Next30Sum,

SUM(CASE
	WHEN b.InvoiceDate >= a.MailOutDate THEN 0
	ELSE b.InvoiceSalesPrice END ) as Prior30Sum

FROM [MailOutData] as a
LEFT JOIN [InvoiceDetail] as b
ON b.InvoiceState = a.MailOutState
AND b.InvoiceDate >= AddDays(a.MailOutDate,-30)
AND b.InvoiceDate < AddDays(a.MailOutDate,30)
GROUP BY a.MailOutState,a.MailOutDate
ORDER BY a.MailOutDate

So the sum of the 30days should be depending on the MailOutData and State. For this particual example.

eponcede · January 17, 2019, 5:51pm

will try this in a bit and come back to you. Thank you

bitsmed · January 17, 2019, 6:26pm

Please provide:

table descriptions in the form of create statements
sample data in the form of insert statement(s)
expected outout from the sample data you provide

eponcede · January 17, 2019, 11:57pm

Here is my DB

CREATE TABLE invoicedetail (
InvoiceDate text,
InvoiceState text,
InvoiceSalesPrice double DEFAULT NULL
)
CREATE TABLE mailoutdata (
MailOutState text COLLATE utf8mb4_unicode_ci,
MailOutQty int(11) DEFAULT NULL,
MailOutDate text COLLATE utf8mb4_unicode_ci
)
INSERT INTO invoicedetail VALUES ('7/13/2018','WY',69),('9/6/2018','WY',69),('9/6/2018','WY',29),('9/6/2018','WY',-35.6),('9/6/2018','WY',139),('9/6/2018','WY',119),('9/26/2018','WY',92),('7/12/2018','AK',88),('7/12/2018','AK',49),('7/18/2018','AK',89),('8/28/2018','AK',259),('8/28/2018','AK',100),('8/28/2018','AK',-35.9);

INSERT INTO mailoutdata VALUES ('WY',562,'2018/29/08'),('AK',6527,'2018/08/08');

for some reason i was not able to get my datetime types working, but i am sure that can be easily solved.

mannesravya · January 18, 2019, 1:07am

For this sample data whats the exact output you would like to have.

eponcede · January 18, 2019, 2:00am

For that example, the results will be shown in a new table consisting of the columns in the below image. The correct sum that I am expecting is in columns B and F at the far right (inside red box)
2019-01-17%2019_55_55-Microsoft%20Excel

Is this better?

mannesravya · January 18, 2019, 2:58am

ok.Thank you

mannesravya · January 18, 2019, 3:05am

select a.MailOutState
,a.MailOutDate
,dateadd(day,30,a.MailOutDate) as next30days
,cast(sum(case
when b.invoicedate>=a.MailOutDate
then isnull(b.InvoiceSalesPrice,0)
else 0
end
) as decimal(10,2))
as Sum30DaysAfter
,dateadd(day,-30,a.MailOutDate) as prior30days
,sum(case
when b.invoicedate<=a.MailOutDate
then isnull(b.InvoiceSalesPrice,0)
else 0
end
)
as Sum30DaysBefore
from mailoutdata as a
left outer join invoicedetail as b
on b.invoicestate=a.MailOutState
and b.invoicedate>=dateadd(day,-30,a.mailoutdate)
and b.invoicedate<dateadd(day,31,a.mailoutdate)
group by a.MailOutState
,a.mailoutdate
order by a.MailOutState desc

eponcede · January 18, 2019, 3:11pm

I changed the dateadd() function to the standard SQL because I was getting that it was not defined.

 select a.MailOutState
,a.MailOutDate
,date_add(a.MailOutDate, INTERVAL 30 DAY) as next30days
,cast(sum(case
when b.invoicedate>=a.MailOutDate
then b.InvoiceSalesPrice
else 0
end
) as decimal(10,2)) as Sum30DaysAfter,
date_add(a.MailOutDate, INTERVAL -30 DAY) as prior30days,
sum(case
when b.invoicedate<=a.MailOutDate
then b.InvoiceSalesPrice
else 0
end
)as Sum30DaysBefore
from mailoutdata as a
left outer join invoicedetail as b
on b.invoicestate=a.MailOutState
and b.invoicedate>=date_add(a.MailOutDate, INTERVAL -30 DAY)
and b.invoicedate<date_add(a.MailOutDate, INTERVAL 30 DAY)

group by a.MailOutState
,a.mailoutdate
order by a.MailOutState desc

and now I am getting a different error

eponcede · January 18, 2019, 3:29pm

for some reason also the isnull() you have is not working, i am runnig the query below and getting the output in the image.

select a.MailOutState
,a.MailOutDate
,date_add(a.MailOutDate, INTERVAL 30 DAY) as next30days
,cast(sum(case
when b.invoicedate>=a.MailOutDate
then b.InvoiceSalesPrice
else 0
end
) as decimal(10,2)) as Sum30DaysAfter,
date_add(a.MailOutDate, INTERVAL -30 DAY) as prior30days,
sum(case
when b.invoicedate<=a.MailOutDate
then b.InvoiceSalesPrice
else 0
end
)as Sum30DaysBefore
from mailoutdata as a
left outer join invoicedetail as b
on b.invoicestate=a.MailOutState
and b.invoicedate>=date_add(a.MailOutDate, INTERVAL -30 DAY)
and b.invoicedate<date_add(a.MailOutDate, INTERVAL 30 DAY)

group by a.MailOutState
,a.mailoutdate
order by a.MailOutState desc

eponcede · January 18, 2019, 4:24pm

The sum cases should have several cases dont they.

When InvoiceDate is < than the Prior30Days it should not be counted
When InvoiceDate is > than the Next30Days it should not be counted
thus leaving only in between >=Prior30Days and <=MailOutDate (this is the sum i am looking for)

bitsmed · January 18, 2019, 7:36pm

Corrected:

isnull issue
table name
converted dateadd from MSSQL to MySQL format (you do know, this forum is focusing on MSSQL?)

This is basically the same query as the one I showed you earlier (at least the logic is):

select a.mailoutstate
      ,a.mailoutdate
      ,date_add(a.mailoutdate,interval 30 days) as next30days
      ,sum(case
              when b.invoicedate>=a.mailoutdate
              then isnull(b.invoicesalesprice,0)
              else 0
           end
          )
       as next30sum
      ,date_add(a.mailoutdate,interval -30 days) as prior30days
      ,sum(case
              when b.invoicedate>=a.mailoutdate
              then 0
              else isnull(b.invoicesalesprice,0)
           end
          )
       as prior30sum
  from mailoutdata as a
       left outer join invoicedetail as b
                    on b.invoicestate=a.mailoutstate
                   and b.invoicedate>=date_add(a.mailoutdate,interval -30 days)
                   and b.invoicedate<date_add(a.mailoutdate,interval 31 days)
 group by a.mailoutstate
         ,a.mailoutdate
;