Declare statement in SQL 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)

Declare statement in SQL view

Author

Topic

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-29 : 02:55:31

Hello again,

Could somenone please help me convert this query into an SQL View

declare @fin_date_start datetime,
@fin_date_end datetime

select @fin_date_start = dateadd(month, 6, dateadd(year, datediff(year, 0, getdate()), 0)) -- July this year
select @fin_date_end = dateadd(month, 12, @fin_date_start) -- July next year

select @fin_date_start, @fin_date_end

select YTD_Total = sum(amountcur)
from ledgertrans
where transdate >= @fin_date_start
and transdate < @fin_date_end

Query provided coutesy of Khtan.

Thanks lotsa recognition,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 03:09:11

Use the formulas to set the variables and put them directly in the WHERE clause.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 03:11:05

[code]SELECT SUM(AmountCur) AS YTD_Total
FROM LedgerTrans
WHERE TransDate >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), '19000701')
AND TransDate < DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), '19000701')[/code]

E 12°55'05.63"
N 56°04'39.26"

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-29 : 03:31:16

Hi Peso and thanks for the repsonse,

The above query adds all values (amountcur)(going 1 year forward)when the date falls after (July YY+1), and 1 year before if the date is in the first half year (july YY-1), where in your query could I check to see this comparasion in your query...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 03:42:43

I have no idea what you mean. Your sentence is not comprehensible to me.

E 12°55'05.63"
N 56°04'39.26"

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-29 : 03:58:41

Ok let me try to paint a clearer picture, my requirement is to create a view to display a summed value (amountcur) when the date(transdate) falls after June 30 i.e 01/07/yyyy to 30/06/yyyy+1 else when the date is before July sum (amountcur) where transdate between 01/07/yyyy to 30/06/yyyy-1 ( in other words the financial year goes from July to June).
Other fields in the View should be monthpart (transdate), FinYear (trandsate)(yy/yy+1) and VoteNo

Hope this helps....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 04:18:07

Huh? VoteNo? Which column is that?
Wasn't this for AmountCur column?

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-29 : 04:18:44

it's continue from here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=128374

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-29 : 04:19:14

post your table structure, some sample data and the result that you want

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 04:21:19

[code]SELECT DATEPART(MONTH, TransDate) AS theMonth,
DATEPART(YEAR, DATEADD(MONTH, -6, TransDate)) AS theYear
SUM(AmountCur) AS YTD_Total
FROM LedgerTrans
GROUP BY DATEPART(MONTH, TransDate),
DATEPART(YEAR, DATEADD(MONTH, -6, TransDate))[/code]

E 12°55'05.63"
N 56°04'39.26"

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-29 : 04:39:17

Voteno, is not the field I am having difficulty with, it was just total , I am hoping to edit the query/view to add fields as I require, I guess this is why you are seeing this filed for the first time.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 04:46:03

The only thin I care for is you to test the suggestion made.
Have you had a chance to test suggestion made 06/29/2009 : 04:21:19 yet?

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-29 : 04:56:05

[code]
select FinYear = datepart(year, FinDate),
Amoutn = sum(AmountCur)
from
(
select FinDate = case when datepart(month, TransDate) >= 7
then dateadd(month, 7 - datepart(month, TransDate), TransDate)
else dateadd(month, -datepart(month, TransDate) - 5, TransDate)
end,
AmountCur
from LedgerTrans
) t
group by datepart(year, FinDate)
[/code]

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

Durbslaw
Starting Member

43 Posts

Posted - 2009-07-01 : 09:17:24

Hello, I hope this will be my last post/question....

How can I alter this query to include all the fields from this one...

SELECT ledgertrans.AccountNum as VoteNo, datepart(month, transdate) AS MonthPart, sum(cast(amountcur as numeric(28,12))) AS PrevExpenditure
FROM ledgertrans, ledgertable
where datepart(yyyy,transdate)>=2008
GROUP BY year(ledgertrans.transDate), ledgertrans.accountnum, datepart(month, transdate)
ORDER BY ledgertrans.Accountnum, year(ledgertrans.transDate);

P.S, one off the reasons why the database was returning 0's was the amountcur field desipte being numeric(28,12) has to be re-cast as numeric(28,12).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-01 : 09:20:46

quote:
Originally posted by Durbslaw

SELECT ledgertrans.AccountNum as VoteNo, datepart(month, transdate) AS MonthPart, sum(cast(amountcur as numeric(28,12))) AS PrevExpenditure
FROM ledgertrans, ledgertable
where datepart(yyyy,transdate)>=2008
GROUP BY year(ledgertrans.transDate), ledgertrans.accountnum, datepart(month, transdate)
ORDER BY ledgertrans.Accountnum, year(ledgertrans.transDate)

There is a really large performance problem in your query!
There is no relation between ledgertrans and ledgertable tables.

SELECT		ledgertrans.AccountNum AS VoteNo,
		year(ledgertrans.transDate) AS YearPart,
		datepart(month, transdate) AS MonthPart,
		sum(cast(amountcur as numeric(28, 12))) AS PrevExpenditure
FROM		ledgertrans
inner join	ledgertable
where		transdate >= '20080101'
GROUP BY	year(ledgertrans.transDate),
		ledgertrans.accountnum,
		datepart(month, transdate) 
ORDER BY	ledgertrans.Accountnum,
		year(ledgertrans.transDate)

This makes you query do a CARTESIAN PRODUCT, also known a CROSS JOIN.

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 10:02:43

[code]SELECT ledgertrans.AccountNum AS VoteNo,
datepart(year, ledgertrans.transDate) AS YearPart,
datepart(month, ledgertrans.transdate) AS MonthPart,
sum(cast(amountcur as numeric(28, 12))) AS PrevExpenditure
FROM ledgertrans
~~inner join ledgertable~~
where transdate >= '20080101'
GROUP BY datepart(year, ledgertrans.transDate),
ledgertrans.accountnum,
datepart(month, ledgertrans.transdate)
ORDER BY ledgertrans.Accountnum,
YearPart[/code]

the ledgertable is not required at all as it is not refereced
you might want to consistently use datepart rather than mixing year() and datepart().

note : Peter's query missed out the ON clause after INNER JOIN

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-01 : 12:02:59

I didn't alter anything, just indented the code.
That's when I saw LedgerTable was used but never referenced, thus making a CARTESIAN product or a CROSS JOIN.

N 56°04'39.26"
E 12°55'05.63"

Durbslaw
Starting Member

43 Posts

Posted - 2009-07-02 : 05:22:34

Hi,

I decieded to abandon this approach and try..

declare @fin_date datetime

select @fin_date = case when datepart(month, transdate()) < 7 then dateadd(month, datediff(month, 0, transdate()) - 12 + 7 - datepart(month, transdate()), 0) else dateadd(month, 6, dateadd(year, datediff(year, 0, transdate()), 0))
end
select ledgertrans.AccountNum as VoteNo, datepart(month,transdate)as MonthPart, @Fin_date ,sum(cast (amount as numeric (28,18)))As PrevEXP
from ledgertrans
where transdate >= @fin_date
group by ledgertrans.accountnum, transdate
order by datepart(month, transdate), ledgertrans.accountnum

This trouble is I cannot get the declare statement to reference the ledgertrans.transdate column

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 05:44:16

why there is a declare statement now. I thought you wanted to create a view ?

The query i posted on 07/01/2009 : 10:02:43 does not work for you ?

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

Durbslaw
Starting Member

43 Posts

Posted - 2009-07-02 : 07:19:10

Hi Kh,

The query is supposed to display the financial year (@Fin_Year), and a sum value for the dates in the @Fin_year. (between July this and June next year, the logic is if the month (datepart) falls into the first 6 months then sum the values in AmountCur from july to june previous year, else to june next year, here is a sample of the result/output

VoteNo|MonthPart|@Fin_Year|YTD_PrevExp
090220|3 |2003/2004|1886745.2345
034562|5 |2004/2005|769459.080798
793750|7 |2005/2006|109832458943.988

VoteNo varchar(10)
MonthPart transdate datetime
Fin_Year transdate datetime
PrevExp Sum(amountcur) where Transdate=Fin_Year

I have tried to complete this without using the declare statement, but I can't seem to find a way, so I decieded to create a stored procedure.

If you aware of a more efficient way please let me know....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 07:39:24

can you post the structure of your table, sample data and the required result ?

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources