Solved

Using a table of dates in a query - why?

Posted on 2014-12-12
7
99 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
7 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 250 total points
Comment Utility
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 65

Expert Comment

by:Jim Horn
Comment Utility
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 69

Expert Comment

by:ScottPletcher
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
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 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
Comment Utility
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
Comment Utility
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 65

Expert Comment

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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Full Text Search string 5 32
Using MS SQL / SQLLite for iPhone  App 7 25
.htaccess file settings 4 32
Report Builder 9 25
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now