daily variance - 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

Transact-SQL (2005)

daily variance

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-09-11 : 08:08:30

Good Morning,
I need you good people's help on this ..

I have a table structure like

id | date | amount
--------------------------------------
1 |2008-09-08 00:00:00.000 | 200933.00
2 |2008-09-09 00:00:00.000 | 133.00
3 |2008-09-09 00:00:00.000 | 9234.00
4 |2008-09-10 00:00:00.000 | 2233.00
5 |2008-09-11 00:00:00.000 | 50032.00

I'm trying to get a view like this

id| date | amount(actual)|amount(target)
-----------------------------------------

where
actual = a particular date
target = a day before actual (yesterday).

Thanks

I sign for fame not for shame but all the same, I sign my name.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-11 : 08:37:34

[code]
select id,
date,
actual = sum(case when date = @particular_date then amount else 0 end),
target = sum(case when date = dateadd(day, -1, @particular_date) then amount else 0 end)
from yourtable
group by id, date
[/code]

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

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-09-11 : 08:44:32

Thanks, khtan.. what if i want a running date...

say am querying the table without using store-procedure... how can I achieve this

check out my code...

select
date
,item
,isnull(amount,0) actual
,target = (case when report_date = datediff(day,-1,report_date) then isnull(amount,0) else 0 end)
from table
order by
report_date desc

it returning zeros for all my target..

I sign for fame not for shame but all the same, I sign my name.

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-09-11 : 09:10:56

check out my code...

select
date
,item
,isnull(amount,0) actual
,target = (case when report_date = dateadd(day,-1,report_date) then isnull(amount,0) else 0 end)
from table
order by
report_date desc

sorry typo

I sign for fame not for shame but all the same, I sign my name.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-11 : 09:59:55

is report_date a column in your table ? I don't see that in your sample data you posted in the initial post

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-11 : 10:01:08

quote:
it returning zeros for all my target..

Of-course you get 0 for all as this statement will never be true

 report_date = dateadd(day,-1,report_date)

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

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-11 : 10:14:09

Please provide sample data of what your desired results would be.

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-09-11 : 11:18:54

2008-09-09 00:00:00.000 Naira 510600.00 0.00
2008-09-08 00:00:00.000 Naira 18503871.76 0.00
2008-09-08 00:00:00.000 Currents 0.00 0.00
2008-09-08 00:00:00.000 ALL-IN-ONE 0.00 0.00
2008-09-08 00:00:00.000 Pounds 0.00 0.00
2008-09-08 00:00:00.000 Dollar 0.00 0.00
2008-09-08 00:00:00.000 Euro 0.00 0.00
2008-09-08 00:00:00.000 Savings 0.00 0.00
2008-09-07 00:00:00.000 Naira 8199311.00 0.00
2008-09-06 00:00:00.000 Naira 14316815.00 0.00
2008-09-05 00:00:00.000 Naira 19700286.55 0.00
2008-09-05 00:00:00.000 Euro 0.00 0.00

This is result of the query,

2008-09-09 00:00:00.000 Naira 510600.00 0.00
2008-09-08 00:00:00.000 Naira 18503871.76 0.00

I expected to get

2008-09-09 00:00:00.000 Naira 510600.00 18503871.76
2008-09-08 00:00:00.000 Naira 18503871.76 0.00

Thanks all

I sign for fame not for shame but all the same, I sign my name.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-11 : 11:59:48

Is this what you are looking for?


declare @DateToUse datetime 
set @DateToUse = '9/10/2008'

select
date
,item
,isnull(amount,0) actual
,target = (case when report_date = dateadd(day,-1,convert(char(10),@DatetoUse,102)) then isnull(amount,0) else 0 end)
from table
order by
report_date desc

Success is 10% Intelegence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-09-11 : 13:54:46

Thanks folks,

see my solution using cursor, but cursor sucks

declare @tab table (
rep_date datetime
,itm varchar(50)
,amt float
,target float
)

declare @tt datetime
declare @target float
declare @report_date datetime
declare @item varchar(100)
declare @amount float

declare CustList cursor for
select
report_date
,item
,amount
from dbo.dwv_op_dash_board

OPEN CustList

FETCH NEXT FROM CustList
INTO @report_date, @item,@amount

WHILE @@FETCH_STATUS = 0
BEGIN

set @tt = dateadd(day,-1,@report_date)

set @target = (select amount from dbo.dwv_op_dash_board where item = @item and report_date = @tt)

insert into @tab (rep_date,itm,amt,target)
select @report_date,@item,@amount,@target

FETCH NEXT FROM CustList
INTO @report_date, @item,@amount

END
CLOSE CustList
DEALLOCATE CustList

select * from @tab
--drop table @tab

I hate cursor, Please how can I get this without cursor?

Thanks

I sign for fame not for shame but all the same, I sign my name.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-11 : 14:02:24

How about this?


select report_date
       ,item
       ,amount
       ,target = isnull(p.amount, 0)
from   dbo.dwv_op_dash_board c
left join dbo.dwv_op_dash_board p
              on p.item = c.item
              and dateadd(day, 1, p.report_date) = c.report_date

Be One with the Optimizer
TG

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-09-11 : 14:14:52

You sqlteam.com people are the best people on earth.. I love you all

Thanks aaaaaaallllll... I almost cried, it worked with cursor but it took 20 minutes, TG code took only 4 secs..
why... why .. why...

Thanks, solved

I sign for fame not for shame but all the same, I sign my name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources