Stored Procedure - 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

Stored Procedure

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-05-08 : 13:08:49

Want to create a stored procedure to calculate usage for items for each period. Not quite sure how to fit it in my statement.

This procedure works fine but I need to give it a year.


CREATE PROCEDURE [UsageByYear]
@Year int = 2009
 AS

Select item_no,sum(quantity) as Usage
from iminvtrx_sql
where doc_type = 'I' and
@Year = year(doc_dt)
group by item_no
GO

But I would like it to look at the current date and Give me Usage for the current month and every month before that going back to the begining of the year. I'm pretty sure I need to squeeze it into my select statment, but I am unsure how. I know what I have below is incorrect, but I'm hoping you can get a sense of what I'm trying to accomplish.


If month(getdate()) = 1 then
Select item_no, sum(quantity) where month(getdate())=month(doc_dt) and year(getdate())=year(doc_dt) AS UsageCurrentPeriod
else if
month(getdate()) = 2 then select item_no, sum(quantity) where month(getdate())=month(doc_dt) and year(getdate())=year(doc_dt) AS UsageCurrentPeriod, sum(quantity) where (month(getdate())-1=month(doc_dt) and year(getdate())=year(doc_dt) AS UsageCurrentPeriod1.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 13:17:48

[code]
CREATE PROCEDURE [UsageByMonth]
AS

Select item_no,DATENAME(mm,doc_dt),sum(quantity) as Usage
from iminvtrx_sql
where doc_type = 'I' and
doc_dt>= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
AND doc_dt< DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
group by item_no,DATENAME(mm,doc_dt)
GO
[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-08 : 13:22:29

[code]
CREATE PROCEDURE [UsageByYear]
AS

Select item_no,sum(quantity) as Usage
from iminvtrx_sql
where doc_type = 'I' and
year(doc_dt)=year(getdate())
group by item_no
GO
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-05-08 : 13:25:18

Is it possible to have each month be a field with the appropriate usage number below it. So that there is one record per item number?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 13:25:44

quote:
Originally posted by webfred

CREATE PROCEDURE [UsageByYear]
AS

Select item_no,sum(quantity) as Usage
from iminvtrx_sql
where doc_type = 'I' and
year(doc_dt)=year(getdate())
group by item_no
GO

No, you're never too old to Yak'n'Roll if you're too young to die.

this wont give what OP asked for. the question was to get monthwise usage for current year upto current month

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 13:26:27

quote:
Originally posted by Vack

Is it possible to have each month be a field with the appropriate usage number below it. So that there is one record per item number?

its possible. are you using sql 2005 or earlier version?

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-05-08 : 13:29:15

2005

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-08 : 13:29:43

He also posted This procedure works fine but I need to give it a year.
So I misunderstood...

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 13:45:05

quote:
Originally posted by Vack

2005

ok use this


CREATE PROCEDURE [UsageByMonth]
 AS
SELECT item_no,
SUM(CASE WHEN DATEPART(mm,doc_dt)=1 THEN quantity ELSE 0 END) AS January,
SUM(CASE WHEN DATEPART(mm,doc_dt)=2 THEN quantity ELSE 0 END) AS February,
SUM(CASE WHEN DATEPART(mm,doc_dt)=3 THEN quantity ELSE 0 END) AS March,
...
SUM(CASE WHEN DATEPART(mm,doc_dt)=12 THEN quantity ELSE 0 END) AS December
from iminvtrx_sql
where doc_type = 'I' and
doc_dt>= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
AND doc_dt< DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
group by item_no
GO

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-05-08 : 14:05:52

Thank you, that was exactly what I was looking for.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 14:09:22

welcome

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-05-08 : 14:15:03

Actually I have one more question. Can you explain this logic to me?

doc_dt>= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
AND doc_dt< DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)

I thought I would be able to figure this out with the code you had given me, but I'm going to want to find the usage back 2 years from the current date. If I run the procedure today I want usage for each period back to 01/01/2007

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 14:18:21

for two years use this


CREATE PROCEDURE [UsageByMonth]
 AS
SELECT item_no,YEAR(doc_dt) AS YearVal,
SUM(CASE WHEN DATEPART(mm,doc_dt)=1 THEN quantity ELSE 0 END) AS January,
SUM(CASE WHEN DATEPART(mm,doc_dt)=2 THEN quantity ELSE 0 END) AS February,
SUM(CASE WHEN DATEPART(mm,doc_dt)=3 THEN quantity ELSE 0 END) AS March,
...
SUM(CASE WHEN DATEPART(mm,doc_dt)=12 THEN quantity ELSE 0 END) AS December
from iminvtrx_sql
where doc_type = 'I' and
doc_dt>= DATEADD(yy,DATEDIFF(yy,0,GETDATE())-2,0)
AND doc_dt< DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
group by item_no,YEAR(doc_dt)
GO

DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) gives start date of current year while DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0) gives start date of next month

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-05-08 : 14:34:49

I know I keep adding to this.. I need to subtract quantity from SUM(CASE WHEN DATEPART(mm,doc_dt)=1 THEN quantity ELSE 0 END) where doc_type = 'R'

Is that possible???

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-05-08 : 14:37:31

Should probably explain a little more.

In my table I have issue records and receipt records.

Doc type I = issue doc type R = Receipt.

the field quantity holds the trx qty.

for one item I may have an I Record and an R record. I need to subtract the R from the I to give me my real usage number.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 14:40:17

yup..it is..something like


CREATE PROCEDURE [UsageByMonth]
 AS
SELECT item_no,YEAR(doc_dt) AS YearVal,
SUM(CASE WHEN DATEPART(mm,doc_dt)=1 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity  END ELSE 0 END) AS January,
SUM(CASE WHEN DATEPART(mm,doc_dt)=2 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity  END ELSE 0 END) AS February,
SUM(CASE WHEN DATEPART(mm,doc_dt)=3 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity  END ELSE 0 END) AS March,
...
SUM(CASE WHEN DATEPART(mm,doc_dt)=12 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity  END ELSE 0 END) AS December
from iminvtrx_sql
where doc_type = 'I' and
doc_dt>= DATEADD(yy,DATEDIFF(yy,0,GETDATE())-2,0)
AND doc_dt< DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
group by item_no,YEAR(doc_dt)
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources