August 26, 2022 at 3:27 pm
Can someone tell me why this does not pull any data?
Data consists of
fcinvoice, finvdate, fcstatus
0000071053, 2016-05-02 00:00:00.000, F
0000071894, 2016-09-13 00:00:00.000, F
0000081833, 2022-03-04 00:00:00.000, F
0000082570, 2022-08-22 00:00:00.000, F
When I run this select statement it does not pull any data but ith I remove either of the last finvdate between lines it pulls the correct data. What I would expect this to pull is the last 2 lines.
select fcustno, fnamount, finvdate
from armast where (finvdate Not Between '01/01/2017' and '01/01/2022') and
(finvdate between '01/01/2022' and '09/01/2022') and
(finvdate between '01/01/2006' and '01/01/2017') and
(fcstatus <> 'V' and fcstatus = 'F') and
fcustno = '004861'
August 26, 2022 at 3:31 pm
If a date is between '01/01/2022' and '09/01/2022', it cannot also be between '01/01/2006' and '01/01/2017'. You need to rethink your logic, as this will never produce any results.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 26, 2022 at 3:38 pm
OR Crap, sorry, thanks.
August 26, 2022 at 4:18 pm
I wouldn't write dates as '09/01/2022'
What does it mean? Is it 1st September or 9th January?
Better to write dates in ISO format so for 9th January 2022 write it as '20220109'
August 26, 2022 at 4:26 pm
OK, but I still can not figure this out. OR does not work. What I need to find out is to gather data for customers who had invoices before a date and after a date but not between dates. Any help on that would be much appreciated, Thanks
August 26, 2022 at 5:16 pm
OK, but I still can not figure this out. OR does not work. What I need to find out is to gather data for customers who had invoices before a date and after a date but not between dates. Any help on that would be much appreciated, Thanks
use not between, I'm not sure what you want but maybe something like this:
select fcustno, fnamount, finvdate
from armast
where (finvdate Not Between '01/01/2017' and '01/01/2022')
and (finvdate not between '01/01/2022' and '09/01/2022')
and (finvdate not between '01/01/2006' and '01/01/2017')
and (fcstatus <> 'V' and fcstatus = 'F')
and fcustno = '004861'
However, I can't see that's what you want as:
where (finvdate Not Between '01/01/2017' and '01/01/2022')
and (finvdate not between '01/01/2022' and '09/01/2022')
Is the same as:
where (finvdate Not Between '01/01/2017' and '09/01/2022')
August 26, 2022 at 5:29 pm
I finally got it. Here is the select statement.
Select cust.fcustno, fcompany, fcreated, cust.fsalespn, Sum(fnamount) as Sales from slcdpmx cust
inner join (select fcustno, fnamount, finvdate
from armast where
fcustno in (Select fcustno from armast where finvdate < Str(year(current_timestamp)-5)+'0101') --5 years ago
and fcustno in (Select fcustno from armast where finvdate > Case when month(current_timestamp) < 4 then str(year(current_timestamp)-1) + '1231'
when month(current_timestamp) > 3 and month(current_timestamp) < 7 then str(year(current_timestamp)) + '0331'
when month(current_timestamp) > 6 and month(current_timestamp) < 10 then str(year(current_timestamp)) + '0630'
else str(year(current_timestamp)) + '0930'
End) -- current quarter
and fcustno not in (Select fcustno from armast where finvdate > Str(year(current_timestamp)-5)+'0101'
and finvdate < Case when month(current_timestamp) < 4 then str(year(current_timestamp)-1) + '1231'
when month(current_timestamp) > 3 and month(current_timestamp) < 7 then str(year(current_timestamp)) + '0331'
when month(current_timestamp) > 6 and month(current_timestamp) < 10 then str(year(current_timestamp)) + '0630'
else str(year(current_timestamp)) + '0930'
End) -- Not between time
) ar
on ar.fcustno = cust.fcustno and finvdate > Case when month(current_timestamp) < 4 then str(year(current_timestamp)-1) + '1231'
when month(current_timestamp) > 3 and month(current_timestamp) < 7 then str(year(current_timestamp)) + '0331'
when month(current_timestamp) > 6 and month(current_timestamp) < 10 then str(year(current_timestamp)) + '0630'
else str(year(current_timestamp)) + '0930'
End
where fcreated < Str(year(current_timestamp)-5)+'0101' and ftype = 'C' --and cust.fcustno = '004861'
Group by cust.fcustno, fcompany, fcreated, cust.fsalespn
order by fcreated
August 26, 2022 at 7:10 pm
You should read the following article on the performance and other issues having to do with the STR() function.
And to those that say "Sometimes performance doesn't matter"... It always does... you just don't know it because you're only looking at the one query. You're not considering the "Death by a Thousand Cuts" because everyone says the same thing. Just imagine if your servers rant just 3 times faster. In order to do that, every has to stop writing code that's automatically 3 times slower.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 26, 2022 at 7:51 pm
SELECT fcustno,
fnamount,
finvdate
FROM dbo.armast
WHERE fcustno = '004861'
AND fcstatus = 'F'
AND (
finvdate between '01/01/2022' and '09/01/2022'
OR finvdate between '01/01/2006' and '01/01/2017'
);
N 56°04'39.16"
E 12°55'05.25"
August 28, 2022 at 4:13 pm
Quick question, can you post the DDL (create table) for the tables, sample data as an insert statement and the expected result set. This problem is trivial but in order to provide a proper solution, one needs the sample setup and data.
😎
August 29, 2022 at 11:37 am
Would like to post the data but I have a solution now. This will run once a quarter at night so it is not a huge hit on processing. I need to move on to other things but thanks all for your insights. I am a novice at this stuff. Not formally trained so I learn as I read your comments. Again, Thanks.
August 29, 2022 at 6:01 pm
Would like to post the data but I have a solution now. This will run once a quarter at night so it is not a huge hit on processing. I need to move on to other things but thanks all for your insights. I am a novice at this stuff. Not formally trained so I learn as I read your comments. Again, Thanks.
I was an "accidental DBA" so I have a grand appreciation for all that. So do most of the people on this forum and that's why they ask for the DDL and some test data. And consider that while you may have a working solution for something that runs "once a quarter at night", that won't always be so. You've just shortcut yourself from a bit more of learning that might prove beneficial when that happens. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2022 at 6:08 pm
Point noted and you are correct.
August 29, 2022 at 6:52 pm
First, whenever you use date/time intervals, you should always use half-closed intervals (one side includes '=' and the other does not). This is required for DATE data, but it's good practice to use it for all time data. Your calculations are mishandling any dates that fall on the last day of the previous quarter. How it is being mishandled depends on the datatype of your finvdate field.
Second, your horribly complex case expression to calculate the beginning of the current quarter (replacing the end of the previous quarter to use half-closed intervals) can be done with a much simpler formula:
DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', CURRENT_TIMESTAMP), '19000101')
Finally, your different date/time interval requirements should be handled with a CASE expression and GROUP BY clause rather than multiple calls to the same table.
A partial rewrite using all of the above (and not having any sample data to work with) looks like follows:
SELECT fcustno,
SUM(CASE WHEN finvdate < d.historical_dt THEN fnamount
WHEN finvdatte >= d.begin_qtr_dt THEN fnamount
ELSE 0
END) AS fnamount,
finvdate
FROM dbo.armast
CROSS APPLY (VALUES(DATEADD(YEAR, DATEDIFF(YEAR, '19000101', CURRENT_TIMESTAMP)-5, '19000101'), DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', CURRENT_TIMESTAMP), '19000101'))) d(historical_dt, begin_qtr_dt)
WHERE fcstatus = 'F'
GROUP BY fcustno
HAVING SUM(CASE WHEN finvdate < d.historical_dt THEN fnamount
WHEN finvdatte >= d.begin_qtr_dt THEN fnamount
ELSE 0
END) > 0
AND SUM(CASE WHEN finvdate >= d.historical_dt AND finvdatte < d.begin_qtr_dt THEN fnamount
ELSE 0
END) = 0 -- recent historical amount
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 29, 2022 at 7:03 pm
Yes, I got the date issue when looking at it some more. But the problem is when selecting the data, for new customers its is easy but for the older customers not so easy. This is a commission think where the person is getting commission on new customers and then on older customers who have not ordered with us in 5 years. Some more things were added so I went through the code and updated it accordingly based on quarters and the code running on the first day of the quarter. I was not aware of the QUARTER part of DateAdd so I will now update my code to use that. Thanks.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply