CODEWITHSUNDEEP
c#sqlsql-server
shubby
shubby

Reputation: 35

query pulling up data in either condition

everything is working fine in this code, the only problem that i have that when i click the "today" from drop down and there is no data for today in DB, its still pulling up data, as if its taking value from "txtCity". only when i put something in txtCity something other than what i have in DB it return empty

        string strVal = hdnOption.Value;
        IFormatProvider provider = new System.Globalization.CultureInfo("en-GB", true);
        DateTime dtStart = new DateTime();
        DateTime? dtEnd = null;
        string strCity = null;

        if (strVal == "0")
        {
            HideCustomSearch();
            dtStart = DateTime.Today;
            dtEnd = DateTime.Today;

            if (txtCityName != null)
            {
                strCity = txtCityName.Text.ToString().Trim();
            }
        }
        if (strVal == "today") 
        {
            HideCustomSearch();
            dtStart = DateTime.Today;
            dtEnd = DateTime.Today;
            if (txtCityName != null)
            {
                strCity = txtCityName.Text.ToString().Trim();
            }
        }
        if (strVal == "weekly")
        {
            HideCustomSearch();
            dtStart = DateTime.Now.AddDays(-7).Date;
            dtEnd = DateTime.Today;
            strCity = txtCityName.Text.ToString().Trim();
        }
        if (strVal == "byweekly")
        {
            HideCustomSearch();
            dtStart = DateTime.Now.AddDays(-15).Date;
            dtEnd = DateTime.Today;
            strCity = txtCityName.Text.ToString().Trim();
        }
        if (strVal == "monthly")
        {
            HideCustomSearch();
            dtStart = DateTime.Now.AddMonths(-1).Date;
            dtEnd = DateTime.Today;
            strCity = txtCityName.Text.ToString().Trim();
        }
        if (strVal == "yearly")
        {
            HideCustomSearch();
            dtStart = DateTime.Now.AddYears(-1).Date;
            dtEnd = DateTime.Today;
            strCity = txtCityName.Text.ToString().Trim();
        }
        if (strVal == "custom")
        {
            ShowCustomSearch();
            //this.txtdtStart = "22/10/2010";
            //dtStart = DateTime.Parse("07-01-2013", provider,                System.Globalization.DateTimeStyles.NoCurrentDateDefault);
            string d = "07-01-2013";
            dtStart = DateTime.ParseExact(d, "dd-MM-yyyy", null, System.Globalization.DateTimeStyles.AllowWhiteSpaces);
            dtEnd = DateTime.ParseExact(d, "dd-MM-yyyy", null, System.Globalization.DateTimeStyles.AllowWhiteSpaces);
            strCity = txtCityName.Text.ToString().Trim();

            hdndtStart.Value = txtdtStart.ToString();
            hdndtEnd.Value = txtdtEnd.Text.ToString();
        }
        FillGridFilter(dtStart, dtEnd, strCity);

P.S. sorry i forgot to add query

if @start_date = DAY(getdate()) and @end_date = null and @city = null
    begin
    select jp.id, city.name[City]
    , row_number() over (order by city.name) [sr_no]
    , count(jp.id) over (partition by name) as no_of_posts 
    , COUNT(od.id) over (partition by name) as no_of_employers
    ,CONVERT(varchar(12), jp.posting_date, 103) [date_created]

from rs_job_posting jp

inner join rs_job_posting_location jpl on jpl.id = jp.id
inner join rs_cor_city city on city.id = jpl.city_fk
inner join rs_organization_detail od on od.id = jp.id

where DAY(posting_date) = @start_date
order by no_of_posts Desc

END

Upvotes: 1

Views: 48

Answers (1)

M.Ali
M.Ali

Reputation: 69574

Try this 

if (@start_date = DAY(getdate()) AND @end_date IS NULL AND @city IS NULL)
  begin
   select jp.id, city.name[City]
        , row_number() over (order by city.name) [sr_no]
        , count(jp.id) over (partition by name) as no_of_posts 
        , COUNT(od.id) over (partition by name) as no_of_employers
        ,CONVERT(varchar(12), jp.posting_date, 103) [date_created]

    from rs_job_posting jp

    inner join rs_job_posting_location jpl on jpl.id = jp.id
    inner join rs_cor_city city on city.id = jpl.city_fk
    inner join rs_organization_detail od on od.id = jp.id

    where DAY(posting_date) = @start_date
    GROUP BY jp.id, city.name, ,CONVERT(varchar(12), jp.posting_date, 103)
    order by no_of_posts Desc 
 END

in Sql Server NULL is an UNKNOWN Value so you really cant Compare an Unknown value to anything, When Checking for NULLs in SQL Server you should use IS NULL or IS NOT NULL, Comparison operators (<, >, <>, <=, >=) dont work with NULL values

Upvotes: 1

Related Questions