UDF or Procedure Help - 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 2000 Forums

SQL Server Development (2000)

UDF or Procedure Help

Author

Topic

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-05 : 07:29:39

Hiiiiii

i had a table a with 3 attributes
i.e sno, date, amount
and output is
sno date amount
1 1/Jan/07 100
1 1/APR/07 200

NOW I WANT FOLLOWING OUTPUT WITH THE HELP OF SOME FUNCTION OR PROCEDURE

PLZ GIVE ME A CODE FOR THAT
WHICH WILL RETRIVE THE FOLLOWING OUTPUT

SNO DATE AMOUNT
1 1/JAN/2007 100
1 1/FEB/2007 100
1 1/MAR/2007 100
1 1/APR/07 200
1 1/MAY/07 200
1 1/JUN/07 200

THE ABOVE OUTPUT SHOULD BE RECOVERED WITH THAT TABLE TILL THE CURRENT MONTH IN OUR SYSTEM

hOPE U GOT MY PROBLEM

HELP PLEASE

WAITING FOR SOMEBODY RESPONSE

With Best Regards
Ruby

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-05 : 07:34:46

SELECT t.sno, dateadd(month, d.m, date), t.amount
from table1 as t
cross join (select 0 as m union all select 1 union all select 2) as d

Peter Larsson
Helsingborg, Sweden

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-05 : 07:56:03

thanx..........

it works

now i want to split wef field into
applicablefrom = firstdate ofmonth
applicableupto = last date of month

hope u got my point
output should be
Sno Date Amount applicablefrom applicableupto
1 01/Jan/2007 100 01/jan/2007 31/Jan/2007

for same output as above post

With Best Regards
Ruby

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-05 : 08:24:58

Add this as two column in your select statement

dateadd(month, datediff(month,0,date), 0),
dateadd(day, -1, dateadd(month, datediff(month,0,date) + 1, 0))

--------------------------------------------------
S.Ahamed

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-05 : 08:39:15

i m using
SELECT t.sno, dateadd(month, d.m, date), t.amount
from table1 as t
cross join (select 0 as m union all select 1 union all select 2) as d

above query for my output and the
answer u provided does not involve the proper output

so insert ure commands to the above query

moreover the above query had one more problem its retriving the multiple enteries for the provided date

With Best Regards
Ruby

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-05 : 08:51:02

SELECT t.sno, dateadd(month, d.m, date), t.amount,
dateadd(month, datediff(month,0,dateadd(month, d.m, date)), 0),
dateadd(day, -1, dateadd(month, datediff(month,0,dateadd(month, d.m, date)) + 1, 0))
from table1 as t
cross join (select 0 as m union all select 1 union all select 2) as d

--------------------------------------------------
S.Ahamed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-05 : 08:56:49

SELECT t.sno, dateadd(month, d.m, t.date) as thedate, dateadd(month, datediff(month, 0, t.date), 0) as thefirst,
dateadd(month, datediff(month, -1, t.date), -1) as thelast, t.amount
from table1 as t
cross join (select 0 as m union all select 1 union all select 2) as d

Peter Larsson
Helsingborg, Sweden

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-05 : 09:02:58

Thanks Friends

It works
Hurry........

Will post some more problem i faced

Hopefully all r solved discussing

With Best Regards
Ruby

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-05 : 09:03:49

Ahh, now I get you!
You want to insert missing monthly records, from the first found until current month?
If a record is missing, use data from last known record?

Peter Larsson
Helsingborg, Sweden

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-05 : 09:06:22

ya
true

actually i was making a function for it
but it creates error in getdate field

so i started with query selection
and u people helped me

Thanx once again

With Best Regards
Ruby

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-06 : 01:14:15

friend wen i use the sql query

SELECT TOP 100 PERCENT t.PERSONNEL_ID, DATEADD(month, d.m, t.WEF_DATE) AS wefdate, t.HEAD_AMOUNT, DATEADD(month, DATEDIFF(month, 0,
DATEADD(month, d.m, t.WEF_DATE)), 0) AS applicablefrom, DATEADD(day, - 1, DATEADD(month, DATEDIFF(month, 0, DATEADD(month, d.m,
t.WEF_DATE)) + 1, 0)) AS applicable_upto, t.HEAD_CODE, t.PLANT_CODE
FROM dbo.PA_PER_FIXEDSAL_T t CROSS JOIN
(SELECT 0 AS m
UNION ALL
SELECT 1
UNION ALL
SELECT 2) d
ORDER BY wefdate

then multiple ouput is coming for middle month

i reply u the answer too

PERSONNEL_ID wefdate HEAD_AMOUNT applicablefrom applicable_upto HEAD_CODE
90801652 01-Mar-07 6600 01-Mar-07 31-Mar-07 BAS
90801652 01-Mar-07 950 01-Mar-07 31-Mar-07 CON
90801652 01-Mar-07 950 01-Mar-07 31-Mar-07 HR50
90801652 01-Apr-07 950 01-Apr-07 30-Apr-07 HR50
90801652 01-Apr-07 950 01-Apr-07 30-Apr-07 CON
90801652 01-Apr-07 6600 01-Apr-07 30-Apr-07 BAS
90801652 01-May-07 1950 01-May-07 31-May-07 HR50
90801652 01-May-07 1950 01-May-07 31-May-07 CON
90801652 01-May-07 950 01-May-07 31-May-07 CON
90801652 01-May-07 6600 01-May-07 31-May-07 BAS
90801652 01-May-07 950 01-May-07 31-May-07 HR50
90801652 01-May-07 8600 01-May-07 31-May-07 BAS
90801652 01-May-07 1950 01-May-07 31-May-07 CON
90801652 01-May-07 1950 01-May-07 31-May-07 HR50
90801652 01-Jun-07 1950 01-Jun-07 30-Jun-07 HR50
90801652 01-Jun-07 1950 01-Jun-07 30-Jun-07 CON
90801652 01-Jun-07 8600 01-Jun-07 30-Jun-07 BAS
90801652 01-Jun-07 1950 01-Jun-07 30-Jun-07 HR50
90801652 01-Jun-07 1950 01-Jun-07 30-Jun-07 CON
90801652 01-Jul-07 1950 01-Jul-07 31-Jul-07 CON
90801652 01-Jul-07 1950 01-Jul-07 31-Jul-07 HR50
90801652 01-Jul-07 8600 01-Jul-07 31-Jul-07 BAS
90801652 01-Jul-07 1950 01-Jul-07 31-Jul-07 HR50
90801652 01-Jul-07 1950 01-Jul-07 31-Jul-07 CON

the green lines are repeated twice

modify the query for sure so that for each month we r able to retieve one value

With Best Regards
Ruby

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-06 : 01:18:12

to the above post i had made a select query which compiles al three heads

SELECT PLANT_CODE, PERSONNEL_ID, applicablefrom, applicable_upto, SUM(HEAD_AMOUNT) AS Expr1
FROM dbo.vw_try
GROUP BY PLANT_CODE, PERSONNEL_ID, applicablefrom, applicable_upto

PLANT_CODE PERSONNEL_ID applicablefrom applicable_upto Expr1
1110 90801652 01-Mar-07 31-Mar-07 8500
1110 90801652 01-Apr-07 30-Apr-07 8500
1110 90801652 01-May-07 31-May-07 24900
1110 90801652 01-Jun-07 30-Jun-07 16400
1110 90801652 01-Jul-07 31-Jul-07 16400

here for 01/May/07 the data coming is 8500 + 16400 = 24900

but it should come 16400

it is coming because in above query the double netry is coming for a particular dat range

Hope this will be solved soon
as i need it urgently

Hope my problem is being understood

With Best Regards
Ruby

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-06 : 01:22:35

can you post sample data of table dbo.PA_PER_FIXEDSAL_T

--------------------------------------------------
S.Ahamed

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-06 : 02:41:08

the ouput of pa_per_fixedsal_t

