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

Variable

Author

Topic

mukhan85
Starting Member

46 Posts

Posted - 2008-07-28 : 10:53:36

Hi, I have the following query, it consists of UNIONs of queries and each query has a subquery and all UNION queries has the same subquery. In order of not doing the same query again and again, is there a way that I could declare a variable, and assign it once by executing the subqeury only once and use that variable in other parts of UNION queries?
THank you.

(SELECT 'Open' AS metric_value,
COUNT(*) AS am_value1,
MONTH(ISNULL(DATEADD(second, Date, '1969-12-31 8:00:00 PM'), DATEADD(second, Date2, '1969-12-31 8:00:00 PM'))) AS am_value2,
YEAR(ISNULL(DATEADD(second, Date, '1969-12-31 8:00:00 PM'), DATEADD(second, Date2, '1969-12-31 8:00:00 PM'))) AS am_value3,
(
SELECT SUM(COUNT(*))
FROM Table
WHERE
YEAR(DATEADD(second, Date, '1969-12-31 8:00:00 PM')) = YEAR(GETDATE()) AND
) as total
FROM Table
WHERE
YEAR(ISNULL(DATEADD(second, Date, '1969-12-31 8:00:00 PM'), DATEADD(second, Date2, '1969-12-31 8:00:00 PM'))) = YEAR(GETDATE()) AND
GROUP BY YEAR(ISNULL(DATEADD(second, Date, '1969-12-31 8:00:00 PM'), DATEADD(second, Date2, '1969-12-31 8:00:00 PM'))),
MONTH(ISNULL(DATEADD(second, Date, '1969-12-31 8:00:00 PM'), DATEADD(second, Date2, '1969-12-31 8:00:00 PM')))
) UNION ALL
(
...

(SELECT SUM(COUNT(*))
FROM Table
WHERE
YEAR(DATEADD(second, Date, '1969-12-31 8:00:00 PM')) = YEAR(GETDATE())
) as total

...
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 11:04:11

you can use a variable to store the value and use it

DECLARE @Sum int

SELECT @Sum = SUM(COUNT(*)) 
FROM Table
WHERE 
YEAR(DATEADD(second, Date, '1969-12-31 8:00:00 PM')) = YEAR(GETDATE())

b/w i'm not sure how the above code worked as i dont think you can have one aggregate function applied over another aggregate function.

mukhan85
Starting Member

46 Posts

Posted - 2008-07-28 : 14:42:21

Hi, thank you a lot. Is there way to find sum for each month and store the result in kind of an array? I found out that I need the sum for each month in a year.
Thank you.

quote:
Originally posted by visakh16

you can use a variable to store the value and use it

DECLARE @Sum int

SELECT @Sum = SUM(COUNT(*))
FROM Table
WHERE
YEAR(DATEADD(second, Date, '1969-12-31 8:00:00 PM')) = YEAR(GETDATE())

b/w i'm not sure how the above code worked as i dont think you can have one aggregate function applied over another aggregate function.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-28 : 15:17:25

http://weblogs.sqlteam.com/jeffs/archive/2007/09/10/group-by-month-sql.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-29 : 03:10:39

quote:
Originally posted by mukhan85

Hi, thank you a lot. Is there way to find sum for each month and store the result in kind of an array? I found out that I need the sum for each month in a year.
Thank you.

quote:
Originally posted by visakh16

you can use a variable to store the value and use it

DECLARE @Sum int

SELECT @Sum = SUM(COUNT(*))
FROM Table
WHERE
YEAR(DATEADD(second, Date, '1969-12-31 8:00:00 PM')) = YEAR(GETDATE())

b/w i'm not sure how the above code worked as i dont think you can have one aggregate function applied over another aggregate function.

just put the sum for each months in a temporary table and use from it later.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources