Solved

Using a table of dates in a query - why?

Posted on 2014-12-12
7
136 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 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 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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 50

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 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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…

696 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