检索特定日期之间出现的最多条目 [英] Retrieving most appearing entry on between specific dates

问题描述

大家好，



我正在尝试为我的网站实施一个应用程序，因为我在特定的日期时间内检索了大部分销售的产品，所以我有一个表销售，它包含产品的销售，



我创建以下查询，它工作正常，它检索最大销售额在2/1之间的产品/ 2014年和2014年2月2日（我的数据库使用美国日期时间表（MM / DD / YYYY））

 SELECT TOP（1）productId FROM Table1 WHERE（Sales_Date BETWEEN '2/1/2014'和'2/2/2014'）AND（productId =（SELECT TOP（1）productId FROM Table1 AS Table1_1 GROUP BY productId ORDER BY COUNT（*）DESC））

我的问题是如何为用户选择日期？



我已经尝试转换我的查询，如下所示：

SELECT TOP（1）productId FROM Table1 WHERE（Sales_Date BETWEEN+ time1 +AND+ time2 +）AND（productId =（SELECT TOP（1）productId FROM Table1 AS Tab le1_1 GROUP BY productId ORDER BY COUNT（*）DESC））

我尝试用两个caledars的日期设置time1和time2但它不起作用

（调试器返回：'12'附近的语法不正确。关键字'ORDER'附近的语法不正确）

DateTime time1 = Calendar1.SelectedDate;

DateTime time2 = Calendar2.SelectedDate;





我也尝试将日期时间转换为美国表格，但要么不起作用

DateTimeFormatInfo dtf2 = new DateTimeFormatInfo（）;

dtf2.ShortDatePattern =MM / dd / yyyy;

dtf2。 DateSeparator =/;

string date22 = time2.ToString（MM / dd / yyyy）;

DateTime new2 = Convert.ToDateTime（time1，dtf2）;

（我在time2做同样的事情）



任何想法我怎么能设置正确的查询日期才能使用这个app正确吗？



Thnx提前！

Jason

解决方案

< blockquote>将它们作为参数发送：

DateTime

 time1 = Calendar1.SelectedDate; 
 DateTime time2 = Calendar2.SelectedDate; 
 SqlCommand cmd =  new  SqlCommand（ @  SELECT TOP（1）productId FROM Table1 WHERE（Sales_Date BETWEEN @SD AND @ED）AND（productId =（SELECT TOP（1）productId FROM Table1 AS Table1_1 GROUP BY productId ORDER BY COUNT（*）DESC）） ）; 
 cmd.Parameters.AddWithValue（  @ SD，time1）; 
 cmd.Parameters.AddWithValue（  @ ED，time2）; 

问题出在DateFormat中caledar也检索时间（我不想要它）

所以我按顺序插入以下代码仅检索来自caledar的日期：

  string  tes1 = Convert.ToString（Convert.ToDateTime（Calendar1） .SelectedDate）.ToShortDateString（））; 
  string  tes2 = Convert.ToString（Convert.ToDateTime（Calendar2.SelectedDate）.ToShortDateString（））; 

 SqlCommand cmd = new SqlCommand（@   SELECT TOP（1）productId FROM Table1 WHERE（Sales_Date BETWEEN @SD AND @ED）AND（productId =（SELECT TOP（1）productId FROM Table1 AS Table1_1 GROUP BY productId ORDER BY COUNT（*）DESC））跨度>）; 
 cmd.Parameters.AddWithValue（  @ SD，tes1）; 
 cmd.Parameters.AddWithValue（  @ ED，tes2）; 

Hi guys,

I' m trying to implement an application for my web site in wich I retrieve the product with most sales in specific datetime, so I have a table sales which it contains the sales of the products,

I create the following query and it works fine, it retrieves the product with most sales between 2/1/2014 and 2/2/2014 (My databases uses the U.S. Date time form (MM/DD/YYYY))

SELECT TOP (1) productId FROM Table1 WHERE (Sales_Date BETWEEN '2/1/2014' AND '2/2/2014') AND (productId = (SELECT TOP (1) productId FROM Table1 AS Table1_1 GROUP BY productId ORDER BY COUNT(*) DESC))

My question is how can I give the choice of the dates to user?

I have already try to transform my query like this:

"SELECT TOP (1) productId FROM Table1 WHERE (Sales_Date BETWEEN " + time1 + " AND " + time2 + ") AND (productId = (SELECT TOP (1) productId FROM Table1 AS Table1_1 GROUP BY productId ORDER BY COUNT(*) DESC))"

I try to set time1 and time2 with a date from two caledars but it didn't work
(Debugger return: Incorrect syntax near '12'.Incorrect syntax near the keyword 'ORDER')
DateTime time1 = Calendar1.SelectedDate;
DateTime time2 = Calendar2.SelectedDate;


I also try to convert the datetime to U.S. form but either didn't work
DateTimeFormatInfo dtf2 = new DateTimeFormatInfo();
dtf2.ShortDatePattern = "MM/dd/yyyy";
dtf2.DateSeparator = "/";
string date22 = time2.ToString("MM/dd/yyyy");
DateTime new2 = Convert.ToDateTime(time1, dtf2);
(I make the same for time2)

Any idea how can I set the date correct to my query in order to work this app properly?

Thnx in advance!
Jason

解决方案

Send them as parameters:
DateTime

time1 = Calendar1.SelectedDate;
DateTime time2 = Calendar2.SelectedDate;
SqlCommand cmd = new SqlCommand(@"SELECT TOP (1) productId FROM Table1 WHERE (Sales_Date BETWEEN @SD AND @ED) AND (productId = (SELECT TOP (1) productId FROM Table1 AS Table1_1 GROUP BY productId ORDER BY COUNT(*) DESC))");
cmd.Parameters.AddWithValue("@SD", time1);
cmd.Parameters.AddWithValue("@ED", time2);

The problem was in DateFormat the caledar retrieving the time also (which I didn't want it)
so I insert the following code in order to retrieve only dates from the caledar:

string tes1 = Convert.ToString(Convert.ToDateTime(Calendar1.SelectedDate).ToShortDateString());
     string tes2 = Convert.ToString(Convert.ToDateTime(Calendar2.SelectedDate).ToShortDateString());

SqlCommand cmd = new SqlCommand(@"SELECT TOP (1) productId FROM Table1 WHERE (Sales_Date BETWEEN @SD AND @ED) AND (productId = (SELECT TOP (1) productId FROM Table1 AS Table1_1 GROUP BY productId ORDER BY COUNT(*) DESC))");
cmd.Parameters.AddWithValue("@SD", tes1);
cmd.Parameters.AddWithValue("@ED", tes2);

这篇关于检索特定日期之间出现的最多条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！