USE [TestDB]
GO
/****** Object: Table [dbo].[Orders] Script Date: 02/19/2018 15:52:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Orders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NULL,
[Item] [varchar](50) NULL,
[Date] [date] NULL,
[DeliveryDate] [date] NULL,
[Qty] [float] NULL,
CONSTRAINT [PK_MonthlyPivot] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Orders] ON
INSERT [dbo].[Orders] ([ID], [OrderID], [Item], [Date], [DeliveryDate], [Qty]) VALUES (1, 1, N'A1', CAST(0xB63D0B00 AS Date), CAST(0xD53D0B00 AS Date), 100)
INSERT [dbo].[Orders] ([ID], [OrderID], [Item], [Date], [DeliveryDate], [Qty]) VALUES (2, 2, N'A2', CAST(0xF13D0B00 AS Date), CAST(0x103E0B00 AS Date), 150)
INSERT [dbo].[Orders] ([ID], [OrderID], [Item], [Date], [DeliveryDate], [Qty]) VALUES (4, 3, N'A3', CAST(0x1A3E0B00 AS Date), CAST(0x383E0B00 AS Date), 250)
INSERT [dbo].[Orders] ([ID], [OrderID], [Item], [Date], [DeliveryDate], [Qty]) VALUES (5, 4, N'A5', CAST(0x4D3E0B00 AS Date), CAST(0x6B3E0B00 AS Date), 300)
INSERT [dbo].[Orders] ([ID], [OrderID], [Item], [Date], [DeliveryDate], [Qty]) VALUES (6, 5, N'A6', CAST(0xE63E0B00 AS Date), CAST(0x043F0B00 AS Date), 350)
SET IDENTITY_INSERT [dbo].[Orders] OFF
/****** Object: Table [dbo].[Dispatch] Script Date: 02/19/2018 15:52:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Dispatch](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NULL,
[Qty] [int] NULL,
[PackType] [varchar](50) NULL,
[PackDate] [date] NULL,
CONSTRAINT [PK_Dispatch] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Dispatch] ON
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (1, 1, 2, N'P', CAST(0xB73D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (2, 1, 3, N'P', CAST(0xB83D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (3, 1, 4, N'P', CAST(0xBA3D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (4, 2, 3, N'P', CAST(0xF43D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (5, 2, 4, N'P', CAST(0xF73D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (6, 2, 6, N'P', CAST(0xFB3D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (7, 2, 9, N'P', CAST(0xFF3D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (8, 1, 2, N'P', CAST(0xB73D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (9, 1, 3, N'P', CAST(0xB83D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (10, 1, 4, N'P', CAST(0xBA3D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (11, 2, 3, N'P', CAST(0xF43D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (12, 2, 4, N'P', CAST(0xF73D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (13, 2, 6, N'P', CAST(0xFB3D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (14, 2, 9, N'P', CAST(0xFF3D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (15, 1, 2, N'S', CAST(0xB73D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (16, 1, 3, N'S', CAST(0xB83D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (17, 1, 4, N'S', CAST(0xBA3D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (18, 2, 3, N'S', CAST(0xF43D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (19, 2, 4, N'S', CAST(0xF73D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (20, 2, 6, N'S', CAST(0xFB3D0B00 AS Date))
INSERT [dbo].[Dispatch] ([ID], [OrderID], [Qty], [PackType], [PackDate]) VALUES (21, 2, 9, N'S', CAST(0xFF3D0B00 AS Date))
SET IDENTITY_INSERT [dbo].[Dispatch] OFF
------------------------------------------------------------------------------------------------------------------------------------------------
-- If the value is 0 then i am returing the same order qty value
-- Select Query return only packtype='P' records, but other also should be displayed even if there is no dispatch record.
Select Distinct A.OrderID,sum(A.Qty) as OrderQty,sum(isnull(B.Qty,A.Qty)) as DispatchQty,DATENAME(MONTH,(A.DeliveryDate)) as DeliveryDate from Orders A Left Outer Join Dispatch B
On A.OrderID=B.OrderID
where B.PackType='P'
Group By A.OrderID,A.DeliveryDate
OrderID OrderQty DispatchQty DeliveryDate
1 900 27 February
2 1800 66 April
3 250 250 May
4 300 300 July
5 350 350 December
