Quarter Parameter

[codrutza]

SQL Server 2000

How do I write in a stored procedure a parameter Quarter @Q:
Q1 FJ.repdate BETWEEN jan1-march31 2010 (or 2011 or any year)
Q2 FJ.repdate BETWEEN april1-june30 2010 (or 2011 or any year)
Q3 FJ.repdate BETWEEN july1-sept30 2010 (or 2011 or any year)
Q4 FJ.repdate BETWEEN oct1-dec31 2010 (or 2011 or any year)


.....
WHERE
(Job.Co='A' or Job.Co='B' )
AND FJ.Customer='ABC'
AND @Q=….

It has to retrieve data for each year, as well.

Pls help

 

[bborissov]

SELECT CAST(((MONTH(GETDATE())-1)/3)+1 AS int) AS Quarter

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[gmmastros]

Select DatePart(Quarter, FJ.repdate) As [RepQuarter]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[codrutza]

Thank you both.

Borisov, is not working, or I do something wrong.

George, I used that solution you gave me and works great. I had to add a parameter year.

@YearRep smallint,
@Q smallint

SELECT…
WHERE …
AND @YearRep=year(FJ.RepDate)
AND @Q=DatePart(Quarter, FJ.RepDate)

 

[codrutza]

SQL Server 2000

I have to write a stored procedure which has
- a parameter @initialdate (yyyy,mm,dd)
- second parameter @Quarter, but this Quarter is not the usual quarter; Q1 it’s starting with @initialdate (for example Saturday 2 jul 2005) and it is 13 weeks (4 for July, 4 weeks for Aug, 5 weeks for Sep), Q2 it’s starting with @initialdate +13 weeks (4 for Oct, 4 weeks for Nov, 5 weeks for Dec), Q3 …..

Pls help

 

[gmmastros]

In that case, I would suggest you add a Calendar table to your database. In this table, you should have one row for every day. Make sure the first date in this table is sufficiently far enough in the past to accommodate all of your data, and then extend the calendar table 50 years in to the future (long after you plan on retiring). In this table, you can have another column for your modified quarter. You can also store other information in this table, like holidays etc...

Then, you can simply join to this table to determine which quarter the date falls within.

Often times I will hear people object to a calendar table because of the extra space required to store it. Each date requires 8 bytes. Multiple this by 10,000 and you have 80,000 bytes (or roughly 80k). Since you will have other columns, you'll need more space than this, but you should easily use less than 1/2 a meg of storage.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[codrutza]

I don't build tables. I am building just reports based on the tables that the developers built. I am using Crystal Reports and stored procedures for reports. I found a solution to my problem if I based my report directly on tables, but I try to use stored procedures instead. Thank you anyway.

 

[gmmastros]

I understand, but that doesn't necessarily you mean you can't use my advice. Let me clarify just a bit...

My advice was to build a calendar table. This would need to be done just once. You could ask your developers to create this table. Tell them it will allow your reports to generate faster, be more accurate, and will consume less than 1/2 a meg of storage space. They will probably allow it.

Then, you can still create a stored procedure for your report. This stored procedure would use the Calendar table that the developers added to the database.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Take a look at this blog post

Why should I consider a Calendar table?

This explains why it's important to have such table in the database and also provides a script for it.


PluralSight Learning Library

 

[codrutza]

I'll see what I can do.
Thank you both.