Calculate sum in subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculate sum in subquery

Author

Topic

tech_1
Posting Yak Master

129 Posts

Posted - 2012-04-17 : 16:22:37

Data Setup

Step 1: Run the following:

quote:

CREATE TABLE [dbo].[Quarterly](
[QuarterDate] [datetime] NOT NULL,
[SomeText] [nvarchar](50) NULL,
CONSTRAINT [PK_Quarterly] PRIMARY KEY CLUSTERED
(
[QuarterDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[TmpDegreeDays](
[Date] [datetime] NOT NULL,
[Value] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_TmpDegreeDays] PRIMARY KEY CLUSTERED
(
[Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Step 2: Enter the following in Quarterly table:

quote:

INSERT [dbo].[Quarterly] ([QuarterDate], [SomeText]) VALUES (CAST(0x00009CF100000000 AS DateTime), N'Blah')
INSERT [dbo].[Quarterly] ([QuarterDate], [SomeText]) VALUES (CAST(0x00009D4B00000000 AS DateTime), N'Fools')
INSERT [dbo].[Quarterly] ([QuarterDate], [SomeText]) VALUES (CAST(0x00009DA600000000 AS DateTime), N'Later')
INSERT [dbo].[Quarterly] ([QuarterDate], [SomeText]) VALUES (CAST(0x00009E0400000000 AS DateTime), N'Something')
INSERT [dbo].[Quarterly] ([QuarterDate], [SomeText]) VALUES (CAST(0x00009E5E00000000 AS DateTime), N'New year')
INSERT [dbo].[Quarterly] ([QuarterDate], [SomeText]) VALUES (CAST(0x00009EC300000000 AS DateTime), N'In april')

Step 3: Insert date ranges from '2010-01-01' to (including) '2012-03-10' into table TmpDegreeDays

Problem:

This is my query:

quote:

DECLARE @startDate datetime, @endDate datetime
SET @startDate = '2010-01-01'
SET @endDate = '2010-12-31'

SELECT q.QuarterDate, q.SomeText, CustomSum =
(SELECT SUM(CAST([Value] AS float))
FROM TmpDegreeDays
WHERE [date] >= q.QuarterDate AND q.QuarterDate < Some query here to get next row QuarterDate)

FROM Quarterly q

WHERE q.QuarterDate BETWEEN @startDate AND @endDate

I want to be able to calculate the SUM of all the [value] (column name) data in TmpDegreeDays from the current QuarterDate up until the the next row of data in the Quarterly result set.

Example:

QuarterDate CustomSum
2010-01-01 Sum of [Value] between 2010-01-01 till 2010-03-31
2010-04-01 Sum of [Value] between 2010-04-01 till 2010-06-30
2010-07-01 Sum of [Value] between 2010-07-01 till 2010-09-31
2010-10-03 Sum of [Value] between 2010-10-03 till 2010-10-03

Does this make sense?

grmnsplx
Starting Member

8 Posts

Posted - 2012-04-17 : 16:33:05

Just join the two tables:

SELECT q.QuarterDate, q.SomeText, sum(t.value) /* do the cast ot convert if you must */
from Quarterly q, TmpDegreeDays t
where q.QuarterDate BETWEEN @startDate AND @endDate
and t.date >= q.QuarterDate
/*add whatever else you need to in terms of date restrictions */
group by q.QuarterDate, q.SomeText

tech_1
Posting Yak Master

129 Posts

Posted - 2012-04-17 : 17:00:46

Thanks. ill see what I can do but I need to know what the quary should also be for your /*add whatever else you need to*/ comment where I need to get the next record QuarterDate value and place it there.

tech_1
Posting Yak Master

129 Posts

Posted - 2012-04-17 : 18:06:04

This ALMOST works:

quote:

;WITH QuaterlyDates AS
(
SELECT QuarterDate, SomeText,
QuarterEndDate = DATEADD(MILLISECOND, -3, DATEADD(MONTH, 3, QuarterDate))
FROM Quarterly
WHERE QuarterDate >= @startDate AND QuarterDate < @endDate
)

SELECT qd.QuarterDate, qd.QuarterEndDate, qd.SomeText, DegreeSum =
(SELECT SUM(CAST(Value AS FLOAT)) FROM TmpDegreeDays WHERE
[Date] >= qd.QuarterDate AND [Date] < qd.QuarterEndDate)
FROM QuaterlyDates qd

I just need to know how to make the QuarterlyEndDate NOT use the DATEADD function but instead use the NEXT DATA ROW QuarterDate as the End date instead

tech_1
Posting Yak Master

129 Posts

Posted - 2012-04-17 : 18:56:29

This *seems* to work but still require clarification if it is the right way:

quote:

;WITH QuaterlyDates AS
(

SELECT QuarterDate, SomeText
FROM Quarterly
WHERE QuarterDate >= @startDate AND QuarterDate < @endDate
)

SELECT qd.QuarterDate, qd.SomeText, DegreeSum =
(SELECT SUM(CAST(Value AS FLOAT)) FROM TmpDegreeDays WHERE
[Date] >= qd.QuarterDate AND [Date] < (SELECT TOP 1 q.QuarterDate FROM Quarterly q WHERE q.QuarterDate > qd.QuarterDate))
, LastDate = (SELECT TOP 1 q.QuarterDate FROM Quarterly q WHERE q.QuarterDate > qd.QuarterDate)
FROM QuaterlyDates qd

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources