Need help to calculate in Single Query

Question

Post reply

Need help to calculate in Single Query

Adelia

SSC-Addicted

Points: 422

November 7, 2019 at 3:08 pm

Go to Answer

#3695942

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

Jonathan AC Roberts

SSCoach

Points: 18263

November 7, 2019 at 3:46 pm

Answer

#3695959

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24278 More actions · Answer 1

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;

How to post data/code on a forum to get the best help.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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%')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Adelia SSC-Addicted Points: 422 More actions · Answer 4

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

DesNorton SSC-Insane Points: 24278 More actions · Answer 5

Adelia wrote:

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

How to post data/code on a forum to get the best help.