-1

I want to write the below query without using union all. The only difference in both the queries are for 'Event' and 'Time1' columns:

timrau
  • 22,578
  • 4
  • 51
  • 64
ravish raja
  • 19
  • 5
  • 1
    `union all` is `union all` it is not wise trying to not using the tool specific for this and expect to get the same result. – T. Peter Jan 04 '21 at 08:34
  • also why is this even a problem. isn't `union all` serve his purpose? – T. Peter Jan 04 '21 at 08:35
  • I am getting the desired result from the above query, but I have similar 10 union all in the continuation of the above query and it looks very cumbersome – ravish raja Jan 04 '21 at 08:39
  • 1
    Your union logic is generating new records. Union is the right tool for this job. – Tim Biegeleisen Jan 04 '21 at 08:39
  • @T.Peter can you please help me in this? – ravish raja Jan 04 '21 at 08:40
  • quote Tim comment, `union all` will fetch every record no matter dupe or not, `union` will not include duplicate data. – T. Peter Jan 04 '21 at 08:41
  • but in this case since all your data got `Event` which will make every row in both table get union together. `union` will not help you. – T. Peter Jan 04 '21 at 08:42
  • any different way of writing the query without using union/union all? – ravish raja Jan 04 '21 at 08:44
  • 1
    NO, but I think the problem is you are using wrong tools all along. you are using same table in both select clause (with exactly same where condition) which I assume you don't need to use `union` at all. you should however use `case` to indicate `Event` and `Time1` . – T. Peter Jan 04 '21 at 08:47
  • @ravishraja why did you vandalize your own post? It makes no sense to anybody else now – HoneyBadger Jan 06 '21 at 12:08

1 Answers1

4

You can use something like this:

Select  L.id
,       L.StudentID
,       L.Name
,       L.Owner
,       O.Stage
,       O.probab
,       Multiplier.Event as Event
,       Multiplier.Time1
From    Lead L 
CROSS APPLY (
                VALUES  ('Lead', case when L.converted='true' and convert(varchar,L.ldate,23)<>convert(varchar,O.odate,23) then O.date else L.date end)
                ,       ('Contact Attempt', case when L.Attempted is null then O.Attempted else L.ContactAttempted end)
            ) AS Multiplier(Event, Time1)
left join Opp O
        ON O.LeadID=L.LeadID
left join Acc A 
        ON A.id=O.AccountId
left join zip Z 
        ON z.CODE = left(L.postalcode,5)
where (L.ldate is not null or o.NewAssigned is not null)

By the way, you should always define the length of varchar.

HoneyBadger
  • 14,750
  • 3
  • 34
  • 48