February 3, 2010 at 7:39 am
Hi All, I hope you can help.
I have a query that produces the total price for a certain date range. This date range can be any date.
For example the date range maybe 1st Jan 2010 – 1st June 2010.
What I want to be able to do is divide this period into months, so for this example there will be five months.
1st Jan
1st Feb
1st Mar
1st Apr
1st May
And for each month calculate the price value. This can be the total value divided by the number of months
The output would look like this:
1st Jan1st Feb 1st Mar 1st Apr1st May
5050505050
So what I’m asking is there anyway I can split a date range into months and display this?
If I’ve not made myself clear then please let me know.
Thanks.
February 3, 2010 at 7:59 am
Yes, it could be done. How to do it in your situation, that is a different story. It would help if you could post the table definition(s) (as CREATE TABLE statement(s) for the table(s) involved), sample data (as a series of INSERT INTO statements for the table(s) involved), expected results based on the sample data, and the code you have written so far.
February 3, 2010 at 8:40 am
OK the following script will create a cut down version of the table in question. If you need the whole table then let me know but I am only interested in these columns.
CREATE TABLE [dbo].[Bell_Invoice](
[InvoiceLineID] [int] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[Price] [money] NOT NULL,
CONSTRAINT [PK_Bell_Invoice] PRIMARY KEY CLUSTERED
(
[InvoiceLineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Here are some INSERT INTO Statements so dummy data can be loaded.
INSERT INTO dbo.Bell_Invoice
VALUES (1,'01/01/2010', '06/01/2010', 500)
INSERT INTO dbo.Bell_Invoice
VALUES (2,'11/23/2010', '06/01/2010', 100)
INSERT INTO dbo.Bell_Invoice
VALUES (3,'08/18/2010', '12/12/2010', 200)
INSERT INTO dbo.Bell_Invoice
VALUES (4,'06/12/2010', '11/26/2010', 950)
INSERT INTO dbo.Bell_Invoice
VALUES (5,'09/28/2010', '10/30/2010', 50)
INSERT INTO dbo.Bell_Invoice
VALUES (6,'09/30/2010', '12/17/2010', 1000)
INSERT INTO dbo.Bell_Invoice
VALUES (7,'04/07/2010', '06/19/2010', 650)
INSERT INTO dbo.Bell_Invoice
VALUES (8,'01/05/2010', '10/06/2010', 400)
INSERT INTO dbo.Bell_Invoice
VALUES (9,'07/09/2010', '11/01/2010', 800)
INSERT INTO dbo.Bell_Invoice
VALUES (10,'05/01/2010', '07/09/2010', 100)
The code I have at the moment is a basic statement that sums the price column based on the start and end date supplied.
select SUM(price) from dbo.Bell_Invoice
WHEN StartDate <= @EndDate and EndDate >=@StartDate
Which gives me results like:
Price
4750
The start and end date will always have the same day, i.e, 1st Jan - 1st June. It will never be 1st Jan - 6th June for example.
What I need is to be able to produce results like:
Jan 1stFeb 1st Mar1st
230800650
I hope this makes sense.
February 3, 2010 at 8:46 am
Hi,
I'm used to the dateformat YYYY-MM-DD, you can use your dateformat instead to fill the table...
create table #a
(
idint IDENTITY(1,1),
date datetime,
price money
)
insert into #a VALUES ('2010-01-12', 10)
insert into #a VALUES ('2010-01-22', 20)
insert into #a VALUES ('2010-02-01', 30)
insert into #a VALUES ('2010-02-25', 40)
insert into #a VALUES ('2010-03-15', 50)
insert into #a VALUES ('2010-03-20', 60)
insert into #a VALUES ('2010-03-30', 70)
insert into #a VALUES ('2010-04-01', 80)
insert into #a VALUES ('2010-04-12', 90)
insert into #a VALUES ('2010-04-17', 10)
insert into #a VALUES ('2010-04-19', 20)
insert into #a VALUES ('2010-05-03', 30)
insert into #a VALUES ('2010-05-22', 40)
select datepart(year, date), datename(month, date), sum(price)
from #a
where date between '2010-02-01' and '2010-04-30'
group by datepart(year, date), datename(month, date)
Hope it helps!
/Markus
February 4, 2010 at 8:17 am
SLLRDK (2/3/2010)
...INSERT INTO dbo.Bell_Invoice
VALUES (1,'01/01/2010', '06/01/2010', 500)
INSERT INTO dbo.Bell_Invoice
VALUES (2,'11/23/2010', '06/01/2010', 100)
...
select SUM(price) from dbo.Bell_Invoice
WHEN StartDate <= @EndDate and EndDate >=@StartDate
Which gives me results like:
Price
4750
...
What I need is to be able to produce results like:
Jan 1stFeb 1st Mar1st
230800650
I hope this makes sense.
Hi,
I can see that your invoices span over several months...
How do you want to handle the amount from the first invoice (span from '01/01/2010' to '06/01/2010', with amount 500) on a query on the span 01/01/2010 to 07/01/2010?
Should the amount add to each month:
Year, month, value
2010, 01, 500
2010, 02, 500
2010, 03, 500
2010, 04, 500
2010, 05, 500
Or do you want it to be divided over the months:
Year, month, value
2010, 01, 100
2010, 02, 100
2010, 03, 100
2010, 04, 100
2010, 05, 100
And what if the invoice span over parts of a month (01/15/2010 - 06/01/2010)?
Year, month, value
2010, 01, 55.5
2010, 02, 111.1
2010, 03, 111.1
2010, 04, 111.1
2010, 05, 111.1
And if you are dividing over months, are you really after dividing over days?
Year, month, value
2010, 01, 62.0
2010, 02, 102.2
2010, 03, 113.1
2010, 04, 109.5
2010, 05, 113.1
Or du you simply want the amount from the invoice to add to the month of the end-date (or start-date)?
Depending of what you're after, the solution could be easy or very complex...
/Markus
February 5, 2010 at 2:17 am
Thanks for your replies.
The amount needs to be dived over the months and your right some invoices will span over part months like (01/15/2010 - 06/01/2010).
So for example an invoice has a start date of 01/01/2010 and an end date of 01/30/2010 with a value of 500.
But the query runs from the 01/15/2010 to 02/15/2010 the result would be
Jan 15th - Feb 14th Feb 15th - March 14th
250 0
The query I have at the moment is:
DECLARE @StartDate AS VARCHAR(10)
DECLARE @EndDate AS VARCHAR(10)
SET @StartDate = '01/01/2010'
SET @EndDate = '01/06/2010'
SELECT CASE WHEN tblInvoiceLine.dteFromDate >= @StartDate and tblInvoiceLine.dteToDate >= @EndDate
THEN SUM(dbo.fCalcNumBusDays(tblInvoiceLine.dteFromDate,@EndDate) * tblInvoiceLine.curBasic/dbo.fCalcNumBusDays(tblInvoiceLine.dteFromDate, tblInvoiceLine.dteToDate))
WHEN tblInvoiceLine.dteFromDate<= @StartDate and tblInvoiceLine.dteToDate <= @EndDate
THEN SUM(dbo.fCalcNumBusDays(@StartDate, tblInvoiceLine.dteToDate) * tblInvoiceLine.curBasic/dbo.fCalcNumBusDays(tblInvoiceLine.dteFromDate, tblInvoiceLine.dteToDate))
WHEN tblInvoiceLine.dteFromDate >= @StartDate and tblInvoiceLine.dteToDate <= @EndDate
THEN SUM(dbo.fCalcNumBusDays(tblInvoiceLine.dteFromDate, tblInvoiceLine.dteToDate) * tblInvoiceLine.curBasic/dbo.fCalcNumBusDays(tblInvoiceLine.dteFromDate, tblInvoiceLine.dteToDate))
WHEN tblInvoiceLine.dteFromDate <= @StartDate and tblInvoiceLine.dteToDate >= @EndDate
THEN SUM(dbo.fCalcNumBusDays(@StartDate,@EndDate) * tblInvoiceLine.curBasic/dbo.fCalcNumBusDays(tblInvoiceLine.dteFromDate, tblInvoiceLine.dteToDate))
END AS 'Price'
FROMtblInvoiceLine
WHEREtblInvoiceLine.dteFromDate <= @EndDate
AND tblInvoiceLine.dteToDate >= @StartDate
GROUP BY dteFromDate,dteToDate
This has a function that calculates the number of working days in the date period. And the result is a sum of the vlaues.
What I want to be able to do is calculate the result for each month of the query.
February 5, 2010 at 9:46 pm
Please see the following article... it has code to do nearly precisely what you want and will also zero fill any missing data. The best part is, you won't have to change the code to get dates to "slide" in a window...
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply