Doubt in the below query - 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

Doubt in the below query

Author

Topic

sowmyav
Starting Member

34 Posts

Posted - 2009-01-22 : 03:07:06

Hi,
could any one please let me know whether the below qry works?

select arr.prod,arr.cal,arr.fld_inv,
sum(arr.fld_inv) over (partition by arr.cal) as cum_fld,
sum(cum_fld) over (partition by arr.cal) as new_fld
from arr,ntf
where arr.prod=ntf.prod and arr.cal=ntf.cal;

I have the doubt on the new_fld whether the cum_fld can be used to compute the new_fld?
else how to get it?

Regards,
Sowmya

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-22 : 03:20:55

quote:
Originally posted by sowmyav

Hi,
could any one please let me know whether the below qry works?

select arr.prod,arr.cal,arr.fld_inv,
sum(arr.fld_inv) over (partition by arr.cal) as cum_fld,
sum(cum_fld) over (partition by arr.cal) as new_fld
from arr inner join ntf
onarr.prod=ntf.prod and arr.cal=ntf.cal
group by arr.prod,arr.cal,arr.fld_inv

I have the doubt on the new_fld whether the cum_fld can be used to compute the new_fld?
else how to get it?

Regards,
Sowmya

u have to keep group by

sowmyav
Starting Member

34 Posts

Posted - 2009-01-22 : 03:25:19

Thank you very much.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-22 : 03:31:19

ur welcome

sowmyav
Starting Member

34 Posts

Posted - 2009-01-22 : 03:38:07

thank u
And one more doubt.Added to that......
whether the below one works?
select arr.prod,arr.cal,arr.fld_inv,
sum(arr.fld_inv) over (partition by arr.cal) as cum_fld,
cum_fld * arr.ARR_3MRA as ARRValue,
cum_fld * arr.NTF_3MRA as NTFValue,
sum(ARRValue) over (partition by arr.cal) as new_fld,
sum(NTFValue) over (partition by arr.cal) as new_fld1
from arr inner join ntf
onarr.prod=ntf.prod and arr.cal=ntf.cal
group by arr.prod,arr.cal,arr.fld_inv

similarly i need new_fld2 which the subtraction of (newfld-newfld1)...how to mention that in the qry? and whther the above one works fine?

thanks in advance

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-22 : 03:43:01

select prod,cal,fld_inv,cum_fld,cum_fld * arr.ARR_3MRA as ARRValue,
cum_fld * arr.NTF_3MRA as NTFValue,
new_fld,new_fld1,new_fld-new_fld1 as difference
from (
select arr.prod,arr.cal,arr.fld_inv,
sum(arr.fld_inv) over (partition by arr.cal) as cum_fld,
sum(ARRValue) over (partition by arr.cal) as new_fld,
sum(NTFValue) over (partition by arr.cal) as new_fld1
from arr inner join ntf
onarr.prod=ntf.prod and arr.cal=ntf.cal
group by arr.prod,arr.cal,arr.fld_inv ) s

sowmyav
Starting Member

34 Posts

Posted - 2009-01-22 : 03:56:25

Thank u so much.
and incase there are details in the cal from 2007-jan till 2008-dec
and I need to fetch the data for one year only(12 months).So whether i can use the below one for that
WHERE Cast((Substring(Cal,5,2) + '-01-' + Substring(Cal,1,4)) as DateTime) > (Select Max(DateAdd(Month,-12,Cast((Substring(Cal,5,2) + '-01-' + Substring(,1,4)) as DateTime)))

*************************
select prod,cal,fld_inv,cum_fld,cum_fld * arr.ARR_3MRA as ARRValue,
cum_fld * arr.NTF_3MRA as NTFValue,
new_fld,new_fld1,new_fld-new_fld1 as difference
from (
select arr.prod,arr.cal,arr.fld_inv,
sum(arr.fld_inv) over (partition by arr.cal) as cum_fld,
sum(ARRValue) over (partition by arr.cal) as new_fld,
sum(NTFValue) over (partition by arr.cal) as new_fld1
from arr inner join ntf
onarr.prod=ntf.prod and arr.cal=ntf.cal
group by arr.prod,arr.cal,arr.fld_inv ) s
WHERE Cast((Substring(Cal,5,2) + '-01-' + Substring(Cal,1,4)) as DateTime) > (Select Max(DateAdd(Month,-12,Cast((Substring(Cal,5,2) + '-01-' + Substring(,1,4)) as DateTime)))

whether this works?

thanks in adv,
Sowmya

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-22 : 04:01:44

u want the records of the 2007 only the u can use as year(cal) = 2007 then u can the values in date 2007 only

sowmyav
Starting Member

34 Posts

Posted - 2009-01-22 : 04:07:19

Thank u...
that's fineBut sometimes it might be 2007-feb to 2007-jan(12 months)..it might start wiht anymonth +12 moremonths ...
So whether the above cast(substring) works fine? or needs changes?

please let me know.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-22 : 04:11:41

i think where dateadd(dd,datediff(dd,0,cal),0) <= dateadd(dd,datediff(dd,365,getdate()),0)

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-22 : 04:14:43

Try this also,

where urdatefield between between DATEADD(m,-12,GETDATE()) and dateadd(dd,datediff(dd,0,getdate()),0)

sowmyav
Starting Member

34 Posts

Posted - 2009-01-22 : 04:43:26

Thanks :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 08:40:46

quote:
Originally posted by bklr

quote:
Originally posted by sowmyav

Hi,
could any one please let me know whether the below qry works?

select arr.prod,arr.cal,arr.fld_inv,
sum(arr.fld_inv) over (partition by arr.cal) as cum_fld,
sum(cum_fld) over (partition by arr.cal) as new_fld
from arr inner join ntf
onarr.prod=ntf.prod and arr.cal=ntf.cal
group by arr.prod,arr.cal,arr.fld_inv

I have the doubt on the new_fld whether the cum_fld can be used to compute the new_fld?
else how to get it?

Regards,
Sowmya

u have to keep group by

how will you use sum(cum_fld) on the same level? cum_fld is column alias created on same level. you can use it immediately on same level it should be


select prod, cal, fld_inv,
sum(cum_fld) over (partition by cal) as new_fld
from
(
select arr.prod,arr.cal,arr.fld_inv,
sum(arr.fld_inv) over (partition by arr.cal) as cum_fld
from arr inner join ntf 
onarr.prod=ntf.prod and arr.cal=ntf.cal
)t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 08:41:31

quote:
Originally posted by sowmyav

thank u
And one more doubt.Added to that......
whether the below one works?
select arr.prod,arr.cal,arr.fld_inv,
sum(arr.fld_inv) over (partition by arr.cal) as cum_fld,
cum_fld * arr.ARR_3MRA as ARRValue,
cum_fld * arr.NTF_3MRA as NTFValue,
sum(ARRValue) over (partition by arr.cal) as new_fld,
sum(NTFValue) over (partition by arr.cal) as new_fld1
from arr inner join ntf
onarr.prod=ntf.prod and arr.cal=ntf.cal
group by arr.prod,arr.cal,arr.fld_inv

similarly i need new_fld2 which the subtraction of (newfld-newfld1)...how to mention that in the qry? and whther the above one works fine?

thanks in advance

you dont require group by if you're using sum with partition by

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 08:47:00

quote:
Originally posted by sowmyav

thank u
And one more doubt.Added to that......
whether the below one works?
select arr.prod,arr.cal,arr.fld_inv,
sum(arr.fld_inv) over (partition by arr.cal) as cum_fld,
cum_fld * arr.ARR_3MRA as ARRValue,
cum_fld * arr.NTF_3MRA as NTFValue,
sum(ARRValue) over (partition by arr.cal) as new_fld,
sum(NTFValue) over (partition by arr.cal) as new_fld1
from arr inner join ntf
onarr.prod=ntf.prod and arr.cal=ntf.cal
group by arr.prod,arr.cal,arr.fld_inv

similarly i need new_fld2 which the subtraction of (newfld-newfld1)...how to mention that in the qry? and whther the above one works fine?

thanks in advance

it should be


select prod,
cal,
fld_inv,
cum_fld,
cum_fld * ARR_3MRA as ARRValue,
cum_fld * NTF_3MRA as NTFValue,
(newfld-newfld1) as newfld2
from
(
select arr.prod,arr.cal,arr.fld_inv,
sum(arr.fld_inv) over (partition by arr.cal) as cum_fld,
arr.ARR_3MRA,
arr.NTF_3MRA,
sum(ARRValue) over (partition by arr.cal) as new_fld,
sum(NTFValue) over (partition by arr.cal) as new_fld1
from arr inner join ntf 
onarr.prod=ntf.prod and arr.cal=ntf.cal
)t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources