Sub query in View - 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

SQL Server 2005 Forums

SQL Server Administration (2005)

Sub query in View

Author

Topic

Durbslaw
Starting Member

43 Posts

Posted - 2009-05-25 : 01:50:15

Hi Guys and Gals,

Thanks for the fantastic forum, I would appreciate any help on the following view creation, the query runs well but will not create a VIEW of the query...

create View Test as
SELECT datepart(mm, date) as Month_Part, sum(sales)As Month_Total
FROM sales
group by datepart(mm,date)
( SELECT sum(sales)As Year_Month
FROM sales
where datepart(yy,date)>2008 )

Thanks and regards,

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-25 : 02:06:41

What are you trying to do?

create View Test as
SELECT datepart(mm, date) as Month_Part, sum(sales)As Month_Total
FROM sales
group by datepart(mm,date)

what is the rest for?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-25 : 02:09:27

you might want to include the year in also . .


select	datepart(year, [date])	as Year_Part,
	datepart(month, [date]) as Month_Part,
	sum(sales)		as Monthly_Total
from	sales
group by datepart(year, [date]), datepart(month, [date])

KH
[spoiler]Time is always against us[/spoiler]

Durbslaw
Starting Member

43 Posts

Posted - 2009-05-25 : 02:34:54

quote:
Originally posted by nr

What are you trying to do?

create View Test as
SELECT datepart(mm, date) as Month_Part, sum(sales)As Month_Total
FROM sales
group by datepart(mm,date)

what is the rest for?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Thanks for your time, the rest of the query is supposed to return a single sum for the year

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-25 : 02:41:59

A view has to contain a single resultset
maybe
SELECT datepart(mm, date) as Month_Part, sum(sales)As Month_Total,
YearTot = (SELECT sum(sales)As Year_Month
FROM sales
where datepart(yy,date)>2008 )
FROM sales
group by datepart(mm,date)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-25 : 02:42:25

[code]select m.Year_Part, m.Month_Part, m.Monthly_Total, y.Yearly_Total
from
(
select datepart(year, [date]) as Year_Part,
datepart(month, [date]) as Month_Part,
sum(sales) as Monthly_Total
from sales
group by datepart(year, [date]), datepart(month, [date])
) m
inner join
(
select datepart(year, [date]) as Year_Part,
sum(sales) as Yearly_Total
from sales
group by datepart(year, [date])
) y on m.Year_Part = y.Year_Part[/code]

KH
[spoiler]Time is always against us[/spoiler]

Durbslaw
Starting Member

43 Posts

Posted - 2009-05-25 : 02:55:18

quote:
Originally posted by khtan

you might want to include the year in also . .

select datepart(year, [date]) as Year_Part,
datepart(month, [date]) as Month_Part,
sum(sales) as Monthly_Total
from sales
group by datepart(year, [date]), datepart(month, [date])

[font=Comic Sans MS]KH
[spoiler]Time is always against us[/spoiler][/font=Comic
Sans MS]

Thanks Khtan, the purpose of the second select and sum is to provide a yearly total.
Grouping by year does not resolve the issuse...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-25 : 02:58:13

quote:
Grouping by year does not resolve the issuse...

Grouping by year is to ensure that your monthly figure ( sum(sales) ) only contain one year of data. Unless it is your intention to sum all January sales, all Feb sales . . irregardless of year.

please refer to last post on 05/25/2009 : 02:42:25 on getting the yearly data

KH
[spoiler]Time is always against us[/spoiler]

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-25 : 03:02:27

Did you look at my last post?
It will give you your value in each row of the resultset.

Does this need to be a view?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Durbslaw
Starting Member

43 Posts

Posted - 2009-05-25 : 03:39:12

quote:

Originally posted by khtan

