从数据表中获取所有患者的最后药物

Question

我想使用 Sql 查询从预订日期为今天的表中获取所有患者的最后药物记录NewPatient...

例如，如果我在两个文本框中输入 10 到 20

第一个文本框 - 10

第二个文本框 - 20

然后，它必须在表中显示前 10 到 20 名患者最后用药的记录，NewPatient预订日期为今天。

我正在使用sql服务器和C#.....

我尝试过这样的事情，但这不起作用

            try
            {
                SuperClass sc = new SuperClass();
                Cursor = Cursors.WaitCursor;
                timer1.Enabled = true;
                rptPatients rpt = new rptPatients();// created report
                SqlCommand MyCommand = new SqlCommand();
                SqlDataAdapter myDA = new SqlDataAdapter();
                DB_DOCTORDataSet myDS = new DB_DOCTORDataSet();//created dataset
                SqlConnection con = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Raj\Desktop\doctor offline\Webdigitronix.Homoeo.Library\WebDigitronix.Homoeo.AppLayer\bin\Debug\db\DB_DOCTOR.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
                MyCommand.Connection = con;
                MyCommand.CommandText = "WITH T AS(SELECT TOP " + txtTo.Text + "  NP.*, row_number() OVER (ORDER BY id) AS RN from NewPatient NP where  BookingDate = '" + dtpBookingDate.Value.Date + "' and isvalid='true' and  medicine!='' and id=(select MAX(id) from newpatient where medicine!='' and isvalid='true'))SELECT * from T where RN>= " + txtFrom.Text + "";
                MyCommand.CommandType = CommandType.Text;
                myDA.SelectCommand = MyCommand;
                myDA.Fill(myDS, "NewPatient");
                rpt.SetDataSource(myDS);
                crystalReportViewer1.ReportSource = rpt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

表结构

CREATE TABLE [dbo].[newpatient] (
 [id]          INT           IDENTITY (1, 1) NOT NULL,
 [serialno]    VARCHAR (MAX) NULL,
 [patientname] VARCHAR (100) CONSTRAINT [DF__newpatien__patie__1273C1CD] DEFAULT ('') NULL,
 [age]         INT           CONSTRAINT [DF__newpatient__age__1367E606] DEFAULT ((0)) NULL,
 [address]     VARCHAR (100) CONSTRAINT [DF__newpatien__addre__145C0A3F] DEFAULT ('') NULL,
 [symptoms]    VARCHAR (MAX) CONSTRAINT [DF__newpatien__sympt__15502E78] DEFAULT ('') NULL,
 [medicine]    VARCHAR (MAX) CONSTRAINT [DF__newpatien__medic__164452B1] DEFAULT ('') NULL,
 [bookingdate] DATETIME      NULL,
 [alloteddate] DATETIME      NULL,
 [village]     VARCHAR (MAX) CONSTRAINT [DF__newpatien__villa__173876EA] DEFAULT ('') NULL,
 [thana]       VARCHAR (MAX) CONSTRAINT [DF__newpatien__thana__182C9B23] DEFAULT ('') NULL,
 [district]    VARCHAR (MAX) CONSTRAINT [DF__newpatien__distr__1920BF5C] DEFAULT ('') NULL,
 [state]       VARCHAR (MAX) CONSTRAINT [DF__newpatien__state__1A14E395] DEFAULT ('') NULL,
 [isvalid]     BIT           CONSTRAINT [DF__newpatien__isval__1B0907CE] DEFAULT ('') NULL,
CONSTRAINT [pk_id_newpatient] PRIMARY KEY CLUSTERED ([id] ASC)

Answer 1

假设PatientID是某个唯一标识患者的列，SomeDateColumn是日期时间/日期列，确定记录的添加时间。

;WITH LastMeds
AS
 (
 SELECT * , rn = ROW_NUMBER() OVER (PARTITION BY PatientID ORDER BY SomeDateColumn DESC)
 FROM newpatient
 WHERE isvalid='true' AND isold='true' 
 AND serialno= @Var AND medicine!=''
 )
SELECT TOP N * FROM LastMeds
WHERE rn = 1
AND CAST(bookingdate AS DATE) = CAST(GETDATE() AS DATE)

限制返回的记录（使用文本框 1 和 2 中传递的值）

你可以做这样的事情

;WITH LastMeds
AS
 (
 SELECT * , rn = ROW_NUMBER() OVER (PARTITION BY PatientID ORDER BY SomeDateColumn DESC)
 FROM newpatient
 WHERE isvalid='true' AND isold='true' 
 AND serialno= @Var AND medicine!=''
 ),
 RecordsRtn
 AS
  (
  SELECT *, rn2 = ROW_NUMBER() OVER (ORDER BY PatientName)
  FROM LastMeds
  WHERE rn = 1
  )
SELECT  *
FROM RecordsRtn
WHERE rn2 >= (@TextBox1) AND rn2 <= (@TextBox2)
AND CAST(bookingdate AS DATE) = CAST(GETDATE() AS DATE)