SQLTeam.com | Weblogs | Forums

Need Pivot Table


#1

Dear Friends,

I had used the following query to extract the pivot output earlier;

With Exporter
AS
(
select country_name 
from exporter_returns
)
SELECT *
FROM
(
SELECT f.country_name,
REPLACE(RIGHT(CONVERT(varchar(11),t.[Date],6),6),' ','-') AS MonthYr,
CASE WHEN COALESCE(Cnt,0) = 0 THEN NULL ELSE QTY END AS Return_Status
FROM Exporter f
CROSS JOIN dbo.CalendarTable('20150401','20150901',0,1)t
OUTER APPLY (SELECT COUNT(1) AS Cnt,f.country_name,sum(isnull(quantity,0)) QTY 
FROM exporter_returns fgl 
WHERE f.country_name=fgl.country_name 
and returns_month >= t.[date]
AND returns_month < DATEADD(mm,1,t.[date]) group by country_name
)tmp
)m
PIVOT (MAX(Return_Status) FOR MonthYr IN ([Apr-15],[May-15],[Jun-15],[Jul-15],[Aug-15],[Sep-15]))p
ORDER BY country_name

And the output is like "Existing OP"
Existing%20OP

Now i need the output by adding a column like "Request OP"

Please help me.


#2

Notice some of your previous questions were not answered bcs you did not provide ddl and dml.
You have to provide sample data not as an embedded picture


#3

Sorry Sir...

Existing Output is;

NAME OF THE COUNTRY Apr-15 May-15 Jun-15 Jul-15 Aug-15 Sep-15
AFGHANISTAN 0 25000 76950 158400 161300 91600
ALGERIA 36800 0 0 0 0 0
ARGENTINA 0 0 0 0 0 0
AUSTRALIA 265264.88 271549.36 284752.32 381506.71 314866.82 350269.05
AUSTRIA 0 3.2 70 0 0 0
AZERBAIJAN 0 41600 46620 0 0 99460
BAHRAIN 9773.6 29401 53378.92 360 42828.28 26996.9
BANGLADESH 824625 799889 170000 1078685 397845 171000

Requested Output is;

NAME OF THE COUNTRY Apr-15-Qty Apr-15-Val Mayb-15-Qty May-15-Val Jun-15-Qty Jun-15-Val Jul-15-Qty Jul-15-Val Aug-15-Qty Aug-15-Val Sep-15-Qty Sep-15-Val
AFGHANISTAN 0 0 25000 1000 76950 2000 158400 3000 161300 4000 91600 5000
ALGERIA 36800 12121 0 0 0 0 0 0 0 0 0 0
ARGENTINA 0 0 0 0 0 0 0 0 0 0 0 0
AUSTRALIA 265264.88 1425 271549.36 1525 284752.32 17878 381506.71 1252 314866.82 452265 350269.05 1365
AUSTRIA 0 0 3.2 1.2 70 52 0 0 0 0 0 0
AZERBAIJAN 0 0 41600 4512 46620 22 0 0 0 0 99460 45221
BAHRAIN 9773.6 1212 29401 1214 53378.92 1245 360 125 42828.28 12455 26996.9 1215
BANGLADESH 824625 1050 799889 1051 170000 18569 1078685 1542 397845 54652 171000 5414

Database Structure is;

rt_id numeric(18, 0) Unchecked
exporter_id nchar(10) Checked
company_name nvarchar(250) Checked
icd_name nvarchar(50) Checked
port_name nvarchar(50) Checked
region nvarchar(50) Checked
country_name nvarchar(50) Checked
variety_name nvarchar(50) Checked
type_name nvarchar(50) Checked
quantity numeric(18, 2) Checked
fob numeric(18, 2) Checked
unitprice numeric(18, 2) Checked
returns_month datetime Checked
submited_date datetime Checked
uname nvarchar(50) Checked
Unchecked

Thanks


#4

Please provide this data with ddl and dml

Create table xyz

Insert into table xyz


#5

USE [Returns]
GO
/****** Object: Table [dbo].[Exporter_Returns] Script Date: 03/06/2019 17:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Exporter_Returns](
[rt_id] [numeric](18, 0) NOT NULL,
[exporter_id] nchar NULL,
[company_name] nvarchar NULL,
[icd_name] nvarchar NULL,
[port_name] nvarchar NULL,
[region] nvarchar NULL,
[country_name] nvarchar NULL,
[variety_name] nvarchar NULL,
[type_name] nvarchar NULL,
[quantity] [numeric](18, 2) NULL,
[fob] [numeric](18, 2) NULL,
[unitprice] [numeric](18, 2) NULL,
[returns_month] [datetime] NULL,
[submited_date] [datetime] NULL,
[uname] nvarchar NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(1 AS Numeric(18, 0)), N'pe-2900 ', N'SUBODH BROTHERS PVT.LTD.', NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A26900000000 AS DateTime), N'manish')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(1 AS Numeric(18, 0)), N'e-5033 ', N'Plantrich Agri Tech Private Limited', NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A26C00000000 AS DateTime), N'teerna')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(1 AS Numeric(18, 0)), N'pe-4112 ', N'KUSUM INTERNATIONAL', N'CALCUTTA SEA', N'CALCUTTA SEA', N'NORTH', N'GERMANY', N'C.T.C', N'Bulk Tea', CAST(50000.00 AS Numeric(18, 2)), CAST(11365606.00 AS Numeric(18, 2)), CAST(227.00 AS Numeric(18, 2)), CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A26D00000000 AS DateTime), N'teerna')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(1 AS Numeric(18, 0)), N'pe-2187 ', N'TONGANI TEA COMPANY LIMITED', NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A27000000000 AS DateTime), N'teerna')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(1 AS Numeric(18, 0)), N'pe-3693 ', N'AQUARIUS MARKETING PVT.LTD.', N'', N'CALCUTTA SEA', N'NORTH', N'U.A.E', N'C.T.C', N'Bulk Tea', CAST(45923.00 AS Numeric(18, 2)), CAST(9130522.00 AS Numeric(18, 2)), CAST(198.00 AS Numeric(18, 2)), CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A27300000000 AS DateTime), N'manish')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(1 AS Numeric(18, 0)), N'pe-2103 ', N'KAIRBETTA ESTATES SYNDICATE', N'TUTICORIN', N'TUTICORIN', N'SOUTH', N'RUSSIAN FED', N'Orthodox', N'Bulk Tea', CAST(11600.00 AS Numeric(18, 2)), CAST(1792090.00 AS Numeric(18, 2)), CAST(154.00 AS Numeric(18, 2)), CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A27400000000 AS DateTime), N'teerna')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(1 AS Numeric(18, 0)), N'pe-2970 ', N'PARAG & CO.', NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A27500000000 AS DateTime), N'manish')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(1 AS Numeric(18, 0)), N'pe-1821 ', N'B.R. MALHOTRA & C0. PVT. LTD.', N'', N'CALCUTTA SEA', N'NORTH', N'U.A.E', N'Orthodox', N'Bulk Tea', CAST(16500.00 AS Numeric(18, 2)), CAST(4224395.00 AS Numeric(18, 2)), CAST(256.00 AS Numeric(18, 2)), CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A27600000000 AS DateTime), N'teerna')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(1 AS Numeric(18, 0)), N'pe-3554 ', N'SAPAT INTERNATIONAL PVT.LTD.,', NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A27A00000000 AS DateTime), N'teerna')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(1 AS Numeric(18, 0)), N'pe-1563 ', N'TEA PROMOTERS(INDIA) PVT LTD', NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A27B00000000 AS DateTime), N'teerna')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(1 AS Numeric(18, 0)), N'pe-1859 ', N'Bokahola Tea Company Pvt. Ltd.', NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A27C00000000 AS DateTime), N'teerna')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(1 AS Numeric(18, 0)), N'E-5110 ', N'Islet Coast Trading Company Pvt. Ltd.', NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A27D00000000 AS DateTime), N'teerna')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(1 AS Numeric(18, 0)), N'pe-2482 ', N'RAGHUNATH EXPORTS COMPANY', NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A27E00000000 AS DateTime), N'abhijit')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(1 AS Numeric(18, 0)), N'pe-1332 ', N'MCLEOD RUSSEL INDIA LIMITED', N'', N'AMINGAON(ICD)', N'NORTH', N'CANADA', N'C.T.C', N'Bulk Tea', CAST(22720.00 AS Numeric(18, 2)), CAST(5244023.00 AS Numeric(18, 2)), CAST(230.00 AS Numeric(18, 2)), CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A28100000000 AS DateTime), N'manish')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(1 AS Numeric(18, 0)), N'pe-3030 ', N'JFK INTERNATIONAL', N'', N'CALCUTTA SEA', N'NORTH', N'RUSSIAN FED', N'C.T.C', N'Bulk Tea', CAST(25500.00 AS Numeric(18, 2)), CAST(2894971.00 AS Numeric(18, 2)), CAST(113.00 AS Numeric(18, 2)), CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A28200000000 AS DateTime), N'manish')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(1 AS Numeric(18, 0)), N'pe-2059 ', N'ANDREW YULE & CO LTD.', N'', N'CALCUTTA SEA', N'NORTH', N'CANADA', N'C.T.C', N'Bulk Tea', CAST(22680.00 AS Numeric(18, 2)), CAST(4434029.00 AS Numeric(18, 2)), CAST(195.00 AS Numeric(18, 2)), CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A28300000000 AS DateTime), N'manish')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(1 AS Numeric(18, 0)), N'pe-2488 ', N'LIMTEX (INDIA)LTD.', N'', N'COCHIN SEA', N'SOUTH', N'PAKISTAN', N'C.T.C', N'Bulk Tea', CAST(363370.00 AS Numeric(18, 2)), CAST(30387145.00 AS Numeric(18, 2)), CAST(83.00 AS Numeric(18, 2)), CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A28400000000 AS DateTime), N'manish')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(1 AS Numeric(18, 0)), N'Pe-4339 ', N'JFK International Ltd.', N'', N'COCHIN SEA', N'SOUTH', N'POLAND', N'C.T.C', N'Bulk Tea', CAST(22500.00 AS Numeric(18, 2)), CAST(1919334.00 AS Numeric(18, 2)), CAST(85.00 AS Numeric(18, 2)), CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A28500000000 AS DateTime), N'teerna')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(16 AS Numeric(18, 0)), N'e-3887 ', N'Inderchand Sitaram', NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0x0000A2E100000000 AS DateTime), CAST(0x0000A31600000000 AS DateTime), N'manish')
INSERT [dbo].[Exporter_Returns] ([rt_id], [exporter_id], [company_name], [icd_name], [port_name], [region], [country_name], [variety_name], [type_name], [quantity], [fob], [unitprice], [returns_month], [submited_date], [uname]) VALUES (CAST(47 AS Numeric(18, 0)), N'pe-3915 ', N'Lochan Tea Limited', NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0x0000A26900000000 AS DateTime), CAST(0x0000A2B900000000 AS DateTime), N'Administrator')

Sir, around 37000 records are there, So i pasted here only 25 records for execution purpose.

Thanks


#6

please test this script locally first


#7

Hi

Please find the corrected script

drop create data .. corrected..
DROP TABLE [dbo].[exporter_returns] 

go 

CREATE TABLE [dbo].[exporter_returns] 
  ( 
     [rt_id]         [NUMERIC](18, 0) NOT NULL, 
     [exporter_id]   NCHAR(100) NULL, 
     [company_name]  NVARCHAR(100) NULL, 
     [icd_name]      NVARCHAR(100) NULL, 
     [port_name]     NVARCHAR(100) NULL, 
     [region]        NVARCHAR(100) NULL, 
     [country_name]  NVARCHAR(100) NULL, 
     [variety_name]  NVARCHAR(100) NULL, 
     [type_name]     NVARCHAR(100) NULL, 
     [quantity]      [NUMERIC](18, 2) NULL, 
     [fob]           [NUMERIC](18, 2) NULL, 
     [unitprice]     [NUMERIC](18, 2) NULL, 
     [returns_month] [DATETIME] NULL, 
     [submited_date] [DATETIME] NULL, 
     [uname]         NVARCHAR(100) NULL 
  ) 
ON [PRIMARY] 

go 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(1 AS NUMERIC(18, 0)), 
        N'pe-2900 ', 
        N'SUBODH BROTHERS PVT.LTD.', 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A24A00000000 AS DATETIME), 
        Cast(0x0000A26900000000 AS DATETIME), 
        N'manish') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(1 AS NUMERIC(18, 0)), 
        N'e-5033 ', 
        N'Plantrich Agri Tech Private Limited', 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A24A00000000 AS DATETIME), 
        Cast(0x0000A26C00000000 AS DATETIME), 
        N'teerna') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(1 AS NUMERIC(18, 0)), 
        N'pe-4112 ', 
        N'KUSUM INTERNATIONAL', 
        N'CALCUTTA SEA', 
        N'CALCUTTA SEA', 
        N'NORTH', 
        N'GERMANY', 
        N'C.T.C', 
        N'Bulk Tea', 
        Cast(50000.00 AS NUMERIC(18, 2)), 
        Cast(11365606.00 AS NUMERIC(18, 2)), 
        Cast(227.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A24A00000000 AS DATETIME), 
        Cast(0x0000A26D00000000 AS DATETIME), 
        N'teerna') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(1 AS NUMERIC(18, 0)), 
        N'pe-2187 ', 
        N'TONGANI TEA COMPANY LIMITED', 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A24A00000000 AS DATETIME), 
        Cast(0x0000A27000000000 AS DATETIME), 
        N'teerna') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(1 AS NUMERIC(18, 0)), 
        N'pe-3693 ', 
        N'AQUARIUS MARKETING PVT.LTD.', 
        N'', 
        N'CALCUTTA SEA', 
        N'NORTH', 
        N'U.A.E', 
        N'C.T.C', 
        N'Bulk Tea', 
        Cast(45923.00 AS NUMERIC(18, 2)), 
        Cast(9130522.00 AS NUMERIC(18, 2)), 
        Cast(198.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A24A00000000 AS DATETIME), 
        Cast(0x0000A27300000000 AS DATETIME), 
        N'manish') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(1 AS NUMERIC(18, 0)), 
        N'pe-2103 ', 
        N'KAIRBETTA ESTATES SYNDICATE', 
        N'TUTICORIN', 
        N'TUTICORIN', 
        N'SOUTH', 
        N'RUSSIAN FED', 
        N'Orthodox', 
        N'Bulk Tea', 
        Cast(11600.00 AS NUMERIC(18, 2)), 
        Cast(1792090.00 AS NUMERIC(18, 2)), 
        Cast(154.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A24A00000000 AS DATETIME), 
        Cast(0x0000A27400000000 AS DATETIME), 
        N'teerna') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(1 AS NUMERIC(18, 0)), 
        N'pe-2970 ', 
        N'PARAG & CO.', 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A24A00000000 AS DATETIME), 
        Cast(0x0000A27500000000 AS DATETIME), 
        N'manish') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(1 AS NUMERIC(18, 0)), 
        N'pe-1821 ', 
        N'B.R. MALHOTRA & C0. PVT. LTD.', 
        N'', 
        N'CALCUTTA SEA', 
        N'NORTH', 
        N'U.A.E', 
        N'Orthodox', 
        N'Bulk Tea', 
        Cast(16500.00 AS NUMERIC(18, 2)), 
        Cast(4224395.00 AS NUMERIC(18, 2)), 
        Cast(256.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A24A00000000 AS DATETIME), 
        Cast(0x0000A27600000000 AS DATETIME), 
        N'teerna') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(1 AS NUMERIC(18, 0)), 
        N'pe-3554 ', 
        N'SAPAT INTERNATIONAL PVT.LTD.,', 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A24A00000000 AS DATETIME), 
        Cast(0x0000A27A00000000 AS DATETIME), 
        N'teerna') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(1 AS NUMERIC(18, 0)), 
        N'pe-1563 ', 
        N'TEA PROMOTERS(INDIA) PVT LTD', 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A24A00000000 AS DATETIME), 
        Cast(0x0000A27B00000000 AS DATETIME), 
        N'teerna') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(1 AS NUMERIC(18, 0)), 
        N'pe-1859 ', 
        N'Bokahola Tea Company Pvt. Ltd.', 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A24A00000000 AS DATETIME), 
        Cast(0x0000A27C00000000 AS DATETIME), 
        N'teerna') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(1 AS NUMERIC(18, 0)), 
        N'E-5110 ', 
        N'Islet Coast Trading Company Pvt. Ltd.', 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A24A00000000 AS DATETIME), 
        Cast(0x0000A27D00000000 AS DATETIME), 
        N'teerna') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(1 AS NUMERIC(18, 0)), 
        N'pe-2482 ', 
        N'RAGHUNATH EXPORTS COMPANY', 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A24A00000000 AS DATETIME), 
        Cast(0x0000A27E00000000 AS DATETIME), 
        N'abhijit') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(1 AS NUMERIC(18, 0)), 
        N'pe-1332 ', 
        N'MCLEOD RUSSEL INDIA LIMITED', 
        N'', 
        N'AMINGAON(ICD)', 
        N'NORTH', 
        N'CANADA', 
        N'C.T.C', 
        N'Bulk Tea', 
        Cast(22720.00 AS NUMERIC(18, 2)), 
        Cast(5244023.00 AS NUMERIC(18, 2)), 
        Cast(230.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A24A00000000 AS DATETIME), 
        Cast(0x0000A28100000000 AS DATETIME), 
        N'manish') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(1 AS NUMERIC(18, 0)), 
        N'pe-3030 ', 
        N'JFK INTERNATIONAL', 
        N'', 
        N'CALCUTTA SEA', 
        N'NORTH', 
        N'RUSSIAN FED', 
        N'C.T.C', 
        N'Bulk Tea', 
        Cast(25500.00 AS NUMERIC(18, 2)), 
        Cast(2894971.00 AS NUMERIC(18, 2)), 
        Cast(113.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A24A00000000 AS DATETIME), 
        Cast(0x0000A28200000000 AS DATETIME), 
        N'manish') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(1 AS NUMERIC(18, 0)), 
        N'pe-2059 ', 
        N'ANDREW YULE & CO LTD.', 
        N'', 
        N'CALCUTTA SEA', 
        N'NORTH', 
        N'CANADA', 
        N'C.T.C', 
        N'Bulk Tea', 
        Cast(22680.00 AS NUMERIC(18, 2)), 
        Cast(4434029.00 AS NUMERIC(18, 2)), 
        Cast(195.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A24A00000000 AS DATETIME), 
        Cast(0x0000A28300000000 AS DATETIME), 
        N'manish') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(1 AS NUMERIC(18, 0)), 
        N'pe-2488 ', 
        N'LIMTEX (INDIA)LTD.', 
        N'', 
        N'COCHIN SEA', 
        N'SOUTH', 
        N'PAKISTAN', 
        N'C.T.C', 
        N'Bulk Tea', 
        Cast(363370.00 AS NUMERIC(18, 2)), 
        Cast(30387145.00 AS NUMERIC(18, 2)), 
        Cast(83.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A24A00000000 AS DATETIME), 
        Cast(0x0000A28400000000 AS DATETIME), 
        N'manish') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(1 AS NUMERIC(18, 0)), 
        N'Pe-4339 ', 
        N'JFK International Ltd.', 
        N'', 
        N'COCHIN SEA', 
        N'SOUTH', 
        N'POLAND', 
        N'C.T.C', 
        N'Bulk Tea', 
        Cast(22500.00 AS NUMERIC(18, 2)), 
        Cast(1919334.00 AS NUMERIC(18, 2)), 
        Cast(85.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A24A00000000 AS DATETIME), 
        Cast(0x0000A28500000000 AS DATETIME), 
        N'teerna') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(16 AS NUMERIC(18, 0)), 
        N'e-3887 ', 
        N'Inderchand Sitaram', 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A2E100000000 AS DATETIME), 
        Cast(0x0000A31600000000 AS DATETIME), 
        N'manish') 

INSERT [dbo].[exporter_returns] 
       ([rt_id], 
        [exporter_id], 
        [company_name], 
        [icd_name], 
        [port_name], 
        [region], 
        [country_name], 
        [variety_name], 
        [type_name], 
        [quantity], 
        [fob], 
        [unitprice], 
        [returns_month], 
        [submited_date], 
        [uname]) 
VALUES (Cast(47 AS NUMERIC(18, 0)), 
        N'pe-3915 ', 
        N'Lochan Tea Limited', 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        NULL, 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0.00 AS NUMERIC(18, 2)), 
        Cast(0x0000A26900000000 AS DATETIME), 
        Cast(0x0000A2B900000000 AS DATETIME), 
        N'Administrator')

#8

hi guna

i am working in my office ..saturdays and sundays also

when i run the SQL

SQL ..
WITH exporter 
     AS (SELECT country_name 
         FROM   exporter_returns) 
SELECT * 
FROM   (SELECT f.country_name, 
               Replace(RIGHT(CONVERT(VARCHAR(11), t.[date], 6), 6), ' ', '-') AS 
                      MonthYr, 
               CASE 
                 WHEN COALESCE(cnt, 0) = 0 THEN NULL 
                 ELSE qty 
               END                                                            AS 
                      Return_Status 
        FROM   exporter f 
               CROSS JOIN dbo.Calendartable('20150401', '20150901', 0, 1)t 
               OUTER apply (SELECT Count(1)                 AS Cnt, 
                                   f.country_name, 
                                   Sum(Isnull(quantity, 0)) QTY 
                            FROM   exporter_returns fgl 
                            WHERE  f.country_name = fgl.country_name 
                                   AND returns_month >= t.[date] 
                                   AND returns_month < Dateadd(mm, 1, t.[date]) 
                            GROUP  BY country_name)tmp)m 
       PIVOT (Max(return_status) 
             FOR monthyr IN ([Apr-15], 
                             [May-15], 
                             [Jun-15], 
                             [Jul-15], 
                             [Aug-15], 
                             [Sep-15]))p 
ORDER  BY country_name

'dbo.CalendarTable'.

data also needed


#9

hi guna

see this SQL ... get the idea .. try
the values for FOB .. val are not coming correctly

SQL .. not giving correct result .. but gives idea
WITH exporter 
     AS (SELECT * 
         FROM   exporter_returns), 
     cte 
     AS (SELECT * 
         FROM   (SELECT f.country_name, 
     Replace(RIGHT(CONVERT(VARCHAR(11), t.[date], 6), 6), ' ', '-') 
     AS 
             MonthYr, 
     CASE 
       WHEN COALESCE(cnt, 0) = 0 THEN NULL 
       ELSE qty 
     END 
     AS 
             Return_Status 
     FROM   exporter f 
     CROSS JOIN dbo.Calendartable('20150401', '20150901', 0, 1)t 
     OUTER apply (SELECT Count(1)                 AS Cnt, 
                         f.country_name, 
                         Sum(Isnull(quantity, 0)) QTY 
                  FROM   exporter_returns fgl 
                  WHERE  f.country_name = fgl.country_name 
                         AND returns_month >= t.[date] 
                         AND returns_month < Dateadd(mm, 1, t.[date]) 
                  GROUP  BY country_name)tmp)m 
     PIVOT (Max(return_status) 
     FOR monthyr IN ([Apr-15], 
                   [May-15], 
                   [Jun-15], 
                   [Jul-15], 
                   [Aug-15], 
                   [Sep-15]))p), 
     cte1 
     AS (SELECT * 
         FROM   (SELECT f.country_name, 
     Replace(RIGHT(CONVERT(VARCHAR(11), t.[date], 6), 6), ' ', '-') 
     AS 
             MonthYr, 
     fob 
     FROM   exporter f 
     CROSS JOIN dbo.Calendartable('20150401', '20150901', 0, 1)t 
     OUTER apply (SELECT Count(1)                 AS Cnt, 
                         f.country_name, 
                         Sum(Isnull(quantity, 0)) QTY 
                  FROM   exporter_returns fgl 
                  WHERE  f.country_name = fgl.country_name 
                         AND returns_month >= t.[date] 
                         AND returns_month < Dateadd(mm, 1, t.[date]) 
                  GROUP  BY country_name)tmp)m 
     PIVOT (Max(fob) 
     FOR monthyr IN ([Apr-15], 
                   [May-15], 
                   [Jun-15], 
                   [Jul-15], 
                   [Aug-15], 
                   [Sep-15]))p) 
SELECT a.country_name, 
       a.[apr-15] AS [Apr-15 Qty], 
       b.[apr-15] AS [Apr-15 Val], 
       a.[may-15] AS [May-15 Qty], 
       b.[may-15] AS [May-15 Val], 
       a.[jun-15] AS [jun-15 Qty], 
       b.[jun-15] AS [jun-15 Val], 
       a.[jul-15] AS [jul-15 Qty], 
       b.[jul-15] AS [jul-15 Val], 
       a.[aug-15] AS [aug-15 Qty], 
       b.[aug-15] AS [aug-15 Val], 
       a.[sep-15] AS [sep-15 Qty], 
       b.[sep-15] AS [sep-15 Val] 
FROM   cte a 
       JOIN cte1 b 
         ON a.country_name = b.country_name 

go

#10

Sir,

I had little bit changed the query as follows and got output.

WITH exporter 
     AS (SELECT * 
         FROM   exporter_returns), 
     cte 
     AS (SELECT * 
         FROM   (SELECT f.country_name, 
     Replace(RIGHT(CONVERT(VARCHAR(11), t.[date], 6), 6), ' ', '-') 
     AS 
             MonthYr, 
     CASE 
       WHEN COALESCE(cnt, 0) = 0 THEN NULL 
       ELSE qty 
     END 
     AS 
             Return_Status 
     FROM   exporter f 
     CROSS JOIN dbo.Calendartable('20150401', '20150901', 0, 1)t 
     OUTER apply (SELECT Count(1)                 AS Cnt, 
                         f.country_name, 
                         Sum(Isnull(quantity, 0)) QTY 
                  FROM   exporter_returns fgl 
                  WHERE  f.country_name = fgl.country_name 
                         AND returns_month >= t.[date] 
                         AND returns_month < Dateadd(mm, 1, t.[date]) 
                  GROUP  BY country_name)tmp)m 
     PIVOT (Max(return_status) 
     FOR monthyr IN ([Apr-15], 
                   [May-15], 
                   [Jun-15], 
                   [Jul-15], 
                   [Aug-15], 
                   [Sep-15]))p), 
     cte1 
     AS (SELECT * 
         FROM   (SELECT f.country_name, 
     Replace(RIGHT(CONVERT(VARCHAR(11), t.[date], 6), 6), ' ', '-') 
     AS 
             MonthYr, 
     CASE 
       WHEN COALESCE(cnt, 0) = 0 THEN NULL 
       ELSE VAL 
     END 
	 As Return_Status1 
     FROM   exporter f 
     CROSS JOIN dbo.Calendartable('20150401', '20150901', 0, 1)t 
     OUTER apply (SELECT Count(1)                 AS Cnt, 
                         f.country_name, 
                         Sum(Isnull(fob, 0)) VAL 
                  FROM   exporter_returns fgl 
                  WHERE  f.country_name = fgl.country_name 
                         AND returns_month >= t.[date] 
                         AND returns_month < Dateadd(mm, 1, t.[date]) 
                  GROUP  BY country_name)tmp)m 
     PIVOT (Max(Return_Status1) 
     FOR monthyr IN ([Apr-15], 
                   [May-15], 
                   [Jun-15], 
                   [Jul-15], 
                   [Aug-15], 
                   [Sep-15]))p) 
SELECT a.country_name, 
       a.[apr-15] AS [Apr-15-Qty], 
       b.[apr-15] AS [Apr-15-Val], 
       a.[may-15] AS [May-15-Qty], 
       b.[may-15] AS [May-15-Val], 
       a.[jun-15] AS [Jun-15-Qty], 
       b.[jun-15] AS [Jun-15-Val], 
       a.[jul-15] AS [Jul-15-Qty], 
       b.[jul-15] AS [Jul-15-Val], 
       a.[aug-15] AS [Aug-15-Qty], 
       b.[aug-15] AS [Aug-15-Val], 
       a.[sep-15] AS [Sep-15-Qty], 
       b.[sep-15] AS [Sep-15-Val] 
FROM   cte a 
       JOIN cte1 b 
         ON a.country_name = b.country_name 

go

Thanks Sir...


#11

ok guna

njoy

:slight_smile:

:+1: