May 18, 2016 at 2:04 am
HI Experts
The below query works on year to month ... works good but i am unable to control the date . E.g my year to date is '10/5/2015' if i run the query on '10/5/2015' it gives me one row result with out entering the date 20150101 start of year ?
SELECT YEAR(START_DATETIME)*100 +MONTH(START_DATETIME),
SUM(SALES)
FROM MY_TABLE
AND YEAR(START_DATETIME)*10000 +MONTH(START_DATETIME)*100+ DAY(START_DATETIME)>=20150101
AND START_DATETIME <= '10/5/2015'
GROUP BY YEAR(START_DATETIME)*100 +MONTH(START_DATETIME)
May 18, 2016 at 6:18 am
The requirement is not really clear
Can you please clarify it a bit further with some DDL, sample data to test against and the expected results
You can check the link in my signature if you are not sure on how to provide this
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 18, 2016 at 10:29 am
smer (5/18/2016)
HI ExpertsThe below query works on year to month ... works good but i am unable to control the date . E.g my year to date is '10/5/2015' if i run the query on '10/5/2015' it gives me one row result with out entering the date 20150101 start of year ?
SELECT YEAR(START_DATETIME)*100 +MONTH(START_DATETIME),
SUM(SALES)
FROM MY_TABLE
AND YEAR(START_DATETIME)*10000 +MONTH(START_DATETIME)*100+ DAY(START_DATETIME)>=20150101
AND START_DATETIME <= '10/5/2015'
GROUP BY YEAR(START_DATETIME)*100 +MONTH(START_DATETIME)
You are missing a WHERE clause.
Converting your START_DATETIME to an INT makes it non-SARGable, which severely affects performance. These two lines are equivalent, but the second is SARGable where the first is not.
YEAR(START_DATETIME)*10000 +MONTH(START_DATETIME)*100+ DAY(START_DATETIME)>=20150101
START_DATETIME >= '20150101'
Please post code to provide sample data that shows your issue as described in the link in my signature.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 18, 2016 at 10:50 am
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, START_DATETIME), 0) AS START_MONTH,
SUM(SALES) AS TOTAL_SALES
FROM MY_TABLE
WHERE START_DATETIME >= '20150101' AND START_DATETIME < '20150511'
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, START_DATETIME), 0)
ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, START_DATETIME), 0)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 18, 2016 at 1:01 pm
Or like this:
d e c l a r e @ProcessDate datetime = '20151005'; -- spaces added to allow code to be posted
select
dateadd(month, datediff(month, 0, mt.START_DATETIME), 0) as StartMonth,
sum(mt.SALES) as TotalSales
from
dbo.MyTable mt
where
mt.START_DATETIME >= dateadd(year, datediff(year, 0, @ProcessDate), 0) and
mt.START_DATETIME < @ProcessDate
group by
dateadd(month, datediff(month, 0, mt.START_DATETIME), 0)
order by
dateadd(month, datediff(month, 0, mt.START_DATETIME), 0);
May 18, 2016 at 2:06 pm
Experts !
Below iam posting the table, sample insert and query ,which i am using. I need when ever i give the end date like '1/7/2014' the results comes from first day of the year (1/1/2014 to 1/7/2014) to given process date with only one row result not to put the start date of the year every time sql auto analyize 1/7/2014 and auto calculate first date of year to date results.
USE [TESTDB]
GO
--DROP TABLE MY_TABLE
CREATE TABLE [dbo].[MY_TABLE](
[GP] [char](32) NULL,
[IDI] [char](32) NULL,
[TYPE] [varchar](14) NULL,
[INVT] [varchar](20) NULL,
[NAME] [varchar](20) NULL,
[START_DATETIME] [datetime] NULL,
[END_DATETIME] [datetime] NULL,
[SALES] [numeric](28, 12) NULL,
)
GO
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','12/30/2013','12/30/2013',1)
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','12/31/2013','12/31/2013',2)
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','1/1/2014','1/1/2014',3)
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','1/2/2014','1/2/2014',4)
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','1/3/2014','1/3/2014',5)
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','1/4/2014','1/4/2014',6)
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','1/5/2014','1/5/2014',7)
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','1/6/2014','1/6/2014',8)
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','1/7/2014','1/7/2014',9)
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','1/8/2014','1/8/2014',10)
SELECT YEAR(START_DATETIME)*100 +MONTH(START_DATETIME) DATE1,
SUM(SALES) 'SALES'
FROM MY_TABLE
WHERE YEAR(START_DATETIME)*10000 +MONTH(START_DATETIME)*100+ DAY(START_DATETIME)>=20140101--DATE WILL AUTO GO TO FIRST DATE OF YEAR
AND START_DATETIME <= '1/7/2014'
GROUP BY YEAR(START_DATETIME)*100 +MONTH(START_DATETIME)
Date1 SALES
20140142.000000000000
May 18, 2016 at 2:11 pm
smer (5/18/2016)
Experts !Below iam posting the table, sample insert and query ,which i am using. I need when ever i give the end date like '1/7/2014' the results comes from first day of the year (1/1/2014 to 1/7/2014) to given process date with only one row result as shown below in the end.
USE [TESTDB]
GO
--DROP TABLE MY_TABLE
CREATE TABLE [dbo].[MY_TABLE](
[GP] [char](32) NULL,
[IDI] [char](32) NULL,
[TYPE] [varchar](14) NULL,
[INVT] [varchar](20) NULL,
[NAME] [varchar](20) NULL,
[START_DATETIME] [datetime] NULL,
[END_DATETIME] [datetime] NULL,
[SALES] [numeric](28, 12) NULL,
)
GO
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','12/30/2013','12/30/2013',1)
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','12/31/2013','12/31/2013',2)
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','1/1/2014','1/1/2014',3)
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','1/2/2014','1/2/2014',4)
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','1/3/2014','1/3/2014',5)
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','1/4/2014','1/4/2014',6)
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','1/5/2014','1/5/2014',7)
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','1/6/2014','1/6/2014',8)
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','1/7/2014','1/7/2014',9)
INSERT [dbo].[MY_TABLE] ([GP], [IDI], [TYPE], [INVT],[NAME] , [START_DATETIME],[END_DATETIME], [SALES]) VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PRODUCT','PHASE' ,'AREA','1/8/2014','1/8/2014',10)
SELECT YEAR(START_DATETIME)*100 +MONTH(START_DATETIME) DATE1,
SUM(SALES) 'SALES'
FROM MY_TABLE
WHERE YEAR(START_DATETIME)*10000 +MONTH(START_DATETIME)*100+ DAY(START_DATETIME)>=20140101--DATE WILL AUTO GO TO FIRST DATE OF YEAR
AND START_DATETIME <= '1/7/2014'
GROUP BY YEAR(START_DATETIME)*100 +MONTH(START_DATETIME)
Date1 SALES
20140142.000000000000
See the code I posted previously.
May 18, 2016 at 2:20 pm
Thanks Lynn Pettis for your code...
code showing many rows . It is possible to have one row result year to date ,only i have to pass the @processdate and code knows the startdate of year?
May 18, 2016 at 2:23 pm
smer (5/18/2016)
Thanks Lynn Pettis for your code...code showing many rows . It is possible to have one row result year to date ,only i have to pass the @processdate and code knows the startdate of year?
If I understand you correctly, change "month" to "year" in the appropriate dateadd functions in the code provided.
May 18, 2016 at 2:44 pm
The data provided only has data for 2013-12 and 2014-01, so there is only one month in the "current" year, which is why you are only getting one row. If you want data for a fiscal year that differs from the calendar year, then you need to make an adjustment that depends on when your fiscal year starts. If you want data for the previous 12 months, then you just need to update your WHERE clause.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 18, 2016 at 2:49 pm
smer (5/18/2016)
Thanks Lynn Pettis for your code...code showing many rows . It is possible to have one row result year to date ,only i have to pass the @processdate and code knows the startdate of year?
I think I misunderstood your question. Yes, by providing the processing date, the query can determine the start of the calendar year. Look at the dateadd function in the WHERE clause. It determines the first of the year based on the value of @ProcessDate.
May 18, 2016 at 2:53 pm
Lynn Pettis ...
Exactly ! this is my requirement. Is this code works on fiscal year to date start from July to June?
May 18, 2016 at 2:56 pm
Lynn Pettis ...
Exactly ! this is my requirement. Is this code works on fiscal year to date start from July to June?
May 18, 2016 at 3:15 pm
smer (5/18/2016)
Lynn Pettis ...Exactly ! this is my requirement. Is this code works on fiscal year to date start from July to June?
This is the first you said of that. Looking at your previous posts everything went back to the first of the calendar year.
Stand by a few minutes.
May 18, 2016 at 3:28 pm
Here is updated code. For 2015-10-05, it will go back to 2015-07-01 for the start of the fiscal year.
If @ProcessDate is 2016-06-30, it will still go back to 2015-07-01.
select
dateadd(month, datediff(month, 0, mt.START_DATETIME), 0) as StartMonth,
sum(mt.SALES) as TotalSales
from
dbo.MyTable mt
where
mt.START_DATETIME >= dateadd(month, -6, dateadd(year, datediff(year, 0, dateadd(month, 6, @ProcessDate)), 0)) and
mt.START_DATETIME < dateadd(day, 1, @ProcessDate) -- changed to capture processed date, originally would exclude
group by
dateadd(month, datediff(month, 0, mt.START_DATETIME), 0)
order by
dateadd(month, datediff(month, 0, mt.START_DATETIME), 0);
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply