Insert month if missing
-
Hi! I receive data that usually has info broken down by months. On some occasions, however, there’s no data for a given month. For example, a single data feed may contain data for Jan and March, but none for Feb.
What kind of sql trick can I use to check for a missing month(s), and then insert one row for the missing month such that the month can be represented (filling in zeros in all the respective fields.)
-
Query your table with an OUTER JOIN against a table of dates (one record / month). Let us know if you need help making the [DATES] table.
Once you understand the BITs, all the pieces come together
-
In this Case It is a good thing to have a Dates / Months Table And then
SELECT D as Date
, I.Data1
, I.Data2
FROM
Dates D LEFT JOIN Imported I
ON Dates.ID = Imported.ID
WHERE
DATES.ID <= MaximumDateThis is just an example and maybe not specific enough due to the lack of further info
* Noel -
You could create a temp table with your months, similar to the following:
select month
from #months
where month not in(
Select distinct b.month
from invoice a,
#months b
wheredatepart(mm, a.InvoiceDate) = month
)
-
Great suggestions! Many thanks. (I didn't think the solution would be difficult, but my brain seems to still be on holiday.)
--Pete
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply