Problem to write a query for specific result

Pnkl

9y965

Hello Guys,

I want take your help to solve out my question so please give me better way to implement the query to get records from search parameter

Below I have drawn one scenario and from that I want to get result

Suppose one organization held the draw of lottery ticket of various categories

LotteryTicketBuyerMaster

BuyerId(Int)	BuyerName(varchar)	EmailId	Category(varchar)	TicketNo(Varchar)
1	Nicks	[email protected]	Electronics	11001,11007
2	Nicks	[email protected]	Automobiles	14015,14020
3	James	[email protected]	Electronics	11002,11005
4	James	[email protected]	Automobiles	14016,14017
5	Mike	[email protected]	Electronics	11003
6	Nelson	[email protected]	Electronics	11004,11006
7	Mira	[email protected]	Furniture	13001
8	Jack	[email protected]	Automobiles	14018,14019
9	Mark	[email protected]	Furniture	13002,13004
10	Lary	[email protected]	Furniture	13003

LuckyTicketMaster

1^st Day Draw

DrawDate(Date)	Category(Varchar)	LuckyTicketNo(Varchar)
01/03/2017	Electronics	11001,11003
01/03/2017	Automobiles	14016,14018

2^nd Day Draw

DrawDate(Date)	Category(Varchar)	LuckyTicketNo(Varchar)
02/03/2017	Electronics	11005,11006
02/03/2017	Automobiles	14018,14020
02/03/2017	Furniture	13002,13003

And at end of the each day I want to send email to lucky winner so, how to write a query in better way to get winner Name and Email in LotteryTicketBuyerMaster from TicketNo by LuckyTicketMaster’s LuckyTicketNo

ResultTable

1^st Day ResultTable

Date	BuyerName	Emailid	Category	TicketNo
01/03/2017	Nicks	[email protected]	Electronics	11001
01/03/2017	Mike	[email protected]	Electronics	11003
01/03/2017	James	[email protected]	Automobiles	14016
01/03/2017	Jack	[email protected]	Automobiles	14018

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
Accepted answer
If you have slightly earlier version of SQL Server then use following script to create a function:
create function [dbo].[Split]
(
   @string nvarchar(max)
  ,@delimiter char(1)
)
returns @output table
(
   [id] int
  ,[splitdata] nvarchar(max)
)
begin

/*
    select *
    from [dbo].[Split](N'123,345,12456,,1578,5', N',')
*/

  declare @start integer = 1, @end integer = charindex(@delimiter, @string), @i integer = 0;

  while @start < LEN(@string) + 1
    begin
      if @end = 0
        set @end = LEN(@string) + 1

      if (substring(@string, @start, @end - @start) <> '')
        begin
          set @i = @i + 1
          insert into @output ([splitdata], [id])
          values(substring(@string, @start, @end - @start), @i)
        end

      set @start = @end + 1

      set @end = charindex(@delimiter, @string, @start)

    end
  return
end
And then use following query:
select l.DrawDate, t.BuyerName, t.EmailId, t.Category, t.[splitdata] [TicketNo]
from @LuckyTicketMaster l
cross apply dbo.Split(l.LuckyTicketNo, ',') k
left outer join
    (
        select *
        from @LotteryTicketBuyerMaster t
        cross apply dbo.Split(t.TicketNo, ',')
    ) t on k.[splitdata] = t.[splitdata]
where [DrawDate] = '20170301'
Nitin Sontakke9y ago
@Manish, strictly speaking, it wasn't even a suggestion. 'Ground work' is probably the proper word.

The full background is actually this: I wanted to work on solution, but had no base data, which I had to prepare from html tables pasted in question. So I prepared the script for that. By the time i was about to start the query, it was time to leave for the home.

So, I thought might as well paste the script here itself so that I can pick it up at home. In addition, if someone else is working on it, s/he doesn't have to take efforts to do something which I already did.

What I particularly disliked is someone down-voted my reply.

Anyways...this is probably common in public forums. Better leave it to that. Thanks!
Manish Kumar9y ago
@Nitin.. Instead of answer it should be suggestion. My Mistake
Nitin Sontakke9y ago
.and here is ANSWER using SQL Server 2016.
declare @LotteryTicketBuyerMaster table
(
  BuyerId Integer
,BuyerName varchar(50)
,EmailId  varchar(50)
,Category  varchar(50)
,TicketNo  varchar(50)
)

insert into @LotteryTicketBuyerMaster values
(1,'Nicks','[email protected]','Electronics','11001,11007')
,(2,'Nicks','[email protected]','Automobiles','14015,14020')
,(3,'James','[email protected]','Electronics','11002,11005')
,(4,'James','[email protected]','Automobiles','14016,14017')
,(5,'Mike','[email protected]','Electronics','11003')
,(6,'Nelson','[email protected]','Electronics','11004,11006')
,(7,'Mira','[email protected]','Furniture','13001')
,(8,'Jack','[email protected]','Automobiles','14018,14019')
,(9,'Mark','[email protected]','Furniture','13002,13004')
,(10,'Lary','[email protected]','Furniture','13003')

declare @LuckyTicketMaster table
(
   DrawDate Date
  ,Category Varchar(50)
  ,LuckyTicketNo Varchar(50)
)

insert into @LuckyTicketMaster values
('2017/03/01','Electronics','11001,11003')
,('2017/03/01','Automobiles','14016,14018')
,('2017/03/02','Electronics','11005,11006')
,('2017/03/02','Automobiles','14018,14020')
,('2017/03/02','Furniture','13002,13003')


select l.DrawDate, t.BuyerName, t.EmailId, t.Category, t.value [TicketNo]
from @LuckyTicketMaster l
cross apply string_split(l.LuckyTicketNo, ',') k
left outer join
    (
        select *
        from @LotteryTicketBuyerMaster t
        cross apply string_split(t.TicketNo, ',')
    ) t on k.value = t.value
where [DrawDate] = '20170301'
Nitin Sontakke9y ago
NOWHERE in my reply did i state that it is an answer!
Manish Kumar9y ago
Apart from Nitin answer, i guess you need to create a UDF (table value) for splitting ticketNo. from comma into a row.
Nitin Sontakke9y ago
Something to begin with whoever is attempting to solve!
declare @LotteryTicketBuyerMaster table
(
  BuyerId Integer
,BuyerName varchar(50)
,EmailId  varchar(50)
,Category  varchar(50)
,TicketNo  varchar(50)
)

insert into @LotteryTicketBuyerMaster values
(1,'Nicks','[email protected]','Electronics','11001,11007')
,(2,'Nicks','[email protected]','Automobiles','14015,14020')
,(3,'James','[email protected]','Electronics','11002,11005')
,(4,'James','[email protected]','Automobiles','14016,14017')
,(5,'Mike','[email protected]','Electronics','11003')
,(6,'Nelson','[email protected]','Electronics','11004,11006')
,(7,'Mira','[email protected]','Furniture','13001')
,(8,'Jack','[email protected]','Automobiles','14018,14019')
,(9,'Mark','[email protected]','Furniture','13002,13004')
,(10,'Lary','[email protected]','Furniture','13003')

declare @LuckyTicketMaster table
(
   DrawDate Date
  ,Category Varchar(50)
  ,LuckyTicketNo Varchar(50)
)

insert into @LuckyTicketMaster values
('2017/03/01','Electronics','11001,11003')
,('2017/03/01','Automobiles','14016,14018')
,('2017/03/02','Electronics','11005,11006')
,('2017/03/02','Automobiles','14018,14020')
,('2017/03/02','Furniture','13002,13003')
Nitin Sontakke9y ago
Which version of SQL Server are you using?