Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DatePart Function Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2008 4:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 10:30 AM
Points: 231, Visits: 198
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 ??
Post #492794
Posted Wednesday, April 30, 2008 6:16 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 15,517, Visits: 27,897
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #492856
Posted Wednesday, April 30, 2008 12:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 6, 2011 8:53 AM
Points: 292, Visits: 118
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.
Post #493172
Posted Wednesday, April 30, 2008 1:57 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #493255
Posted Wednesday, April 30, 2008 10:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #493411
Posted Wednesday, April 30, 2008 11:41 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #493417
Posted Thursday, May 1, 2008 6:04 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 15,517, Visits: 27,897
Oops. Modulo 3.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #493506
Posted Thursday, May 1, 2008 6:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #493508
Posted Thursday, May 1, 2008 6:15 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
Grant Fritchey (5/1/2008)
Oops. Modulo 3.


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


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #493509
Posted Thursday, May 1, 2008 8:06 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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".


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #493589
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse