Update Query Date Range

Question

Update Query Date Range

scotsditch

Ten Centuries

Points: 1020
More actions
December 13, 2013 at 10:58 am

#395985

Hi I have the query shown below which counts unique customers generated over a date range.
I'd like to make it so the date range part,
C.dateCreated between '06/01/2012 00:00:00' and '12/01/2013 00:00:00'
automatically increments forward 1 month (example below),
C.dateCreated between '07/01/2012 00:00:00' and '1/01/2014 00:00:00'
each time I run it at the beginning of the new month.
I was thinking maybe a stored procedure with default parameters, does anybody know a slick trick for doing something like this?
-- unique customers created per month
select C.clientID, STR(YEAR(C.dateCreated), 4) + '/' + STR(MONTH(C.dateCreated), 2) as YearMonth, COUNT(C.customerName) customerCount
from (select C.clientID, C.lastName + ', ' + C.firstName as customerName, MIN(C.dateCreated) as dateCreated
from Customer C
inner join Client CL
on CL.clientID = C.clientID
where CL.clientName in ('Visa Signature USA', 'Visa Infinite Canada')
and C.dateCreated between '06/01/2012 00:00:00' and '12/01/2013 00:00:00'
group by C.clientID, C.lastName + ', ' + C.firstName) C
group by C.clientID, STR(YEAR(C.dateCreated), 4) + '/' + STR(MONTH(C.dateCreated), 2)
order by C.clientID, STR(YEAR(C.dateCreated), 4) + '/' + STR(MONTH(C.dateCreated), 2)

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

scotsditch (12/13/2013)
Hi I have the query shown below which counts unique customers generated over a date range.
I'd like to make it so the date range part,
C.dateCreated between '06/01/2012 00:00:00' and '12/01/2013 00:00:00'
automatically increments forward 1 month (example below),
C.dateCreated between '07/01/2012 00:00:00' and '1/01/2014 00:00:00'
each time I run it at the beginning of the new month.
I was thinking maybe a stored procedure with default parameters, does anybody know a slick trick for doing something like this?
-- unique customers created per month
select C.clientID, STR(YEAR(C.dateCreated), 4) + '/' + STR(MONTH(C.dateCreated), 2) as YearMonth, COUNT(C.customerName) customerCount
from (select C.clientID, C.lastName + ', ' + C.firstName as customerName, MIN(C.dateCreated) as dateCreated
from Customer C
inner join Client CL
on CL.clientID = C.clientID
where CL.clientName in ('Visa Signature USA', 'Visa Infinite Canada')
and C.dateCreated between '06/01/2012 00:00:00' and '12/01/2013 00:00:00'
group by C.clientID, C.lastName + ', ' + C.firstName) C
group by C.clientID, STR(YEAR(C.dateCreated), 4) + '/' + STR(MONTH(C.dateCreated), 2)
order by C.clientID, STR(YEAR(C.dateCreated), 4) + '/' + STR(MONTH(C.dateCreated), 2)

It is a bit difficult to figure out what you want here. I think you want something that would be dynamic based on the current date?

Something like this work?

SELECT C.clientID,

Str(Year(C.dateCreated), 4) + '/'

+ Str(Month(C.dateCreated), 2) AS YearMonth,

Count(C.customerName) customerCount

FROM (SELECT C.clientID,

C.lastName + ', ' + C.firstName AS customerName,

Min(C.dateCreated) AS dateCreated

FROM Customer C

INNER JOIN Client CL

ON CL.clientID = C.clientID

WHERE CL.clientName IN ( 'Visa Signature USA', 'Visa Infinite Canada' )

and c.dateCreated > dateadd(mm, datediff(mm, 0, GETDATE()) - 6, 0)

and c.dateCreated < dateadd(mm, datediff(mm, 0, GETDATE()), 0)

GROUP BY C.clientID,

C.lastName + ', ' + C.firstName) C

GROUP BY C.clientID,

Str(Year(C.dateCreated), 4) + '/'

+ Str(Month(C.dateCreated), 2)

ORDER BY C.clientID,

Str(Year(C.dateCreated), 4) + '/'

+ Str(Month(C.dateCreated), 2)

_______________________________________________________________

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/

scotsditch Ten Centuries Points: 1020 More actions · Answer 2

Thanks, that worked perfect.

What I'd like to do is schedule this query to run at the beginning of each month and send me the results.

Would you happen to have any tips on an easy way to do that?

I was thinking maybe create a report and schedule a subscription to have the report server email it to me, or possibly a sql server agent job to run a stored procedure.

Also I'm a little new to forums, could you give me a tip on how you get your query to format so nicely when it displays on the forum post?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

scotsditch (12/13/2013)
Also I'm a little new to forums, could you give me a tip on how you get your query to format so nicely when it displays on the forum post?

When you create your post, you'll notice "IFCode Shorcuts" to the left of the window you type your response in. If you look at THIS message by pressing the QUOTE button on it, you'll see how the following code is formatted.

SELECT 'This is formatted code' FROM dbo.SomeTable

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

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

scotsditch (12/13/2013)
I was thinking maybe create a report and schedule a subscription to have the report server email it to me, or possibly a sql server agent job to run a stored procedure.

That sounds like two of the simplest ways to do this. I would suggest either of those as a viable option.

Glad my original reply helped solve the issue for you and thanks for letting me know.

_______________________________________________________________

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/