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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy