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

date

Author

Topic

Rangapure.S.Swapnil
Starting Member

3 Posts

Posted - 2009-12-29 : 10:50:45

hi,

i am working on reporting function.I want to have two column as "value field" one colummn will have the value as of date which user enters and the second value column should have the value for corresponfing previous quarter,

Means if the user want the report for 21/12/2009 then one column should have the value for that date and the second column should have the value as of 30/09/2009.
is he enters 20/09/2009 then corresponding quarter value for 6/30/2009..

how can i get the values.and i have to get the value in a single query..

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-29 : 10:59:36

SELECT DATEADD(q,datediff(q,0,'12/21/2009'),-1)

Jim

Everyday I learn something that somebody else already knew

Rangapure.S.Swapnil
Starting Member

3 Posts

Posted - 2009-12-29 : 11:13:02

Thank you ..sir

but i am finding difficult to retrieve the value ...i have following query..how can i retrieve value on the bases of @dtDate1..i think i have to use temp table agian to save the output of this query then retrieve the value of @dtDate1 by applying join...
is it right??
Jimf

declare @iHHID float
declare @dtDate date
set @dtDate ='20091221'
set @iHHID = 194292
declare @dtDate1 date
set @dtDate1 =DATEADD(q,datediff(q,0,@dtDate),-1)

select pos.iPPSAccountId ,abc.vchAccountNumber ,abc.dtPositionStartDate ,abc.vchAccountName ,abc.RealizedGain_Loss
,sum(vu.mMarketValue) 'Value As of Date'
,sum(pos.mTotalCost)-sum(vu.mMarketValue) as 'GainLoss'

from #abc as abc with(NOLOCK)
INNER JOIN PPS..PPSPosition pos with(NOLOCK) on pos.iPPSPositionId =abc.iPPSPositionId
INNER JOIN pps..PPSPositionMV vu with (nolock) on vu.iPPSPositionId=abc.iPPSPositionId

where vu.iPPSPositionId in(select #abc.iPPSPositionId from #abc )
--and LAT.vchLedgerAccountTypeId in('LTRGLP','STRGLP')
and vu.dtDate=@dtDate
and vu.dtDate<=GETDATE()

group by abc.vchAccountNumber ,abc.dtPositionStartDate ,abc.vchAccountName ,abc.RealizedGain_Loss ,pos.iPPSAccountId

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-29 : 11:21:31

I'm not quite sure I'm following you. You can use @dtDate1 anywhere in your query, just like @dtDate. If you want to retriever the actual vale of @dtDate1, you can just add it to your select query statement

Jim

Select
pos.iPPSAccountId ,abc.vchAccountNumber ,abc.dtPositionStartDate ,abc.vchAccountName ,abc.RealizedGain_Loss
,sum(vu.mMarketValue) 'Value As of Date'
,sum(pos.mTotalCost)-sum(vu.mMarketValue) as 'GainLoss'
,@dtDate as EnteredDate
,@dtDate as PreviousQuarter

Everyday I learn something that somebody else already knew

Rangapure.S.Swapnil
Starting Member

3 Posts

Posted - 2009-12-29 : 11:49:02

i dont what to retrieve the value of @dtDate1.
i want to retrieve the values from the table for that date and the date which user entered.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-29 : 11:54:43

Then just include the @dtDate1 just like you have used @dtDate. Like this...

select pos.iPPSAccountId ,abc.vchAccountNumber ,abc.dtPositionStartDate ,abc.vchAccountName ,abc.RealizedGain_Loss
,sum(vu.mMarketValue) 'Value As of Date'
,sum(pos.mTotalCost)-sum(vu.mMarketValue) as 'GainLoss' 
from #abc as abc with(NOLOCK)
INNER JOIN PPS..PPSPosition pos with(NOLOCK) on pos.iPPSPositionId =abc.iPPSPositionId 
INNER JOIN pps..PPSPositionMV vu with (nolock) on vu.iPPSPositionId=abc.iPPSPositionId 
where vu.iPPSPositionId in(select #abc.iPPSPositionId from #abc )
and vu.dtDate<@dtDate
and vu.dtDate>=@dtDate1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources