Determine datetime quarterly - 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 2005 Forums

Transact-SQL (2005)

Determine datetime quarterly

Author

Topic

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-07-23 : 18:23:46

I need help determining the quarter in which the report date is in relative to the 1stQrtBillingMonths. 1stQrtBillingMonths = 3 months following the month in TxDate. CurrentQrtBillingMonths = the 3 months for the quarter in which the report is being run. CurrentBillingCycle = the last month of the quarter in which the report is run. Here is what I have so far. The || is used to denote columns.

Office||TxDate||1stQrtBillingMonths||CurrentQrtBillingMonths||CurrentBillingCycle
AthensTX||01/03/08||02/01/08-04/01/08||(help here)||04/01/08

If I ran the report any time in February thru April, the CurrentBillingCycle = 04/01/08. If I were to run the report in September, then my CurrentQrtBillingMonths 08/01/08-10/01/08. And my CurrentBillingCycle = 10/01/08.

Here is a snippet of the code I've written.

use server1
declare @GetDate as datetime
set @getDate = '09/01/2008' --testing purposes

Select
Office,
p.PatsFirstName+' '+p.PatsLastName as 'PatientName',
convert(varchar(10),appt.dt_appt,101) as 'TxDate',
convert(varchar(10),dateadd(dd,30,appt.dt_appt),101)+' - '+ convert(varchar(10),dateadd(mm,3,appt.dt_appt),101) as '1stQrtBillingMonths',
...(CurrentQrtBillingMonths goes here)
convert(varchar(10),dateadd(mm, 3-datediff(mm, appt.dt_appt, @getDate) % 3, @getDate),101) as 'CurrentBillingCycle',
...

Any help is greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 18:43:06

[code]declare @GetDate as datetime
set @getDate = 'January 3 2008'

SELECT @getDate AS TxDate,
CONVERT(CHAR(8), DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @getDate), '19000201'), 1)
+ '-'
+ CONVERT(CHAR(8), DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @getDate), '19000401'), 1) AS [1stQrtBillingMonths],
CONVERT(CHAR(8), DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', @getDate), '19000101'), 1)
+ '-'
+ CONVERT(CHAR(8), DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', @getDate), '19000301'), 1) AS CurrentQrtBillingMonths,
CONVERT(CHAR(8), DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @getDate), '19000401'), 1) AS CurrentBillingCycle[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources