BAM
asked on
Using a table of dates in a query - why?
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]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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
;
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]()
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
Oops. I posted the link to the draft, which was deleted when I published the final version. My bad.
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.