基于日期约束从数据库中过滤和显示gridview数据

本文关键字：过滤 显示 数据 gridview 数据库 于日期 日期 约束 | 更新日期: 2023-09-27 18:01:57

以下是我的表格:

create table users
(
userid int identity(1,1) primary key,
username varchar(150),
password varchar(20),
email varchar(150),
mobile varchar(50),
address varchar(2000),
service_start_date datetime,
createdate datetime,
modifydate datetime,
deletedate datetime,
tablestatus varchar(50),
roles varchar(50)

)

create table service_transaction
(
srvid int identity(1,1),
srv_startdate datetime,
srv_enddate datetime,
script varchar(1000),
position_type varchar(100),
entryprice varchar(100),
targetprice varchar(100),
comments varchar(100),
srv_type varchar(100),
srv_status varchar(100),
createdate datetime,
modifydate datetime,
deletedate datetime,
tablestatus varchar(50)
)

create table usersubscription
(
sub_id int identity(1,1) primary key,
u_id int foreign key references users(userid), 
sub_startdate datetime,
sub_enddate datetime,
service_type varchar(100),
createdate datetime,
modifydate datetime,
deletedate datetime,
tablestatus varchar(50)
)

我需要一个查询来根据特定用户的订阅开始日期和订阅结束日期筛选服务事务。

基于日期约束从数据库中过滤和显示gridview数据

我认为你需要规范化你的表一点。试试下面的方法，这是我对你问题的理解。

DECLARE @filterDate AS DATETIME
SET @filterDate = '2014-05-05 17:04:45'
SELECT * FROM service_transaction AS ST
JOIN usersubscription AS US ON ST.srv_type = US.service_type
JOIN users AS U ON U.userid  = US.userid
WHERE @filterDate BETWEEN US.sub_startdate AND US.sub_enddate AND U.userid = '12345'

我已经添加了一个userId过滤器，您可以用您正在查询的userId替换12345。

相关文章：