Need help to calculate in Single Query

  • 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
  • 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;
  • 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
  • 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

  • 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
  • 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

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

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