Please help me on a piece of code

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

Please help me on a piece of code

Author

Topic

lw1990
Yak Posting Veteran

85 Posts

Posted - 2010-06-10 : 16:26:48

Hi,
I’m trying to create a view from a table, which makes a new field according to the date value of the datetime field in the table. If the value of ChargeDate in the table is within one week from today, write the PastweekAmt with the value of ChargeAmt.

The table is ChargeHistory:
ChargeDate | CharegeAmt
4/8/2010 | $100
5/8/2010 | $200
6/8/2010 | $300
6/10/2010 | $321

The view I’m trying to make is:
ChargeDate | ChargeAmt | PastweekAmt
4/8/2010 | $100
5/8/2010 | $200
6/8/2010 | $300 | $300
6/10/2010 | $321 | $321

The script of the view should be:
Select ChargeDate, ChargeAmt,
(What is the code here?) as PastweekAmt
From ChargeHistory

Please help me to figure out the (What is the code here?)

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-10 : 19:44:45

SELECT *,
CASE WHEN DATEDIFF(DAY, ChargeDate, GETDATE()) BETWEEN 0 AND 6 THEN ChargeAmt ELSE NULL END AS PastWeekAmt
FROM ChargeHistory

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

lw1990
Yak Posting Veteran

85 Posts

Posted - 2010-06-10 : 23:07:56

Thanks Peso,
It works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources