27800

How to avoid duplicates when joining multiple tables

Question:

The table contain three records, see below the temporary table :

Declare @TimeStamp table (Id int, SalesMan_ID int, Module_Cdoe varchar(50),Time_In varchar(10),Time_Out varchar(10)) Insert into @TimeStamp values (1,13524,'Analysis','1:30pm','1:40pm'), (2,12543,'Analysis','2:10pm','2:30pm'), (3,12543,'Analysis','3:10pm','3:30pm') ;with cteBase (Seq,Module_Cdoe) as( Select 1,'Photo' union all Select 2,'Survey' union all Select 3,'Merchand' ) Select id=Row_Number() over (Order By Time_In,SalesMan_id,Seq) ,SalesMan_id ,b.Module_Cdoe ,Time_In ,Time_Out from @TimeStamp A Join cteBase B on (A.Module_Cdoe='Analysis') Order By 1

From the above query the result is :

id SalesMan_id Module_Cdoe Time_In Time_Out 1 13524 Photo 1:30pm 1:40pm 2 13524 Survey 1:30pm 1:40pm 3 13524 Merchand 1:30pm 1:40pm 4 12543 Photo 2:10pm 2:30pm 5 12543 Survey 2:10pm 2:30pm 6 12543 Merchand 2:10pm 2:30pm 7 12543 Photo 3:10pm 3:30pm 8 12543 Survey 3:10pm 3:30pm 9 12543 Merchand 3:10pm 3:30pm

But I need like see below:

id SalesMan_id Module_Cdoe Time_In Time_Out 1 13524 Photo 1:30pm 1:40pm 2 13524 Survey 1:30pm 1:40pm 3 13524 Merchand 1:30pm 1:40pm 4 12543 Photo 2:10pm 2:30pm 5 12543 Survey 2:10pm 2:30pm 6 12543 Merchand 2:10pm 2:30pm

(OR)

id SalesMan_id Module_Cdoe Time_In Time_Out 1 13524 Photo 1:30pm 1:40pm 2 13524 Survey 1:30pm 1:40pm 3 13524 Merchand 1:30pm 1:40pm 4 12543 Photo 3:10pm 3:30pm 5 12543 Survey 3:10pm 3:30pm 6 12543 Merchand 3:10pm 3:30pm

So what is the query to achieve my expectations?

Answer1:

As requested, this is just a small twist on the original.

Declare @TimeStamp table (Id int, SalesMan_ID int, Module_Cdoe varchar(50),Time_In varchar(10),Time_Out varchar(10)) Insert into @TimeStamp values (1,13524,'Analysis','1:30pm','1:40pm'), (2,12543,'Analysis','2:10pm','2:30pm'), (3,12543,'Analysis','3:10pm','3:30pm') ;with cteBase (Seq,Module_Cdoe) as( Select 0,'Analysis' union all Select 1,'Photo' union all Select 2,'Survey' union all Select 3,'Merchand' ) , cteDistPop as ( Select Distinct SalesMan_Id ,Time_In = Max(Time_In) ,Time_Out = Max(Time_Out) From @TimeStamp Group By SalesMan_Id ) Select id=Row_Number() over (Order By Time_In,SalesMan_id,Seq) ,SalesMan_id ,b.Module_Cdoe ,Time_In ,Time_Out from cteDistPop A Cross Join cteBase B Order By 1

Returns -- <strong>Not clear on "Analysis" In or Out. Either way you control that from within cteBase</strong>

id SalesMan_id Module_Cdoe Time_In Time_Out 1 13524 Analysis 1:30pm 1:40pm 2 13524 Photo 1:30pm 1:40pm 3 13524 Survey 1:30pm 1:40pm 4 13524 Merchand 1:30pm 1:40pm 5 12543 Analysis 3:10pm 3:30pm <<-- Notice Later Date 6 12543 Photo 3:10pm 3:30pm 7 12543 Survey 3:10pm 3:30pm 8 12543 Merchand 3:10pm 3:30pm

Answer2:

Try like this,

Declare @TimeStamp table (Id int, SalesMan_ID int, Module_Code varchar(50),Time_In varchar(10),Time_Out varchar(10)) Insert into @TimeStamp values (1,13524,'Analysis','1:30pm','1:40pm'), (2,12543,'Analysis','2:10pm','2:30pm'), (3,12543,'Analysis','3:10pm','3:30pm')

First expected table output

select *From ( Select Row_Number() over (Order By Time_In,SalesMan_id,Seq) id ,SalesMan_id ,b.Module_Code ,Time_In ,Time_Out ,row_number() over (partition by SalesMan_Id,B.Module_code order by id) as rn from @TimeStamp A Join (select *from (values (1,'Photo'),(2,'Survey'),(3,'Merchand'))cteBase(Seq,Module_Code)) B on (A.Module_Code='Analysis'))t where rn=1 Order By 1

Second expected table output

select *From ( Select Row_Number() over (Order By Time_In,SalesMan_id,Seq) id ,SalesMan_id ,b.Module_Code ,Time_In ,Time_Out ,row_number() over (partition by SalesMan_Id,B.Module_code order by id desc) as rn from @TimeStamp A Join (select *from (values (1,'Photo'),(2,'Survey'),(3,'Merchand'))cteBase(Seq,Module_Code)) B on (A.Module_Code='Analysis'))t where rn=1 Order By 1

Recommend

  • Dependancy Injection not working with ASPNETCore.TestHost
  • Is 404 the right return code for a resource that currently doesn't exist?
  • PL/SQL: re-write SELECT statement using IN parameter in stored procedure
  • Fancybox - Opening a link inside a fancybox into the same fancybox?
  • Is it valid to use a C99-style designated initializer list to initialize the members of a bit field
  • How select second line from top 2 or something similar?
  • How to intersect multiple polygons?
  • Looping SQL statement insert
  • enumerate days of week in t-sql
  • Apache Flink: Skewed data distribution on KeyedStream
  • Excel Marcro - delete a row based on row number [closed]
  • How to create a Partial-like that requires a single property to be set
  • Confusing SQL error in SELECT NULL, *, NULL, NULL
  • C#'s “protected internal” means “protected” *OR* “internal”. Does any keyword mean “protected”
  • C++ usrsctp callback parameters null
  • Row_number() function for Informix
  • Is there a way to pivot a customer ID and a their most recent order dates?
  • Valums Ajax file Upload handle the up. file?
  • Max of several columns
  • Division with Aggregate Functions in SQL Not Behaving as Expected
  • Argument 5: cannot convert from 'System.Drawing.Image' to 'string' - calling cla
  • Linq Full Outer Join on Two Objects
  • distinct values from multiple fields within one table ORACLE SQL
  • Save image as is in photo album using swift
  • Merging rows to columns
  • How to render a blob on a canvas element?
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • Record samples being played with OpenAL
  • Breeze - Deleted Items nav properties bug
  • Scrapy recursive link crawler
  • javaw.exe and eclipse startup problems
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • How to delete a row from a dynamic generate table using jquery?
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • How do you join a server to an Active Directory (domain)?
  • How to stop GridView from loading again when I press back button?
  • need help with bizarre java.net.HttpURLConnection behavior
  • How does Linux kernel interrupt the application?