Duplicate records is populating

Question

question

srinnippu-1270 asked Apr 30, '22 srinnippu-1270 answered May 4, '22

Duplicate records is populating

Hi All,

I am joining 2 table Date and Holiday Table using Date columns in the tables.my requirement is to populating Indicator in single row where there is common date of specific region.

I am using Case statement to fetech data into respective columns

US_Holiday_Ind : case Region == US then 'Y','N' END
UK_Holiday_Ind : case Region == UK then 'Y','N' END
Canada_Holiday_Ind : Case when Region == Canada then 'Y' ,'N' END

Pls suggest on this

azure-sqldatabase-edge

image.png (12.3 KiB)

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnuragSharma-MSFT · May 02 at 05:53 AM

Hi @srinnippu-1270, welcome to Microsoft Q&A forum.

Would it be possible for you to share the structure of those 2 Date and Holiday tables with few rows (just like above screenshot), so it would be easier to write the queries?

0 ·

srinnippu-1270 AnuragSharma-MSFT · May 02 at 10:36 AM

i am using case statement to derive those indicator columns by using Region Name

Thank in advance

0 ·

image.png (63.3 KiB)

Answer 1 · 2022-05-02T13:34:32.547Z

AnuragSharma-MSFT answered May 2, '22 AnuragSharma-MSFT commented May 4, '22

Hi @srinnippu-1270, thanks for providing more details.

Please try running below script:

 WITH CTEResult as(
 SELECT * FROM Holiday_Date 
  PIVOT
 (MIN(Holiday_Name) FOR regionname IN (US,UK,Canada)) AS PivotTable
  )
  UPDATE d 
 SET d.US_Holiday_IND = CASE WHEN hd.US is not null THEN 'Y' ELSE 'N' END,
 d.UK_Holiday_IND = CASE WHEN hd.UK is not null  THEN 'Y' ELSE 'N' END,
 d.Canada_Holiday_IND = CASE WHEN hd.Canada is not null  THEN 'Y' ELSE 'N' END
 FROM date AS d
 INNER JOIN CTEResult AS hd 
        on d.date = hd.Holiday_Date

This script will update 'Date' table with values from Holiday_date table.

Below is the result:

Please let me know if you see any issue with this script and we can look again.

If answer is helpful please click on as it could help other members of the Microsoft Q&A community who have similar questions and are looking for solutions. Thank you for helping to improve Microsoft Q&A!

image.png (11.0 KiB)

image.png (4.8 KiB)

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

srinnippu-1270 · May 02 at 04:17 PM

If we see below there is no us_holiday_IN ,UK_Holiday_IND and canada_Holiday_IND is present in Holiday table not in Date table.

SET d.US_Holiday_IND = CASE WHEN hd.US is not null THEN 'Y' ELSE 'N' END,
d.UK_Holiday_IND = CASE WHEN hd.UK is not null THEN 'Y' ELSE 'N' END,
d.Canada_Holiday_IND = CASE WHEN hd.Canada is not null THEN 'Y' ELSE 'N' END

pls suggest

0 ·

Answer 2 · 2022-05-04T10:07:38.997Z

srinnippu-1270 answered May 4, '22

Thank you much it works as expected

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question