I can query one by one. Need help how to become Single Query

Question

Post reply

I can query one by one. Need help how to become Single Query

Adelia

SSC-Addicted

Points: 422

October 3, 2023 at 8:18 pm

Go to Answer

#4300755

Hi,

This is my table and data,

CREATE TABLE [dbo].[rpt_AR_StatementOfAcct_003](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ref2] [varchar](50) NULL,
[ref1] [varchar](50) NULL,
[trans_type_Descrp] [varchar](100) NULL,
[thisAmt] [decimal](16, 2) NULL,
[trans_date] [datetime] NULL,
[today_date] [datetime] NULL,
[HowManyDays] [int] NULL,
 CONSTRAINT [PK_rpt_AR_StatementOfAcct_003] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Here it is

This is my Query - One by one

select sum(thisAmt) as aging_0_30

from [dbo].[rpt_AR_StatementOfAcct_003]

where HowManyDays >= 0 And HowManyDays <= 30

I got this

sql-002

select sum(thisAmt) as aging_31_60

from [dbo].[rpt_AR_StatementOfAcct_003]

where HowManyDays >= 31 And HowManyDays <= 60

I got this,

sql-003

select sum(thisAmt) as aging_61_90

from [dbo].[rpt_AR_StatementOfAcct_003]

where HowManyDays >= 61 And HowManyDays <= 90

I got this

sql-004

How to make it into Single Query, then I can get output as following

aging_0_30     | aging_31_60       | aging_61_90
-339848.24       -78019.02           235867.57

Please help

This topic was modified 1 year, 1 month ago by Adelia.
This topic was modified 1 year, 1 month ago by Adelia.

ScottPletcher

SSC Guru

Points: 101091
More actions
October 3, 2023 at 8:27 pm
Answer
#4300756
```
SELECT
    SUM(CASE WHEN HowManyDays <= 30 THEN thisAmt ELSE 0 END) AS aging_0_30,
    SUM(CASE WHEN HowManyDays >= 31 AND HowManyDays <= 60 THEN thisAmt ELSE 0 END) AS aging_31_60,
    SUM(CASE WHEN HowManyDays >= 61 AND HowManyDays <= 90 THEN thisAmt ELSE 0 END) AS aging_61_90
FROM dbo.rpt_AR_StatementOfAcct_003
WHERE HowManyDays >= 0 AND HowManyDays <= 90 
```
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

Adelia

SSC-Addicted

Points: 422

October 3, 2023 at 8:34 pm

#4300783

Thank you sir

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

SELECT aging_1_30  = SUM(CASE WHEN st.HowManyDays <= 30 THEN st.thisAmt END)
     , aging_31_60 = SUM(CASE WHEN st.HowManyDays > 30 AND st.HowManyDays <= 60 THEN st.thisAmt END)
     , aging_61_90 = SUM(CASE WHEN st.HowManyDays > 60 THEN st.thisAmt END)
  FROM dbo.rpt_AR_StatementsOfAcct_003      st
 WHERE st.HowManyDays >= 1
   AND st.HowManyDays <= 90;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Michael L John One Orange Chip Points: 27054 More actions · Answer 3

I suspect that this is actual data from your company's databases. There does not appear to be any personal information in this, but in many organizations publishing corporate data in a public forum would be grounds for termination.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/