?
Solved

Using a table of dates in a query - why?

Posted on 2014-12-12
7
Medium Priority
?
142 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 22

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 69

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 51

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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
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…
Suggested Courses

770 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