search between date with query have calculation and counter

Question

Hello everyone i have big query to calculation and counter by clinic ID

SELECT nc.ID AS ClinicID, nc.Name AS ClinicName, 
       SUM(cr.CountRecept * cs.Price) AS TotalPriceService, SUM(cr.TotalPaid) AS TotalPaid, 
       SUM(cs.Price * cr.Company_Percentage / 100) AS TotalInsurance, 
       SUM(cr.CountRecept) AS TotalCountRecept
FROM ClinicsServices AS cs INNER JOIN
       (SELECT  tc.Date_Write, COUNT(ID) AS CountRecept, Clinic_Service_ID,  
        company_Percentage, Company_ID, SUM(Paid_Patient) AS TotalPaid
        FROM dbo.TicketsClinics AS tc WHERE  (Status = 1) 
        GROUP BY Clinic_Service_ID, Company_Percentage, Company_ID, tc.Date_Write) AS cr ON  
        cs.ID = cr.Clinic_Service_ID INNER JOIN
       (SELECT  ID, NAME FROM dbo.Clinics AS c GROUP BY ID, Name) AS nc ON cs.Clinic_ID = c.ID
GROUP BY nc.Name, nc.ID

it is true query but i want add between date

 AND tc.Date_Write BETWEEN tc.Date_Write AND tc.Date_Write

in subquery

 Select tc.Date_Write
 Group by tc.Date_Write

in main query

like this

   SELECT nc.ID AS ClinicID, nc.Name AS ClinicName, 
          SUM(cr.CountRecept * cs.Price) AS TotalPriceService, 
          SUM(cr.TotalPaid) AS TotalPaid, 
          SUM(cs.Price * cr.Company_Percentage / 100) AS TotalInsurance, 
          SUM(cr.CountRecept) AS TotalCountRecept, cr.Date_Write
   FROM dbo.ClinicsServices AS cs INNER JOIN
          (SELECT tc.Date_Write, COUNT(ID) AS CountRecept, Clinic_Service_ID,  
                  Company_Percentage, Company_ID, SUM(Paid_Patient) AS TotalPaid
           FROM dbo.TicketsClinics AS tc
           WHERE  (Status = 1) AND tc.Date_Write BETWEEN tc.Date_Write AND tc.Date_Write 
           GROUP BY Clinic_Service_ID, Company_Percentage, Company_ID, tc.Date_Write) 
                     AS cr ON cs.ID = cr.Clinic_Service_ID 
           INNER JOIN (SELECT  ID, NAME FROM dbo.Clinics AS c GROUP BY ID, Name) 
                              AS nc ON  cs.Clinic_ID = nc.ID
           GROUP BY nc.Name, nc.ID, cr.Date_Write

it is false query why because it is display every receipt but i want display 1 - TotalPriceService 2 - TotalPaid 3 - TotalInsurance 4 - TotalCounterReceipt 5 - FromDate 6 - ToDate

the true query that returns calculation and counter i want add search by date i know the second query it is wrong but i want search by date BETWEEN tc.Date_Write FROMDATE AND TODATE how do this thank you for help me

Answer 1

Your BETWEEN clause checks whether a date is between itself. This will return true for every record.

To use BETWEEN correctly, you need to supply two other dates. This query seems like a candidate for a stored procedure that has two date parameters, a "from" date and a "to" date, like this:

CREATE PROCEDURE usp_GetClinicStats(
    @FromDate DATETIME,
    @ToDate DATETIME
    )
AS
BEGIN
  SELECT nc.ID AS ClinicID, nc.Name AS ClinicName, 
          SUM(cr.CountRecept * cs.Price) AS TotalPriceService, 
          SUM(cr.TotalPaid) AS TotalPaid, 
          SUM(cs.Price * cr.Company_Percentage / 100) AS TotalInsurance, 
          SUM(cr.CountRecept) AS TotalCountRecept, cr.Date_Write
   FROM dbo.ClinicsServices AS cs INNER JOIN
          (SELECT tc.Date_Write, COUNT(ID) AS CountRecept, Clinic_Service_ID,  
                  Company_Percentage, Company_ID, SUM(Paid_Patient) AS TotalPaid
           FROM dbo.TicketsClinics AS tc
           WHERE Status = 1
             AND tc.Date_Write BETWEEN CONVERT(VARCHAR, @FromDate, 111) AND CONVERT(VARCHAR, @ToDate, 111) 
           GROUP BY Clinic_Service_ID, Company_Percentage, Company_ID, tc.Date_Write) 
                     AS cr ON cs.ID = cr.Clinic_Service_ID 
           INNER JOIN (SELECT  ID, NAME FROM dbo.Clinics AS c GROUP BY ID, Name) 
                              AS nc ON  cs.Clinic_ID = nc.ID
           GROUP BY nc.Name, nc.ID, cr.Date_Write
END

Answer 2

     CREATE PROCEDURE usp_GetClinicStats(
@FromDate DATETIME,
@ToDate DATETIME
)
AS
  BEGIN
      SELECT nc.ID AS ClinicID, nc.Name AS ClinicName, 
             SUM(cr.CountRecept * cs.Price) AS TotalPriceService, 
             SUM(cr.TotalPaid) AS TotalPaid, 
             SUM(cs.Price * cr.Company_Percentage / 100) AS TotalInsurance, 
             SUM(cr.CountRecept) AS TotalCountRecept, cr.Date_Write
      FROM dbo.ClinicsServices AS cs INNER JOIN
      (SELECT tc.Date_Write, COUNT(ID) AS CountRecept, Clinic_Service_ID,  
              Company_Percentage, Company_ID, SUM(Paid_Patient) AS TotalPaid
       FROM dbo.TicketsClinics AS tc
       WHERE  (Status = 1) AND convert(varchar,tc.Date_Write,111) BETWEEN  
       convert(varchar,@FromDate,111) AND convert(varchar,@ToDate,111)
       GROUP BY Clinic_Service_ID, Company_Percentage, Company_ID, tc.Date_Write) 
                 AS cr ON cs.ID = cr.Clinic_Service_ID 
       INNER JOIN (SELECT  ID, NAME FROM dbo.Clinics AS c GROUP BY ID, Name) 
                          AS nc ON  cs.Clinic_ID = nc.ID
       GROUP BY nc.Name, nc.ID, cr.Date_Write
    END