October 22, 2018 at 9:03 am
hi,
I have following sample data set:
SaleID SaleMarkdown StartDate(yyyymmdd) EndDate(yyyymmdd) SaleInPreviousYear previousyearsaleID
1 20% 20160101 20160301
2 30% 20170501 20170630
3 40% 20170101 20170201
4 10% 20180101 20180301
5 15% 20180401 20180430
Last two columns are empty at the moment.
I would like to find out if there was a sale +-30 days in the previous year for a given sale.
For example for saleid 4 the sale date range is between 20180101 and 20180301. Was there a sale in the previous year during this time period? if so update SaleInPreviousYear column to yes and update previousyearsaleID column with the saleid of the previous year. In this case saleid 3 falls within +-30 days of the sale start or sale end date.
SaleID SaleMarkdown StartDate(yyyymmdd) EndDate(yyyymmdd) SaleInPreviousYear previousyearsaleID
1 20% 20160101 20160301
2 30% 20170501 20170630
3 40% 20170101 20170201
4 10% 20180101 20180301 yes 3
5 15% 20180401 20180430
Sale 5 is another example where sale 2 occured in the +-30 range of start and end date in the previous year.
How can I accomplish this in TSQL?
Thanks
October 22, 2018 at 9:13 am
One way is to create a calendar table (either permanent or on-the-fly) and join to that. You'll get much better and answers much quicker if you post table DDL and sample data in consumable format (ie CREATE TABLE and INSERT statements).
John
October 22, 2018 at 9:16 am
sqlstar2011 - Monday, October 22, 2018 9:03 AMhi,I have following sample data set:
SaleID SaleMarkdown StartDate(yyyymmdd) EndDate(yyyymmdd) SaleInPreviousYear previousyearsaleID
1 20% 20160101 20160301
2 30% 20170501 20170630
3 40% 20170101 20170201
4 10% 20180101 20180301
5 15% 20180401 20180430Last two columns are empty at the moment.
I would like to find out if there was a sale +-30 days in the previous year for a given sale.
For example for saleid 4 the sale date range is between 20180101 and 20180301. Was there a sale in the previous year during this time period? if so update SaleInPreviousYear column to yes and update previousyearsaleID column with the saleid of the previous year. In this case saleid 3 falls within +-30 days of the sale start or sale end date.
SaleID SaleMarkdown StartDate(yyyymmdd) EndDate(yyyymmdd) SaleInPreviousYear previousyearsaleID
1 20% 20160101 20160301
2 30% 20170501 20170630
3 40% 20170101 20170201
4 10% 20180101 20180301 yes 3
5 15% 20180401 20180430Sale 5 is another example where sale 2 occured in the +-30 range of start and end date in the previous year.
How can I accomplish this in TSQL?
Thanks
Could you post the code you currently have written?
October 22, 2018 at 9:39 am
Here is the DDL and insert statements for the sample data:
create table dateRangeTest(
saleid int,
salemarkdown decimal(3,2),
startdate date,
enddate date,
saleinpreviousyear varchar(5),
previousyearsaleID int
)
insert into dateRangeTest values(1, .20, '01/01/2016', '03/01/2016',NULL, NULL)
insert into dateRangeTest values(2, .30, '05/01/2017', '06/30/2017',NULL, NULL)
insert into dateRangeTest values(3, .40, '01/01/2017', '02/01/2017',NULL, NULL)
insert into dateRangeTest values(4, .10, '01/01/2018', '03/01/2018',NULL, NULL)
insert into dateRangeTest values(5, .15, '04/01/2018', '04/30/2018',NULL, NULL)
thanks
October 22, 2018 at 9:48 am
sqlstar2011 - Monday, October 22, 2018 9:39 AMHere is the DDL and insert statements for the sample data:create table dateRangeTest(
saleid int,
salemarkdown decimal(3,2),
startdate date,
enddate date,
saleinpreviousyear varchar(5),
previousyearsaleID int
)insert into dateRangeTest values(1, .20, '01/01/2016', '03/01/2016',NULL, NULL)
insert into dateRangeTest values(2, .30, '05/01/2017', '06/30/2017',NULL, NULL)
insert into dateRangeTest values(3, .40, '01/01/2017', '02/01/2017',NULL, NULL)
insert into dateRangeTest values(4, .10, '01/01/2018', '03/01/2018',NULL, NULL)
insert into dateRangeTest values(5, .15, '04/01/2018', '04/30/2018',NULL, NULL)thanks
Well, that looks like is shows the results you want. What I asked was for the code you are running that actually generates the results.
October 22, 2018 at 9:51 am
hi Lynn,
I don't have anything at this point. As I am struggling with how to actually write SQL for this.
I was looking for some example that I can work with.
Thanks
October 22, 2018 at 10:19 am
I dont think creating a calendar table from the base table will work.
I created a temp table from the base table:
select distinct startdate, enddate
into #calendar
from dateRangeTest
but something like this:
SELECT a.saleid, a.salemarkdown, a.startdate, a.enddate, c.startdate,c.enddate
from dateRangeTest a
INNER JOIN #calendar c
ON a.startDate BETWEEN c.startdate AND c.enddate
is going to return everything.
How can I filter out records.
For example the record with saleid 1 - shoud not show up since there is no record for it in the previous dates.
October 22, 2018 at 10:54 am
I see a similar solution in this post:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9834a8f9-2acf-4d2d-b4a7-78ad8be15d0a/how-to-produce-the-effect-of-a-date-range-inner-join-without-actually-doing-one?forum=transactsql
how should I go about constructing a calendar table in my case?
October 22, 2018 at 10:57 am
sqlstar2011 - Monday, October 22, 2018 9:03 AMhi,I have following sample data set:
SaleID SaleMarkdown StartDate(yyyymmdd) EndDate(yyyymmdd) SaleInPreviousYear previousyearsaleID
1 20% 20160101 20160301
2 30% 20170501 20170630
3 40% 20170101 20170201
4 10% 20180101 20180301
5 15% 20180401 20180430Last two columns are empty at the moment.
I would like to find out if there was a sale +-30 days in the previous year for a given sale.
For example for saleid 4 the sale date range is between 20180101 and 20180301. Was there a sale in the previous year during this time period? if so update SaleInPreviousYear column to yes and update previousyearsaleID column with the saleid of the previous year. In this case saleid 3 falls within +-30 days of the sale start or sale end date.
SaleID SaleMarkdown StartDate(yyyymmdd) EndDate(yyyymmdd) SaleInPreviousYear previousyearsaleID
1 20% 20160101 20160301
2 30% 20170501 20170630
3 40% 20170101 20170201
4 10% 20180101 20180301 yes 3
5 15% 20180401 20180430Sale 5 is another example where sale 2 occured in the +-30 range of start and end date in the previous year.
How can I accomplish this in TSQL?
Thanks
SELECT d1.saleid,
d1.salemarkdown,
d1.startdate,
d1.enddate,
IIF(SalesMadeLastYear.SalesIds IS NULL,'No','Yes') saleinpreviousyear,
ISNULL(SalesMadeLastYear.SalesIds,'') previousyearsaleIDs
FROM dateRangeTest d1
OUTER APPLY(SELECT STUFF((SELECT ',' + CONVERT(varchar,d2.saleid) AS [text()]
FROM dateRangeTest d2
WHERE d2.startdate >= dateadd(yy,-1,d1.startdate)
AND d2.enddate <= dateadd(yy,-1,d1.enddate)
FOR XML PATH('')), 1, 1, '' )) SalesMadeLastYear(SalesIds)
October 22, 2018 at 11:44 am
Jonathan AC Roberts - Monday, October 22, 2018 10:57 AMsqlstar2011 - Monday, October 22, 2018 9:03 AMhi,I have following sample data set:
SaleID SaleMarkdown StartDate(yyyymmdd) EndDate(yyyymmdd) SaleInPreviousYear previousyearsaleID
1 20% 20160101 20160301
2 30% 20170501 20170630
3 40% 20170101 20170201
4 10% 20180101 20180301
5 15% 20180401 20180430Last two columns are empty at the moment.
I would like to find out if there was a sale +-30 days in the previous year for a given sale.
For example for saleid 4 the sale date range is between 20180101 and 20180301. Was there a sale in the previous year during this time period? if so update SaleInPreviousYear column to yes and update previousyearsaleID column with the saleid of the previous year. In this case saleid 3 falls within +-30 days of the sale start or sale end date.
SaleID SaleMarkdown StartDate(yyyymmdd) EndDate(yyyymmdd) SaleInPreviousYear previousyearsaleID
1 20% 20160101 20160301
2 30% 20170501 20170630
3 40% 20170101 20170201
4 10% 20180101 20180301 yes 3
5 15% 20180401 20180430Sale 5 is another example where sale 2 occured in the +-30 range of start and end date in the previous year.
How can I accomplish this in TSQL?
Thanks
SELECT d1.saleid,
d1.salemarkdown,
d1.startdate,
d1.enddate,
IIF(SalesMadeLastYear.SalesIds IS NULL,'No','Yes') saleinpreviousyear,
ISNULL(SalesMadeLastYear.SalesIds,'') previousyearsaleIDs
FROM dateRangeTest d1
OUTER APPLY(SELECT STUFF((SELECT ',' + CONVERT(varchar,d2.saleid) AS [text()]
FROM dateRangeTest d2
WHERE d2.startdate >= dateadd(yy,-1,d1.startdate)
AND d2.enddate <= dateadd(yy,-1,d1.enddate)
FOR XML PATH('')), 1, 1, '' )) SalesMadeLastYear(SalesIds)
Thanks - can you please explain your solution. What is the purpose of XML PATH?
October 22, 2018 at 11:54 am
sqlstar2011 - Monday, October 22, 2018 11:44 AMJonathan AC Roberts - Monday, October 22, 2018 10:57 AMsqlstar2011 - Monday, October 22, 2018 9:03 AMhi,I have following sample data set:
SaleID SaleMarkdown StartDate(yyyymmdd) EndDate(yyyymmdd) SaleInPreviousYear previousyearsaleID
1 20% 20160101 20160301
2 30% 20170501 20170630
3 40% 20170101 20170201
4 10% 20180101 20180301
5 15% 20180401 20180430Last two columns are empty at the moment.
I would like to find out if there was a sale +-30 days in the previous year for a given sale.
For example for saleid 4 the sale date range is between 20180101 and 20180301. Was there a sale in the previous year during this time period? if so update SaleInPreviousYear column to yes and update previousyearsaleID column with the saleid of the previous year. In this case saleid 3 falls within +-30 days of the sale start or sale end date.
SaleID SaleMarkdown StartDate(yyyymmdd) EndDate(yyyymmdd) SaleInPreviousYear previousyearsaleID
1 20% 20160101 20160301
2 30% 20170501 20170630
3 40% 20170101 20170201
4 10% 20180101 20180301 yes 3
5 15% 20180401 20180430Sale 5 is another example where sale 2 occured in the +-30 range of start and end date in the previous year.
How can I accomplish this in TSQL?
Thanks
SELECT d1.saleid,
d1.salemarkdown,
d1.startdate,
d1.enddate,
IIF(SalesMadeLastYear.SalesIds IS NULL,'No','Yes') saleinpreviousyear,
ISNULL(SalesMadeLastYear.SalesIds,'') previousyearsaleIDs
FROM dateRangeTest d1
OUTER APPLY(SELECT STUFF((SELECT ',' + CONVERT(varchar,d2.saleid) AS [text()]
FROM dateRangeTest d2
WHERE d2.startdate >= dateadd(yy,-1,d1.startdate)
AND d2.enddate <= dateadd(yy,-1,d1.enddate)
FOR XML PATH('')), 1, 1, '' )) SalesMadeLastYear(SalesIds)Thanks - can you please explain your solution. What is the purpose of XML PATH?
Also how does the join happen here? In other examples that I have seen for outerapply there is some sort of reference to the common column like this example:
SELECT * FROM Department D OUTER APPLY ( SELECT * FROM Employee E WHERE E.DepartmentID = D.DepartmentID ) A GO
Sort of lost.
Also if you can expand on the STUFF function that will be helpful
thanks. 🙂
October 22, 2018 at 12:11 pm
sqlstar2011 - Monday, October 22, 2018 11:54 AMsqlstar2011 - Monday, October 22, 2018 11:44 AMJonathan AC Roberts - Monday, October 22, 2018 10:57 AMsqlstar2011 - Monday, October 22, 2018 9:03 AMhi,I have following sample data set:
SaleID SaleMarkdown StartDate(yyyymmdd) EndDate(yyyymmdd) SaleInPreviousYear previousyearsaleID
1 20% 20160101 20160301
2 30% 20170501 20170630
3 40% 20170101 20170201
4 10% 20180101 20180301
5 15% 20180401 20180430Last two columns are empty at the moment.
I would like to find out if there was a sale +-30 days in the previous year for a given sale.
For example for saleid 4 the sale date range is between 20180101 and 20180301. Was there a sale in the previous year during this time period? if so update SaleInPreviousYear column to yes and update previousyearsaleID column with the saleid of the previous year. In this case saleid 3 falls within +-30 days of the sale start or sale end date.
SaleID SaleMarkdown StartDate(yyyymmdd) EndDate(yyyymmdd) SaleInPreviousYear previousyearsaleID
1 20% 20160101 20160301
2 30% 20170501 20170630
3 40% 20170101 20170201
4 10% 20180101 20180301 yes 3
5 15% 20180401 20180430Sale 5 is another example where sale 2 occured in the +-30 range of start and end date in the previous year.
How can I accomplish this in TSQL?
Thanks
SELECT d1.saleid,
d1.salemarkdown,
d1.startdate,
d1.enddate,
IIF(SalesMadeLastYear.SalesIds IS NULL,'No','Yes') saleinpreviousyear,
ISNULL(SalesMadeLastYear.SalesIds,'') previousyearsaleIDs
FROM dateRangeTest d1
OUTER APPLY(SELECT STUFF((SELECT ',' + CONVERT(varchar,d2.saleid) AS [text()]
FROM dateRangeTest d2
WHERE d2.startdate >= dateadd(yy,-1,d1.startdate)
AND d2.enddate <= dateadd(yy,-1,d1.enddate)
FOR XML PATH('')), 1, 1, '' )) SalesMadeLastYear(SalesIds)Thanks - can you please explain your solution. What is the purpose of XML PATH?
Also how does the join happen here? In other examples that I have seen for outerapply there is some sort of reference to the common column like this example:
Sort of lost.
Also if you can expand on the STUFF function that will be helpfulthanks. 🙂
"Also how does the join happen here?"WHERE d2.startdate >= dateadd(yy,-1,d1.startdate)
AND d2.enddate <= dateadd(yy,-1,d1.enddate)
"Also if you can expand on the STUFF function that will be helpful"
I put the XML in there so if you had more than one order for a for a given date range then it would list all the salesIds in a csv.
Read this for more details: https://sqlwhisper.wordpress.com/2013/03/24/stuff-and-for-xml-path-for-string-concatenation/
October 24, 2018 at 6:46 am
sqlstar2011 - Monday, October 22, 2018 9:03 AMhi,I have following sample data set:
SaleID SaleMarkdown StartDate(yyyymmdd) EndDate(yyyymmdd) SaleInPreviousYear previousyearsaleID
1 20% 20160101 20160301
2 30% 20170501 20170630
3 40% 20170101 20170201
4 10% 20180101 20180301
5 15% 20180401 20180430Last two columns are empty at the moment.
I would like to find out if there was a sale +-30 days in the previous year for a given sale.
For example for saleid 4 the sale date range is between 20180101 and 20180301. Was there a sale in the previous year during this time period? if so update SaleInPreviousYear column to yes and update previousyearsaleID column with the saleid of the previous year. In this case saleid 3 falls within +-30 days of the sale start or sale end date.
SaleID SaleMarkdown StartDate(yyyymmdd) EndDate(yyyymmdd) SaleInPreviousYear previousyearsaleID
1 20% 20160101 20160301
2 30% 20170501 20170630
3 40% 20170101 20170201
4 10% 20180101 20180301 yes 3
5 15% 20180401 20180430Sale 5 is another example where sale 2 occured in the +-30 range of start and end date in the previous year.
How can I accomplish this in TSQL?
Thanks
I bel
Try thisSELECT B.saleid
, A.salemarkdown
, A.startdate
, A.enddate
--, DATEADD(dd,-30,DATEADD(yy,-1, A.startdate)) [previous_year_start]
--, DATEADD(dd,30,DATEADD(yy,-1, A.enddate)) [previous_year_end]
, CASE WHEN DATEADD(dd,-30,DATEADD(yy,-1, A.startdate)) <= A.startdate THEN 'yes' END saleinpreviousyear
, A.saleid [previousyearsaleID]
FROM dateRangeTest A
INNER JOIN dateRangeTest B ON A.startdate >= DATEADD(dd,-30,DATEADD(yy,-1, B.startdate))
AND A.enddate <= DATEADD(dd,30,DATEADD(yy,-1, B.enddate))
[font="Verdana"]Sal Young[/font]
[font="Verdana"]MCITP Database Administrator[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply