Database

Ankit RS wrote:

Any ideas/solutions would be much appreciated.

DECLARE @startDate AS DATE;
DECLARE @stopDate AS DATE;

SET @startDate = DATETIMEFROMPARTS(@Year, 1, 1, 0, 0, 0, 0);
SET @stopDate = DATETIMEFROMPARTS(@Year, @Month, @Day, 23, 59, 59, 999);

WITH cteProductInfo As
(
    SELECT
        PartNumber,
        Cost,
        Price,
        ShortDescription,
        LongDescription,
        PostageImage,
        Thumbnail,
        VendorID,
        VendorName,
        Weight,
        ShipHeight,
        ShipWidth,
        ShipDepth,
        LimitedItem
    FROM
        PRODUCTINFO
    
    UNION ALL
    
    SELECT
        PartNumber,
        Cost,
        Price,
        ShortDescription,
        LongDescription,
        PostageImage,
        Thumbnail,
        VendorID,
        VendorName,
        Weight,
        ShipHeight,
        ShipWidth,
        ShipDepth,
        LimitedItem
    FROM
        PRODUCTINFO_RECYCLEBIN
),
cteOrders As
(
    SELECT
        PartNumber,
        ManPartNumber, 
        SUM(coH.Qty) as TotalQty,
        CAST(SUM(coH.Qty * coh.Cost) AS decimal(10, 2)) as TotalCost,
        CAST(SUM(coH.Qty * coh.Price) AS decimal(10, 2)) as TotalPrice
    FROM
        CompletedOrdersCartHistory
    GROUP BY
        PartNumber,
        ManPartNumber
)
SELECT
    coH.PartNumber,
    coH.ManPartNumber,
    coH.TotalQty,
    coH.TotalCost,
    coH.TotalPrice,
    pI.Cost,
    pI.Price,
    pI.ShortDescription,
    pI.LongDescription,
    pI.PostageImage,
    pI.Thumbnail,
    pI.VendorID,
    pI.VendorName,
    pI.Weight,
    pI.ShipHeight,
    pI.ShipWidth,
    pI.ShipDepth,
    pI.LimitedItem,
    vI.Address1,
    vI.Address2,
    vI.City,
    vI.StateCode,
    vI.CountryCode,
    vI.ZipCode,
    vI.ContactName,
    vI.VendorPhone
FROM 
    cteOrders as coH
    LEFT JOIN cteProductInfo AS pI
    ON coH.PartNumber = pI.PartNumber
    LEFT JOIN VendorInfo AS vI
    ON pI.VendorID = vI.VendorID
WHERE 
    coh.OrderDate > @startDate
AND 
    coh.OrderDate < @stopDate
AND 
    coh.PartNumber = @PartNumber
ORDER BY 
    coH.PartNumber
;

Mycroft Holmes wrote:

I meant Tim's response...

V. wrote:

he DBA and developer and analyst and tester and ...

public class MyParam
    {
        public string name { get; set; }
        public string value { get; set; }
    }
    /// 
    /// Summary description for QueryContainer SGH
    /// 
    public class QueryContainer
    {
 
        string _query;
 
        public List parameterList = new List(); 
 
        public QueryContainer(string query) { _query = query; }
 
        public string Query
        {
            get
            {
                return _query;
            }
 
            set { _query = value;  }
        }
    }<pre>
 
The calling code:
 

<pre>
        public int GetAccountSortByAccountCode(int account)
        {
            QueryContainer Instance = new QueryContainer("SELECT ac_sort_order FROM lkup_account_codes where ac_code = <a href="http://www.codeproject.com/Members/account">@account</a>");
 
            MyParam myParam = new MyParam();
 
            myParam.name = "@account";
            myParam.value = account.ToString();
 
            Instance.parameterList.Add(myParam);
 
            return Convert.ToInt32(ExecuteScaler(Instance, 1));
        } 
<pre>
 
The implementing code:
 
<pre>
                if (_connection == null || _connection.State == ConnectionState.Closed)
                {
                    OpenConnection();
                }
 
                DbCommand command = _provider.CreateCommand();
                command.Connection = _connection;
                {
                    command.CommandText = Instance.Query;
                    command.CommandType = CommandType.Text;
 
                    foreach (var p in Instance.parameterList)
                    {
                        SqlParameter param = new SqlParameter(p.name, p.value);
                        command.Parameters.Add(param);
                    }
 
                    if (_useTransaction) { command.Transaction = _transaction; }
 
                    try
                    {
                        returnValue = command.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        if (ex is EntryPointNotFoundException)
                            throw ex;
                        //if (_useTransaction == true)
                        //_transaction.Rollback();
                        RollBack();
 
                        LogBLL bll = new LogBLL();
                        bll.WriteErrorLog(ex);
 
                        _iserror = true;
                    }
<pre>

public class MyParam
    {
        public string name { get; set; }
        public string value { get; set; }
    }
    /// <summary>
    /// Summary description for QueryContainer SGH
    /// </summary>
    public class QueryContainer
    {

        string _query;

        public List<myparam> parameterList = new List<myparam>(); 

        public QueryContainer(string query) { _query = query; }

        public string Query
        {
            get
            {
                return _query;
            }

            set { _query = value;  }
        }
    }

The calling code:

<pre>
        public int GetAccountSortByAccountCode(int account)
        {
            QueryContainer Instance = new QueryContainer("SELECT ac_sort_order FROM lkup_account_codes where ac_code = <a href="http://www.codeproject.com/Members/account">@account</a>");

            MyParam myParam = new MyParam();

            myParam.name = "@account";
            myParam.value = account.ToString();

            Instance.parameterList.Add(myParam);

            return Convert.ToInt32(ExecuteScaler(Instance, 1));
        } 
<pre>

The implementing code:

<pre>
                if (_connection == null || _connection.State == ConnectionState.Closed)
                {
                    OpenConnection();
                }

                DbCommand command = _provider.CreateCommand();
                command.Connection = _connection;
                {
                    command.CommandText = Instance.Query;
                    command.CommandType = CommandType.Text;

                    foreach (var p in Instance.parameterList)
                    {
                        SqlParameter param = new SqlParameter(p.name, p.value);
                        command.Parameters.Add(param);
                    }

                    if (_useTransaction) { command.Transaction = _transaction; }

                    try
                    {
                        returnValue = command.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        if (ex is EntryPointNotFoundException)
                            throw ex;
                        //if (_useTransaction == true)
                        //_transaction.Rollback();
                        RollBack();

                        LogBLL bll = new LogBLL();
                        bll.WriteErrorLog(ex);

                        _iserror = true;
                    }

<pre>