February 3, 2015 at 10:35 am
I have some location assignment data that I need to convert. It's hard to explain so here goes.
I need to know how long each account spent in a certain location for each month of it's overall startdate/enddate period.
E.g.
Account 1 stayed in USA for 31 days in January, and 15 days in February.
Account 1 stayed in UK for 13 days in February and 26 days in March.
Etc.
create table #temp(account int, loc varchar(10), startdate datetime, enddate datetime)
insert into #temp select 1,'USA','2014-01-01','2014-02-15'
insert into #temp select 1,'UK','2014-02-16','2014-03-26'
insert into #temp select 1,'AU','2014-03-27','2014-06-07'
insert into #temp select 2,'UK','2014-08-15','2014-09-01'
insert into #temp select 2,'AU','2014-09-02','2014-10-17'
select * from #temp
drop table #temp
Thanks in advance for any help/suggestions.
February 3, 2015 at 10:43 am
i might be reading the question too simply;
can you just sum up the differences btween the two dates?
--days
/*--Results
account loc (No column name)
1 AU 72
2 AU 45
1 UK 38
2 UK 17
1 USA 45
*/
select account,SUM(DATEDIFF(dd,startdate,enddate)) from #temp group by account
select account,loc,SUM(DATEDIFF(dd,startdate,enddate)) from #temp group by account,loc
--hours?
select account,SUM(DATEDIFF(hh,startdate,enddate)) from #temp group by account
select account,loc,SUM(DATEDIFF(hh,startdate,enddate)) from #temp group by account,loc
Lowell
February 3, 2015 at 1:31 pm
Lowell (2/3/2015)
i might be reading the question too simply;can you just sum up the differences btween the two dates?
--days
/*--Results
account loc (No column name)
1 AU 72
2 AU 45
1 UK 38
2 UK 17
1 USA 45
*/
select account,SUM(DATEDIFF(dd,startdate,enddate)) from #temp group by account
select account,loc,SUM(DATEDIFF(dd,startdate,enddate)) from #temp group by account,loc
--hours?
select account,SUM(DATEDIFF(hh,startdate,enddate)) from #temp group by account
select account,loc,SUM(DATEDIFF(hh,startdate,enddate)) from #temp group by account,loc
Thanks for your reply. Unfortunately, that doesn't quite give me what I need. I need to know how many days per account per location per month. Your results gave me per account per location.
February 3, 2015 at 1:48 pm
Have you considered using a calendar table to join with the date ranges and use COUNT() grouping by account, location and month? Do you need an example?
February 3, 2015 at 1:51 pm
in that case, you need to join against a calendar of months, and simply group by month
something like this is an example
CREATE TABLE #TallyMonthCalendar(TheMonthStart date)
INSERT INTO #TallyMonthCalendar
SELECT dateadd( mm,-48 + RW ,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) As TheDate
FROM (
SELECT TOP 96
row_number() OVER (ORDER BY sc1.id) AS RW
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2
) X
SELECT account,
loc,
Dateadd(mm, Datediff(mm, 0, startdate), 0) AS TheMonth,
Sum(Datediff(dd, startdate, enddate))
FROM #temp
LEFT JOIN #TallyMonthCalendar
ON Dateadd(mm, Datediff(mm, 0, startdate), 0) = #TallyMonthCalendar.TheMonthStart
GROUP BY account,
loc,
Dateadd(mm, Datediff(mm, 0, startdate), 0)
ORDER BY Dateadd(mm, Datediff(mm, 0, startdate), 0)
if you need to show the gaps, whetere there was no hours worked in a month, you need to join on the calendar first:
SELECT account,
loc,
TheMonthStart,
Sum(Datediff(dd, startdate, enddate))
FROM #TallyMonthCalendar
LEFT JOIN #temp
ON Dateadd(mm, Datediff(mm, 0, startdate), 0) = #TallyMonthCalendar.TheMonthStart
GROUP BY account,
loc,
TheMonthStart
ORDER BY TheMonthStart
Lowell
February 3, 2015 at 2:41 pm
I think a "standard tally table" (table of seq numbers from 0 to some big number) is all you really need.
--add up the days
SELECT account, loc, CONVERT(varchar(7), month, 111) AS month, SUM(days) AS days
FROM (
--calc the days for each individual month:
-- can run this query stand-alone to check results row by row, month by month
SELECT
account,
DATEADD(MONTH, months.tally, 0) AS month,
loc,
DATEDIFF(DAY,
CASE WHEN months.tally = DATEDIFF(MONTH, 0, startdate)
THEN startdate
ELSE DATEADD(MONTH, months.tally, 0) END,
CASE WHEN months.tally = DATEDIFF(MONTH, 0, enddate)
THEN DATEADD(DAY, 1, enddate)
ELSE DATEADD(MONTH, months.tally + 1, 0) END) AS days
FROM #temp
INNER JOIN dbo.tally months ON --"standard" tally table, just seq numbers from 0 to, say, 1M
months.tally BETWEEN DATEDIFF(MONTH, 0, startdate) AND DATEDIFF(MONTH, 0, enddate)
--ORDER BY account, loc, month
) AS derived
GROUP BY
account, loc, month
ORDER BY
account, loc, month
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 3, 2015 at 6:33 pm
As it turns out, I have a pretty efficient Calendar Table-generating function that makes short work of this:
create table #temp(account int, loc varchar(10), startdate datetime, enddate datetime);
insert into #temp select 1,'USA','2014-01-01','2014-02-15';
insert into #temp select 1,'UK','2014-02-16','2014-03-26';
insert into #temp select 1,'AU','2014-03-27','2014-06-07';
insert into #temp select 2,'UK','2014-08-15','2014-09-01';
insert into #temp select 2,'AU','2014-09-02','2014-10-17';
SELECT account, loc, startdate=MAX(startdate), enddate=MAX(enddate)
,[Year]
,[Month]
,[Days]=COUNT(*)
FROM #temp a
CROSS APPLY dbo.GenerateCalendar(startdate, 1+DATEDIFF(day, startdate, enddate))
GROUP BY account, loc, [Year], [Month]
ORDER BY account, loc, [Year], [Month];
GO
drop table #temp;
It makes the code pretty sweet and simple too!
If you're interested, you can find the GenerateCalendar function here: Calendar Tables in T-SQL[/url]
Jeff Moden contributed to making this function about as top-performing as it can get, while at the same time calculating a whole plethora of interesting day-related values.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply