DatePart Function

Question

DatePart Function

vblllove

Ten Centuries

Points: 1273
More actions
April 30, 2008 at 4:42 am

#380249

Hi
Im using DatePart Function for Gjenerating the Quartely and Monthly sales in sql Server 2005 Stored Procedure with Pivot Tables. DatePart(qq,date) as Quarter, DatePart(mm,Date) as Monthly.
I want also to Display the Sales on 4 month Basis and 6 month basis using pivot. Any suggestion on how to come arround this Function since doesnt provide the 4 month or 6 month ??

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

Use Modulo 4 and Modulo 6 to limit the values to quarterly & every six months. Of course, you can only use June & December for the six month breaks.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Noffer Ten Centuries Points: 1090 More actions · Answer 2

For the pivot, I posted something like what you are trying to do a little while ago..

http://www.sqlservercentral.com/Forums/Topic475805-338-1.aspx#bm476235

It shouldn't be too hard to switch it around for a quarterly summary too.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Something that I used to use that is just a tabular implementation of Grant's suggestion:

create table Months (MonthNo int, Month4Period int, Month6Period int)

insert into Months

Select 1, 1, 1

Union All Select 2, 1, 1

Union All Select 3, 1, 1

Union All Select 4, 1, 1

Union All Select 5, 2, 1

Union All Select 6, 2, 1

Union All Select 7, 2, 2

Union All Select 8, 2, 2

Union All Select 9, 3, 2

Union All Select 10, 3, 2

Union All Select 11, 3, 2

Union All Select 12, 3, 2

Then you just add:

, (Select Month4Period From Months Where MonthNo=DatePart(mm,Date)) as Month4

, (Select Month6Period From Months Where MonthNo=DatePart(mm,Date)) as Month6

to your query columns.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

I'm curious... Why 4 months? That's a third of a year...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Jeff Moden (4/30/2008)
I'm curious... Why 4 months? That's a third of a year...

That's probabrly why, Jeff. I did some work for an insurance company several years ago and they requested this exact same breakdown: Every 3 Mos, Every 4 Mos and Every 6 Mos, in a year. Some kind of reporting paradigm that they use.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 6

Oops. Modulo 3.

:blush:

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

rbarryyoung (4/30/2008)
Jeff Moden (4/30/2008)
I'm curious... Why 4 months? That's a third of a year...
That's probabrly why, Jeff. I did some work for an insurance company several years ago and they requested this exact same breakdown: Every 3 Mos, Every 4 Mos and Every 6 Mos, in a year. Some kind of reporting paradigm that they use.

Hmmm.... Wonder why they do that? The 4 month thing, I mean. Is there any advantage?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Grant Fritchey (5/1/2008)
Oops. Modulo 3.
:blush:

Heh... I've done the exact same thing... start thinking "Quarters", start writing "4's" 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Jeff Moden (5/1/2008)
rbarryyoung (4/30/2008)
Jeff Moden (4/30/2008)
I'm curious... Why 4 months? That's a third of a year...
That's probabrly why, Jeff. I did some work for an insurance company several years ago and they requested this exact same breakdown: Every 3 Mos, Every 4 Mos and Every 6 Mos, in a year. Some kind of reporting paradigm that they use.
Hmmm.... Wonder why they do that? The 4 month thing, I mean. Is there any advantage?

Got me. I was actually doing work for a Claims Adjustment company whose customers were the insurance companies and their only explanation was "That's what our customers want".

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]