Please help with SQL statement.
-
How to find records with historical gaps?
Let me explain the problem. For example I have table:
CustomerID Month Year
------------------------------------------
X 5 2003
X 6 2003
X 7 2003
X 8 2003
X 9 2003
X 11 2003
X 12 2003
X 1 2004
X 2 2004
X 3 2004
As you can see this customer has missed one record for month 10. So question is how to find all customers who have at least one missed record?
Thank you for any effort.
P.S. Historically records could start and finish on a middle of year. For example, for this customer there are records just from 5/2003 to 3/2004.
-
How about something like this :
Select CustomerID, Year, Count(*)
from dbo.HistoryTable
group by CustomerID, Year
having count(*) < 12 or (Year = year(getdate()) and COUNT(*) < Month(Getdate()))
This returns only the years where gaps occure..
If you need something more precise just ask and I'll figure it out.
-
Since there is only 12 months in a year, why not just keep this very simple at create twelve select statements, one for each month, that returns any entries that are missing.
select CustomerId, Year
from HistoryTable
where Month = 1
group by CustomerId, Year
having count(Month) = 0
order by CustomerId, Year
etc.. through month 12.
Dave N
-
I would personally preffer 1 single statement for that... much less work if you have to change the query.
-
I might be wrong, but where is month 4?
SELECT t1.Number, DATENAME(month,t1.Number*28)
FROM master..spt_values t1
LEFT JOIN #t t2
ON t1.Number=t2.[month]
WHERE t1.type='P' AND t1.Number > 0 AND t1.Number<=12
AND t2.[month] IS NULL
If you need this frequently, I would consider building a numbers table.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
ooh, I forgot...
Don't you think a single date column would do? There is no need to split this into two columns.
Just reread the thread. Feel free to ignore my posting. Must have been a bit blind
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hehe; this is what happens when you post on too many threads Frank. You need your beauty sleep now..
-
Ok,
Then put it in a while loop. You then have one select statements and all 12 months are covered.
declare x int
set x = 1
while x <= 12
begin
select statement
Dave N
-
while x <= 12
begin
select customerid, month, year
from historytable
where month = x
group by customerid, month, year
having count(month) = 0
set x = x + 1
end
-
Then what.. 12 recordsets???
How about 12 inserts in a temp table then a select form that temp table??
Now how about a single select to return the information to the client in a single step??
I don't want to seem mean or anything... but most people would chose option 3. But that's only a presumption.
-
Or
select c.CustomerID, n.Number as [Month], ay.y as [Year]
from (select Number from master..spt_values where type = 'P' and number between 1 and 12)n
cross join (select distinct CustomerID from dtes) c
cross join (select 2003 y union all select 2004 ) ay
Left join dtes d on c.CustomerID = d.CustomerID and n.Number = d.M and d.Y = ay.y
where d.M is null and ( ay.y *100 + n.number between 200305 and 200403)
order by 3,2
* Noel -
No skin of my nose. It is just a solution that is KISS.
Dave N
-
I preffer Noeld's solution.. Still simple and will work forever without maintenant (with 1-2 modifications).
Just my 0.02 cents.
-
Thank you every one for suggestions. I think the best solution is:
select CustomerID
from ReportB
group by CustomerID
having Max([Year]*12+Month)- Min([Year]*12+[Month])+1 <> Count(*)
-
I see what you're doing, nice trick. But the problem is that it returns only customers with missing entries. Noeld's solution also provides which months/years are missing. This might be a little more helpfull to the users.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply