Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ Vendor and the Coupon Puzzle ]

In this puzzle we have to find the latest discount for each row in the vendor table. The Please check the sample input and the expected output for details.

Sample Input

Vendor

Vendor Date
McDonald 02-03-2014
SubWay 01-05-2014
McDonald 02-06-2014
McDonald 14-03-2014
McDonald 04-06-2014
McDonald 05-08-2014
SubWay 09-06-2013
SubWay 07-06-2014
SubWay 01-05-2014
SubWay 15-05-2014

Coupon

VendorName CouponDate Discount(%)
McDonald 05-03-2014 10
McDonald 01-06-2014 20
McDonald 16-02-2014 25
McDonald 25-03-2014 15
McDonald 10-06-2014 27
McDonald 10-08-2014 30
SubWay 11-03-2014 25
SubWay 18-04-2014 30
SubWay 30-01-2014 15
SubWay 02-05-2014 17

Expected output

Vendor Date Discount
McDonald 02-03-2014 25
SubWay 01-05-2014 30
McDonald 02-06-2014 20
McDonald 14-03-2014 10
McDonald 04-06-2014 20
McDonald 05-08-2014 27
SubWay 09-06-2013 0
SubWay 07-06-2014 17
SubWay 01-05-2014 30
SubWay 15-05-2014 17

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


CREATE TABLE [dbo].[Coupons]

(

[VendorName] [varchar](50) NULL,

[CouponDate] [datetime] NULL,

[Discount(%)] [int] NULL

)

CREATE TABLE [dbo].[Vendors]

(

[Vendor] [varchar](50) NULL,

[Date] [datetime] NULL

) ON [PRIMARY]

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'McDonald', CAST(0x0000A2E500000000 AS DateTime), 10)

GO

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'McDonald', CAST(0x0000A33D00000000 AS DateTime), 20)

GO

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'McDonald', CAST(0x0000A2D400000000 AS DateTime), 25)

GO

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'McDonald', CAST(0x0000A2F900000000 AS DateTime), 15)

GO

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'McDonald', CAST(0x0000A34600000000 AS DateTime), 27)

GO

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'McDonald', CAST(0x0000A38300000000 AS DateTime), 30)

GO

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'SubWay', CAST(0x0000A2EB00000000 AS DateTime), 25)

GO

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'SubWay', CAST(0x0000A31100000000 AS DateTime), 30)

GO

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'SubWay', CAST(0x0000A2C300000000 AS DateTime), 15)

GO

INSERT [dbo].[Coupons] ([VendorName], [CouponDate], [Discount(%)]) VALUES (N'SubWay', CAST(0x0000A31F00000000 AS DateTime), 17)

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'McDonald', CAST(0x0000A2E200000000 AS DateTime))

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'SubWay', CAST(0x0000A31E00000000 AS DateTime))

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'McDonald', CAST(0x0000A33E00000000 AS DateTime))

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'McDonald', CAST(0x0000A2EE00000000 AS DateTime))

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'McDonald', CAST(0x0000A34000000000 AS DateTime))

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'McDonald', CAST(0x0000A37E00000000 AS DateTime))

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'SubWay', CAST(0x0000A1D800000000 AS DateTime))

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'SubWay', CAST(0x0000A34300000000 AS DateTime))

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'SubWay', CAST(0x0000A31E00000000 AS DateTime))

GO

INSERT [dbo].[Vendors] ([Vendor], [Date]) VALUES (N'SubWay', CAST(0x0000A32C00000000 AS DateTime))

GO

Please leave a comment if you need solution to the above puzzle

Keep Learning