java - How can I setting parameters to query based on quarter date

Question

I need to find the start of the quarter startQ1,startQ2 ect. run a query that takes 6 query parameters. I dont really know a way of doing this. So if I can use a calendar to find that today is March1st (startq1) I wan't to run the query with the q1 array ... Hope that's clearer.

            Calendar cal = Calendar.getInstance();

        java.sql.Date startQ1 = java.sql.Date.valueOf("2014-03-01");
        java.sql.Date startQ2 = java.sql.Date.valueOf("2014-06-01");
        java.sql.Date startQ3 = java.sql.Date.valueOf("2014-09-01");
        java.sql.Date startQ4 = java.sql.Date.valueOf("2014-12-01");

        java.sql.Date [][] q1 = new java.sql.Date[3][2];
        q1[0][0] = java.sql.Date.valueOf("2014-07-01");
        q1[0][1] = java.sql.Date.valueOf("2014-09-30");
        q1[1][0] = java.sql.Date.valueOf("2014-10-01");
        q1[1][1] = java.sql.Date.valueOf("2014-12-31");
        q1[2][0] = java.sql.Date.valueOf("2015-04-01");
        q1[2][1] = java.sql.Date.valueOf("2015-06-30");

        java.sql.Date [][] q2 = new java.sql.Date[3][2];
        q2[0][0] = java.sql.Date.valueOf("2014-10-01");
        q2[0][1] = java.sql.Date.valueOf("2014-12-31");
        q2[1][0] = java.sql.Date.valueOf("2015-01-01");
        q2[1][1] = java.sql.Date.valueOf("2015-03-31");
        q2[2][0] = java.sql.Date.valueOf("2015-07-01");
        q2[2][1] = java.sql.Date.valueOf("2015-09-30");

        java.sql.Date [][] q3 = new java.sql.Date[3][2];
        q3[0][0] = java.sql.Date.valueOf("2015-01-01");
        q3[0][1] = java.sql.Date.valueOf("2015-03-31");
        q3[1][0] = java.sql.Date.valueOf("2015-04-01");
        q3[1][1] = java.sql.Date.valueOf("2015-06-30");
        q3[2][0] = java.sql.Date.valueOf("2015-10-01");
        q3[2][1] = java.sql.Date.valueOf("2015-12-31");

        java.sql.Date [][] q4 = new java.sql.Date[3][2];
        q4[0][0] = java.sql.Date.valueOf("2015-04-01");
        q4[0][1] = java.sql.Date.valueOf("2015-06-30");
        q4[1][0] = java.sql.Date.valueOf("2015-07-01");
        q4[1][1] = java.sql.Date.valueOf("2015-09-30");
        q4[2][0] = java.sql.Date.valueOf("2016-01-01");
        q4[2][1] = java.sql.Date.valueOf("2016-03-31");

        if(cal.getTime() == startQ1) { // I know can't actually test this way.
            for(int i = 0; i < 3; i++) {
                for(int j = 0; j < 2; j++) {
                ps.setDate(1, q1[i][j]);
                ps.setDate(2, q1[i][j]);
                ps.setDate(3, q1[i][j]);
                ps.setDate(4, q1[i][j]);
                ps.setDate(5, q1[i][j]);
                ps.setDate(6, q1[i][j]);
                }
            }
        }

Here's the query:

            PreparedStatement ps=conn.prepareStatement("select distinct b.new_tagID,"
                + "Case WHEN convert(nvarchar(100),b.new_account ) IS NULL THEN 'NO_DATA' ELSE convert(nvarchar(100),"
                + "b.new_account ) END AS AccountID,"
                + "Case WHEN c.Name IS NULL THEN 'NO_DATA' ELSE c.Name END as  Name,"
                + "Case WHEN convert(nvarchar(100),a.OwnerId) IS NULL THEN 'NO_DATA' ELSE convert(nvarchar(100),"
                + "a.OwnerId) END as OwnerId, b.new_EOL,"
                + "Case WHEN a.JobTitle IS NULL THEN 'NO_DATA' ELSE a.JobTitle END as JobTitle,"
                + "Case WHEN a.FirstName IS NULL THEN 'NO_DATA' ELSE a.FirstName END as FirstName,"
                + "Case WHEN a.LastName IS NULL THEN 'NO_DATA' ELSE a.LastName END as LastName,"
                + "Case WHEN a.EMailAddress1 IS NULL THEN 'NO_DATA' ELSE a.EMailAddress1 END as EMailAddress1,"
                + "a.new_Support_RenewalContact,d.YomiFullName,d.InternalEMailAddress,"
                + "(select InternalEMailAddress from dbo.SystemUserBase where SystemUserId = e.new_VPId ) new_VPId,"
                + "(select InternalEMailAddress from dbo.SystemUserBase where SystemUserId = e.new_RVPId ) new_RVPId,"
                + "b.new_RenewalOpptyIDyr2,b.new_RenewalOpptyIDyr3,b.new_RenewalOpptyIDyr4,b.new_RenewalOpptyIDyr5,"
                + "Case  when b.new_LevelofSupport = 100000000 then 'Platinum' "
                + "when b.new_LevelofSupport = 100000001 then 'Standard'"
                + "when b.new_LevelofSupport = 100000002 then 'NONE'"
                + "when b.new_LevelofSupport = 100000003 then 'Standard with AR'"
                + "end as LevelOFSupport"
                + " FROM"
                + " dbo.new_assetExtensionBase as b left outer join dbo.contact as a on a.AccountID=b.new_account"
                + " left outer join dbo.AccountBase as c on b.new_account=c.AccountId"
                + " left outer join dbo.SystemUserBase as d on c.OwnerId=d.SystemUserId"
                + " left outer join dbo.SystemUserExtensionBase as e on d.SystemUserId=e.SystemUserId "
                + "WHERE "
                + "(b.new_EOL >= ?) and "
                + "(b.new_EOL <= ? ) or "
                + "(b.new_EOL >= ?) and "
                + "(b.new_EOL <= ?) or "
                + "(b.new_EOL >= ?) and "
                + "(b.new_EOL <= ?) "
                + " order by Case WHEN convert(nvarchar(100),b.new_account ) IS NULL THEN 'NO_DATA' ELSE convert(nvarchar(100),b.new_account ) END,"
                + " a.new_Support_RenewalContact desc,b.new_RenewalOpptyIDyr2 desc,b.new_RenewalOpptyIDyr3 desc,b.new_RenewalOpptyIDyr4 desc,"
                + "b.new_RenewalOpptyIDyr5 desc");

Answer 1

Your question is unintelligible. You should edit it.

I can help with one aspect: How to get quarters of the year.

First: Do not use the java.util.Date & Calendar classes. They are notoriously troublesome. Instead use either Joda-Time or the new java.time.* package bundled with Java 8.

If your definition of quarters is the first day of the year and then every 3 months, each starting on the 1st of the month, then you are working way too hard in your example code. You know that regardless of year, the month numbers will always be the same:

• 1 = January
• 4 = April
• 7 = July
• 10 = October

You can use that month number when constructing a date-time value. Alternatively, you can construct a date-time for first day of the year, then add 3 months repeatedly.

A Joda-Time DateTime, like a java.util.Date, represents both a date and a time-of-day. So you need to set the time of day to the first moment of the day for your quarters. When in doubt, call the DateTime instance's withTimeAtStartOfDay method.

You should specify a time zone as the beginning of the day depends on the particular time zone. If you fail to specify a time zone, the JVM's default time zone is used.

DateTimeZone timeZone = DateTimeZone.forID( "America/Montreal" );
DateTime q1_2014 = new DateTime( 2014, 1, 1, 0, 0, 0, timeZone );
DateTime q2_2014 = q1_2014.plusMonths( 3 );
DateTime q3_2014 = q1_2014.plusMonths( 6 );
DateTime q4_2014 = q1_2014.plusMonths( 9 );