如何根据季度日期设置参数进行查询 [英] How can I setting parameters to query based on quarter date
本文介绍了如何根据季度日期设置参数进行查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要找到开始季度startQ1，startQ2等等。运行一个需要6个查询参数的查询。我真的不知道这样做的方式。所以如果我可以使用一个日历来查找今天是March1st（startq1），我不会用q1数组运行查询...希望更清楚。
日历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）{//我知道实际上不能这样测试。 （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]）;
}
}
}
这是查询： p>
PreparedStatement ps = conn.prepareStatement（select distinct b.new_tagID，
+Case WHEN convert（nvarchar ），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作为OwnerId，b.new_EOL，
+案例WHEN a.JobTitle IS NULL THEN'NO_DATA'ELSE a.JobTitle END作为JobTitle，
+案例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 a s LastName
+案例WHEN a.EMailAddress1 IS NULL THEN'NO_DATA'ELSE a.EMailAddress1 END as EMailAddress1，
+a.new_Support_RenewalContact，d.YomiFullName，d.InternalEMailAddress，
+（从dbo.SystemUserBase中选择InternalEMailAddress，其中SystemUserId = e.new_VPId）new_VPId
+（从Dbo.SystemUserBase选择InternalEMailAddress，其中SystemUserId = e.new_RVPId）new_RVPId
+ b.new_RenewalOpptyIDyr2，b.new_RenewalOpptyIDyr3，b.new_RenewalOpptyIDyr4，b.new_RenewalOpptyIDyr5
+当b.new_LevelofSupport = 100000000然后'Platinum'
+当b.new_LevelofSupport = 100000001时， b.new_LevelofSupport = 100000002然后'NONE'
+当b.new_LevelofSupport = 100000003然后'标准与AR'
+结束为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> =？）和
+（b.new_EOL <=？ ）或
+（b.new_EOL> =？）和
+（b.new_EOL <=？）或
+（b.new_EOL&
+（b.new_EOL< =？）
+按WHIT转换顺序（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 ）;
解决方案
你的问题是无法理解的，你应该编辑它
我可以帮助一方面：如何获得今年的季度。
第一：不要使用java.util.Date& Calendar类，它们是非常麻烦的，而是使用 Joda-Time 或新的java.time。* pa ckage与Java 8捆绑。
如果您对季度的定义是一年中的第一天，然后每3个月，每个月从1月1日开始，那么您在您的示例代码中的工作方式太难了。你知道，不管年份如何，月份数字总是一样的：
- 1 =一月
- 4 =四月
- 7 =七月
- 10 =十月
您可以在构建日期时间值时使用该月份号码。或者，您可以构建一年的第一天的日期时间，然后重复添加3个月。
Joda-Time DateTime ，像java.util.Date一样，代表日期和时间 - 当天。所以你需要设定一天中的时间到你宿舍的一天的第一时间。如有疑问，请调用DateTime实例的 withTimeAtStartOfDay 方法。
您应该指定一个时区，因为一天的开始依赖于特定的时区。如果您未指定时区，则使用JVM的默认时区。
DateTimeZone timeZone = DateTimeZone.forID（America /蒙特利尔）;
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）;
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");
解决方案
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 );
这篇关于如何根据季度日期设置参数进行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
