Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using a table of dates in a query - why?

Posted on 2014-12-12
7
Medium Priority
?
151 Views
Last Modified: 2014-12-15
I inherited this query from my predecessor. It returns the correct data, but it seems like a hard way to get there. He has a table that contains all the dates between 1-1-2000 and 12-31-2030 which he uses extensively in queries. It seems like there has to be a more elegant way of doing this, but I'm far from being an expert so maybe there's a reason for going this route. It also seems like there should be a better way of getting the count of late deliveries (Line 79). We don't care how late they are, we just need to know the number of late deliveries to get the percentage of deliveries that were not late, broken out between the two warehouses. Below is the query with a small piece of sample data. Normally this would cover one or more whole months.

DECLARE @Calendar TABLE

(
	CalendarDate DATE
)

DECLARE @PorMasterDetail TABLE
(
     MLatestDueDate  DATETIME,
     MLastReceiptDat DATETIME,
	 MWarehouse CHAR(2)
)

INSERT INTO @PorMasterDetail
VALUES ('2014-12-04 00:00:00.000', '2014-12-03 00:00:00.000', '20'),
('2014-12-05 00:00:00.000', '2014-12-04 00:00:00.000', '10'),
('2014-12-08 00:00:00.000', '2014-12-02 00:00:00.000', '10'),
('2014-12-08 00:00:00.000', '2014-12-02 00:00:00.000', '10'),
('2014-12-05 00:00:00.000', '2014-12-03 00:00:00.000', '20'),
('2014-12-15 00:00:00.000', '2014-12-03 00:00:00.000', '20'),
('2014-12-01 00:00:00.000', '2014-12-01 00:00:00.000', '10'),
('2014-12-03 00:00:00.000', '2014-12-02 00:00:00.000', '20'),
('2014-12-09 00:00:00.000', '2014-12-04 00:00:00.000', '20'),
('2014-12-05 00:00:00.000', '2014-12-05 00:00:00.000', '10'),
('2014-12-05 00:00:00.000', '2014-12-04 00:00:00.000', '10'),
('2015-04-04 00:00:00.000', '2014-12-02 00:00:00.000', '20'),
('2014-12-05 00:00:00.000', '2014-12-05 00:00:00.000', '10'),
('2014-12-02 00:00:00.000', '2014-12-02 00:00:00.000', '20'),
('2014-12-02 00:00:00.000', '2014-12-03 00:00:00.000', '20'),
('2014-12-02 00:00:00.000', '2014-12-02 00:00:00.000', '20'),
('2014-12-02 00:00:00.000', '2014-12-02 00:00:00.000', '20'),
('2014-12-02 00:00:00.000', '2014-12-02 00:00:00.000', '20'),
('2014-12-02 00:00:00.000', '2014-12-02 00:00:00.000', '20'),
('2014-12-10 00:00:00.000', '2014-12-05 00:00:00.000', '10'),
('2014-12-15 00:00:00.000', '2014-12-04 00:00:00.000', '20'),
('2014-12-15 00:00:00.000', '2014-12-05 00:00:00.000', '20'),
('2014-12-05 00:00:00.000', '2014-12-04 00:00:00.000', '20'),
('2014-12-10 00:00:00.000', '2014-12-02 00:00:00.000', '20'),
('2014-12-10 00:00:00.000', '2014-12-01 00:00:00.000', '20'),
('2014-12-05 00:00:00.000', '2014-12-01 00:00:00.000', '10'),
('2014-12-04 00:00:00.000', '2014-12-01 00:00:00.000', '20'),
('2014-12-04 00:00:00.000', '2014-12-01 00:00:00.000', '20'),
('2014-12-02 00:00:00.000', '2014-12-03 00:00:00.000', '20'),
('2014-12-18 00:00:00.000', '2014-12-02 00:00:00.000', '20'),
('2014-12-03 00:00:00.000', '2014-12-02 00:00:00.000', '20'),
('2014-12-03 00:00:00.000', '2014-12-02 00:00:00.000', '20'),
('2014-12-02 00:00:00.000', '2014-12-02 00:00:00.000', '20'),
('2014-12-02 00:00:00.000', '2014-12-02 00:00:00.000', '20'),
('2014-12-02 00:00:00.000', '2014-12-02 00:00:00.000', '20'),
('2014-12-05 00:00:00.000', '2014-12-05 00:00:00.000', '10'),
('2014-12-01 00:00:00.000', '2014-12-01 00:00:00.000', '10')

INSERT INTO @Calendar
VALUES ('2014-12-01'),
('2014-12-02'),
('2014-12-03'),
('2014-12-04'),
('2014-12-05')

SELECT
    C.[Year],
    C.[Month],
    t1.Hardware,
    t1.[Raw Materials]
FROM (SELECT DISTINCT
		YEAR(C.CalendarDate) AS [Year],
		MONTH(C.CalendarDate) AS M,
        DATENAME(m, C.CalendarDate) AS [Month]
        FROM   @Calendar C
        WHERE  C.CalendarDate >= '20141201' AND C.CalendarDate <= '20141212') C
       LEFT JOIN (SELECT
					W10.[Year],
                    W10.[M],
                    W20.Hardware,
                    W10.[Raw Materials]
                  FROM   (SELECT
							CAST(YEAR([MLastReceiptDat]) AS INT) AS [Year],
                            CAST(MONTH([MLastReceiptDat]) AS INT) AS [M],
                            CAST(CAST((COUNT(*) - SUM(CASE WHEN MLastReceiptDat - MLatestDueDate > 0 THEN 1 ELSE 0
                                                      END))AS DECIMAL(10, 4)) / CAST(COUNT(*) AS DECIMAL(10, 4))AS DECIMAL(10, 4)) AS [Hardware]
                          FROM @PorMasterDetail
                          WHERE  (MWarehouse = '20')
                          GROUP  BY YEAR([MLastReceiptDat]), MONTH([MLastReceiptDat]), MWarehouse) W20
                          INNER JOIN (SELECT
										CAST(YEAR([MLastReceiptDat]) AS INT) AS [Year],
                                        CAST(MONTH([MLastReceiptDat]) AS INT) AS [M],
                                        CAST(CAST((COUNT(*) - SUM(CASE WHEN MLastReceiptDat - MLatestDueDate > 0 THEN 1 ELSE 0
                                                                  END))AS DECIMAL(10, 4)) / CAST(COUNT(*) AS DECIMAL(10, 4)) AS DECIMAL(10, 4)) AS [Raw Materials]
                                     FROM   @PorMasterDetail t2
                                     WHERE  (MWarehouse = '10')
                                     GROUP  BY YEAR([MLastReceiptDat]), MONTH([MLastReceiptDat]), MWarehouse) W10
									 ON W10.[Year] = W20.[Year] AND W10.[M] = W20.[M]) t1
									 ON t1.[Year] = C.[Year] AND t1.[M] = C.[M]
ORDER  BY C.[Year], C.[M] 

Open in new window

0
Comment
Question by:BZ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 23

Accepted Solution

by:
Steve Wales earned 1000 total points
ID: 40496963
Doug Lane of the Brent Ozar Unlimited consultancy posted a video about his reasons for using a date table just the other day, and Brent has a link in his comment to the video with an example of how to populate such a table.

http://www.brentozar.com/archive/2014/12/simply-must-date-table-video/

I haven't watched the complete thing, but it can simplify code by not having DATEPART etc  functions all through the code.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40497001
Here's my article that explains it ... Build your own calendar table to perform complex date expressions

The short answer:  Not everybody lives in your world, and SQL Server isn't built to handle everybody's different world when it comes to dates.
Some places have goofy holidays of their own where stores are closed.
The entire state of Wisconsin shuts down for Green Bay Packers football games.  Businesses have to plan for that.
My last client was an airline, and I had to build functionality to accurately the predict the effects of Daylight Savings Time (or lack thereof) for all airports.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40497148
It is just silly to use a calendar table, 365 row summary, to get 12 individual months that are known up front.  Many people get so wedded to the notion of a calendar table they can't ignore it even when it makes no sense for a specific query.  [Btw, how far then do you take this?  Some companies close for half-days or even hours, so do we really need a table with every hour between 2000 and 2099?]  A nonwork days calendar makes perfect sense, but using a full calendar for basic date actions is not justified to me.

As to the rest, the @PorMasterDetail table needs to be read only once, not twice.  And the dates should not be split into separate yr & mth until the very end, as that is effectively just formatting of the underlying date.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 27

Expert Comment

by:Zberteoc
ID: 40497939
I prefer this solution:
USE YourDatabase
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnTally]()
RETURNS TABLE --WITH SCHEMABINDING 
AS
/*******************************************************************************\
Function	: fnTally

Purpose		: returns a set with numbers from 1 to 10,000 
			  to be used in parsing and sequential data generation whithout loop
			  
Parameters	: no parameters

Invoke		:
	
		select * from [dbo].[fnTally]()
		select N from [dbo].[fnTally]()
		select substring('abcdef',N,1) as chr from [dbo].[fnTally]() where N<len('abcdef') -- parsing a string
		select dateadd(dd, N, '2007-01-01') as dte from [dbo].[fnTally]() --gets dates for about 30 years

\*******************************************************************************/
RETURN
	WITH 
	E1(N) AS 
	( --10E+1 or 10 rows
		 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
		 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
		 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
	),                         
   E2(N) AS 
   ( --10E+2 or 100 rows	
		SELECT 1 FROM E1 a, E1 b
	),
   E4(N) AS 
   ( --10E+4 or 10,000 rows max
		SELECT 1 FROM E2 a, E2 b
	)
			 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as N FROM E4
	;

Open in new window

The function itself generates numbers from 1 to 10000 and, as specified in commented examples inside the function, you can use it to produce dates for 30 years like this:

select dateadd(dd, N, '2007-01-01') as dte from [dbo].[fnTally]()

Open in new window

0
 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 40500218
You don't need the last subselect since you can do all the operations in the LEFT JOIN:
SELECT
    C.[Year],
    C.[Month],
	SUM(W.Hardware) AS Hardware,
	SUM(W.[Raw Materials]) AS [Raw Materials]
FROM (SELECT DISTINCT
		YEAR(C.CalendarDate) AS [Year],
		MONTH(C.CalendarDate) AS M,
        DATENAME(m, C.CalendarDate) AS [Month]
        FROM   @Calendar C
        WHERE  C.CalendarDate >= '20141201' AND C.CalendarDate <= '20141212') C
       LEFT JOIN (SELECT
					CAST(YEAR([MLastReceiptDat]) AS INT) AS [Year],
                    CAST(MONTH([MLastReceiptDat]) AS INT) AS [M],
                    CASE MWarehouse
						WHEN '20' THEN
							CAST(CAST((COUNT(*) - SUM(CASE WHEN MLastReceiptDat - MLatestDueDate > 0 THEN 1 ELSE 0
                                              END))AS DECIMAL(10, 4)) / CAST(COUNT(*) AS DECIMAL(10, 4))AS DECIMAL(10, 4))
                        END AS [Hardware],
					CASE MWarehouse
						WHEN '10' THEN
							CAST(CAST((COUNT(*) - SUM(CASE WHEN MLastReceiptDat - MLatestDueDate > 0 THEN 1 ELSE 0
                                              END))AS DECIMAL(10, 4)) / CAST(COUNT(*) AS DECIMAL(10, 4))AS DECIMAL(10, 4))
						END AS [Raw Materials]
                  FROM @PorMasterDetail
                  WHERE  MWarehouse IN ('10','20')
                  GROUP  BY YEAR([MLastReceiptDat]), MONTH([MLastReceiptDat]), MWarehouse) W
			ON W.[Year] = C.[Year] AND W.[M] = C.[M]
GROUP BY C.Year, C.Month
ORDER  BY C.[Year], C.[Month] 

Open in new window

0
 

Author Closing Comment

by:BZ
ID: 40500343
Thanks to everyone for your helpful comments. All are appreciated. I see a should not have asked two questions in the same topic. Live and learn.

Steve: I watched the video and it makes sense. The table I currently have contains a single date, so I wasn't seeing much benefit in that approach.

Vitor: Thank you so much for straightening this out.

Jim: Your article has been removed.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40500375
Oops.  I posted the link to the draft, which was deleted when I published the final version.  My bad.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question