Solved

Using a table of dates in a query - why?

Posted on 2014-12-12
7
106 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
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 65

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 69

Expert Comment

by:ScottPletcher
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 26

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 46

Assisted Solution

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

895 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

12 Experts available now in Live!

Get 1:1 Help Now