Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

simplify the query

Status
Not open for further replies.
Hafidzkonek

Hafidzkonek

Programmer
Aug 9, 2019
3
0
0
MY
hi anyone can help me to simply this query

ALTER PROC [dbo].[USP_BFF_MV_RESERVATION]
@rt_ri_cd VARCHAR(10)
AS
BEGIN
SET NOCOUNT ON

DECLARE @rt_tts_ver VARCHAR(10),
@rt_today_date DATETIME,
@rt_sync_date_fr DATETIME,
@rt_sync_date_to DATETIME,
@rt_rtn_status VARCHAR(100),
@rt_ls_bbf VARCHAR(100),
@rt_ls_bbf2 VARCHAR(100),
@rt_ls_bbf_desc VARCHAR(400),
@rt_bk_id INT,
@rt_bk_cancel_yn CHAR(1),
@rt_bk_for_hotel VARCHAR(400)

DECLARE @rt_bke_id NUMERIC(10,0),
@rt_bke_date DATETIME,
@rt_bke_mv INT,
@rt_bke_bbf INT,
@rt_bke_bbfa INT,
@rt_bke_bbfc INT,
@rt_bke_bk_id NUMERIC(10,0)

IF rtrim(ltrim(@rt_ri_cd)) LIKE '%SCHA%' OR @rt_ri_cd LIKE '%SVILLA%' OR @rt_ri_cd LIKE '%ANGGERIK%'
BEGIN
SELECT bk_no,
ISNULL(atm_title,bk_title) AS atm_title,
ISNULL(atm_guest_name,bk_name) AS atm_guest_name,
ISNULL(atm_mobile,ISNULL(bk_mobile,bk_contact_no)) AS atm_mobile,
bk_chkin_dt,
bk_chkout_dt ,
ri_cd,
ri_ut_cd,
bk_for_hotel,
atm_agr_snp_no,
bk_unit,
bk_by,
' ' AS bbf_mv,
atm_voc_no,
bk_noshow_yn,
bk_id,
bk_cancel_yn,
(SELECT msip_name
FROM membership (NOLOCK), agreement (NOLOCK)
WHERE agr_msip_id = msip_id
AND agr_id = bk_agr_id) AS msip_name,
(SELECT pro_cd FROM product (NOLOCK), agreement (NOLOCK) WHERE pro_id = agr_pro_id AND bk_agr_id = agr_id ) AS pro_cd,
bk_name,
bk_contact_no,
bk_mobile,
bk_room_no,
bk_date
INTO #RESV_TRX
FROM booking (NOLOCK) LEFT OUTER JOIN attempt (NOLOCK) ON atm_bk_id = bk_id,rst_inv (NOLOCK)
WHERE bk_ri_id = ri_id
AND bk_chkin_dt BETWEEN DATEADD(MM, -6, CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 107)))
AND DATEADD(MM, 6, CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 107)))
AND (ri_cd LIKE 'SCHA%' OR ri_cd LIKE 'SVILLA%' OR ri_cd LIKE 'ANGGERIK%')
AND bk_cancel_yn = 'N'
AND bk_prgref <> 'CANCEL'

SELECT @rt_bk_id = bk_id FROM #RESV_TRX

SELECT bke_id, bke_bk_id, bke_date, bke_mv, bke_bbf, bke_bbfa, bke_bbfc
INTO #BFAST_TRX
FROM #RESV_TRX JOIN booking_extra ON bk_id = bke_bk_id
WHERE bke_bk_id = @rt_bk_id
ORDER BY bk_cancel_yn, bk_date, bk_no, bke_date


-- join: bbf_mv + bk_for_hotel --
DECLARE bk_cursor CURSOR FOR
SELECT bk_id, bk_for_hotel, bk_cancel_yn FROM #RESV_TRX

OPEN bk_cursor
FETCH NEXT FROM bk_cursor INTO @rt_bk_id, @rt_bk_for_hotel, @rt_bk_cancel_yn

WHILE @@FETCH_STATUS = 0
BEGIN
SET @rt_ls_bbf_desc = ''

DECLARE bfast_cursor CURSOR FOR
SELECT bke_id,bke_bk_id bke_date, bke_mv, bke_bbf, bke_bbfa, bke_bbfc
FROM #BFAST_TRX WHERE bke_bk_id = @rt_bk_id

OPEN bfast_cursor
FETCH NEXT FROM bfast_cursor INTO
@rt_bke_id, @rt_bke_date, @rt_bke_mv, @rt_bke_bbf, @rt_bke_bbfa, @rt_bke_bbfc

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @rt_ls_bbf = ''
IF (@rt_bke_bbf >= 1) AND (@rt_bke_mv >= 1)
SELECT @rt_ls_bbf = 'BBF x ' + LTRIM(STR(@rt_bke_bbf)) + ' & MV x ' + LTRIM(STR(@rt_bke_mv)) + ' on ' + CONVERT(VARCHAR(10),@rt_bke_date,103)
ELSE
IF (@rt_bke_bbf >= 1) AND (@rt_bke_mv <= 0 )
SELECT @rt_ls_bbf = 'BBF x ' + LTRIM(STR(@rt_bke_bbf)) + ' on ' + CONVERT(VARCHAR(10),@rt_bke_date,103)
ELSE
IF (@rt_bke_bbf <= 0 ) AND ( @rt_bke_mv >= 1)
SELECT @rt_ls_bbf = 'MV x ' + LTRIM(STR(@rt_bke_mv)) + ' on ' + CONVERT(VARCHAR(10),@rt_bke_date,103)

SELECT @rt_ls_bbf2 = ''
IF (@rt_bke_bbfa >= 1) AND (@rt_bke_bbfc >= 1)
SELECT @rt_ls_bbf2 = 'BBFA x ' + LTRIM(STR(@rt_bke_bbfa)) + ' & BBFC x ' + LTRIM(STR(@rt_bke_bbfc)) + ' on ' + CONVERT(VARCHAR(10),@rt_bke_date,103)
ELSE
IF (@rt_bke_bbfa >= 1) AND (@rt_bke_bbfc <= 0 )
SELECT @rt_ls_bbf2 = 'BBFA x ' + LTRIM(STR(@rt_bke_bbfa)) + ' on ' + CONVERT(VARCHAR(10),@rt_bke_date,103)
ELSE
IF (@rt_bke_bbfa <= 0 ) AND ( @rt_bke_bbfc >= 1)
SELECT @rt_ls_bbf2 = 'BBFC x ' + LTRIM(STR(@rt_bke_bbfc)) + ' on ' + CONVERT(VARCHAR(10),@rt_bke_date,103)


IF (@rt_ls_bbf <> '' AND @rt_ls_bbf2 <> '')
SELECT @rt_ls_bbf = @rt_ls_bbf + ' & ' + @rt_ls_bbf2
ELSE
IF (@rt_ls_bbf2 <> '')
SELECT @rt_ls_bbf = @rt_ls_bbf2

IF (@rt_ls_bbf <> '')
IF (@rt_ls_bbf_desc='')
SELECT @rt_ls_bbf_desc = @rt_ls_bbf
ELSE
SELECT @rt_ls_bbf_desc = @rt_ls_bbf_desc + ',' + @rt_ls_bbf

FETCH NEXT FROM bfast_cursor INTO
@rt_bke_id,@rt_bke_date, @rt_bke_mv, @rt_bke_bbf, @rt_bke_bbfa, @rt_bke_bbfc
END
CLOSE bfast_cursor
DEALLOCATE bfast_cursor

IF (@rt_ls_bbf_desc <> '')
UPDATE #RESV_TRX SET bbf_mv = @rt_ls_bbf_desc WHERE bk_id = @rt_bk_id

FETCH NEXT FROM bk_cursor INTO @rt_bk_id, @rt_bk_for_hotel, @rt_bk_cancel_yn

END
SELECT * FROM #RESV_TRX
CLOSE bk_cursor
DEALLOCATE bk_cursor
END
ELSE
BEGIN
SELECT bk_no,
ISNULL(atm_title,bk_title) AS atm_title,
ISNULL(atm_guest_name,bk_name) AS atm_guest_name,
ISNULL(atm_mobile,ISNULL(bk_mobile,bk_contact_no)) AS atm_mobile,
bk_chkin_dt,
bk_chkout_dt ,
ri_cd,
ri_ut_cd,
bk_for_hotel,
atm_agr_snp_no,
bk_unit,
bk_by,
' ' AS bbf_mv,
atm_voc_no,
bk_noshow_yn,
bk_id,
bk_cancel_yn,
(SELECT msip_name
FROM membership (NOLOCK), agreement (NOLOCK)
WHERE agr_msip_id = msip_id
AND agr_id = bk_agr_id) AS msip_name,
(SELECT pro_cd FROM product (NOLOCK), agreement (NOLOCK) WHERE pro_id = agr_pro_id AND bk_agr_id = agr_id ) AS pro_cd,
bk_name,
bk_contact_no,
bk_mobile,
bk_room_no,
bk_date
INTO #RESV_TRX2
FROM booking (NOLOCK) LEFT OUTER JOIN attempt (NOLOCK) ON atm_bk_id = bk_id,rst_inv (NOLOCK)
WHERE bk_ri_id = ri_id
AND bk_chkin_dt BETWEEN DATEADD(MM, -6, CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 107)))
AND DATEADD(MM, 6, CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 107)))
AND (ri_cd LIKE '%'+@rt_ri_cd+'%')
AND bk_cancel_yn = 'N'
AND bk_prgref <> 'CANCEL'


SELECT @rt_bk_id = bk_id FROM #RESV_TRX2

SELECT bke_id, bke_bk_id, bke_date, bke_mv, bke_bbf, bke_bbfa, bke_bbfc
INTO #BFAST_TRX2
FROM #RESV_TRX2 JOIN booking_extra ON bk_id = bke_bk_id
WHERE bke_bk_id = @rt_bk_id
ORDER BY bk_cancel_yn, bk_date, bk_no, bke_date


-- join: bbf_mv + bk_for_hotel --
DECLARE bk_cursor CURSOR FOR
SELECT bk_id, bk_for_hotel, bk_cancel_yn FROM #RESV_TRX2

OPEN bk_cursor
FETCH NEXT FROM bk_cursor INTO @rt_bk_id, @rt_bk_for_hotel, @rt_bk_cancel_yn

WHILE @@FETCH_STATUS = 0
BEGIN
SET @rt_ls_bbf_desc = ''

DECLARE bfast_cursor CURSOR FOR
SELECT bke_id,bke_bk_id bke_date, bke_mv, bke_bbf, bke_bbfa, bke_bbfc
FROM #BFAST_TRX2 WHERE bke_bk_id = @rt_bk_id

OPEN bfast_cursor
FETCH NEXT FROM bfast_cursor INTO
@rt_bke_id, @rt_bke_date, @rt_bke_mv, @rt_bke_bbf, @rt_bke_bbfa, @rt_bke_bbfc

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @rt_ls_bbf = ''
IF (@rt_bke_bbf >= 1) AND (@rt_bke_mv >= 1)
SELECT @rt_ls_bbf = 'BBF x ' + LTRIM(STR(@rt_bke_bbf)) + ' & MV x ' + LTRIM(STR(@rt_bke_mv)) + ' on ' + CONVERT(VARCHAR(10),@rt_bke_date,103)
ELSE
IF (@rt_bke_bbf >= 1) AND (@rt_bke_mv <= 0 )
SELECT @rt_ls_bbf = 'BBF x ' + LTRIM(STR(@rt_bke_bbf)) + ' on ' + CONVERT(VARCHAR(10),@rt_bke_date,103)
ELSE
IF (@rt_bke_bbf <= 0 ) AND ( @rt_bke_mv >= 1)
SELECT @rt_ls_bbf = 'MV x ' + LTRIM(STR(@rt_bke_mv)) + ' on ' + CONVERT(VARCHAR(10),@rt_bke_date,103)

SELECT @rt_ls_bbf2 = ''
IF (@rt_bke_bbfa >= 1) AND (@rt_bke_bbfc >= 1)
SELECT @rt_ls_bbf2 = 'BBFA x ' + LTRIM(STR(@rt_bke_bbfa)) + ' & BBFC x ' + LTRIM(STR(@rt_bke_bbfc)) + ' on ' + CONVERT(VARCHAR(10),@rt_bke_date,103)
ELSE
IF (@rt_bke_bbfa >= 1) AND (@rt_bke_bbfc <= 0 )
SELECT @rt_ls_bbf2 = 'BBFA x ' + LTRIM(STR(@rt_bke_bbfa)) + ' on ' + CONVERT(VARCHAR(10),@rt_bke_date,103)
ELSE
IF (@rt_bke_bbfa <= 0 ) AND ( @rt_bke_bbfc >= 1)
SELECT @rt_ls_bbf2 = 'BBFC x ' + LTRIM(STR(@rt_bke_bbfc)) + ' on ' + CONVERT(VARCHAR(10),@rt_bke_date,103)


IF (@rt_ls_bbf <> '' AND @rt_ls_bbf2 <> '')
SELECT @rt_ls_bbf = @rt_ls_bbf + ' & ' + @rt_ls_bbf2
ELSE
IF (@rt_ls_bbf2 <> '')
SELECT @rt_ls_bbf = @rt_ls_bbf2

IF (@rt_ls_bbf <> '')
IF (@rt_ls_bbf_desc='')
SELECT @rt_ls_bbf_desc = @rt_ls_bbf
ELSE
SELECT @rt_ls_bbf_desc = @rt_ls_bbf_desc + ',' + @rt_ls_bbf

FETCH NEXT FROM bfast_cursor INTO
@rt_bke_id,@rt_bke_date, @rt_bke_mv, @rt_bke_bbf, @rt_bke_bbfa, @rt_bke_bbfc
END
CLOSE bfast_cursor
DEALLOCATE bfast_cursor

IF (@rt_ls_bbf_desc <> '')
UPDATE #RESV_TRX2 SET bbf_mv = @rt_ls_bbf_desc WHERE bk_id = @rt_bk_id

FETCH NEXT FROM bk_cursor INTO @rt_bk_id, @rt_bk_for_hotel, @rt_bk_cancel_yn

END
SELECT * FROM #RESV_TRX2
CLOSE bk_cursor
DEALLOCATE bk_cursor
END
END
 
Sort by date Sort by votes
Why do you want to simplify it? (Assuming it can be simplified) It works, does it not? Then don't mess with it.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
155
Andrzejek
Andrzejek
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
169
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
76
Chris Miller
Chris Miller
WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
130
Chris Miller
Chris Miller
ahmedsa2018
  • Locked
  • Question
How to enhance query to run faster ?
Replies
0
Views
50
ahmedsa2018
ahmedsa2018

Part and Inventory Search

Sponsor

Back
Top