SQL Tricks

URL: https://stackoverflow.com/questions/43100103/break-into-multiple-rows-based-on-date-range-of-a-single-row Break into multiple rows based on date range of a single row You can use a Calendar or dates table for this sort of thing. For only 152kb in memory, you can have 30 years of dates in a table with this: /* dates table */ declare @ fromdate date = '20000101' ; declare @ years int = 30 ; /* 30 years, 19 used data pages ~152kb in memory, ~264kb on disk */ ; with n as ( select n from ( values ( 0 ),( 1 ),( 2 ),( 3 ),( 4 ),( 5 ),( 6 ),( 7 ),( 8 ),( 9 )) t ( n )) select top ( datediff ( day , @ fromdate , dateadd ( year ,@ years ,@ fromdate ))) [ Date ]= convert ( date , dateadd ( day , row_number () over ( order by ( select 1 )) -1 ,@ fromdate )) into dbo . Dates from n as deka cross join n as hecto cross join n as kilo cross join n as tenK cross join n as hundredK order by [ Date ]; create ...

URL: https://www.sqlservertutorial.net/sql-server-basics/sql-server-temporary-tables/ SQL Server Temporary Tables Summary : in this tutorial, you will learn how to create SQL Server temporary tables and how to manipulate them effectively. Temporary tables are tables that exist temporarily on the SQL Server. The temporary tables are useful for storing the immediate result sets that are accessed multiple times. Creating temporary tables SQL Server provided two ways to create temporary tables via SELECT INTO and CREATE TABLE statements. Create temporary tables using SELECT INTO statement The first way to create a temporary table is to use the SELECT INTO statement as shown below: 1 2 3 4 5 6 7 SELECT      select_list INTO      temporary_table FROM      table_name . . . . The name of the temporary table starts with a hash symbol ( # ). For example, the following statement c...

URL: http://www.jessespevack.com/blog/2017/9/9/how-to-summarize-sql-records-by-day How to Summarize SQL Records by Day Some readers of this blog have been asking about useful SQL queries that have come up in my current job. So I wanted to share a query that is not super complicated, but very useful.  The use case for this query is when you are looking to view a summary of records by day. For example, maybe you have many invoices everyday, but would like to track total invoice revenue by day. First, let's figure out how to get the total revenue for a specific day. To do so we can use something like: SELECT SUM ( total ) FROM invoices WHERE created_at BETWEEN '2017-08-24 00:00:00' AND '2017-08-24 23:59:59' ; The above query will add up the total attribute on each invoice created on August 8, 2017. So now we know how much revenue we generated on this date, but what if I want to find revenue for every day this month? One option w...