select	m.Year_Part, m.Month_Part, m.Monthly_Total, y.Yearly_Total
from
(
	select	datepart(year, [date])	as Year_Part,
		datepart(month, [date]) as Month_Part,
		sum(sales)		as Monthly_Total
	from	sales
	group by datepart(year, [date]), datepart(month, [date])
) m
inner join
(
	select	datepart(year, [date])	as Year_Part,
		sum(sales)		as Yearly_Total
	from	sales
	group by datepart(year, [date])
) y	on	m.Year_Part	= y.Year_Part

KH
[spoiler]Time is always against us[/spoiler]

THANK YOU, this worked like a charm....

Durbslaw
Starting Member

43 Posts

Posted - 2009-05-25 : 03:51:56

quote:
Originally posted by nr

A view has to contain a single resultset
maybe
SELECT datepart(mm, date) as Month_Part, sum(sales)As Month_Total,
YearTot = (SELECT sum(sales)As Year_Month
FROM sales
where datepart(yy,date)>2008 )
FROM sales
group by datepart(mm,date)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

The solution was in KH's last post, thanks again...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-25 : 04:45:06

you can also try this. . since you are using SQL Server 2005. See which gives you better performance


SELECT	m.Year_Part, m.Month_Part, m.Monthly_Total,
	Year_Total = SUM(m.Monthly_Total) OVER (PARTITION BY m.Year_Part)
FROM
(
	SELECT	DATEPART(YEAR, [date])	AS Year_Part,
		DATEPART(MONTH, [date]) AS Month_Part,
		SUM(sales)		AS Monthly_Total
	FROM	sales
	GROUP BY DATEPART(YEAR, [date]), DATEPART(MONTH, [date])
) m

KH
[spoiler]Time is always against us[/spoiler]

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-04 : 03:38:00

quote:

Originally posted by khtan

select	m.Year_Part, m.Month_Part, m.Monthly_Total, y.Yearly_Total
from
(
	select	datepart(year, [date])	as Year_Part,
		datepart(month, [date]) as Month_Part,
		sum(sales)		as Monthly_Total
	from	sales
	group by datepart(year, [date]), datepart(month, [date])
) m
inner join
(
	select	datepart(year, [date])	as Year_Part,
		sum(sales)		as Yearly_Total
	from	sales
	group by datepart(year, [date])
) y	on	m.Year_Part	= y.Year_Part

KH
[spoiler]Time is always against us[/spoiler]

Hi, still have not completed this project 100%, on the TestDB, the above query works well, however in production nulls are returned in the current format, however altering the query as below
select m.Year_Part, m.Month_Part, m.Monthly_Total, y.Yearly_Total
from
(
select datepart(year, [date]) as Year_Part,
datepart(month, [date]) as Month_Part,
sum(sales) as Monthly_Total, Sales
from sales
group by datepart(year, [date]), datepart(month, [date])
) m
inner join
(
select datepart(year, [date]) as Year_Part,
sum(sales) as Yearly_Total, sales
from sales
group by datepart(year, [date])
) y on m.Year_Part = y.Year_Part[/code]

causes values to be returned, further to this records are duplicated to the extent that the system throw up a rescoure error..
How can I resolve?

Thanks again....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 03:43:40

why are you adding the Sales column in there ? Your query will gives syntax error

KH
[spoiler]Time is always against us[/spoiler]

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-04 : 04:39:38

Without the Salescolumn, the query will return zero's, with the Sales column , the values returned are totals, strangely, this does not seem to be a problem in the test database....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 06:07:21

can you post the exact query that you used ? The query you posted in 06/04/2009 : 03:38:00 will not be able to run at all.
You will get some error like

"Column sales.sales is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

KH
[spoiler]Time is always against us[/spoiler]

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-05 : 06:30:28

select m.Year_Part, m.Month_Part, m.Monthly_Total, y.Yearly_Total
from
(
select datepart(year, transdate) as Year_Part,
datepart(month, transdate) as Month_Part,
sum(amountcur) as Monthly_Total, amountcur
from temp
where datepart(yy,transdate) >=2008 and datepart(dd, transdate)between 1 and 31
group by datepart(month, transdate),transdate, amountcur
) m
inner join
(
select datepart(year, transdate) as Year_Part,
sum(amountcur) as Yearly_Total
from temp
where datepart (mm,transdate) between 1 and 12
group by datepart(year, transdate),transdate, amountcur
) y on m.Year_Part = y.Year_Part

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-18 : 04:16:23

quote:

Originally posted by khtan

select	m.Year_Part, m.Month_Part, m.Monthly_Total, y.Yearly_Total
from
(
	select	datepart(year, [date])	as Year_Part,
		datepart(month, [date]) as Month_Part,
		sum(sales)		as Monthly_Total
	from	sales
	group by datepart(year, [date]), datepart(month, [date])
) m
inner join
(
	select	datepart(year, [date])	as Year_Part,
		sum(sales)		as Yearly_Total
	from	sales
	group by datepart(year, [date])
) y	on	m.Year_Part	= y.Year_Part

KH
[spoiler]Time is always against us[/spoiler]

Two questions Khtan,the first is to include further fields, from this and other tables, where would those fields be placed in the above query, and secondly how to convert the date format to dd/mm/yyyy...

Thanks a ton.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-18 : 12:09:45

quote:
Two questions Khtan,the first is to include further fields, from this and other tables, where would those fields be placed in the above query, and secondly how to convert the date format to dd/mm/yyyy...

1. You can include other field. The question is what are you going to do with this field ? Are you performing any aggregated function on this field ? Or it will be part of the group by ?

2. Formatting should be done at the front end, not in the database.

KH
[spoiler]Time is always against us[/spoiler]

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-19 : 02:17:25

here is the full query, the first part is pretty striaght forward, the first sum value is for Monthly total for a Financial year, the second sum is a Year_to_date, and the last sum is an aggregade total for the financial year

select ledgertable.accountnum as VoteNo, ledgertable.AccountPLtype as VoteType, ledgertable.Accountname as VoteDescription, sum(amountcur)as CurrentExp, DATEPART(month, ledgertrans.transdate) AS MonthPart, FinYear = (case when
datepart(mm,transdate) between 7 and 12 then
datename(yyyy, transdate) + '/' + datename (yyyy,dateadd(yyyy,1, transdate))
else
datename (yy,dateadd(yyyy,-1, transdate)) + '/' + datename (yyyy, transdate)
end)
from dbo.ledgertable, dbo.ledgertrans, dbo.ledgerbudget
where ledgertable.accountnum = ledgertrans.accountnum AND ledgertable.accountnum between 100000 AND 500000 AND ledgertable.accountnum =ledgerbudget.accountnum AND ledgertrans.transdate>='2008-07-01'
and ledgertable.AccountPLtype=0
group by DatePart(mm, transdate),ledgertable.accountnum, ledgertrans.transdate, ledgertable.AccountPLtype, ledgertable.Accountname, ledgerbudget.amount, DATEPART(month, ledgertrans.transdate)
( select sum(amountcur) As PrevExpenditure
from
ledgertrans, ledgerbudget
where ledgertrans.accountnum=ledgerbudget.accountnum AND ledgertrans.transdate=ledgerbudget.startdate AND ledgertrans.transdate>='2008-07-01' AND datepart(yy,transdate)between datename (yy,dateadd(yy,-05,transdate)) AND datename (yy,dateadd(yy,0.5,transdate))
group by transdate )
(select sum(amount) As CurrentBudget
from
ledgerbudget, ledgertrans
where ledgertrans.accountnum=ledgerbudget.accountnum AND ledgertrans.transdate=ledgerbudget.startdate AND ledgertrans.transdate>='2008-07-01' AND datepart(yy,startdate)between datename (yy,dateadd(yy,-05,startdate)) AND datename (yy,dateadd(yy,0.5,startdate))
group by startdate)

P.S. the numeric field in the amountcur, do not sum unless you convert the field into integer and then back again, if you can explain why, this would help greatly...

thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources