Databases
StackOverflow2013
Postsdbo
POASP.NET C# Sql Join a Time range without duplicating [asp.net cleansing]
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POASP.NET C# Sql Join a Time range without duplicating [asp.net cleansing]
    primarykey
    data
    text
    <p>I have the below two tables</p> <p><strong>MBA</strong></p> <pre><code>+--------+----------+------------+--------------+------------------+------------+ |mbaId | Channel | Product | ProgDate | ProgStartTime |ProgEndTime | +--------+----------+ -----------+--------------+------------------+------------+ |12 | AA | SS | 01/04/2011 | 16:00:00 | 20:00:00 | |13 | AA | SS | 01/04/2011 | 16:00:00 | 20:00:00 | |14 | AA | SS | 01/04/2011 | 16:00:00 | 20:00:00 | |15 | AA | SS | 01/04/2011 | 17:00:00 | 18:00:00 | +--------+----------+------------+--------------+------------------+------------+ </code></pre> <p><strong>MAP</strong> </p> <pre><code>+----------+--------+---------------+-------------------+---------+----+ |mapId |Channel | Product |ProgDate | AdvTime| | +----------+--------+---------------+-------------------+---------+----+ |8 | AA | SS | 01/04/2011 | 19:35:14| 30 | |9 | AA | SS | 01/04/2011 | 18:40:19| 27 | |10 | AA | SS | 01/04/2011 | 19:36:58| 35 | |11 | AA | SS | 01/04/2011 | 17:47:13| 28 | +----------+--------+---------------+-------------------+---------+----+ </code></pre> <p><strong>I would require the below output</strong></p> <pre><code>+-----------+-------+-------+-------------+---------+--------------+-----------+-----+ | mapId |Channel|Product| ProgDate | AdvTime |ProgStartTime |ProgEndTime|mbaid| +-----------+-------+-------+-------------+---------+--------------+-----------+-----+ | 8 | AA | SS | 01/04/2011 | 19:35:14|16:00:00 | 20:00:00 | 12 | | 9 | AA | SS | 01/04/2011 | 18:40:19|16:00:00 | 20:00:00 | 13 | | 10 | AA | SS | 01/04/2011 | 19:36:58|16:00:00 | 20:00:00 | 14 | | 11 | AA | SS | 01/04/2011 | 17:47:13|16:00:00 | 17:00:00 | 15 | +-----------+-------+-------+-------------+---------+--------------+-----------+-----+ </code></pre> <p><strong>I use the below code</strong></p> <pre><code>select * from MBA2 as mba inner join Map2 as map on(map.Channel=mba.Channel and map.Product=mba.Product) where( (MBA.ProgStartTime &lt; MBA.ProgEndTime and MBA.ProgStartTime &lt;= case when datediff(mi, MBA.ProgStartTime, MBA.ProgEndTime) &lt;= 60 then dateadd(mi, 5, Map.AdvTime) else Map.AdvTime end and MBA.ProgEndTime &gt;= case when datediff(mi, MBA.ProgStartTime, MBA.ProgEndTime) &lt;= 60 then dateadd(mi, -5, Map.AdvTime) else Map.AdvTime end) or (MBA.ProgStartTime &gt; MBA.ProgEndTime and (MBA.ProgStartTime &lt;= case when 1440 - datediff(mi, MBA.ProgEndTime, MBA.ProgStartTime) &lt;= 60 then dateadd(mi, 5, Map.AdvTime) else Map.AdvTime end or MBA.ProgEndTime &gt;= case when 1440 - datediff(mi, MBA.ProgEndTime, MBA.ProgStartTime) &lt;= 60 then dateadd(mi, -5, Map.AdvTime) else Map.AdvTime end))) order by advtime asc </code></pre> <p>But i get duplicates i.e the value 19:35:14 matches the range 16:00:00 to 20:00:00 for the ids 12,13 and 14. I need one value in MAP match one value in MBA.</p> <p>SO i used the below code to add data into a dataset and find the duplicates in MAPID</p> <pre><code>if (repeatID.Contains(int.Parse(dr["mapID"].ToString()))) { duplicateID.Add(int.Parse(dr["mapID"].ToString())); } else { DataRow dr1 = dt.NewRow(); dr1[0] = int.Parse(dr["mapID"].ToString()); dr1[10] = int.Parse(dr["mbaID"].ToString()); dr1[1] = (dr["Channel"].ToString()); dr1[2] = (dr["Product"].ToString()); dr1[3] = (dr["ProgDate"].ToString()); dr1[4] = (dr["AdvTime"].ToString()); dr1[5] = (dr["Progstarttime"].ToString()); dr1[6] = (dr["Progendtime"].ToString()); dr1[7] = (dr["Channel"].ToString()); dr1[8] = (dr["Product"].ToString()); dr1[9] = (dr["ProgDate"].ToString()); dt.Rows.Add(dr1); } repeatID.Add(int.Parse(dr["mapID"].ToString())); i = i + 1; } sCon.Close(); } </code></pre> <p>And remove the repeating id row. Then use the below query to display the data without duplicates and again to datatable. It returns without duplicate mapid in datatable but with MBAID Dulicates.</p> <pre><code>using (SqlConnection sCon = new SqlConnection(connec)) { foreach (int id in duplicateID) { for (int i = 0; i &lt; dt.Rows.Count; i++) { DataRow ddr = dt.Rows[i]; if (ddr["mapID"].ToString() == id.ToString()) ddr.Delete(); } sCon.Open(); { SqlCommand cmd = new SqlCommand(@"select Distinct top 1 mp.Id as mapid, mb.Id as mbaid, mp.Channel, mp.Product, mp.ProgDate, mp.AdvTime, mb.Channel, mb.ProgStartTime,mb.ProgEndTime,mb.progdate, convert(time, dateadd(minute, datediff(minute, mb.progStartTime, mb.progEndTime), 0)) as timeDiff from MBA22 as mb inner join Map22 as mp on(mp.Channel=mb.Channel and mp.Product=mb.Product and mb.ProgDate=mp.ProgDate ) where( (mb.ProgStartTime &lt; mb.ProgEndTime and mb.ProgStartTime &lt;= case when datediff(mi, mb.ProgStartTime, mb.ProgEndTime) &lt;= 60 then dateadd(mi, 5, mp.AdvTime) else mp.AdvTime end and mb.ProgEndTime &gt;= case when datediff(mi, mb.ProgStartTime, mb.ProgEndTime) &lt;= 60 then dateadd(mi, -5, mp.AdvTime) else mp.AdvTime end) or (mb.ProgStartTime &gt; mb.ProgEndTime and (mb.ProgStartTime &lt;= case when 1440 - datediff(mi, mb.ProgEndTime, mb.ProgStartTime) &lt;= 60 then dateadd(mi, 5, mp.AdvTime) else mp.AdvTime end or mb.ProgEndTime &gt;= case when 1440 - datediff(mi, mb.ProgEndTime, mb.ProgStartTime) &lt;= 60 then dateadd(mi, -5, mp.AdvTime) else mp.AdvTime end))) and mp.Id = '" + id + "' order by timeDiff asc", sCon); cmd.CommandTimeout = 0; SqlDataReader dr = cmd.ExecuteReader(); </code></pre> <p>PLEASE HELPPPP</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USChendur Srinivasan
    1. USChendur Srinivasan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POASP.NET C# Sql Join a Time range without duplicating [asp.net cleansing]
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COYou will either have to add MBAId in MAP table or MAPId in MBA table. And join using it.
      singulars
      1. POASP.NET C# Sql Join a Time range without duplicating [asp.net cleansing]
      1. USRavi Singh
    2. CO@RaviSingh But i dont have any foreign key relationship within the tables
      singulars
      1. POASP.NET C# Sql Join a Time range without duplicating [asp.net cleansing]
      1. USChendur Srinivasan
    3. COok it is a little bit confusing the way you have set the question. As I understand you need a one map for one mba without repeating the advertising time/ map right?
      singulars
      1. POASP.NET C# Sql Join a Time range without duplicating [asp.net cleansing]
      1. USTony Shih
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload