August 10, 2015 at 9:24 am
Hi
I need to display all the dates within a range even with no data
for example right now my query get the records with the range say...
The range is 7/1/15 thru 7/7/15
I Get...
Joe 7/1/15 xxx
Joe 7/3/15 ccc
Joe 7/5/15 xxx
I want...
Joe 7/1/15 xxx
Joe 7/2/15
Joe 7/3/15 ccc
Joe 7/4/15
Joe 7/5/15 xxx
Joe 7/6/15
Joe 7/7/15
Thanks
August 10, 2015 at 9:28 am
You need an outer join with a calendar table.
Something like this:
SELECT *
FROM CalendarTable c
LEFT
JOIN SomeTable s ON c.calDate = s.SomeDate
WHERE c.calDate >= @StartDate
AND c.calDate < @EndDate + 1
August 10, 2015 at 9:47 am
Hi Luis
Thanks for getting back.
I tried something like that and it only returns values with data. I think it it is my where clause?
SELECT RTRIM(data.dbo.CLI.LNAME) + ', ' + data.dbo.CLI.FNAME AS client, b.DATESERV, REPLACE(b.CLINO, ' ', '') AS clino,
RTRIM(data.dbo.CLN.CLNLNAME) + ', ' + data.dbo.CLN.CLNFNAME AS clinician, LTRIM(b.PROGNO) AS progno, b.CLMINUTE,
LEFT(data.dbo.IMPORTSVAC.ServiceCodeId_ext, CHARINDEX('^', data.dbo.IMPORTSVAC.ServiceCodeId_ext) - 1) AS dataservicecode, a.AMOUNT,
CASE WHEN a.REVERSED = 'false' AND a.TRANSTYPE = 1 THEN 1 ELSE 0 END AS session, CASE WHEN a.REVERSED = 'false' AND
a.TRANSTYPE = 1 THEN b.INUNIT END AS unit, CASE WHEN a.TRANSTYPE = 1 AND a.REVERSED = 'false' THEN a.AMOUNT END AS charge, a.ALLOWAMT,
CASE WHEN a.TRANSTYPE = 1 AND a.REVERSED = 'false' THEN a.ALLOWAMT END AS expected, CASE WHEN a.TRANSTYPE = 6 AND
a.REVERSED = 'false' THEN a.AMOUNT WHEN a.TRANSTYPE = 6 AND a.REVERSED = 'true' THEN a.AMOUNT * - 1 END AS paid,
b.CLNNO + b.CLINO + CONVERT(VARCHAR(23), b.DATESERV, 121) + b.TIMESERV AS sort, data.dbo.PGDM.PROGDES, dbo.dimDate.DateFull
FROM data.dbo.SVDT AS a INNER JOIN
data.dbo.SVAC AS b ON a.TRANSNO = b.TRANSNO INNER JOIN
data.dbo.IMPORTSVAC ON b.PK_SVAC = data.dbo.IMPORTSVAC.InternalId INNER JOIN
data.dbo.PGDM ON b.PROGNO = data.dbo.PGDM.PROGNO RIGHT OUTER JOIN
dbo.dimDate ON b.DATESERV = dbo.dimDate.DateFull RIGHT OUTER JOIN
data.dbo.CLI ON b.CLINO = data.dbo.CLI.CLINO LEFT OUTER JOIN
data.dbo.CLN ON b.CLNNO = data.dbo.CLN.CLNNO
WHERE (a.TRANSTYPE = '0' OR
a.TRANSTYPE = '1' OR
a.TRANSTYPE = '6') AND (b.SERVTYPE = ' 5') AND (dbo.dimDate.DateFull BETWEEN CONVERT(DATETIME, '2015-07-01 00:00:00', 102) AND CONVERT(DATETIME,
'2015-07-10 00:00:00', 102))
August 10, 2015 at 9:55 am
Yes it is. Add in to the OR set OR a.TRANSTYPE IS NULL and replace b.SERVTYPE = ' 5' with (b.SERVTYPE = ' 5' OR b.SERVTYPE IS NULL)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2015 at 10:05 am
The simplest way was to use the original query as a CTE or derived table and then join it to the calendar table.
WITH cteQuery AS(
SELECT RTRIM(data.dbo.CLI.LNAME) + ', ' + data.dbo.CLI.FNAME AS client,
b.DATESERV, REPLACE(b.CLINO, ' ', '') AS clino,
RTRIM(data.dbo.CLN.CLNLNAME) + ', ' + data.dbo.CLN.CLNFNAME AS clinician,
LTRIM(b.PROGNO) AS progno,
b.CLMINUTE,
LEFT(data.dbo.IMPORTSVAC.ServiceCodeId_ext, CHARINDEX('^', data.dbo.IMPORTSVAC.ServiceCodeId_ext) - 1) AS dataservicecode,
a.AMOUNT,
CASE WHEN a.REVERSED = 'false' AND a.TRANSTYPE = 1 THEN 1 ELSE 0 END AS session,
CASE WHEN a.REVERSED = 'false' AND a.TRANSTYPE = 1 THEN b.INUNIT END AS unit,
CASE WHEN a.TRANSTYPE = 1 AND a.REVERSED = 'false' THEN a.AMOUNT END AS charge,
a.ALLOWAMT,
CASE WHEN a.TRANSTYPE = 1 AND a.REVERSED = 'false' THEN a.ALLOWAMT END AS expected,
CASE WHEN a.TRANSTYPE = 6 AND a.REVERSED = 'false' THEN a.AMOUNT
WHEN a.TRANSTYPE = 6 AND a.REVERSED = 'true' THEN a.AMOUNT * - 1 END AS paid,
b.CLNNO + b.CLINO + CONVERT(VARCHAR(23), b.DATESERV, 121) + b.TIMESERV AS sort,
data.dbo.PGDM.PROGDES
FROM data.dbo.SVDT AS a
INNER JOIN data.dbo.SVAC AS b ON a.TRANSNO = b.TRANSNO
INNER JOIN data.dbo.IMPORTSVAC ON b.PK_SVAC = data.dbo.IMPORTSVAC.InternalId
INNER JOIN data.dbo.PGDM ON b.PROGNO = data.dbo.PGDM.PROGNO
RIGHT OUTER JOIN data.dbo.CLI ON b.CLINO = data.dbo.CLI.CLINO
LEFT OUTER JOIN data.dbo.CLN ON b.CLNNO = data.dbo.CLN.CLNNO
WHERE (a.TRANSTYPE = '0' OR a.TRANSTYPE = '1' OR a.TRANSTYPE = '6')
AND (b.SERVTYPE = ' 5')
)
SELECT q.*,
d.DateFull
FROM cteQuery q
RIGHT OUTER JOIN dbo.dimDate d ON b.DATESERV = d.DateFull
AND d.DateFull BETWEEN CONVERT(DATETIME, '2015-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2015-07-10 00:00:00', 102)
By the way, three-part and four-part column references (such as dbo.dimDate.DateFull) are deprecated and you should avoid using them. Use table alias to prevent this. If DateFull is a datetime column, you don't need to explicitly convert the strings. You can simply use AND d.DateFull BETWEEN '20150701' AND '20150710'
August 10, 2015 at 10:11 am
jbalbo (8/10/2015)
HiI need to display all the dates within a range even with no data
for example right now my query get the records with the range say...
The range is 7/1/15 thru 7/7/15
I Get...
Joe 7/1/15 xxx
Joe 7/3/15 ccc
Joe 7/5/15 xxx
I want...
Joe 7/1/15 xxx
Joe 7/2/15
Joe 7/3/15 ccc
Joe 7/4/15
Joe 7/5/15 xxx
Joe 7/6/15
Joe 7/7/15
Thanks
You didn't state what your calendar table was, although I assume it's dimDate. I replaced dimDate in your query, and then added a quick CTE that can generate the dates needed for you, and parameterized the date values at the beginning of the query. If dimDate is indeed your calendar table, you can modify the CTE to use the dimDate table instead.
DECLARE @StartDate AS datetime = '2015-07-01';
DECLARE @EndDate AS datetime = '2015-07-10';
WITH CALENDAR AS (
SELECT @StartDate AS THE_DATE
UNION ALL
SELECT DATEADD(day, 1, C.THE_DATE)
FROM CALENDAR AS C
WHERE DATEADD(day, 1, C.THE_DATE) <= @EndDate
),
MY_DATA AS (
SELECT RTRIM(I.LNAME) + ', ' + I.FNAME AS client,
b.DATESERV,
REPLACE(b.CLINO, ' ', '') AS clino,
RTRIM(N.CLNLNAME) + ', ' + N.CLNFNAME AS clinician,
LTRIM(b.PROGNO) AS progno,
b.CLMINUTE,
LEFT(IV.ServiceCodeId_ext,
CHARINDEX('^', IV.ServiceCodeId_ext) - 1) AS dataservicecode,
a.AMOUNT,
CASE WHEN a.REVERSED = 'false' AND a.TRANSTYPE = 1 THEN 1 ELSE 0 END AS [session],
CASE WHEN a.REVERSED = 'false' AND a.TRANSTYPE = 1 THEN b.INUNIT END AS unit,
CASE WHEN a.TRANSTYPE = 1 AND a.REVERSED = 'false' THEN a.AMOUNT END AS charge,
a.ALLOWAMT,
CASE WHEN a.TRANSTYPE = 1 AND a.REVERSED = 'false' THEN a.ALLOWAMT END AS expected,
CASE
WHEN a.TRANSTYPE = 6 AND a.REVERSED = 'false' THEN a.AMOUNT
WHEN a.TRANSTYPE = 6 AND a.REVERSED = 'true' THEN a.AMOUNT * - 1
END AS paid,
b.CLNNO + b.CLINO + CONVERT(varchar(23), b.DATESERV, 121) + b.TIMESERV AS sort,
PG.PROGDES
--dbo.dimDate.DateFull
FROM data.dbo.SVDT AS a
INNER JOIN data.dbo.SVAC AS b
ON a.TRANSNO = b.TRANSNO
INNER JOIN data.dbo.IMPORTSVAC AS IV
ON b.PK_SVAC = IV.InternalId
INNER JOIN data.dbo.PGDM AS PG
ON b.PROGNO = PG.PROGNO
--RIGHT OUTER JOIN dbo.dimDate AS DD
--ON b.DATESERV = DD.DateFull
RIGHT OUTER JOIN data.dbo.CLI AS I
ON b.CLINO = I.CLINO
LEFT OUTER JOIN data.dbo.CLN AS N
ON b.CLNNO = N.CLNNO
WHERE a.TRANSTYPE IN ('0', '1', '6')
AND b.SERVTYPE = ' 5'
AND b.DATESERV
BETWEEN CONVERT(datetime, @StartDate, 102)
AND CONVERT(datetime, @EndDate, 102)
)
SELECT C.THE_DATE AS DATESERV, MD.client, MD.clino, MD.clinician, MD.progno,
MD.CLMINUTE, MD.dataservicecode, MD.AMOUNT, MD.[session], MD.unit,
MD.charge, MD.ALLOWAMT, MD.expected, MD.paid, MD.sort, MD.PROGDES
FROM CALENDAR AS C
LEFT OUTER JOIN MY_DATA AS MD
ON C.THE_DATE = MD.DATESERV
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 10, 2015 at 10:19 am
Thank you all for the info, not only do they work, but I actually learned something...
So I now get 3,4 and the 5th as null , which is what I asked for
What I should have explained was that I need each date for each clientname
Joe 7/1/15 xxx
Joe 7/2/15 xxx
Joe 7/3/15 null
Joe 7/4/15 null
Joe 7/5/15 null
Joe 7/6/15 xxx ....
Sam 7/1/15 null
Sam 7/2/15 ccc
Sam 7/3/15 ccc
Sam 7/4/15 null
Sam 7/5/15 null
Sam 7/6/15 null ...
August 10, 2015 at 10:22 am
Please don't use a recursive CTE to generate a calendar table as it's only a way of hidden RBAR. You can read more about this on this article: http://www.sqlservercentral.com/articles/T-SQL/74118/
As an alternative, you can use an approach similar to this which is a reduced version of Itzik-Style Cross-Join:
WITH E(n) AS(
SELECT n FROM(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E(n)
),
CALENDAR AS (
SELECT TOP(DATEDIFF(dd, @StartDate, @EndDate) + 1) --
DATEADD( DD, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @StartDate) THE_DATE
FROM E a, E b, E c
)
SELECT *
FROM CALENDAR;
August 10, 2015 at 10:47 am
You need to create a cartesian product that includes all dates for all the clients. The following code might need to be divided as it might cause problems to the engine to process if there's too much information. I'll let you decide the best choice.
WITH cteQuery AS(
SELECT RTRIM(data.dbo.CLI.LNAME) + ', ' + data.dbo.CLI.FNAME AS client,
b.DATESERV,
REPLACE(b.CLINO, ' ', '') AS clino,
RTRIM(data.dbo.CLN.CLNLNAME) + ', ' + data.dbo.CLN.CLNFNAME AS clinician,
LTRIM(b.PROGNO) AS progno,
b.CLMINUTE,
LEFT(data.dbo.IMPORTSVAC.ServiceCodeId_ext, CHARINDEX('^', data.dbo.IMPORTSVAC.ServiceCodeId_ext) - 1) AS dataservicecode,
a.AMOUNT,
CASE WHEN a.REVERSED = 'false' AND a.TRANSTYPE = 1 THEN 1 ELSE 0 END AS session,
CASE WHEN a.REVERSED = 'false' AND a.TRANSTYPE = 1 THEN b.INUNIT END AS unit,
CASE WHEN a.TRANSTYPE = 1 AND a.REVERSED = 'false' THEN a.AMOUNT END AS charge,
a.ALLOWAMT,
CASE WHEN a.TRANSTYPE = 1 AND a.REVERSED = 'false' THEN a.ALLOWAMT END AS expected,
CASE WHEN a.TRANSTYPE = 6 AND a.REVERSED = 'false' THEN a.AMOUNT
WHEN a.TRANSTYPE = 6 AND a.REVERSED = 'true' THEN a.AMOUNT * - 1 END AS paid,
b.CLNNO + b.CLINO + CONVERT(VARCHAR(23), b.DATESERV, 121) + b.TIMESERV AS sort,
data.dbo.PGDM.PROGDES
FROM data.dbo.SVDT AS a
INNER JOIN data.dbo.SVAC AS b ON a.TRANSNO = b.TRANSNO
INNER JOIN data.dbo.IMPORTSVAC ON b.PK_SVAC = data.dbo.IMPORTSVAC.InternalId
INNER JOIN data.dbo.PGDM ON b.PROGNO = data.dbo.PGDM.PROGNO
RIGHT OUTER JOIN data.dbo.CLI ON b.CLINO = data.dbo.CLI.CLINO
LEFT OUTER JOIN data.dbo.CLN ON b.CLNNO = data.dbo.CLN.CLNNO
WHERE (a.TRANSTYPE = '0' OR a.TRANSTYPE = '1' OR a.TRANSTYPE = '6')
AND (b.SERVTYPE = ' 5')
),
cteClients AS(
SELECT DISTINCT client, clino
FROM cteQuery
),
cteFullClientsDates AS(
SELECT c.client,
c.clino,
d.Datefull
FROM cteClients c
CROSS JOIN dbo.dimDate d --ON c.DATESERV = d.DateFull (This shouldn't go here)
WHERE d.DateFull BETWEEN CONVERT(DATETIME, '2015-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2015-07-10 00:00:00', 102)
)
SELECT d.client,
d.DateFull AS DATESERV,
d.clino,
q.clinician,
q.progno,
q.CLMINUTE,
q.dataservicecode,
q.AMOUNT,
q.session,
q.unit,
q.charge,
q..ALLOWAMT,
q.expected,
q.paid,
q.sort,
q.PROGDES
FROM cteQuery q
RIGHT OUTER JOIN cteFullClientsDates d ON q.DATESERV = d.DateFull
AND q.client = d.client
AND q.clino = d.clino
August 10, 2015 at 11:11 am
Luis Cazares (8/10/2015)
Please don't use a recursive CTE to generate a calendar table as it's only a way of hidden RBAR. You can read more about this on this article: http://www.sqlservercentral.com/articles/T-SQL/74118/As an alternative, you can use an approach similar to this which is a reduced version of Itzik-Style Cross-Join:
WITH E(n) AS(
SELECT n FROM(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E(n)
),
CALENDAR AS (
SELECT TOP(DATEDIFF(dd, @StartDate, @EndDate) + 1) --
DATEADD( DD, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @StartDate) THE_DATE
FROM E a, E b, E c
)
SELECT *
FROM CALENDAR;
Given the rather small size of the likely range of values here, any suffered performance is likely to be irrelevant, and the recursive CTE is a lot easier to understand, so I'm going to fall back on Jeff Moden's signature line: "Just because I can do something in T-SQL, doesn't mean I shouldn't", as I'll take readability over irrelevant performance hits every day of the week. I understand where you're coming from, and if this user's application of the code will be in a heavy volume OLTP application, then you are exactly right. If it's reporting, as it appears to be, this shouldn't be an issue.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 10, 2015 at 12:12 pm
sgmunson (8/10/2015)
Given the rather small size of the likely range of values here, any suffered performance is likely to be irrelevant, and the recursive CTE is a lot easier to understand, so I'm going to fall back on Jeff Moden's signature line: "Just because I can do something in T-SQL, doesn't mean I shouldn't", as I'll take readability over irrelevant performance hits every day of the week. I understand where you're coming from, and if this user's application of the code will be in a heavy volume OLTP application, then you are exactly right. If it's reporting, as it appears to be, this shouldn't be an issue.
I know that performance might not be an issue here, but it's a matter of habits. If you're used to the cross join style tally table generator, you won't suffer to understand it and will be able to write it easily for any amount of data needed. And if you use some 3rd party add-ins, you can create templates or snippets to generate the code immediately.
August 10, 2015 at 12:18 pm
sgmunson (8/10/2015)
Luis Cazares (8/10/2015)
Please don't use a recursive CTE to generate a calendar table as it's only a way of hidden RBAR. You can read more about this on this article: http://www.sqlservercentral.com/articles/T-SQL/74118/As an alternative, you can use an approach similar to this which is a reduced version of Itzik-Style Cross-Join:
WITH E(n) AS(
SELECT n FROM(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E(n)
),
CALENDAR AS (
SELECT TOP(DATEDIFF(dd, @StartDate, @EndDate) + 1) --
DATEADD( DD, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @StartDate) THE_DATE
FROM E a, E b, E c
)
SELECT *
FROM CALENDAR;
Given the rather small size of the likely range of values here, any suffered performance is likely to be irrelevant, and the recursive CTE is a lot easier to understand, so I'm going to fall back on Jeff Moden's signature line: "Just because I can do something in T-SQL, doesn't mean I shouldn't", as I'll take readability over irrelevant performance hits every day of the week. I understand where you're coming from, and if this user's application of the code will be in a heavy volume OLTP application, then you are exactly right. If it's reporting, as it appears to be, this shouldn't be an issue.
And Jeff would be the most adamant against using a recursive CTE to generate a dynamic calendar table. Just because you can use a recursive CTE to do counting doesn't mean you should.
August 10, 2015 at 1:04 pm
Luis Cazares (8/10/2015)
sgmunson (8/10/2015)
Given the rather small size of the likely range of values here, any suffered performance is likely to be irrelevant, and the recursive CTE is a lot easier to understand, so I'm going to fall back on Jeff Moden's signature line: "Just because I can do something in T-SQL, doesn't mean I shouldn't", as I'll take readability over irrelevant performance hits every day of the week. I understand where you're coming from, and if this user's application of the code will be in a heavy volume OLTP application, then you are exactly right. If it's reporting, as it appears to be, this shouldn't be an issue.I know that performance might not be an issue here, but it's a matter of habits. If you're used to the cross join style tally table generator, you won't suffer to understand it and will be able to write it easily for any amount of data needed. And if you use some 3rd party add-ins, you can create templates or snippets to generate the code immediately.
As an absolute rule, I do not get into "habits", where SQL coding is concerned. I code based on the need and take the entire requirement into account, including performance characteristics as well as maintainability. Doing otherwise tends to create problems.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 10, 2015 at 1:20 pm
Hi
Thanks everyone one for your input
Although most of this is beyond me 🙂
I was able to rough my way through it
It did error on me so I changed the cross join on to a where and added dateserv to cteClients
where I got output but nothing on days that had no records (3,4, and 5)
Not sure if the changes I made messed it up
WITH cteQuery AS(
SELECT RTRIM(data.dbo.CLI.LNAME) + ', ' + data.dbo.CLI.FNAME AS client,
b.DATESERV,
REPLACE(b.CLINO, ' ', '') AS clino,
RTRIM(data.dbo.CLN.CLNLNAME) + ', ' + data.dbo.CLN.CLNFNAME AS clinician,
LTRIM(b.PROGNO) AS progno,
b.CLMINUTE,
LEFT(data.dbo.IMPORTSVAC.ServiceCodeId_ext, CHARINDEX('^', data.dbo.IMPORTSVAC.ServiceCodeId_ext) - 1) AS dataservicecode,
a.AMOUNT,
CASE WHEN a.REVERSED = 'false' AND a.TRANSTYPE = 1 THEN 1 ELSE 0 END AS session,
CASE WHEN a.REVERSED = 'false' AND a.TRANSTYPE = 1 THEN b.INUNIT END AS unit,
CASE WHEN a.TRANSTYPE = 1 AND a.REVERSED = 'false' THEN a.AMOUNT END AS charge,
a.ALLOWAMT,
CASE WHEN a.TRANSTYPE = 1 AND a.REVERSED = 'false' THEN a.ALLOWAMT END AS expected,
CASE WHEN a.TRANSTYPE = 6 AND a.REVERSED = 'false' THEN a.AMOUNT
WHEN a.TRANSTYPE = 6 AND a.REVERSED = 'true' THEN a.AMOUNT * - 1 END AS paid,
b.CLNNO + b.CLINO + CONVERT(VARCHAR(23), b.DATESERV, 121) + b.TIMESERV AS sort,
data.dbo.PGDM.PROGDES
FROM data.dbo.SVDT AS a
INNER JOIN data.dbo.SVAC AS b ON a.TRANSNO = b.TRANSNO
INNER JOIN data.dbo.IMPORTSVAC ON b.PK_SVAC = data.dbo.IMPORTSVAC.InternalId
INNER JOIN data.dbo.PGDM ON b.PROGNO = data.dbo.PGDM.PROGNO
RIGHT OUTER JOIN data.dbo.CLI ON b.CLINO = data.dbo.CLI.CLINO
LEFT OUTER JOIN data.dbo.CLN ON b.CLNNO = data.dbo.CLN.CLNNO
WHERE (a.TRANSTYPE = '0' OR a.TRANSTYPE = '1' OR a.TRANSTYPE = '6')
AND (b.SERVTYPE = ' 5')
),
cteClients AS(
SELECT DISTINCT client, clino, DATESERV
FROM cteQuery
),
cteFullClientsDates AS(
SELECT c.client,
c.clino,
d.Datefull
FROM cteClients c
CROSS JOIN dbo.dimDate d where c.DATESERV = d.DateFull
AND d.DateFull BETWEEN CONVERT(DATETIME, '2015-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2015-07-10 00:00:00', 102)
)
SELECT d.client,
d.DateFull AS DATESERV,
d.clino,
q.clinician,
q.progno,
q.CLMINUTE,
q.dataservicecode,
q.AMOUNT,
q.session,
q.unit,
q.charge,
q.ALLOWAMT,
q.expected,
q.paid,
q.sort,
q.PROGDES
FROM cteQuery q
RIGHT OUTER JOIN cteFullClientsDates d ON q.DATESERV = d.DateFull
AND q.client = d.client
AND q.clino = d.clino
August 10, 2015 at 1:21 pm
sgmunson (8/10/2015)
Luis Cazares (8/10/2015)
sgmunson (8/10/2015)
Given the rather small size of the likely range of values here, any suffered performance is likely to be irrelevant, and the recursive CTE is a lot easier to understand, so I'm going to fall back on Jeff Moden's signature line: "Just because I can do something in T-SQL, doesn't mean I shouldn't", as I'll take readability over irrelevant performance hits every day of the week. I understand where you're coming from, and if this user's application of the code will be in a heavy volume OLTP application, then you are exactly right. If it's reporting, as it appears to be, this shouldn't be an issue.I know that performance might not be an issue here, but it's a matter of habits. If you're used to the cross join style tally table generator, you won't suffer to understand it and will be able to write it easily for any amount of data needed. And if you use some 3rd party add-ins, you can create templates or snippets to generate the code immediately.
As an absolute rule, I do not get into "habits", where SQL coding is concerned. I code based on the need and take the entire requirement into account, including performance characteristics as well as maintainability. Doing otherwise tends to create problems.
So you're saying that you choose to use a poor performance option just because you expect to have a low number of rows?
That's exactly the reason that most developers write crappy SQL code (and I'm not referring to you). They test over a small number of rows and don't realise that the number of rows will grow and performance will suffer.
When you share code in a public forum, anyone can take it and might apply it to a larger set of rows than what it was intended for. That's why it's preferred to give the best option available instead of the one that's "good enough".
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply