Database

var queryBuilder = new StringBuilder();
queryBuilder.Append("select tag_id, category_id, product_id, product_name, gate_id, in_time, out_time, remarks from transaction where DATE(in_time) BETWEEN @FromDate And @ToDate");
command.Parameters.AddWithValue("@FromDate", fromDate);
command.Parameters.AddWithValue("@ToDate", toDate);

queryBuilder.Append("AND (1 = 1");

if (!string.IsNullOrEmpty(R_Tag.Text))
{
    queryBuilder.Append(" OR tag_id = @RTag");
    command.Parameters.AddWithValue("@RTag", R_Tag.Text);
}
if (!string.IsNullOrEmpty(R_Category.Text))
{
    queryBuilder.Append(" OR category_id = @RCategory");
    command.Parameters.AddWithValue("@RCategory", R_Category.Text);
}
// Repeat for other fields...

queryBuilder.Append(")");
command.CommandText = queryBuilder.ToString();

Member 10263519 wrote:

queryBuilder.Append("AND (1 = 1)");

queryBuilder.Append("AND (1 = 1");

if (!string.IsNullOrEmpty(R_Tag.Text))
{
    queryBuilder.Append(" OR tag_id = ?RTag");
    command.Parameters.AddWithValue("?RTag", R_Tag.Text);
}

Jassim Rahma wrote:

unique random alphanumeric PIN

DECLARE
	@D DATETIME
	
SET @D = GETDATE()

SELECT @D

SELECT CONVERT(DATE,@D)

;WITH Dates AS
(
    SELECT GETDATE()as DateValue
    UNION ALL
    SELECT DateValue -1
    FROM Dates
    WHERE DateValue -1 >= dateadd(d,-10000,GETDATE())
)
select convert(date,DateValue) from Dates
OPTION (MAXRECURSION 32747)

select convert(date,DateCreated) as TrkDate ,SUM(allocated) as Tot from vektronix.[dbo].[Tracking] t
where [TrackingTypeId] = '5'
group by convert(date,DateCreated)

;WITH Dates AS
(
    SELECT GETDATE()as DateValue
    UNION ALL
    SELECT DateValue -1
    FROM Dates
    WHERE DateValue -1 >= dateadd(d,-90,GETDATE())
)

SELECT convert(date,DateValue) as ProdDate , coalesce(trk.Tot,0)as ProdQty
FROM Dates D
left join (select convert(date,DateCreated) as TrkDate ,SUM(allocated) as Tot from vektronix.[dbo].[Tracking] t
where [TrackingTypeId] = '5'
group by convert(date,DateCreated)) 
as trk on trk.TrkDate = convert(date,DateValue)
group by DateValue, trk.Tot
order by datevalue desc
OPTION (MAXRECURSION 32747)

WITH Datetimes AS
(
    SELECT  GETDATE()as DateValue
    UNION ALL
    SELECT  DateValue -1
    FROM    Datetimes
    WHERE   DateValue -1 >= dateadd(d,-90,GETDATE())
)
,dates AS
(
    SELECT  DISTINCT convert(date,datevalue) AS ProdDate
    FROM    Datetimes
)
,trk AS
(
    SELECT  convert(date,DateCreated) as TrkDate
           ,allocated
    from    vektronix.[dbo].[Tracking] t
    where   [TrackingTypeId] = '5'
)
,trkgrouped as
(
    SELECT  trkdate
           ,SUM(allocated) as Tot
    FROM    trk
    GROUP BY trkdate
)

SELECT  ProdDate
       ,coalesce(Tot,0)as ProdQty
FROM    Dates D
left JOIN trkgrouped trk on trk.TrkDate = d.ProdDate
order by ProdDate desc
OPTION (MAXRECURSION 32747)