Personnel_id Head_code Wef_date Head_Amount
90801652 BAS 01/03/2007 6600
90801652 CON 01/03/2007 950
90801652 HR50 01/03/2007 950
90801652 BAS 01/05/2007 8600
90801652 CON 01/05/2007 1950
90801652 HR50 01/05/2007 1950
90801652 HR50 01/05/2007 1950
90801652 CON 01/05/2007 1950

from wef_date 01/03/2007 total amount is plus with head bas con and hr50 i.e 8500
and
from wef_date 01/03/2007 total amount is plus with head bas con and hr50 i.e 16400

Hope now u got it

With Best Regards
Ruby

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-06 : 03:01:51

Now view6 is the ouput which i need to convert

select * from view6 where personnel_id = '90801652'

Personnel_id Plant code Expr1 wef_date
90801652 1110 8500 01/03/2007
90801652 1110 16400 01/05/2007

SELECT t.PERSONNEL_ID
, dateadd(month, d.m, WEF_DATE)as WEF_DATE
, t.expr1 as HEAD_AMOUNT
,dateadd(month, datediff(month,0,dateadd(month, d.m, WEF_DATE)), 0) as applicable_from,
dateadd(day, -1, dateadd(month, datediff(month,0,dateadd(month, d.m, WEF_DATE)) + 1, 0)) as applicable_upto
from view6 as t
cross join (select 0 as m union all select 1 union all select 2) as d
where personnel_id = '90801652'
order by WEF_DATE

Personnel_id wef_date head_amount applicable_from applicable_upto
90801652 01/03/2007 8500 01/03/2007 31/03/2007
90801652 01/04/2007 8500 01/04/2007 30/04/2007
90801652 01/05/2007 8500 01/05/2007 31/05/2007
90801652 01/05/2007 16400 01/05/2007 31/05/2007
90801652 01/06/2007 16400 01/06/2007 30/06/2007
90801652 01/07/2007 16400 01/07/2007 31/07/2007

here in this for month of 01/05/2007 two enteries are coming for above query
but i need only one i.e which will give ouput 16400 regarding the above one

Hopefully this query is understood by u

Please reply urgently as i m also trying

With Best Regards
Ruby

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-06 : 04:13:02

quote:
Originally posted by ruby_sqlserver

to the above post i had made a select query which compiles al three heads

SELECT PLANT_CODE, PERSONNEL_ID, applicablefrom, applicable_upto, SUM(HEAD_AMOUNT) AS Expr1
FROM dbo.vw_try
GROUP BY PLANT_CODE, PERSONNEL_ID, applicablefrom, applicable_upto

PLANT_CODE PERSONNEL_ID applicablefrom applicable_upto Expr1
1110 90801652 01-Mar-07 31-Mar-07 8500
1110 90801652 01-Apr-07 30-Apr-07 8500
1110 90801652 01-May-07 31-May-07 24900
1110 90801652 01-Jun-07 30-Jun-07 16400
1110 90801652 01-Jul-07 31-Jul-07 16400

here for 01/May/07 the data coming is 8500 + 16400 = 24900

but it should come 16400

it is coming because in above query the double netry is coming for a particular dat range

Hope this will be solved soon
as i need it urgently

Hope my problem is being understood

With Best Regards
Ruby

Time being use max(HEAD_AMOUNT) instead of SUM(HEAD_AMOUNT)

--------------------------------------------------
S.Ahamed

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-06 : 04:41:42

if i use maximum instead of sum then it will not take values for previous months and i want all values should be retrive

for the itme being max is working but this is not proper solution

i want date range changed for all the cost for a id

Hope u got my point

With Best Regards
Ruby

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-06 : 04:44:24

Ruby,

Don mind if you re-post your table DDL, sample data and the expected result ? There are bits of these all over the thread, kind of messy and confusing.

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-06 : 05:31:14

hello khtan

i m reposting my query

Now view6 is the ouput which i need to convert

select * from view6 where personnel_id = '90801652'

Personnel_id Plant code Expr1 wef_date
90801652 1110 8500 01/03/2007
90801652 1110 16400 01/05/2007

SELECT t.PERSONNEL_ID
, dateadd(month, d.m, WEF_DATE)as WEF_DATE
, t.expr1 as HEAD_AMOUNT
,dateadd(month, datediff(month,0,dateadd(month, d.m, WEF_DATE)), 0) as applicable_from,
dateadd(day, -1, dateadd(month, datediff(month,0,dateadd(month, d.m, WEF_DATE)) + 1, 0)) as applicable_upto
from view6 as t
cross join (select 0 as m union all select 1 union all select 2) as d
where personnel_id = '90801652'
order by WEF_DATE

Personnel_id wef_date head_amount applicable_from applicable_upto
90801652 01/03/2007 8500 01/03/2007 31/03/2007
90801652 01/04/2007 8500 01/04/2007 30/04/2007
90801652 01/05/2007 8500 01/05/2007 31/05/2007
90801652 01/05/2007 16400 01/05/2007 31/05/2007
90801652 01/06/2007 16400 01/06/2007 30/06/2007
90801652 01/07/2007 16400 01/07/2007 31/07/2007

here in this for month of 01/05/2007 two enteries are coming for above query
but i need only one i.e which will give ouput 16400 regarding the above one

Hopefully this query is understood by u

Please reply urgently as i m also trying

i just want that query above should be modified so that multiple enteries for a month is not displayed as i had mentioned earlier

Hope now u got the problem i suppose

With Best Regards
Ruby

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-06 : 05:43:41

these 2 rows


90801652 01/05/2007  8500 01/05/2007 31/05/2007
90801652 01/05/2007 16400 01/05/2007 31/05/2007

are from diff record of view6. What is the criteria of choosing the one red over the other

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-06 : 06:51:10

Basically my purpose is

my cuurent table named view6 displays values as

Personnel_id Plant code Expr1 wef_date
90801652 1110 8500 01/03/2007
90801652 1110 16400 01/05/2007

and i want a query for displaying data like this

Personnel_id wef_date head_amount applicable_from applicable_upto
90801652 01/03/2007 8500 01/03/2007 31/03/2007
90801652 01/04/2007 8500 01/04/2007 30/04/2007
90801652 01/05/2007 16400 01/05/2007 31/05/2007
90801652 01/06/2007 16400 01/06/2007 30/06/2007
90801652 01/07/2007 16400 01/07/2007 31/07/2007

so i enter query as suggested like

SELECT t.PERSONNEL_ID
, dateadd(month, d.m, WEF_DATE)as WEF_DATE
, t.expr1 as HEAD_AMOUNT
,dateadd(month, datediff(month,0,dateadd(month, d.m, WEF_DATE)), 0) as applicable_from,
dateadd(day, -1, dateadd(month, datediff(month,0,dateadd(month, d.m, WEF_DATE)) + 1, 0)) as applicable_upto
from view6 as t
cross join (select 0 as m union all select 1 union all select 2) as d
where personnel_id = '90801652'
order by WEF_DATE

and output comes like

Personnel_id wef_date head_amount applicable_from applicable_upto
90801652 01/03/2007 8500 01/03/2007 31/03/2007
90801652 01/04/2007 8500 01/04/2007 30/04/2007
90801652 01/05/2007 8500 01/05/2007 31/05/2007
90801652 01/05/2007 16400 01/05/2007 31/05/2007
90801652 01/06/2007 16400 01/06/2007 30/06/2007
90801652 01/07/2007 16400 01/07/2007 31/07/2007

which gives repeated value for month of May

but that i dont want
i want

Personnel_id wef_date head_amount applicable_from applicable_upto
90801652 01/03/2007 8500 01/03/2007 31/03/2007
90801652 01/04/2007 8500 01/04/2007 30/04/2007
90801652 01/05/2007 16400 01/05/2007 31/05/2007
90801652 01/06/2007 16400 01/06/2007 30/06/2007
90801652 01/07/2007 16400 01/07/2007 31/07/2007

so either edit my query or give me a solution for the above thing

Hope now u got my point

Reply

With Best Regards
Ruby

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources