ok but what happens years from now having the 2015 set?
Also, prefer being able to see it on the panel like your other formula in the beginning.
Having the Declare, it does not allow you to see it in the panel view
you can set it to (date will change every calendar year):
declare @season int=year(getdate());
or (date will change every Juli 1. = current season):
declare @season int=datediff(year,0,dateadd(month,(datediff(month,0,getdate()))+6,0))+1900;
Yes, tried the 1st one, which gives me 2015 and 2014 though?
then tried it with +1 for 2016, which gives me the 5305 with null membership (what I've been trying to get but included with the rest of the data output, so thank you for that!)
Will try that.
Need to see current year AND future years. why i was trying to do >=
Gotten a lot further for sure
Ok the last formula just shows 2016 record.
Also, the Declare is disallowing grid pane view.
I do have a Function I am using now that mimics the DateSeial() in Access using this:
CREATE FUNCTION dbo.DateSerial
(
@year int ,
@month int ,
@day bigint
)
RETURNS datetime
AS
BEGIN
DECLARE @date datetime
-- catch invalid year entries and default appropriately
SET @year =
CASE WHEN @year < 1900 THEN 1900
WHEN @year > 9999 THEN year(getdate())
ELSE @year
END
-- convert date by adding together like yyyymmdd
SET @date = cast(@year * 10000 + 101 AS char(8))
;
-- Add to date the proper months subtracting 1,
-- since we used 1 as start instead of zero.
SET @date = dateadd(mm , @month - 1 , @date)
-- Add to date the proper days subtracting 1,
-- since we used 1 as start instead of zero.
SET @date = dateadd(dd , @day - 1 , @date)
;
RETURN @date ;
END
;then
>= dbo.DateSerial(YEAR(GETDATE()), 6, 30) on EndDate.
It works great.
I tried to incorporate that into your formula for the date issue but failing.
[code]
declare @season int=Year(GetDate());
declare @seasonstart date=dateadd(month,6,dateadd(year,@season-1901,0));
declare @seasonend date=dateadd(year,1,@seasonstart);
select c.ClubNo
,c.SortName
,c.ClubName
,c.BSProgram
,c.ClubSection
,c.Code
,c.President
,c.Email
,c.Phone
,c.FacilityName
,c.StreetOne
,c.StreetTwo
,c.City
,c.State
,c.PostalCode
,c.URL
,c.Certified
,c.FacilityLastUpdate
,c.ByLawsUploadDate
,c.ProgramStatusId
,c.Status
,year(p.EndDate) as YearEnd
,p.MembershipTypeId
,p.PersonId
,rtrim(p.InvoiceNumber) as InvNo
from dbo.v060ClubOfficersPresOrNot as c
left outer join attribute.PersonMembership as p
on p.OrganizationId=c.ClubID
and p.StartDate<@seasonend
and p.EndDate>=@seasonstart
and p.MembershipTypeId in (1,2,3,4)
where c.ClubNo = 5305[/code]
This is producing 17 records, which includes 1 record from 2014
but then not sure where the 16th record in 2015 is coming since there are only 15 in 2015.
Yet when I specify 2015 in the Declare statement I get 15 and when I replace with 2016 I get the club record without membership.
These are accurate.
However when attempting to get 2015 and 2016 and future years, doesn't seem to work??
I tried removing the startdate and the declarations then put the dateserial function for enddate within the LEFT OUTER JOIN and I get an error
Put
SET NOCOUNT ON
in front of the first statement perhaps?
(You can turn it back OFF after the last statement if you need to, but it is only set for the session, so probably won't be a problem leaving in that state)
bitsmed,
i've been so focused on getting the data right for 5305 to work like 8004 i didn't notice this other issue.
ClubNo 22 is repeating the invoices??
This shows 22 records correctly
[code]
SELECT c.ClubID, c.ClubNo, c.SortName, c.ClubName, c.BSProgram, c.ClubSection, c.Code, c.President,
c.Email, c.Phone, c.FacilityName, c.StreetOne, c.StreetTwo, c.City, c.State, c.PostalCode,
c.URL, c.Certified, c.FacilityLastUpdate, c.ByLawsUploadDate,
c.ProgramStatusId, c.Status,
YEAR(p.EndDate) AS YearEnd, RTRIM(p.InvoiceNumber) AS InvNo, p.PersonId, p.MembershipTypeId
FROM dbo.v060ClubOfficersPresOrNot AS c LEFT OUTER JOIN
dbo.PersonMembership AS p ON p.OrganizationId = c.ClubID AND p.EndDate >= GETDATE() AND GETDATE() >= DATEADD(month, - 6 - CASE WHEN p.MembershipTypeId = 3 THEN 4 ELSE 0 END,
DATEADD(year, DATEDIFF(year, 0, DATEADD(month, DATEDIFF(month, 0, p.EndDate) + 6, 0)), 0))
AND p.MembershipTypeId IN (1, 2, 3, 4)
GROUP BY c.ClubID, c.ClubNo, c.SortName, c.ClubName, c.BSProgram, c.ClubSection, c.Code, c.President, c.Email, c.Phone, c.FacilityName, c.StreetOne, c.StreetTwo,
c.City, c.State, c.PostalCode, c.URL, c.Certified, c.FacilityLastUpdate, c.ByLawsUploadDate, c.ProgramStatusId,
c.Status, YEAR(p.EndDate), p.PersonId, RTRIM(p.InvoiceNumber), p.MembershipTypeId
HAVING (c.ClubNo IN (5305, 8004, 22))[/code]
So can this part
p.EndDate >= GETDATE() AND GETDATE() >= DATEADD(month, - 6 - CASE WHEN p.MembershipTypeId = 3 THEN 4 ELSE 0 END,
DATEADD(year, DATEDIFF(year, 0, DATEADD(month, DATEDIFF(month, 0, p.EndDate) + 6, 0)), 0))
handle >=Year(GetDate())?
That looks a bit risky?? Likely that you will get perfectly reasonable, but wrong, results if you accidentally have a year > 9999
If you changed that to
WHEN @year > 9999 THEN NULL
then that should mean that the function returns NULL for invalid year, which should cause other things to break etc. and thus be detected.
If you are using SQL2012 (I don't think it was in SQL2008??) there is a new function DateFromParts which will do that job:
select DateFromParts(Year(GetDate()), 6, 30)
gives:
2015-06-30
It will give you an error if you attempt to do
select DateFromParts(Year(GetDate()), 6, 31)
which might be an additional benefit?
Oh that's great. Though I'm using 2008 on my laptop at the moment.
But thank you, that will help not having to worry about the DateSerial Function on 2012. I'll test the DateFromParts.
In the meanwhile ...
To summarize issue,
when the formula is changed to handle >=EndYear it does not produce the correct information for 5305.
the correct information is only produced when doing an =EndYear.
The details need to include all years correctly from current and future years.
I don't know why it does not, meaning why it's only correct when it's set to look at the individual year but not when it's a range of years.
ClubNo 5305 has memberships in 2015 but not in 2016.
It needs to show both. It only shows one or the other based on the set EndYear. When it's set to >= it does not show the row where it doesn't have the membership like it does for ClubNo 8004.
ClubNo 8004 has no memberships in the current year or future and does show when it's set to 2015 or 2016 or range.
ClubNo 22 has memberships from 2015 - 2019 and shows in both and all cases.
What's the matter with 5305 data not producing the no membership in the future year?
Difference I see is that they have a membership in 2015 but not in 2016 whereas 8004 doesn't have a membership in any years.
But that's the issue, it shouldn't matter, 5305 should show 2015 memberships and that they don't have in the future year(s).
Hi Kristen,
I upgraded to 2014 and this works! Thank you!
DateFromParts(Year(GetDate()), 6, 30)
Blimey that was quick! Doesn't your organisation required :
- Budget
- Upgrade plan
- Shiny New Server
- Test plan for migration of all databases
- A plan for the night-out-to-celebrate
Lol!
Upgraded my computer with the express.
Though the date format works the issue with getting all the correct data is not yet resolved.
I did try a simple query using the date format and Is Null where I originally started and came on here for help.
I get 8004 but not for 5305
SELECT TOP (100) PERCENT dbo.PersonMembership.OrganizationId, dbo.PersonMembership.PersonId, dbo.PersonMembership.MembershipTypeId,
dbo.PersonMembership.EndDate, dbo.PersonMembership.InvoiceNumber, dbo.PersonMembership.EndYear, dbo.PersonMembership.PaymentDate,
dbo.v060ClubOfficersPresOrNot.ClubNo
FROM dbo.PersonMembership RIGHT OUTER JOIN
dbo.v060ClubOfficersPresOrNot ON dbo.PersonMembership.OrganizationId = dbo.v060ClubOfficersPresOrNot.ClubID
WHERE (dbo.PersonMembership.EndDate >= DateFromParts(YEAR(GETDATE()), 6, 30) OR
dbo.PersonMembership.EndDate IS NULL) AND (dbo.v060ClubOfficersPresOrNot.ClubNo = 5305 OR
dbo.v060ClubOfficersPresOrNot.ClubNo = 8004)
ORDER BY dbo.v060ClubOfficersPresOrNot.ClubNo
Anyway to use
in this? I tried but failing
p.EndDate >= GETDATE() AND GETDATE() >= DATEADD(month,
- 6 - CASE WHEN p.MembershipTypeId = 3 THEN 4 ELSE 0 END, DATEADD(year, DATEDIFF(year, 1, DATEADD(month, DATEDIFF(month, 0, p.EndDate) + 6, 0)), 0))
One more try. This will not produce exactly the result from your initial query (more fields added), but hopefully you can use it anyway.
For the detailed result, try this:
with seasontally(seasonyear,seasonstartdate,seasonenddate)
as (select (datediff(month,0,max(EndDate))+6)/12+1900 /* last season year */
,dateadd(month,(datediff(month,0,max(EndDate))-6)/12*12+6,0) /* last season start date */
,dateadd(month,(datediff(month,0,max(EndDate))+6)/12*12+6,0) /* last season end date+1 */
from dbo.personmembership
union all
select seasonyear-1
,dateadd(year,-1,seasonstartdate)
,dateadd(year,-1,seasonenddate)
from seasontally
where seasonyear>(datediff(month,0,getdate())-6)/12+1900 /* previous season year */
)
select s.*
,count(p.personid) over(partition by c.clubid,s.seasonyear) as members
,c.*
,p.membershiptypeid
,p.personid
,rtrim(p.invoicenumber) as invno
from (select distinct
clubno
,sortname
,clubname
,bsprogram
,clubsection
,code
,president
,email
,phone
,facilityname
,streetone
,streettwo
,city
,[state]
,postalcode
,url
,certified
,facilitylastupdate
,bylawsuploaddate
,programstatusid
,[status]
,clubid
from dbo.v060clubofficerspresornot
) as c
outer apply seasontally as s
left outer join dbo.personmembership as p
on p.organizationid=c.clubid
and p.startdate<s.seasonenddate
and p.enddate>=s.seasonstartdate
order by s.seasonyear
,c.sortname
,c.clubname
,p.personid
;
For the sum result, try this:
with seasontally(seasonyear,seasonstartdate,seasonenddate)
as (select (datediff(month,0,max(EndDate))+6)/12+1900 /* last season year */
,dateadd(month,(datediff(month,0,max(EndDate))-6)/12*12+6,0) /* last season start date */
,dateadd(month,(datediff(month,0,max(EndDate))+6)/12*12+6,0) /* last season end date+1 */
from dbo.personmembership
union all
select seasonyear-1
,dateadd(year,-1,seasonstartdate)
,dateadd(year,-1,seasonenddate)
from seasontally
where seasonyear>(datediff(month,0,getdate())-6)/12+1900 /* previous season year */
)
select s.seasonyear
,c.clubno
,c.sortname
,c.clubname
,count(distinct p.personid) as members
from (select distinct
clubid
,clubno
,sortname
,clubname
from dbo.v060clubofficerspresornot
) as c
outer apply seasontally as s
left outer join dbo.personmembership as p
on p.organizationid=c.clubid
and p.startdate<s.seasonenddate
and p.enddate>=s.seasonstartdate
group by s.seasonyear
,c.clubno
,c.sortname
,c.clubname
order by s.seasonyear
,c.sortname
,c.clubname
;
Now, I'll be happy to explain these queries, but first, lets see if this is anywhere near the result you're looking for.
Oh yay! THANK YOU!
Here are comments and questions on this
it is producing the overall. I get that you applied it to all 4 years due to the MemberTypeID = 3 which is a 4 year membership.
If that's how it can be done then that's fine as long as the membership updates IF they end-up have memberships in 2017, etc ... as right now they are showing 0 for 5305 and 8004 for the next 4 years. It's true both do not have memberships in 2016 but they may in 2017 or later on in that 4 year period since their memberships are only for 1 year at a time as they fall in the MemberTypeID IN (1,2,4).
NOTE: the details, the 1st formula in the last post shows 7/1 instead of 6/30.
And is there a way it can be viewed in the Grid Pane?
Or at a minimum be saved as a View that the Crystal Report will need to link to for the reporting tool for the end user?
eeck, actually ClubNo 22 count is off.
When I looked at the details for 22 from 2015-2019 they have 82 invoices but your formulas shows totaling 91?
seasonyear clubno sortname clubname members
2015 22 ADIRONDACKS SC of the Adirondacks, Inc. 44
2016 22 ADIRONDACKS SC of the Adirondacks, Inc. 42
2017 22 ADIRONDACKS SC of the Adirondacks, Inc. 3
2018 22 ADIRONDACKS SC of the Adirondacks, Inc. 2
2019 22 ADIRONDACKS SC of the Adirondacks, Inc. 0
It appears some of the PersonID and InvoiceNumber combo are duplicating for some reason on these.
I see and what it should be the counts are:
- 40 invoices for 2015
- 39 invoices for 2016
- 1 for 2017
- 2 for 2018
- 0 for 2019
Total: 82 from 2015-2019
But the counts for 5305 is correct
Here's my View with the correct details based on the Group for the fields from c and fields from p
SELECT c.ClubNo, c.SortName, c.ClubName, c.StreetOne, c.StreetTwo, c.CareOf, c.City, c.State, c.PostalCode, c.OrgType, c.ClubRegion, c.ClubSection,
c.Email, c.Phone, c.FacilityName, c.PresClubSec, c.PresCareOf, c.PresAdd1, c.PresAdd2, c.PresCity, c.PresState, c.PresZip, c.President, c.PresEmail, c.PresPhone,
c.URL, c.Certified, c.Code, c.FacilityLastUpdate, c.ByLawsUploadDate, c.BSProgram, c.ProgramStatusId, c.Status, c.IsActive, p.MembershipTypeId, p.PersonId,
p.InvoiceNumber, p.EndDate, YEAR(p.EndDate) AS EndYear
FROM dbo.v060ClubOfficersPresOrNot AS c LEFT OUTER JOIN
dbo.PersonMembership AS p ON c.ClubID = p.OrganizationId
GROUP BY c.ClubNo, p.MembershipTypeId, p.PersonId, p.InvoiceNumber, p.EndDate, YEAR(p.EndDate), c.SortName, c.ClubName, c.StreetOne, c.StreetTwo, c.CareOf, c.City,
c.State, c.PostalCode, c.OrgType, c.ClubRegion, c.ClubSection, c.Email, c.Phone, c.FacilityName, c.PresClubSec, c.PresCareOf, c.PresAdd1, c.PresAdd2, c.PresCity,
c.PresState, c.PresZip, c.President, c.PresEmail, c.PresPhone, c.URL, c.Certified, c.Code, c.FacilityLastUpdate, c.ByLawsUploadDate, c.BSProgram,
c.ProgramStatusId, c.Status, c.IsActive