Record for each day/client for date range even with no data?

Question

Record for each day/client for date range even with no data?

jbalbo

SSCertifiable

Points: 7583
More actions
March 15, 2018 at 7:43 am

#410321

Hi
I need to write out a record for each day within a date range(month) even with no data
example or what I have
date client data
1/1/18 1    xxx
1/3/18 1    xxx
1/4/18   1   xxx
what I need
date client data
1/1/18 1    xxx
1/2/18 1    null
1/3/18   1   xxx
1/4/18    1 xxx
I 'm thinking I need some cross join?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

jbalbo - Thursday, March 15, 2018 7:43 AM
Hi
I need to write out a record for each day within a date range(month) even with no data
example or what I have
date client data
1/1/18 1    xxx
1/3/18 1    xxx
1/4/18   1   xxx
what I need
date client data
1/1/18 1    xxx
1/2/18 1    null
1/3/18   1   xxx
1/4/18    1 xxx
I 'm thinking I need some cross join?
Thanks

You need to have a table that has all the dates as the "base" for your query. You could do this with a calendar table or a tally table quite easily.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jbalbo SSCertifiable Points: 7583 More actions · Answer 2

Hi Sean
Thanks for getting back ..
I do have a dimdate table
would you know of a short example I could follow?

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Here is how I would handle this. I have a tally table on my system that I keep as a view. It is using the same tally table as Jeff Moden's string splitter. Here is what the view looks like.

create View [dbo].[cteTally] as

WITH E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ) select N from cteTally GO

Now in this example I created a table variable to hold the data. You would use your real table instead.

declare @Something table(MyDate date, client int, data char(3))

insert @Something values ('20180101', 1, 'xxx') ,('20180103', 1,'xxx') ,('20180104', 1,'xxx')

select dateadd(day, t.N - 1, '20180101') , s.client , s.data from cteTally t left join @Something s on s.MyDate = dateadd(day, t.N - 1, '20180101') where t.N <= 4

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sgmunson SSC Guru Points: 110640 More actions · Answer 4

jbalbo - Thursday, March 15, 2018 7:53 AM
Hi Sean
Thanks for getting back ..
I do have a dimdate table
would you know of a short example I could follow?
Thanks

That's going to depend on the structure of your DimDate table, but basically, you need a query that selects a range of dates from that table as single records, and a FULL OUTER join to that SELECT to get the final results.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)