0

I have datewise data in 30 different tables. The names of the tables are as follows - wh_available_items.01/06/2022, wh_available_items.02/06/2022, and so on for the rest of the dates of the month of June.

I want to combine all these records in a single report. I can do that using the following query -

SELECT '1 June 2022', a.* FROM table1 AS a
UNION 
SELECT '2 June 2022', b.* FROM table2 AS b
UNION
...
SELECT '30 June 2022', a4.* FROM table30 AS a4

I wanted to know if there's a way I could write a dynamic query that would generate a monthly report at the end of every month. So when a report is required for the month of July, it would integrate datewise tables for that month and create a similar report.

Thanks for helping!

Zaid Azim
  • 31
  • 1
  • 6
  • I believe the dynamism part will be for the SQL that will in turn generate the resulting SQL query combining all tables with UNION. Possibly a recursive CTE query can be used for same to generate date-wise table names. – Pankaj Jul 13 '22 at 05:08
  • If there is the possibility that a day is missing I would use a procedure checking what tables exist for the month and year required before generating dynamic code. – P.Salmon Jul 13 '22 at 06:57
  • Luckily, that's not a possibility. – Zaid Azim Jul 13 '22 at 08:07
  • I'd start by fixing the schema so you just have one table called `wh_available_items`, and each row is date stamped. That would make this sort of report trivial. – Tangentially Perpendicular Jul 13 '22 at 08:40
  • @TangentiallyPerpendicular that is perhaps the most rational way to approach this. However, I want to see if that can be done using a SQL query. – Zaid Azim Jul 14 '22 at 05:13

0 Answers0