July 29, 2015 at 10:25 am
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax)
In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
ANy ideas ? I know you gurus probably laugh at my novice skills.
July 29, 2015 at 10:36 am
dandenise316 (7/29/2015)
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax)In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
ANy ideas ? I know you gurus probably laugh at my novice skills.
You seem to have forgotten the most important part of your question, the part that explains what you are trying to do. Please take a few moments and read the first link in my signature for best practices when posting questions.
And nobody around here is going to laugh at novice skills. Everybody was a novice at some point. The way to grow is by asking questions and challenging yourself. Kudos to you for having the courage to that.
_______________________________________________________________
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/
July 29, 2015 at 10:41 am
It's pretty simple, instead of
SELECT
DATE_TRUNC('month', n.campaign_date) <--- i want the date trunc to reflect ONLY the 1st - 23rd of every month. I then want to get the count of various metrics in the same table assocaited with the 1-23rd of every month. I'm basically doing reporting month vs month to gauge effectiveness.
,COUNT(numberofresponders)
,othermetric
,othermetric
FROM
table
WHERE
campaign_date >= '2015-01-01
July 29, 2015 at 10:54 am
dandenise316 (7/29/2015)
It's pretty simple, instead ofSELECT
DATE_TRUNC('month', n.campaign_date) <--- i want the date trunc to reflect ONLY the 1st - 23rd of every month. I then want to get the count of various metrics in the same table assocaited with the 1-23rd of every month. I'm basically doing reporting month vs month to gauge effectiveness.
,COUNT(numberofresponders)
,othermetric
,othermetric
FROM
table
WHERE
campaign_date >= '2015-01-01
Simple for you because you understand what you are trying to do. Without the explanation it didn't make any sense to me.
The challenge I see is that you are referencing DATE_TRUNC which is a PostgreSQL function. This site is dedicated to SQL Server. The way you do this in sql server is going to be very different. Are you using sql server or PostgreSQL?
_______________________________________________________________
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/
July 29, 2015 at 11:07 am
you havent given any us any scripts to set up sample data plus the DATE_TRUNC is not MS SQL
but heres an idea for MS SQL
SELECT TOP 10000
CustomerID = CAST(Abs(Checksum(Newid()) % 90000 + 1) AS INT),
TransDate = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2012', '2016'), '2012')
INTO #TransData
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
SELECT YEAR(TransDate) AS CYear, MONTH(TransDate) as CMonth, COUNT(CustomerID) AS CustCnt
FROM #TransData
WHERE DAY(Transdate) < = 23 ---- alter accordingly
GROUP BY YEAR(TransDate), MONTH(TransDate)
ORDER BY YEAR(TransDate), MONTH(TransDate)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 29, 2015 at 11:18 am
J Livingston SQL (7/29/2015)
you havent given any us any scripts to set up sample data plus the DATE_TRUNC is not MS SQLbut heres an idea for MS SQL
SELECT TOP 10000CustomerID = CAST(Abs(Checksum(Newid()) % 90000 + 1) AS INT),
TransDate = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2012', '2016'), '2012')
INTO #TransData
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
SELECT YEAR(TransDate) AS CYear, MONTH(TransDate) as CMonth, COUNT(CustomerID) AS CustCnt
FROM #TransData
WHERE DAY(Transdate) < = 23 ---- alter accordingly
GROUP BY YEAR(TransDate), MONTH(TransDate)
ORDER BY YEAR(TransDate), MONTH(TransDate)
Or, based on the snippet provided:
SELECT
month(campaign_date) CalendarMonth
,COUNT(numberofresponders)
,othermetric
,othermetric
FROM
table
WHERE
campaign_date >= '2015-01-01' and
day(campaign_date) between 1 and 23
group by
month(campaign_date)
order by
month(campaign_date);
By the way, neither of these may be the most performant or scalable solutions. Without more details as to what you are trying to accomplish, DDL for the table(s) involved, sample data for the tables, and expected results all you are going to get are shots in the dark.
July 29, 2015 at 11:46 am
Thanks for the input guys..
I tried using the second solution with month(n.campaign_date)
..
WHERE
day(campaign_date) between 1 and 23
And I'm getting the following error.
ERROR: 42883: function day(timestamp without time zone) does not exist
July 29, 2015 at 11:51 am
dandenise316 (7/29/2015)
Thanks for the input guys..I tried using the second solution with month(n.campaign_date)
..
WHERE
day(campaign_date) between 1 and 23
And I'm getting the following error.
ERROR: 42883: function day(timestamp without time zone) does not exist
is that a PostgresSQL error?
this, as has already been said is a forum for MS SQL Server
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 29, 2015 at 12:01 pm
J Livingston SQL (7/29/2015)
dandenise316 (7/29/2015)
Thanks for the input guys..I tried using the second solution with month(n.campaign_date)
..
WHERE
day(campaign_date) between 1 and 23
And I'm getting the following error.
ERROR: 42883: function day(timestamp without time zone) does not exist
is that a PostgresSQL error?
this, as has already been said is a forum for MS SQL Server
We are supposedly moving to PostgreSQL, so perhaps I should start learning it. Would help with answering this type of question.
July 29, 2015 at 12:35 pm
It is. I apologize, for some reason, I figured this forum is able to tackle anything and any version of SQL
July 29, 2015 at 1:33 pm
dandenise316 (7/29/2015)
It is. I apologize, for some reason, I figured this forum is able to tackle anything and any version of SQL
Sometimes you can find people with the expertise you are looking for here, but for the most part we are MS SQL Server DBAs/Developers and that is where our strengths exist.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply