December 23, 2009 at 11:14 pm
Dear All ,
Please find below the data
MonthStart MonthEnd MonthTotalDaysAmount
Mar3/9/09 0:003/13/09 0:004 71
Mar3/23/09 0:004/5/09 0:0014 75
I need a query which returns
MonthStart MonthEnd MonthTotalDaysAmount
Mar3/9/09 0:003/13/09 0:004 71
Mar3/23/09 0:003/31/09 0:009 48.21429
Apr4/1/09 0:004/5/09 0:005 26.78571
Please help in this regards
Regards
Prakash
December 24, 2009 at 12:30 am
Please share the table structure & some of the datas stored in it. So that others can help u.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 24, 2009 at 12:38 am
December 24, 2009 at 1:31 am
Hi Prakash,
Assumption of the parameter passed to table, try this
declare @fromdate datetime,@todate datetime,@amount numeric(10,2)
set @fromdate = '2009-10-05'
set @todate = '2010-10-20'
set @amount = 10000
set nocount on
/*table for month detais*/
declare @abc table
(slno int IDENTITY(1,1),MonthName CHAR(11))
/*IDENTITY insert to month table */
WHILE 1 = 1
BEGIN
INSERT INTO @abc DEFAULT VALUES
IF @@IDENTITY = 1000
BEGIN
BREAK
END
END
select slno,MonthName,
[Start Month],
[End Month],
datediff(day,[Start Month],[End Month])+1 [TotalDays],
(@amount/(datediff(day,@fromdate,@todate))* datediff(day,[Start Month],[End Month]))[Amount]
from
(select
slno,
MonthName,
case when cast(datename(mm,@fromdate) as varchar(3))= left(MonthName,3) and year(@fromdate)= right(MonthName,4)
then @fromdate else cast(('01/'+replace(MonthName,' ','/'))as datetime)end [Start Month],
case when cast(datename(mm,@todate) as varchar(3))= left(MonthName,3) and year(@todate)= right(MonthName,4)
then @todate else
DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,cast(('01/'+replace(MonthName,' ','/'))as datetime))+1,0)))end [End Month]
from
(SELECT slno,STUFF(CONVERT(CHAR(11),DATEADD(mm, slno-1, @fromdate),100),4,3,'') AS MonthName
FROM @abc
WHERE slno <= DATEDIFF(mm,@fromdate,dateadd(m,+1,@todate))) as x) as y
set nocount off
December 24, 2009 at 1:46 am
I haven't check the rest of your good post, but don't ever use @@IDENTITY... if a trigger is ever place on the table in question, @@IDENTITY will return incorrect answers. Use SCOPE_IDENTITY() or OUTPUT instead.
--Jeff Moden
December 24, 2009 at 1:47 am
declare @start_date datetime
declare @end_date datetime
declare @diff int
set @start_date = '3/9/2009'
set @end_date = '3/13/2009'
set @diff = datediff(dd,@start_date,@end_date)
create table #Results ([Month Start] datetime, [Month End] datetime, [Total Days] int, [Amount] money)
insert into #Results ([Month Start], [Month End], [Total Days], [Amount])
select @start_date [Month Start], @end_date [Month End], @diff [Total Days], sum(tbl.value) [Amount]
from [table_name] tbl
where tbl.[date_value] >= @start_date and tbl.[date_value] < @end_date --optional "< @end_date+1"
group by @start_date, @end_date, @diff
set @start_date = '3/23/2009'
set @end_date = '3/31/2009'
set @diff = datediff(dd,@start_date,@end_date)
insert into #Results ([Month Start], [Month End], [Total Days], [Amount])
select @start_date [Month Start], @end_date [Month End], @diff [Total Days], sum(tbl.value) [Amount]
from [table_name] tbl
where tbl.[date_value] >= @start_date and tbl.[date_value] < @end_date --optional "< @end_date+1"
group by @start_date, @end_date, @diff
set @start_date = '4/1/2009'
set @end_date = '4/5/2009'
set @diff = datediff(dd,@start_date,@end_date)
insert into #Results ([Month Start], [Month End], [Total Days], [Amount])
select @start_date [Month Start], @end_date [Month End], @diff [Total Days], sum(tbl.value) [Amount]
from [table_name] tbl
where tbl.[date_value] >= @start_date and tbl.[date_value] < @end_date --optional "< @end_date+1"
group by @start_date, @end_date, @diff
select * from #Results order by [Month Start]
December 24, 2009 at 1:54 am
freeman.e.l (12/24/2009)
declare @start_date datetime
declare @end_date datetime
declare @diff int
set @start_date = '3/9/2009'
set @end_date = '3/13/2009'
set @diff = datediff(dd,@start_date,@end_date)
create table #Results ([Month Start] datetime, [Month End] datetime, [Total Days] int, [Amount] money)
insert into #Results ([Month Start], [Month End], [Total Days], [Amount])
select @start_date [Month Start], @end_date [Month End], @diff [Total Days], sum(tbl.value) [Amount]
from [table_name] tbl
where tbl.[date_value] >= @start_date and tbl.[date_value] < @end_date --optional "< @end_date+1"
group by @start_date, @end_date, @diff
set @start_date = '3/23/2009'
set @end_date = '3/31/2009'
set @diff = datediff(dd,@start_date,@end_date)
insert into #Results ([Month Start], [Month End], [Total Days], [Amount])
select @start_date [Month Start], @end_date [Month End], @diff [Total Days], sum(tbl.value) [Amount]
from [table_name] tbl
where tbl.[date_value] >= @start_date and tbl.[date_value] < @end_date --optional "< @end_date+1"
group by @start_date, @end_date, @diff
set @start_date = '4/1/2009'
set @end_date = '4/5/2009'
set @diff = datediff(dd,@start_date,@end_date)
insert into #Results ([Month Start], [Month End], [Total Days], [Amount])
select @start_date [Month Start], @end_date [Month End], @diff [Total Days], sum(tbl.value) [Amount]
from [table_name] tbl
where tbl.[date_value] >= @start_date and tbl.[date_value] < @end_date --optional "< @end_date+1"
group by @start_date, @end_date, @diff
select * from #Results order by [Month Start]
That's nice... maybe I'm reading the code incorrectly but I believe the goal is to automatically sense the change in dates... not hard code them. 😉
--Jeff Moden
December 24, 2009 at 1:56 am
Hi Jeff,
But still am using the @@IDENTITY, yet no issue raised, ok will check your advice.
December 24, 2009 at 2:03 am
arun.sas (12/24/2009)
Hi Jeff,But still am using the @@IDENTITY, yet no issue raised, ok will check your advice.
Ah... sorry, my bad.l I just started reading your code more in depth and now see that you're using @@IDENTITY on a table variable. Obviously, no chance of a trigger being there but it's still not a good habit to get into. If a trigger is present on a table that you're inserting into and that trigger inserts into yet another table that has an identity column, @@IDENTITY can and will return the IDENTITY value of that second table which is probably not what want.
Also... heh... what's up with the While Loop and the -3 ms? :hehe: I thought you've been on this forum long enough to know ways around those types of things. 😉
--Jeff Moden
December 24, 2009 at 2:08 am
MonthStart MonthEnd MonthTotalDaysAmount
Mar3/9/09 0:003/13/09 0:004 71
Mar3/23/09 0:004/5/09 0:0014 75
I need a query which returns
MonthStart MonthEnd MonthTotalDaysAmount
Mar3/9/09 0:003/13/09 0:004 71
Mar3/23/09 0:003/31/09 0:009 48.21429
Apr4/1/09 0:004/5/09 0:005 26.78571
Looks like the forum code ate up the nice spacing that Prakash tried to post... I posted his quote in a "code plain" IFCode above just to make it a little easier on the eyes.
--Jeff Moden
December 24, 2009 at 9:51 am
The question was not clear by any means. Additionally, the second example does not have a logical relationship that holds true for Start Date, End Date and TotalDays.
December 24, 2009 at 9:58 am
freeman.e.l (12/24/2009)
The question was not clear by any means. Additionally, the second example does not have a logical relationship that holds true for Start Date, End Date and TotalDays.
I thought it was quite clear. Any dates that span a month boundry have to be split at the month boundry along with the amounts. Excellent catch on the data discrepancy and I agree that it didn't help that the TotalDays for the first entry was actually calculated incorrectly by the OP (according to the rest of his output example)... there are actually 5 days from 3/9 to 3/13 inclusive.
Heh... Let's be honest here.... would you write such a hard-coded solution for yourself and would your boss love you for it? 😉 You also caught the discrepancy in the posted data... Would your boss love you for not asking the question about the discrepancy in the written requirements (as sparse as they were)? Pretend these problems found on the forum are real problems for you because, for the people posting them, they are.
Thanks for the feedback... I'll be back in a minute with a solution to this problem.
--Jeff Moden
December 24, 2009 at 10:14 am
Prakash-485822 (12/23/2009)
Dear All ,Please find below the data
MonthStart MonthEnd MonthTotalDaysAmountMar3/9/09 0:003/13/09 0:004 71
Mar3/23/09 0:004/5/09 0:0014 75
I need a query which returns
MonthStart MonthEnd MonthTotalDaysAmount
Mar3/9/09 0:003/13/09 0:004 71
Mar3/23/09 0:003/31/09 0:009 48.21429
Apr4/1/09 0:004/5/09 0:005 26.78571
Please help in this regards
Regards
Prakash
Heh... Prakash, you made a bit of a mistake in the TotalDays column and it threw some folks for a loop (including me) until I understood that you made a mistake. If you include both the StartMonth and EndMonth dates like you did everywhere else in your example, there are actually 5 days from 3/9 to 3/13 inclusive. Count them on your fingers and see.
Anyway, Arun was definitely on the right track... This isn't such a difficult problem if you have a nice Tally table to work with. You can either build one on the fly just for this problem or you can build a permanent one. Heh... just don't use a loop to build either. For information on a couple of ways how to build a Tally table and how it works to replace loops in a set based fashion, please see the following article.
http://qa.sqlservercentral.com/articles/T-SQL/62867/
As a bit of a side bar (since you're kind of new to this forum), you need to do three things on future posts...
1. Post a written description along with your data. People who want to help you don't necessarily have the time to analyze your data to figure out what you want to do especially when you have errors in the data you posted.
2. Post table creation and data creation code in a readily consumable format. Don't assume that you know how to do that. Read and heed the following article and people will give you much more accurate code answers much more quickly because, like me, they like to test their answers before they post them. If you don't do this, people like me are going to start ignoring your questions. Seriously... Here's the link.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
3. Learn how to use the IFCode shortcuts posted on the left of the message window when you're creating a post... as you found out, posting without these does things like eat up repetative spaces and all manner of other formatting annoyances.
Ok... I'll get off the soap box now... here's a solution that works. Like I said above, it does use a Tally table so go read the Tally table article and find a new world of easy, fast, set based code...
--===== Create and populate a test table.
-- This is NOT a part of the solution
IF OBJECT_ID('TempDB..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable;
CREATE TABLE #TestTable
(
Month CHAR(3),
StartMonth DATETIME,
EndMonth DATETIME,
TotalDays INT,
Amount INT
);
INSERT INTO #TestTable
(Month, StartMonth, EndMonth, TotalDays, Amount)
SELECT 'Mar','3/9/09 0:00','3/13/09 0:00','4','71' UNION ALL
SELECT 'Mar','3/23/09 0:00','4/5/09 0:00','14','75';
--===== All set with the test data creation... solve the problem.
-- Do notice that the first row of data above has an incorrect
-- number of TotalDays. 9 to 13 inclusive is 5 days, not 4.
-- Count them on your fingers... you will see.
WITH
ctePreNumber AS
( --=== Use a cross-join to generate all dates and assign
-- "groups" of StartMonth days and NumericMonths so
-- we can easily group on them to create the final output.
-- This also splits amounts into decimal daily values even
-- if the original AMOUNT column is an INT.
SELECT StartMonth + t.N -1 AS Date,
DATEDIFF(dd,0,StartMonth) AS NumericStartDay,
DATEDIFF(mm,0,StartMonth + t.N -1) AS NumericMonth,
1.0 * Amount/(DATEDIFF(dd,StartMonth,EndMonth) + 1) AS DailyAmount
FROM #TestTable test
CROSS JOIN dbo.Tally t
WHERE t.N BETWEEN 1 AND DATEDIFF(dd, test.StartMonth, test.EndMonth) + 1
) --=== This simply aggregates the individual date rows we made above.
-- Notice how the GROUP BY uses the "groups" we made above.
SELECT LEFT(DATENAME(mm,MIN(Date)),3) AS Month,
MIN(Date) AS StartMonth,
MAX(Date) AS EndMonth,
DATEDIFF(dd, MIN(Date), MAX(Date)) + 1 AS TotalDays,
CAST(SUM(DailyAmount) AS DECIMAL(9,5)) AS Amount
FROM ctePreNumber
GROUP BY NumericMonth, NumericStartDay
ORDER BY StartMonth;
--Jeff Moden
December 24, 2009 at 10:46 am
Ah... one other thing... just to prove that it works across multiple month boundaries and even year boundaries, here's a change in the data. My recommendation would be that you also make some changes... column names like StartMonth for a date that isn't the start of a month don't make sense. The output of the Month column also loses it's value if more than 1 year is traversed. With that in mind, I recommend the following which deviates from your requested output, but will likely make your boss happy... it also makes the "Month" sortable on spreadsheets and the like. With that in mind, here's my recommendations in the form of code... not much has changed...
--===== Create and populate a test table.
-- This is NOT a part of the solution
IF OBJECT_ID('TempDB..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable;
CREATE TABLE #TestTable
(
Month CHAR(3),
StartMonth DATETIME,
EndMonth DATETIME,
TotalDays INT,
Amount INT
);
INSERT INTO #TestTable
(Month, StartMonth, EndMonth, TotalDays, Amount)
SELECT 'Mar','3/9/09 0:00','3/13/09 0:00','4','71' UNION ALL
SELECT 'Mar','3/23/09 0:00','4/5/09 0:00','14','75' UNION ALL
SELECT 'XXX','01/15/2009','12/15/2010','00','2000';
--===== All set with the test data creation... solve the problem.
-- Do notice that the first row of data above has an incorrect
-- number of TotalDays. 9 to 13 inclusive is 5 days, not 4.
-- Count them on your fingers... you will see.
WITH
ctePreNumber AS
(--==== Use a cross-join to generate all dates and assign
-- "groups" of StartMonth days and NumericMonths so
-- we can easily group on them to create the final output.
-- This also splits amounts into decimal daily values even
-- if the original AMOUNT column is an INT.
SELECT StartMonth + t.N -1 AS Date,
DATEDIFF(dd,0,StartMonth) AS NumericStartDay,
DATEDIFF(mm,0,StartMonth + t.N -1) AS NumericMonth,
1.0 * Amount/(DATEDIFF(dd,StartMonth,EndMonth) + 1) AS DailyAmount,
StartMonth
FROM #TestTable test
CROSS JOIN dbo.Tally t
WHERE t.N BETWEEN 1 AND DATEDIFF(dd, test.StartMonth, test.EndMonth) + 1
)--==== This simply aggregates the individual date rows we made above.
-- Notice how the GROUP BY uses the "groups" we made above.
SELECT CONVERT(CHAR(7),MIN(Date),120) AS Month,
MIN(Date) AS StartDate,
MAX(Date) AS EndDate,
DATEDIFF(dd, MIN(Date), MAX(Date)) + 1 AS TotalDays,
CAST(SUM(DailyAmount) AS DECIMAL(9,5)) AS Amount
FROM ctePreNumber pn
GROUP BY NumericMonth, NumericStartDay
ORDER BY Month;
Notice that I didn't rely on the Month or TotalDays column in the original data because of the data discrepancy we found in the original data. Here's the output from the code above...
Month StartDate EndDate TotalDays Amount
------- ----------------------- ----------------------- ----------- ---------------------------------------
2009-01 2009-01-15 00:00:00.000 2009-01-31 00:00:00.000 17 48.57143
2009-02 2009-02-01 00:00:00.000 2009-02-28 00:00:00.000 28 80.00000
2009-03 2009-03-23 00:00:00.000 2009-03-31 00:00:00.000 9 48.21429
2009-03 2009-03-01 00:00:00.000 2009-03-31 00:00:00.000 31 88.57143
2009-03 2009-03-09 00:00:00.000 2009-03-13 00:00:00.000 5 71.00000
2009-04 2009-04-01 00:00:00.000 2009-04-30 00:00:00.000 30 85.71429
2009-04 2009-04-01 00:00:00.000 2009-04-05 00:00:00.000 5 26.78571
2009-05 2009-05-01 00:00:00.000 2009-05-31 00:00:00.000 31 88.57143
2009-06 2009-06-01 00:00:00.000 2009-06-30 00:00:00.000 30 85.71429
2009-07 2009-07-01 00:00:00.000 2009-07-31 00:00:00.000 31 88.57143
2009-08 2009-08-01 00:00:00.000 2009-08-31 00:00:00.000 31 88.57143
2009-09 2009-09-01 00:00:00.000 2009-09-30 00:00:00.000 30 85.71429
2009-10 2009-10-01 00:00:00.000 2009-10-31 00:00:00.000 31 88.57143
2009-11 2009-11-01 00:00:00.000 2009-11-30 00:00:00.000 30 85.71429
2009-12 2009-12-01 00:00:00.000 2009-12-31 00:00:00.000 31 88.57143
2010-01 2010-01-01 00:00:00.000 2010-01-31 00:00:00.000 31 88.57143
2010-02 2010-02-01 00:00:00.000 2010-02-28 00:00:00.000 28 80.00000
2010-03 2010-03-01 00:00:00.000 2010-03-31 00:00:00.000 31 88.57143
2010-04 2010-04-01 00:00:00.000 2010-04-30 00:00:00.000 30 85.71429
2010-05 2010-05-01 00:00:00.000 2010-05-31 00:00:00.000 31 88.57143
2010-06 2010-06-01 00:00:00.000 2010-06-30 00:00:00.000 30 85.71429
2010-07 2010-07-01 00:00:00.000 2010-07-31 00:00:00.000 31 88.57143
2010-08 2010-08-01 00:00:00.000 2010-08-31 00:00:00.000 31 88.57143
2010-09 2010-09-01 00:00:00.000 2010-09-30 00:00:00.000 30 85.71429
2010-10 2010-10-01 00:00:00.000 2010-10-31 00:00:00.000 31 88.57143
2010-11 2010-11-01 00:00:00.000 2010-11-30 00:00:00.000 30 85.71429
2010-12 2010-12-01 00:00:00.000 2010-12-15 00:00:00.000 15 42.85714
--Jeff Moden
December 24, 2009 at 2:21 pm
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply