DATEADD

Question

Post reply

DATEADD

m.kadlag

SSC Rookie

Points: 41
More actions
August 17, 2010 at 12:13 pm

#224882

Hi All, I have a table with following structure:
ID
DATETIME
ID will be generated for different timestamps.
I have a small query to be written which will return the count of ID's date wise. I have writen the script as follows:
select dateadd(day, datediff(day, '99991231', datetime), '99991231'),
count(id) as ABCD
from tablename
group by dateadd(day, datediff(day, '99991231', datetime), '99991231')
order by dateadd(day, datediff(day, '99991231', datetime), '99991231'
THE ISSUE IS THAT : this returns the data only for the dates for which records are available.
Need the query to return count of id as ZERO in case no record for that date exists..
Please suggest ASAP.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

Check the following article by Jeff Moden on using a Tally table..

http://www.sqlservercentral.com/articles/T-SQL/62867/

towards the bottom there's a dozens of other uses section. I think it should get you pointed in the direction you need to head.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

Heh... maybe I've taken things to far. No one has to write code about the Tally table anymore. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

Jeff, one of the best parts of your articles are the clear concise, well formatted/documented real world code examples that can be easily understood and implemented. There's just no way I could have explained it better.

It's interesting though that this was the second thread in a row where someone had posted, read Jeff's tally table article. Look at the code under heading X understand and use it.

Seems like that's the mark of very, very good reference material 😉

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

Gosh, Luke... :blush: You've absolutely made my week... maybe my year. I'm humbled by your incredible comments. I really appreciate your feedback because I've had a whole lot of "well meaning" folks say that I'm a bit too "folksy" in my articles and that I should "write more professionally rather than writing like someone talks". I just can't bring myself to do such a thing because I know how I like to be taught and I write the same way as that. Heh... and, NO, I AM NOT SMARTER THAN A 5TH GRADER. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

Luke L (8/18/2010)
Jeff, one of the best parts of your articles are the clear concise, well formatted/documented real world code examples that can be easily understood and implemented. There's just no way I could have explained it better.

Seems like that's the mark of very, very good reference material 😉
-Luke.

AGREED - I have learned a tremendous amount from Jeff's articles. Especially coming from a GUI development position and new to T-SQL. Just hope others listen and learn as I did.

Thamks Jeff

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

You're welcome, Ron, and thanks for the feedback. I knew I did ok... just had no idea of how ok. Thanks, guys.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luke L SSC-Dedicated Points: 35547 More actions · Answer 7

Jeff, I'd say you do a fair bit more than just OK, I appreciate the humility, but in this case I don't think it's warranted. As far as the folksy stuff bah, more times than not easy to read and understand trumps dry technical writing every day and twice on Sunday for me. But like I said before it's the examples you choose to illustrate your points that really does it for me.

They are real world problems that many people face (just like the OP here), not some contrived example put forth to show a trick that will work only in some very narrow situation... While those examples can be fun to read and try to understand, code that I will need and be able to apply tomorrow or next week instead of 2 years down the road is what I read these articles for.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

m.kadlag (8/17/2010)
Hi All, I have a table with following structure:
ID
DATETIME
ID will be generated for different timestamps.
I have a small query to be written which will return the count of ID's date wise. I have writen the script as follows:
THE ISSUE IS THAT : this returns the data only for the dates for which records are available.
Need the query to return count of id as ZERO in case no record for that date exists..
Please suggest ASAP.

So, I've got to ask... are you all set on this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Divine Flame SSCoach Points: 15943 More actions · Answer 9

Hi,

I faced a similar situation when I need to get the list of all the tables with the index on them. I had to show null for the those tables also that dont have any index, so I wrote below given script. You may want to use the similar kind of script for your requirement.

* you can use ISNULL() function to replace the null values with 0 in the result set as you have specified. I wanted to show NULL so I didnt use that here.

Hope this helps 🙂

DECLARE @TABLE_NAME SYSNAME

DECLARE @INDEX_NAMESYSNAME

DECLARE @INDEX_KEYSVARCHAR(3000)

DECLARE @INDEX_TYPEVARCHAR(1000)

IF EXISTS (SELECT 1 FROM TEMPDB..SYSOBJECTS WHERE NAME LIKE '%#TABLE_INDEX_INFO%')

BEGIN

DROP TABLE #TABLE_INDEX_INFO

END

CREATE TABLE #TABLE_INDEX_INFO

(

S_NO INT IDENTITY (1,1),

TABLE_NAME SYSNAME ,

INDEX_NAME SYSNAME,

INDEX_TYPE VARCHAR(1000),

INDEX_KEYS VARCHAR(3000)

)

IF EXISTS (SELECT 1 FROM TEMPDB..SYSOBJECTS WHERE NAME LIKE '%#SP_HELP_INDEX_RESULTS%')

BEGIN

DROP TABLE #SP_HELP_INDEX_RESULTS

END

CREATE TABLE #SP_HELP_INDEX_RESULTS

(

INDEX_NAME SYSNAME,

INDEX_TYPE VARCHAR(1000),

INDEX_KEYS VARCHAR(3000)

)

DECLARE CUR_TABLES CURSOR FAST_FORWARD

FOR SELECT NAME FROM SYS.TABLES WHERE TYPE = 'U'

OPEN CUR_TABLES

FETCH NEXT FROM CUR_TABLES INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0

BEGIN

TRUNCATE TABLE #SP_HELP_INDEX_RESULTS

INSERT INTO #SP_HELP_INDEX_RESULTS (INDEX_NAME,INDEX_TYPE,INDEX_KEYS) EXECUTE SP_HELPINDEX @TABLE_NAME

INSERT INTO #TABLE_INDEX_INFO (TABLE_NAME,INDEX_NAME,INDEX_KEYS,INDEX_TYPE)

SELECT @TABLE_NAME,INDEX_NAME,INDEX_KEYS,INDEX_TYPE FROM #SP_HELP_INDEX_RESULTS

FETCH NEXT FROM CUR_TABLES INTO @TABLE_NAME

END

CLOSE CUR_TABLES

DEALLOCATE CUR_TABLES

SELECT DB_NAME() as 'Database', T.NAME AS 'TableName',TI.INDEX_NAME as 'IndexName',TI.INDEX_KEYS as 'IndexKeys',TI.INDEX_TYPE as 'IndexType'

FROM SYS.TABLES T LEFT OUTER JOIN #TABLE_INDEX_INFO TI

ON T.NAME = TI.TABLE_NAME

ORDER BY T.NAME

-Sujeet

Sujeet Singh

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 10

sujeetps (8/20/2010)
Hi,
I faced a similar situation when I need to get the list of all the tables with the index on them. I had to show null for the those tables also that dont have any index, so I wrote below given script. You may want to use the similar kind of script for your requirement.
* you can use ISNULL() function to replace the null values with 0 in the result set as you have specified. I wanted to show NULL so I didnt use that here.
Hope this helps 🙂
DECLARE @TABLE_NAME SYSNAME
DECLARE @INDEX_NAMESYSNAME
DECLARE @INDEX_KEYSVARCHAR(3000)
DECLARE @INDEX_TYPEVARCHAR(1000)
IF EXISTS (SELECT 1 FROM TEMPDB..SYSOBJECTS WHERE NAME LIKE '%#TABLE_INDEX_INFO%')
BEGIN
DROP TABLE #TABLE_INDEX_INFO
END
CREATE TABLE #TABLE_INDEX_INFO
(
S_NO INT IDENTITY (1,1),
TABLE_NAME SYSNAME ,
INDEX_NAME SYSNAME,
INDEX_TYPE VARCHAR(1000),
INDEX_KEYS VARCHAR(3000)
)
IF EXISTS (SELECT 1 FROM TEMPDB..SYSOBJECTS WHERE NAME LIKE '%#SP_HELP_INDEX_RESULTS%')
BEGIN
DROP TABLE #SP_HELP_INDEX_RESULTS
END
CREATE TABLE #SP_HELP_INDEX_RESULTS
(
INDEX_NAME SYSNAME,
INDEX_TYPE VARCHAR(1000),
INDEX_KEYS VARCHAR(3000)
)
DECLARE CUR_TABLES CURSOR FAST_FORWARD
FOR SELECT NAME FROM SYS.TABLES WHERE TYPE = 'U'
OPEN CUR_TABLES
FETCH NEXT FROM CUR_TABLES INTO @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE TABLE #SP_HELP_INDEX_RESULTS
INSERT INTO #SP_HELP_INDEX_RESULTS (INDEX_NAME,INDEX_TYPE,INDEX_KEYS) EXECUTE SP_HELPINDEX @TABLE_NAME
INSERT INTO #TABLE_INDEX_INFO (TABLE_NAME,INDEX_NAME,INDEX_KEYS,INDEX_TYPE)
SELECT @TABLE_NAME,INDEX_NAME,INDEX_KEYS,INDEX_TYPE FROM #SP_HELP_INDEX_RESULTS
FETCH NEXT FROM CUR_TABLES INTO @TABLE_NAME
END
CLOSE CUR_TABLES
DEALLOCATE CUR_TABLES
SELECT DB_NAME() as 'Database', T.NAME AS 'TableName',TI.INDEX_NAME as 'IndexName',TI.INDEX_KEYS as 'IndexKeys',TI.INDEX_TYPE as 'IndexType'
FROM SYS.TABLES T LEFT OUTER JOIN #TABLE_INDEX_INFO TI
ON T.NAME = TI.TABLE_NAME
ORDER BY T.NAME
-Sujeet

Correct... the ISNULL() function could be the thing to use here. But, the original problem doesn't have a given date to use the ISNULL() function on for any dates that are missing like your table index example does.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 11

Jeff Moden (8/19/2010)
m.kadlag (8/17/2010)
Hi All, I have a table with following structure:
ID
DATETIME
ID will be generated for different timestamps.
I have a small query to be written which will return the count of ID's date wise. I have writen the script as follows:
THE ISSUE IS THAT : this returns the data only for the dates for which records are available.
Need the query to return count of id as ZERO in case no record for that date exists..
Please suggest ASAP.
So, I've got to ask... are you all set on this?

Kadlag... you wanted help "ASAP". The courtesy of a reply to my question above is in order here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Jeff Moden (8/18/2010)
... I've had a whole lot of "well meaning" folks say that I'm a bit too "folksy" in my articles and that I should "write more professionally rather than writing like someone talks"...

Ignore them, Jeff. People vote with their fingertips. Try and count 'em. What puts your articles head and shoulders above many is the 'digestibility'. The tally table used to be an advanced concept but with your clear and digestible explanation of how it works, it's now just another tool in every TSQL developers' box.

Stick to "folksy". We like reading it, you like writing it. Seemples! <<squeak>>

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 13

Chris Morris-439714 (8/20/2010)
'digestibility'. The tally table used to be an advanced concept but... ... it's now just another tool in every TSQL developers' box.

I guess that's the real key and what I've always aimed for. Thanks for the encouragement, Chris. I'll keep it "Seemples" and digestible. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SteveBell SSCrazy Points: 2008 More actions · Answer 14

Jeff,

Stay folksy. Your articles are great the way they are.

I'm new to the forums and not a DBA by profession. I read the posts and articles here to broaden my understanding of SQL Server and the issues that arise.

I read your Tally Table article and it was very helpful.

Steve