My table and data as follow,
USE [WeightManagement]
GO
/****** Object: Table [dbo].[crpt_NotaPenyataKewangan_BL_Vertical_1] Script Date: 7/11/2019 10:41:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[crpt_NotaPenyataKewangan_BL_Vertical_1](
[idx] [int] IDENTITY(-2147483648,1) NOT NULL,
[batch_Id] [uniqueidentifier] NULL,
[group_1Digit] [char](5) NULL,
[group_2Digit] [char](5) NULL,
[group_3Digit] [char](5) NULL,
[chart_code] [char](5) NULL,
[pusat_kos] [nvarchar](10) NULL,
[close_amt] [decimal](18, 2) NULL,
[kod] [nvarchar](5) NULL,
[year_num] [int] NULL,
[period_num] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[crpt_NotaPenyataKewangan_BL_Vertical_1] ON
GO
INSERT [dbo].[crpt_NotaPenyataKewangan_BL_Vertical_1] ([idx], [batch_Id], [group_1Digit], [group_2Digit], [group_3Digit], [chart_code], [pusat_kos], [close_amt], [kod], [year_num], [period_num]) VALUES (-2147483648, N'4245a9c9-4e34-4178-94f9-002a0fe7846b', N'20000', N'29000', N'29100', N'29143', NULL, CAST(0.00 AS Decimal(18, 2)), N'PS', 2014, NULL)
GO
INSERT [dbo].[crpt_NotaPenyataKewangan_BL_Vertical_1] ([idx], [batch_Id], [group_1Digit], [group_2Digit], [group_3Digit], [chart_code], [pusat_kos], [close_amt], [kod], [year_num], [period_num]) VALUES (-2147483647, N'4245a9c9-4e34-4178-94f9-002a0fe7846b', N'20000', N'29000', N'29100', N'29144', NULL, CAST(0.00 AS Decimal(18, 2)), N'PS', 2014, NULL)
GO
INSERT [dbo].[crpt_NotaPenyataKewangan_BL_Vertical_1] ([idx], [batch_Id], [group_1Digit], [group_2Digit], [group_3Digit], [chart_code], [pusat_kos], [close_amt], [kod], [year_num], [period_num]) VALUES (-2147483646, N'4245a9c9-4e34-4178-94f9-002a0fe7846b', N'20000', N'29000', N'29100', N'29199', NULL, CAST(0.00 AS Decimal(18, 2)), N'PS', 2014, NULL)
GO
INSERT [dbo].[crpt_NotaPenyataKewangan_BL_Vertical_1] ([idx], [batch_Id], [group_1Digit], [group_2Digit], [group_3Digit], [chart_code], [pusat_kos], [close_amt], [kod], [year_num], [period_num]) VALUES (-2147483645, N'4245a9c9-4e34-4178-94f9-002a0fe7846b', N'20000', N'29000', N'29200', N'29201', NULL, CAST(0.00 AS Decimal(18, 2)), N'PS', 2014, NULL)
GO
INSERT [dbo].[crpt_NotaPenyataKewangan_BL_Vertical_1] ([idx], [batch_Id], [group_1Digit], [group_2Digit], [group_3Digit], [chart_code], [pusat_kos], [close_amt], [kod], [year_num], [period_num]) VALUES (-2147483644, N'4245a9c9-4e34-4178-94f9-002a0fe7846b', N'20000', N'29000', N'29200', N'29299', NULL, CAST(0.00 AS Decimal(18, 2)), N'PS', 2014, NULL)
GO
INSERT [dbo].[crpt_NotaPenyataKewangan_BL_Vertical_1] ([idx], [batch_Id], [group_1Digit], [group_2Digit], [group_3Digit], [chart_code], [pusat_kos], [close_amt], [kod], [year_num], [period_num]) VALUES (-2147483643, N'4245a9c9-4e34-4178-94f9-002a0fe7846b', N'20000', N'29000', N'29400', N'29413', NULL, CAST(0.00 AS Decimal(18, 2)), N'PS', 2014, NULL)
GO
INSERT [dbo].[crpt_NotaPenyataKewangan_BL_Vertical_1] ([idx], [batch_Id], [group_1Digit], [group_2Digit], [group_3Digit], [chart_code], [pusat_kos], [close_amt], [kod], [year_num], [period_num]) VALUES (-2147483642, N'4245a9c9-4e34-4178-94f9-002a0fe7846b', N'30000', N'31000', N'31100', N'31104', NULL, CAST(162808665.65 AS Decimal(18, 2)), N'PS', 2014, NULL)
GO
INSERT [dbo].[crpt_NotaPenyataKewangan_BL_Vertical_1] ([idx], [batch_Id], [group_1Digit], [group_2Digit], [group_3Digit], [chart_code], [pusat_kos], [close_amt], [kod], [year_num], [period_num]) VALUES (-2147483641, N'4245a9c9-4e34-4178-94f9-002a0fe7846b', N'30000', N'31000', N'31100', N'31105', NULL, CAST(84782015.49 AS Decimal(18, 2)), N'PS', 2014, NULL)
GO
SET IDENTITY_INSERT [dbo].[crpt_NotaPenyataKewangan_BL_Vertical_1] OFF
GO
/****** Object: Index [PK_crpt_NotaPenyataKewangan_BL_Vertical_1] Script Date: 7/11/2019 10:41:39 PM ******/
ALTER TABLE [dbo].[crpt_NotaPenyataKewangan_BL_Vertical_1] ADD CONSTRAINT [PK_crpt_NotaPenyataKewangan_BL_Vertical_1] PRIMARY KEY NONCLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Declare @batch_Id uniqueidentifier
Set @batch_Id = '4245a9c9-4e34-4178-94f9-002a0fe7846b'
Declare @year1 int;
Declare @year2 int;
Set @year1 = 2014; /*current year*/
Set @year2 = 2013; /*last year*/
Declare @get_customeVal_1 decimal(18,2);
Declare @get_customeVal_2 decimal(18,2);
Declare @get_customeVal_3 decimal(18,2);
Declare @get_customeVal_4 decimal(18,2);
Declare @get_customeVal_5 decimal(18,2);
Set @get_customeVal_1 = 0.00;
Set @get_customeVal_2 = 0.00;
Set @get_customeVal_3 = 0.00;
Set @get_customeVal_4 = 0.00;
Set @get_customeVal_5 = 0.00;
Select @get_customeVal_1 = isnull(sum(close_amt),0.00) from crpt_NotaPenyataKewangan_BL_Vertical_1 where batch_Id=@batch_Id and year_num=@year1 and chart_code like '3%';
Select @get_customeVal_2 = isnull(sum(close_amt),0.00) from crpt_NotaPenyataKewangan_BL_Vertical_1 where batch_Id=@batch_Id and year_num=@year1 and chart_code = '36101';
--final val current year
Select @get_customeVal_3 = (@get_customeVal_1 - @get_customeVal_2)
Select @get_customeVal_1 = isnull(sum(close_amt),0.00) from crpt_NotaPenyataKewangan_BL_Vertical_1 where batch_Id=@batch_Id and year_num=@year2 and chart_code like '3%';
Select @get_customeVal_2 = isnull(sum(close_amt),0.00) from crpt_NotaPenyataKewangan_BL_Vertical_1 where batch_Id=@batch_Id and year_num=@year2 and chart_code = '36101';
--final val last year
Select @get_customeVal_4 = (@get_customeVal_1 - @get_customeVal_2)
Select get_customeVal_3, get_customeVal_4
Please help me to execute in Single Query
Here's a simple method to do that:
SELECT year1a.get_customeVal_1-year1b.get_customeVal_2 AS get_customeVal_3,
year2a.get_customeVal_1-year2b.get_customeVal_2 AS get_customeVal_4
FROM (SELECT 1 X) X
CROSS APPLY(SELECT ISNULL(SUM(close_amt), 0.00) get_customeVal_1
FROM crpt_NotaPenyataKewangan_BL_Vertical_1
WHERE batch_Id = @batch_Id
AND year_num = @year1
AND chart_code LIKE '3%') year1a
CROSS APPLY(SELECT ISNULL(SUM(close_amt), 0.00) get_customeVal_2
FROM crpt_NotaPenyataKewangan_BL_Vertical_1
WHERE batch_Id = @batch_Id
AND year_num = @year1
AND chart_code = '36101') year1b
CROSS APPLY(SELECT ISNULL(SUM(close_amt), 0.00) get_customeVal_1
FROM crpt_NotaPenyataKewangan_BL_Vertical_1
WHERE batch_Id = @batch_Id
AND year_num = @year2
AND chart_code LIKE '3%') year2a
CROSS APPLY(SELECT ISNULL(SUM(close_amt), 0.00) get_customeVal_2
FROM crpt_NotaPenyataKewangan_BL_Vertical_1
WHERE batch_Id = @batch_Id
AND year_num = @year2
AND chart_code = '36101') year2b
November 7, 2019 at 3:51 pm
Something like this?
DECLARE @batch_Id uniqueidentifier = '4245a9c9-4e34-4178-94f9-002a0fe7846b';
DECLARE @year1 int = 2014; /*current year*/
DECLARE @year2 int = 2013; /*last year*/
WITH cteBase AS (
SELECT batch_Id
, get_customeVal_1_Y1 = ISNULL( SUM( CASE WHEN year_num = @year1 AND chart_code LIKE '3%' THEN close_amt END ), 0.00 )
, get_customeVal_2_Y1 = ISNULL( SUM( CASE WHEN year_num = @year1 AND chart_code = '36101' THEN close_amt END ), 0.00 )
, get_customeVal_1_Y2 = ISNULL( SUM( CASE WHEN year_num = @year2 AND chart_code LIKE '3%' THEN close_amt END ), 0.00 )
, get_customeVal_2_Y2 = ISNULL( SUM( CASE WHEN year_num = @year2 AND chart_code = '36101' THEN close_amt END ), 0.00 )
FROM dbo.crpt_NotaPenyataKewangan_BL_Vertical_1
WHERE batch_Id = @batch_Id
AND year_num IN ( @year1, @year2 )
AND chart_code LIKE '3%'
GROUP BY batch_Id
)
SELECT batch_Id
, get_customeVal_3 = get_customeVal_1_Y1 - get_customeVal_2_Y1
, get_customeVal_4 = get_customeVal_1_Y2 - get_customeVal_2_Y2
FROM cteBase AS cte;
November 7, 2019 at 4:16 pm
I like it! And you can simplify it further:
SELECT batch_Id
, get_customeVal_3 = ISNULL( SUM( CASE WHEN year_num = @year1 AND chart_code <> '36101' THEN close_amt END ), 0.00 )
, get_customeVal_4 = ISNULL( SUM( CASE WHEN year_num = @year2 AND chart_code <> '36101' THEN close_amt END ), 0.00 )
FROM dbo.crpt_NotaPenyataKewangan_BL_Vertical_1
WHERE batch_Id = @batch_Id
AND year_num IN ( @year1, @year2 )
AND chart_code LIKE '3%'
GROUP BY batch_Id
November 7, 2019 at 4:26 pm
SELECT
get_customeVal_3 = ISNULL(SUM(CASE WHEN year_num = @year1 AND chart_code = '3' THEN close_amt ELSE 0 END),0)
- ISNULL(SUM(CASE WHEN year_num = @year1 AND chart_code = '36101' THEN close_amt ELSE 0 END),0),
get_customeVal_4 = ISNULL(SUM(CASE WHEN year_num = @year2 AND chart_code = '3' THEN close_amt ELSE 0 END))
- ISNULL(SUM(CASE WHEN year_num = @year2 AND chart_code = '36101' THEN close_amt ELSE 0 END),0)
FROM crpt_NotaPenyataKewangan_BL_Vertical_1
WHERE batch_Id = @batch_Id
AND year_num IN (@year1, @year2)
AND (chart_code = '36101' OR chart_code like '3%')
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 7, 2019 at 9:06 pm
Hye all,
My below T-SQL is that accurate calculation?
SELECT
get_customeVal_3 = ISNULL( SUM( CASE WHEN year_num = 2015 AND chart_code <> '36101' AND chart_code <> '39102' AND chart_code <> '84101' THEN close_amt END ), 0.00 ),
get_customeVal_4 = ISNULL( SUM( CASE WHEN year_num = 2014 AND chart_code <> '36101' AND chart_code <> '39102' AND chart_code <> '84101' THEN close_amt END ), 0.00 )
FROM dbo.crpt_NotaPenyataKewangan_BL_Vertical_1
WHERE batch_Id = 'FE143BFB-E8C2-4703-BFB4-0DA43A3E3E6E'
AND year_num IN ( 2015, 2014 )
AND ( chart_code LIKE '3%' OR chart_code like '8%' )
GROUP BY batch_Id
November 8, 2019 at 3:38 am
Hye all,
My below T-SQL is that accurate calculation?
SELECT
get_customeVal_3 = ISNULL( SUM( CASE WHEN year_num = 2015 AND chart_code <> '36101' AND chart_code <> '39102' AND chart_code <> '84101' THEN close_amt END ), 0.00 ),
get_customeVal_4 = ISNULL( SUM( CASE WHEN year_num = 2014 AND chart_code <> '36101' AND chart_code <> '39102' AND chart_code <> '84101' THEN close_amt END ), 0.00 )
FROM dbo.crpt_NotaPenyataKewangan_BL_Vertical_1
WHERE batch_Id = 'FE143BFB-E8C2-4703-BFB4-0DA43A3E3E6E'
AND year_num IN ( 2015, 2014 )
AND ( chart_code LIKE '3%' OR chart_code like '8%' )
GROUP BY batch_Id
Only you can validate the accuracy of your query. Run your old code and your new code against your data, and compare the results.
That said, I would probably write your query as follows
SELECT
get_customeVal_3 = ISNULL( SUM( CASE WHEN year_num = 2015 AND chart_code NOT IN ('36101', '39102', '84101') THEN close_amt END ), 0.00 ),
get_customeVal_4 = ISNULL( SUM( CASE WHEN year_num = 2014 AND chart_code NOT IN ('36101', '39102', '84101') THEN close_amt END ), 0.00 )
FROM dbo.crpt_NotaPenyataKewangan_BL_Vertical_1
WHERE batch_Id = 'FE143BFB-E8C2-4703-BFB4-0DA43A3E3E6E'
AND year_num IN ( 2015, 2014 )
AND ( chart_code LIKE '3%' OR chart_code like '8%' )
GROUP BY batch_Id
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply