Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL: Use Generate Series to Fill Missing Dates

- 06.06.26 - ErcanOPAK

📅 JOIN with Missing Dates? Fill the Gaps

Sales report missing dates = misleading. Generate series creates sequences. Fill missing dates with zero sales.

📝 PostgreSQL generate_series

-- Generate numbers 1 to 100
SELECT generate_series(1, 100);

-- Generate dates
SELECT generate_series(
  '2024-01-01'::date,
  '2024-12-31'::date,
  '1 day'::interval
);

-- Generate with step
SELECT generate_series(0, 100, 10);  -- 0,10,20,...,100

-- Fill missing sales data
WITH date_range AS (
  SELECT generate_series(
    '2024-01-01'::date,
    '2024-12-31'::date,
    '1 day'
  ) AS sale_date
)
SELECT 
  dr.sale_date,
  COALESCE(SUM(s.amount), 0) AS total_sales
FROM date_range dr
LEFT JOIN sales s ON dr.sale_date = s.sale_date
GROUP BY dr.sale_date
ORDER BY dr.sale_date;

🎯 MySQL/MariaDB (Recursive CTE)

WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT n FROM numbers;

-- Dates in MySQL
WITH RECURSIVE dates AS (
  SELECT DATE('2024-01-01') AS date
  UNION ALL
  SELECT date + INTERVAL 1 DAY
  FROM dates
  WHERE date < '2024-12-31'
)
SELECT date FROM dates;

💡 Use Cases

  • Sales reports with zero-sales days included
  • Calendar tables for date dimension
  • Time series analysis
  • Generate test data (numbers, dates, timestamps)

"Sales chart looked wrong. Days with no sales were missing. generate_series filled gaps. Chart now shows zeros on missing days. Data representation is accurate."

— Data Analyst

Related posts:

SQL: Use Window Functions for Advanced Analytics

SQL: Use Window Functions for Advanced Analytics

Parameter Sniffing — The #1 SQL Server Mystery You Must Understand

Parameter Sniffing — The #1 SQL Server Mystery You Must Understand

SQL Server: Fix an OUTPUT Clause That Returns the Wrong Values Because a Trigger Already Fired Befor...

SQL Server: Fix an OUTPUT Clause That Returns the Wrong Values Because a Trigger Already Fired Befor...

Views: 16

Post navigation

.NET Core: Use Environment Variables for Multi-Environment Configuration
C#: Use Default Interface Methods for Versioning and Mixins

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (976)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (852)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (607)
  • Average of all values in a column that are not zero in SQL (569)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (546)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (976)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (852)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com