Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update Query Date Range Expand / Collapse
Author
Message
Posted Friday, December 13, 2013 10:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:03 PM
Points: 29, Visits: 72
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)
Post #1522807
Posted Friday, December 13, 2013 12:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 13,298, Visits: 12,149
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1522839
Posted Friday, December 13, 2013 4:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:03 PM
Points: 29, Visits: 72
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?
Post #1522890
Posted Friday, December 13, 2013 6:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 36,978, Visits: 31,500
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1522909
Posted Monday, December 16, 2013 7:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 13,298, Visits: 12,149
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1523227
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse