Using Datediff Function To Return 1st Of Month. Different Results With T-SQL And SSIS

May 15, 2007

Hi



I regularly use the T-SQL date functions to return the 1st of a particualr month.



e.g.



SELECT DATEADD(m,DATEDIFF(m,0,getdate()),0)



returns 2007-05-01 00:00:00.000



i.e the first of the current month at midnight.

However, when I try to use a similar expression as a derived column in SSIS it returns a completely different date.



DATEADD("month",DATEDIFF("month",(DT_DATE)0,GETDATE()),(DT_DATE)0)



returns 30/05/2007 00:00:00





Any ideas why and how I can obtain the first of a particualr month using SSIS derived column?





View 3 Replies


ADVERTISEMENT

DATEDIFF Function Dosen't Return Anything If Used Inside ASPX Page!!

Feb 12, 2008

Dear all, I have the Following Query:SELECT DATEDIFF(day, GETDATE(), RECENT_RESERVATION)AS Expr1,RECENT_RESERVATION FROM EMP WHERE SUN=empName;when i run it inside the query analyzer, it returns two columns. but if run it inside The ASPX page it retuns only one column wich is  RECENT_RESERVATION date.Note: i am using one methode that takes care of reading from SQL and assigning the result into an array, it works fine everywhere, but with this query it dosen't work. Any suggestions??   

View 6 Replies View Related

Data Warehousing :: DateDiff Function To Return Positive Value Irrespective Of Values Passed

Aug 7, 2015

I have a requirement to use DateDiff(Months,DateTime1,DateTime2) and this must return positive integer values.

Currently negative numbers are being returned because DateTime1 < DateTime2  or DateTime1 > DateTime2 .

The DateTime1 and  DateTime2  values are dynamic and either of them can be bigger than the other.

Any query solution so that always positive value is returned.

View 3 Replies View Related

SQL Server 2014 :: CLR Function And NET Framework Return Different Results

Nov 1, 2014

I have rather simple CLR function:

[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString GetUserName()
{
return (SqlString)WindowsIdentity.GetCurrent().Name;
}

When I get result from .NET console app, I get correct answer "JungleSektor". However, when SQL Server executes this code, it gives me "NT ServiceMSSQL $ SQL2014". How to get correct result?

View 1 Replies View Related

Datediff Needs To Deliver Month AND Days

Aug 26, 2005

with datediff all I can get it to return is months or a total of thedays...so if the difference in dates is 12 months 4 days how do I adjust theSQL to accommodate both?

View 3 Replies View Related

DateDiff Always Return 0

Feb 14, 2006

I just discover the result for my query that had use the function dateDiff seems to be in-correct, no matter what's the date, the dateDiff always return a zero. Am I using it in-correctly?
select DateDiff(mm,11/1/2004, 12/31/2005)
SELECT component_id, component_description,SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 2 THEN component_qty ELSE 0 END) AS mm1,SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 1 THEN component_qty ELSE 0 END) AS mm2,SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 0 THEN component_qty ELSE 0 END) AS mm3,sum(component_qty) as totalFROM view_jobcomponent WHERE date_complete between '10/1/2004' and '12/31/2004' GROUP BY component_id, component_descriptionorder by component_id, component_description

View 1 Replies View Related

Using Datediff To Return Y Or N

Jun 12, 2008

Hi GUYS

I am trying to use the datediff function to return a Y if difference is greater than 0 and a N if difference is less than or equal to 0

select if datediff(dd,maturity_date,log_changed) > 0 'Y','N')

and i am getting a syntax error.Can someone show me where i am getting it wrong.

In god we trust,everything else we test.

View 3 Replies View Related

DateDiff - Return Months In Decimal?

Jun 24, 2004

I've been trying to create a function that returns the difference, in months, between two dates. The DateDiff function (m) returns an integer, but I really need a decimal. I could return the days instead, but it would be difficult to figure out how the number of months from this, especially when the dates span multiple years and I need to adjust for leap year. Does anyone know of a resource that might have a solution for this?

Thanks,
Rob

View 8 Replies View Related

DATEDIFF Return Monday - Friday Or Just Weekdays

Aug 30, 2006

I have a query and am trying to just return the difference between two dates but not include weekends.For instance, if I have 08/21/2006 - 08/28/2006, there are 6 weekdays.  I tried this, but I am getting 7 as a result.SELECT DATEDIFF(weekday, request_start_date, request_end_date) AS days_off, request_id
FROM request Any help would be greatly appreciated.

View 2 Replies View Related

HELP Me In This Datediff() Function....

Nov 23, 2006

Hi, I am facing problem rite now.. I want to calculate the date different minutes between 23:00:00 and 01:00:00.
My code :
datediff(Minute,'01:00:00','23:00:00')
The result is 1320 minutes. (22 hours)... But, the result that I want is 120 minutes (2 hours)....
Can anybody help ???
Thanks in advance...

View 2 Replies View Related

Using DateDiff Function For Age

Nov 12, 2007

Hi,I'm using the datediff function to display the ages of the users in my database. However the age rounds up once they are 35.5 etc...I could create another function which works similar to the DateDiff function, but use math.floor to always round down, but I need to use this function in a SQL statement WHERE clause. Is there any way around this?Thanks,Curt. 

View 3 Replies View Related

Datediff Function

Apr 25, 2007

hi
select datediff(m,'3/5/2003',getdate as experience

output is
Experience
----------
49

select (datediff(m,'3/5/2003',getdate()))/12 as experience

output is
Experience
----------
4

but 49/12 is 4.08333=4.1

how i can get this
Actually my task is to output as years.months

Thanks in advance


Malathi Rao

View 7 Replies View Related

Please Help With Datediff Function

Oct 5, 2006

I am trying to break up the age into column from a dob field for a cross tab report. I can query the datediff with an alias but can not individually change the columns I need like a virtual or temp column, but can't figure out how to accomplish it.

use 'Database'

Select datediff(Year, dob, getdate()) As "Under 22", datediff(Year, dob, getdate()) As "22-45",

datediff(Year, dob, getdate()) As "46-65", datediff(Year, dob, getdate()) As "Over 65"

from 'Table'

WHERE (DATEDIFF(yy, dob, GETDATE()) < 22 OR

DATEDIFF(yy, dob, GETDATE()) BETWEEN 22 AND 45 OR

DATEDIFF(yy, dob, GETDATE()) BETWEEN 46 AND 65 OR

DATEDIFF(yy, dob, GETDATE()) > 66) AND (status = 'Current' OR

status = 'Previous' OR

status = 'non-billable')

View 5 Replies View Related

A More Precise DateDiff Function

Feb 7, 2007

This will give you the time information about how apart two dates are.CREATE FUNCTION dbo.fnTimeApart
(
@FromTime DATETIME,
@ToTime DATETIME
)
RETURNS @Time TABLE ([year] SMALLINT, [month] TINYINT, [day] TINYINT, [hour] TINYINT, [minute] TINYINT, [second] TINYINT, [millisecond] SMALLINT)
AS
BEGIN
DECLARE@Temp DATETIME,
@Mts INT,
@year SMALLINT,
@month TINYINT,
@day TINYINT,
@hour TINYINT,
@minute TINYINT,
@second TINYINT,
@millisecond SMALLINT

IF @FromTime > @ToTime
SELECT@Temp = @FromTime,
@FromTime = @ToTime,
@ToTime = @Temp

SET@Mts =CASE
WHEN DATEPART(day, @FromTime) <= DATEPART(day, @ToTime) THEN 0
ELSE -1
END + DATEDIFF(month, @FromTime, @ToTime)

SELECT@year = @Mts / 12,
@month = @Mts % 12,
@Temp = DATEADD(month, @Mts, @FromTime)

SELECT@day = datediff(hour, @Temp, @ToTime) / 24,
@Temp = DATEADD(day, @day, @Temp)

SELECT@hour = DATEDIFF(minute, @Temp, @ToTime) / 60,
@Temp = DATEADD(hour, @hour, @Temp)

SELECT@minute = DATEDIFF(second, @Temp, @ToTime) / 60,
@Temp = DATEADD(minute, @minute, @Temp)

SELECT@second = DATEDIFF(millisecond, @Temp, @ToTime) / 1000,
@Temp = DATEADD(second, @second, @Temp),
@millisecond = DATEDIFF(millisecond, @Temp, @ToTime)

INSERT@Time (year, month, day, hour, minute, second, millisecond)
SELECT@year,
@month,
@day,
@hour,
@minute,
@second,
@millisecond

RETURN
ENDAnd to test the functionSELECTd.FromDate,
d.ToDate,
x.*
FROM(
SELECT'19690906' AS FromDate, '19760608' AS ToDate UNION ALL
SELECT'19991231', '20000101' UNION ALL
SELECT'20070207', '20070208' UNION ALL
SELECT'20000131', '20000228' UNION ALL
SELECT'20070202', '20070201' UNION ALL
SELECT'20070207', '20070307' UNION ALL
SELECT'20000131', '20000301' UNION ALL
SELECT'20011231 15:24:13.080', '20020101 17:15:56.343' UNION ALL
SELECT'20011231 17:15:56.343', '20020101 15:24:13.080' UNION ALL
SELECT'20020101 15:24:13.080', '20011231 17:15:56.343' UNION ALL
SELECT'20000131', '20000229'
) AS d
CROSS APPLYdbo.fnTimeApart(d.FromDate, d.ToDate) AS x
ORDER BYd.FromDate,
d.ToDateAnd the output isFromDateToDateyearmonthdayhourminutesecondmillisecond
19690906197606086920000
19991231200001010010000
200001312000022800280000
200001312000022900290000
20000131200003010110000
20011231 15:24:13.08020020101 17:15:56.34300115143263
20011231 17:15:56.34320020101 15:24:13.08000022816736
20020101 15:24:13.08020011231 17:15:56.34300022816736
20070202200702010010000
20070207200702080010000
20070207200703070100000
Peter Larsson
Helsingborg, Sweden

View 14 Replies View Related

Datediff Function With Hrs And Minutes

Feb 9, 2007

I have two date columns one is sent_date and other is approved_date
my requirment is to find the difference between the two dates
which can be minutes/hrs/days.
using datediff function iam able to get it in minusts or hrs but my
output should be of the format hh:mm
23:10 (ie 23 hrs and 10 min) or say
48:00 (for 2 days)

sample date
sent_date approved_date
2/28/06 11:06 2/28/06 11:39
2/2/06 17:42 2/2/06 18:03
2/8/06 16:55 2/8/06 17:38
1/27/06 17:00 1/27/06 17:54
1/26/06 12:08 1/26/06 12:09
2/28/06 15:46 2/28/06 16:26
1/23/06 10:01 1/23/06 10:43
1/26/06 13:46 1/26/06 13:59
1/13/06 13:51 1/13/06 14:47

View 4 Replies View Related

Stuggling With Datediff Function

Nov 2, 2007

recently i created a system using php and mysql to record faults that the ict technicians could get reported faults out of. so far it does everything i want but some senior members of staff now want the front end to give a report of status, one of the things they wish to see is how many jobs have been completed this week month and year

within my tables is a field date. naturally it has the date submited contained within it. the code i have been using to try and get an output is

mysql_query("SELECT date, datediff('day', date, CURRENTDATE) FROM softwarerepairtable WHERE datediff<= '7' and complete='yes' ");

the complete feild is another contained within the datebase. i have also tried SELECT * datediff('day', date, CURRENTDATE) FROM softwarerepairtable WHERE datediff<= '7' and complete='yes' ");

unfortunatly my limited knowledge of php and sql is holding me back on this. any help guys would be appriacted

View 2 Replies View Related

Query On Datediff Function

May 18, 2007

Hi,



I am trying to use DataDiff function and I have used the following queries:



1.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.320') as test

Expected Result: 0 milliseconds

Actual Result: 0 milliseconds



2.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.321') as test

Expected Result: 1 milliseconds

Actual Result: 0 milliseconds



3.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.322') as test

Expected Result: 2 milliseconds

Actual Result: 3 milliseconds



4.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.323') as test

Expected Result: 3 milliseconds

Actual Result: 3 milliseconds



5.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.324') as test

Expected Result: 4 milliseconds

Actual Result: 3 milliseconds



6.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.325') as test

Expected Result: 5 milliseconds

Actual Result: 6 milliseconds



7.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.326') as test

Expected Result: 6 milliseconds

Actual Result: 6 milliseconds



8.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.327') as test

Expected Result: 7 milliseconds

Actual Result: 6 milliseconds



9.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.328') as test

Expected Result: 8 milliseconds

Actual Result: 6 milliseconds



10.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.329') as test

Expected Result: 9 milliseconds

Actual Result: 10 milliseconds



Does any one know, why datediff does not return the Expected Result? There does not seem to be any consistency.



Thanks,

Tim

View 1 Replies View Related

Datediff With Today Function

Jan 25, 2008

I want to create and expression that basically says if a datetime value is today minus 2 days then do another formula.

Right now it looks like this...




Code Snippet
=iif(Fields!Channel_Id.Value="SFE1" and Fields!Report_Date.Value=Today-2,0,(Fields!Total_Apps.Value-Fields!total_apps_null_status.Value))





Basically, it spits an error saying [BC30452] Operator '-' is not defined for types 'Date' and 'Integer'.

Should this be done with DateDiff? if so, how can you specify two days ago?

Thanks much

C-

View 5 Replies View Related

Use DateDiff Function Within Select Statement

Feb 3, 2015

I'm trying to use the DateDiff function within my select statement, but I'd like to add the parameter of greater than 30 days. This will have the query only return records where my bill stop date is greater than 30 days from the completion date. I currently have the datediff function within my select statement as

DATEDIFF (d,A.StopBillDate, a.CompletionDate) as [DIFFERENCE]

I would prefer to keep the datediff function within the select statement so as to have difference in days appear as a column within my output.I have been unable to add the parameter of > 30 days to the query without getting an error.

View 2 Replies View Related

Help Needed For Datediff Function For SQL Query

Apr 9, 2007

Hi Experts,I am working on SSRS 2005, and I am facing a problem in counting theno of days.My database has many fields but here I am using only two fieldsThey are Placement_Date and Discharge_DateIf child is not descharged then Discharge_Date field is empty.I am writing below query to count the number of days but is is notworking it is showing the error"The conversion of a char data type to a datetime data type resultedin an out-of-range datetime value."select casewhen convert(datetime,Discharge_Date,103) = '' thendatediff(day,CONVERT(datetime,Placement_Date,103), GETDATE())elsedatediff(day,CONVERT(datetime,Placement_Date,103),CONVERT(datetime,Discharge_Date,103))end NoOfDaysfrom Placement_DetailsSo please tell me where I am wrong?Any help will be appriciated.RegardsDinesh

View 3 Replies View Related

Transact SQL :: Error - The Datediff Function Resulted In Overflow

Sep 12, 2015

When running a query such as this on a database view:

select count(*) from WWALMDB.dbo.v_AlarmEventHistory2

SQL throws the following error:

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Oddly on a Table the count(*) function works.

Is there a limit on views or the count(*) function that I am not aware of?

View 3 Replies View Related

'Return' Statement In A Function,Get,or Operator Must Return A Value....Question

Aug 28, 2007


I'm a Reporting Services New-Be.

I'm trying to create a report that's based on a SQL-2005 Stored Procedure.

I added the Report Designer, a Report dataset ( based on a shared datasource).

When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.

I'll only reproduce one instance of the error for the sake of brevity.

[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.

I've searched on this error and it looks like it's a Visual Basic error :

http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx

My guess is that BIDS is creating some VB code behind the scenes for the report.

I can't find any other information that seems to fit this error.

The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.

BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.

Can anyone help ?

Thanks,

View 5 Replies View Related

Fetch Data Of User Who Have Created Profile Within 7 Days - DateDiff Function

May 2, 2015

I have added one webpage designed in ASP.Net with C# and sql server 2005 as database. There is table for user registration in which there is a column for ProfileCreationDate the data type of that column is date time .

I would like to fetch data of those user who have created profile within 7 days. For getting desired result I am trying this query.

select Name ,Profession,ProfileCreationDate from tblRegistration where DATEDIFF ( Day , '" + System.DateTime.Now + "',ProfileCreationDate)<7 order by ProfileCreationDate DESC

System.DateTime.Now is a function for getting current date time in C#

The query is neither giving error nor giving desired result.

View 4 Replies View Related

SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View

Jul 29, 2015

My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see

Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table

View 9 Replies View Related

Month-to-month Function

Sep 10, 2007

Hi I trying to find a way to determine the number of working days per month starting from the current date to the last day of the current month.And within the same store procedure determine the number of working days as normal (each month is independent from the next). For example: The store procedure is executed

September:
@CurrentDate = 9/10/2007
@EndDate = last working day 9/30/2007

Total# of working days = 15

October:
@CurrentDate = 10/1/2007
@EndDate = last working day 10/31/2007

Total# of working days = 23

November:
@CurrentDate = 11/1/2007
@EndDate = last working day 11/30/2007

Total# of working days = 22
etc.
Any ideas of how i can approch this?

Thanks in advance.

View 3 Replies View Related

How To Return Records Based On Previous Month

Jan 11, 2005

I'm storing records that contain a date/time data type. I am needing two links on a reports page (asp), the first should return all records for the current month and the second link should return all records for the last three months (including current month). I have no idea how to just sort by month.

I'm also not sure what to include here in this post to help you answer my question. On the form that is submitted initially the text field is named "txtSubmitDate" and in the database it's stored in a field called "submitdate" and is 8 characters in length.

I've tried:
'SELECT TODAY'S MONTH
SqlJunk = "SELECT * FROM eom WHERE MONTH(submitdate) = MONTH(GETDATE())-1"

'SELECT TODAY'S MONTH and the last 2 months
SqlJunk2 = "SELECT * FROM eom WHERE MONTH(submitdate) = MONTH(GETDATE()) OR MONTH(submitdate) = MONTH(GETDATE())-1 OR MONTH(submitdate) = MONTH(GETDATE())-2 ORDER BY submitdate ASC"

These are not working because it can't handle the change in year (going from january 2005 back to december 2004, etc).

Any ideas?

View 7 Replies View Related

Need To Return Month/YYYY And Sort By It In Report

Aug 20, 2007

I have a report that is grouped by Month and Year.

So I need it to display:

Jan, 2007
Feb, 2007
Mar, 2007
Apr, 2007
Etc…

AND it needs to be sorted with Jan as the first month and Dec as the last (as if it was 1-12 for months).

Now I can get it to display by taking the month name and concatenating it to the year, but then of course it won’t sort the way I want it to.

Any suggestions on this?

Perhaps I could/should return two values, the first being what I display on the report, and the second being what I sort on as a hidden field? So I could return 200701, 200702, 200703, and so on, to sort on that?

View 2 Replies View Related

Trying To Group Report Results By Year And Month By Other Variables

May 28, 2008

Below is the code. It gives 2 errors.
1 @StartDate must be defined (it is a report parameter)
2 I cannot seem to get this to create a 5 character field (yy/mm) so I can group count results by this field (Along with all the other queried field)
Help will be appreciated. DECLARE @t TABLE (Owner nvarchar(5), cdate nvarchar(7), status nvarchar(10), jtype nvarchar(5))
DECLARE @sowners nvarchar(5)
DECLARE @mydate datetime
DECLARE @sstatus nvarchar(10)
DECLARE @sjtype nvarchar(5)
DECLARE @chardate nvarchar(7)
DECLARE @Jmonth Int
DECLARE @Jyear Int
DECLARE @Jcharyear nvarchar(4)
DECLARE @Jcharmonth Nvarchar(2)
DECLARE jobcur CURSOR FAST_FORWARD READ_ONLY FOR SELECT Owners, createdate, UserField2, Jobtype
FROM Requirements
WHERE (((Owners IS NOT NULL) AND (Owners <> 'Par')) AND (CreateDate >= @StartDate))
OPEN JobCur
FETCH NEXT FROM JobCur INTO @sowners, @mydate, @sstatus, @sjtype
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Jyear = (Year(@mydate))
SET @Jmonth = (Month(@mydate))
SET @Jcharyear = CAST(@Jyear,4)
SET @Jcharmonth = CAST(@Jmonth,2)
IF Len(Jcharmonth) = 1
BEGIN
SET Jcharmonth = '0' + Jcharmonth
END
IF @Jmonth >= 10
BEGIN
SET @chardate = @Jyear + '/' + @Jmonth
END
IF @Jmonth < 10
BEGIN
SET @chardate = @Jyear + '/0' + @Jmonth
END
INSERT INTO @t (Owner, cdate, status, jtype)
VALUES (@sowners,@chardate,@sstatus,@sjtype)
FETCH NEXT FROM jobcur INTO @sowners, @mydate, @sstatus, @sjtype
END
CLOSE jobcur
DEALLOCATE jobcur
BEGIN
SELECT * FROM @t
END

View 6 Replies View Related

Function To Get End Of The Month

Jul 25, 2013

I am using sql server 2005. I am trying to create a function that gives me the end of the month. I know how to get the current date.

What I ultimately want to do is display how many days are left in the month no matter what month I am currently in.

Upon research I found EOMONTH [URL] .....

But that doesn't work for server 2005.

View 3 Replies View Related

End Of The Month Function

Feb 27, 2008

Using RS 2000, is there a way to schedule a report for email delivery on the last day of each month. It looks like that would have to be a change made to the Report Manager. Is it possible to reconfigure the report manager to add a last day of the month or month end option?

thanks in advance
OS

View 3 Replies View Related

T-SQL (SS2K8) :: How To Return 3 Month Rolling Average Count Per Username

Mar 30, 2015

how to return the 3 month rolling average count per username? This means, that if jan = 4, feb = 5, mar = 5, then 3 month rolling average will be 7 in April. And if apr = 6, the May rolling average will be 8.

Columns are four:

username, current_tenure, move_in_date, and count.

DDL (create script generated by SSMS from sample table I created, which is why the move_in_date is in hex form. When run it's converted to date. Total size of table 22 rows, 4 columns.)

CREATE TABLE [dbo].[countHistory](
[username] [varchar](50) NULL,
[current_tenure] [int] NULL,
[move_in_date] [smalldatetime] NULL,
[Cnt_Lead_id] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

[code]....

View 9 Replies View Related

T-SQL (SS2K8) :: Return Value In A Status Field Which Has Latest Year And Month

May 11, 2015

I have table in which month & year are stored, Like this

Month Year
10 2014
11 2014
12 2014
1 2015
2 2015
3 2015
4 2015

I wanted a query in which it should return the value in a status field which has latest year & month.

View 9 Replies View Related

Pls Help W/ A Query To Return Running Balances From The Previous Rolling 3 Month Period.

Oct 18, 2007

Please refer to this table in this discussion:






Charges

Date


1

1/1/07


2

2/1/07


3

3/1/07


4

4/1/07


5

5/1/07


6

6/1/07


7

7/1/07


8

8/1/07


9

9/1/07


10

10/1/07


11

11/1/07


12

12/1/07
What i'm trying to do is return a result with total charges from 3 months previous based on a date parameter or just as a whole for that matter. For example:

If my date parameter is 6/14/07, i'd like my total charges to be 15 (6+5+4).
If my date parameter is 7/10/07, the charges would be 18 (7+6+5)

I hope that makes sense of where i'm going with this. I've played with this using the "Guru's Guide" solution by Ken Henderson, but still to no avail yet. Here's his code syntax:





Code Block

SELECT a.DayCount, a.Sales, SUM(b.Sales)
FROM Sales a CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales
Here is the result set i'm looking for:






Charges

Date


1

1/1/07


2

2/1/07


6

3/1/07


9

4/1/07


12

5/1/07


15

6/1/07


18

7/1/07


21

8/1/07


24

9/1/07


27

10/1/07


30

11/1/07


33

12/1/07
Each date's charges are a culmination of 3 months worth of charges.

Any help would be greatly appreciated. I'll be trying to figure this one out concurrently and if i do, i'll post back!

Thanks in advance!

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved