locked
SQL Query RRS feed

  • Question

  • User-2051275383 posted

    Hey guys,

    I am wanting to use the result from my query as part of the data being pulled out - See below:

        var CheckCalibration = db.Query(@"SELECT t1.Id as Id,
                  t1.Vendor as Vendor,
                  t1.Type as Type,
                  t1.Calibration_Period as Calibration_Period,
                  t1.Model as Model,
                  t2.Id as Unit_Id,
                  t2.Parent_Device_ID as Parent_Device_ID,
                  t2.Serial_Number as Serial_Number,
                  t2.Calibration_Date as Last_Calibrated
                  FROM Devices AS t1 INNER JOIN
                  Device_Assets AS t2 ON t1.Id = t2.Parent_Device_ID
                  WHERE t2.Calibration_Date <=@0 ORDER BY t1.Vendor DESC", DateTime.Now.AddMonths(-Calibration_Period));

    Where it has DateTime.Now.AddMonths(-Calibration_Period) is where I am having issues. If I put in a numerical value (ie. 6) - it works fine. However I want it to pull results based on a numerical value stored in the t1.Calibration_Period table.

    Any help would be much appreciated :)

    Cheers!

    Thursday, November 12, 2015 7:16 PM

Answers

  • User-1716253493 posted
    SELECT t1.Id as Id,
    t1.Vendor as Vendor,
    t1.Type as Type,
    t1.Calibration_Period as Calibration_Period,
    t1.Model as Model,
    t2.Id as Unit_Id,
    t2.Parent_Device_ID as Parent_Device_ID,
    t2.Serial_Number as Serial_Number,
    t2.Calibration_Date as Last_Calibrated
    FROM Devices AS t1 INNER JOIN
    Device_Assets AS t2 ON t1.Id = t2.Parent_Device_ID
    WHERE t2.Calibration_Date <= DateAdd(month, 0 - t1.Calibration_Period, getdate())  ORDER BY t1.Vendor DESC

    or

    var CheckCalibration = db.Query(@"SELECT t1.Id as Id,
    t1.Vendor as Vendor,
    t1.Type as Type,
    t1.Calibration_Period as Calibration_Period,
    t1.Model as Model,
    t2.Id as Unit_Id,
    t2.Parent_Device_ID as Parent_Device_ID,
    t2.Serial_Number as Serial_Number,
    t2.Calibration_Date as Last_Calibrated
    FROM Devices AS t1 INNER JOIN
    Device_Assets AS t2 ON t1.Id = t2.Parent_Device_ID
    WHERE t2.Calibration_Date <= DateAdd(month, 0 - t1.Calibration_Period, @0) ORDER BY t1.Vendor DESC", DateTime.Now);

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 12, 2015 7:44 PM
  • User325035487 posted

    int count = CheckCalibration.Count();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 13, 2015 1:23 AM

All replies

  • User-1716253493 posted
    SELECT t1.Id as Id,
    t1.Vendor as Vendor,
    t1.Type as Type,
    t1.Calibration_Period as Calibration_Period,
    t1.Model as Model,
    t2.Id as Unit_Id,
    t2.Parent_Device_ID as Parent_Device_ID,
    t2.Serial_Number as Serial_Number,
    t2.Calibration_Date as Last_Calibrated
    FROM Devices AS t1 INNER JOIN
    Device_Assets AS t2 ON t1.Id = t2.Parent_Device_ID
    WHERE t2.Calibration_Date <= DateAdd(month, 0 - t1.Calibration_Period, getdate())  ORDER BY t1.Vendor DESC

    or

    var CheckCalibration = db.Query(@"SELECT t1.Id as Id,
    t1.Vendor as Vendor,
    t1.Type as Type,
    t1.Calibration_Period as Calibration_Period,
    t1.Model as Model,
    t2.Id as Unit_Id,
    t2.Parent_Device_ID as Parent_Device_ID,
    t2.Serial_Number as Serial_Number,
    t2.Calibration_Date as Last_Calibrated
    FROM Devices AS t1 INNER JOIN
    Device_Assets AS t2 ON t1.Id = t2.Parent_Device_ID
    WHERE t2.Calibration_Date <= DateAdd(month, 0 - t1.Calibration_Period, @0) ORDER BY t1.Vendor DESC", DateTime.Now);

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 12, 2015 7:44 PM
  • User-2051275383 posted

    Awesome - That appears to of worked!!

    Could you kindly help me do a COUNT on the number of results returned too?

    Thanks!

    Thursday, November 12, 2015 8:35 PM
  • User-1716253493 posted
    Int rowscount = dt.Rows.Count;

    Thursday, November 12, 2015 8:43 PM
  • User-2051275383 posted

    Int rowscount = dt.Rows.Count;

    Sorry - Could you please explain how I should do this using Razor syntax?

    Thursday, November 12, 2015 11:21 PM
  • User325035487 posted

    int count = CheckCalibration.Count();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 13, 2015 1:23 AM