For every date in one table find the financial period in another table - struggling to find an answer

Question

For every date in one table find the financial period in another table - struggling to find an answer

susan.swaffield

SSC Rookie

Points: 32
More actions
March 13, 2013 at 1:56 pm

#269786

I have searched the web and struggling to find the right answer to this. I think it is simple but it is alluding me.
We have one table with data which includes a date field say table a. In our financial baseperiods table we have a Period, Status, StartDate and EndDate - say table b
I need a query that for each record in table a it appends the relevant period value and status value based on the date being between the start date and end date. I have been able to put it together to get the period but cannot get the second column. Here is what returns the period but I also need the status.
Select q.OrderDate, q.Period
FROM (SELECT OrderDate,
(SELECT Period From CanonDW.dbo.vw_base_Periods WHERE OrderDate BETWEEN StartDate and EndDATE) as Period
FROM Mpack.dbo.SalesOrders
WHERE OrderDate Is Not Null)q
WHere q.Period is not null
Thanks for your help.:-)

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

Hi and welcome to SSC. Since you are new around here you probably have not had a chance to see the article about best practices when posting questions. You can find it behind the first link in my signature. Basically we need to see ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. It does take some effort to put this together but you will be rewarded with quick responses that are tested, accurate and fast.

_______________________________________________________________

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/

susan.swaffield SSC Rookie Points: 32 More actions · Answer 2

Thanks for the heads up, I will work through the details

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 3

I made some guesses...is this what you were trying to do?

Some sample data.

IF OBJECT_ID('tempdb..#SalesOrders') IS NOT NULL

DROP TABLE #SalesOrders

CREATE TABLE #SalesOrders (

[OrderID] INT IDENTITY(1,1) NOT NULL,

[OrderDate] DATETIME NULL,

PRIMARY KEY (OrderID))

INSERT INTO #SalesOrders

SELECT '2012-02-03' UNION ALL

SELECT '2012-03-03' UNION ALL

SELECT '2012-03-22' UNION ALL

SELECT '2012-04-08' UNION ALL

SELECT '2012-04-18' UNION ALL

SELECT '2012-04-30'

IF OBJECT_ID('tempdb..#BasePeriods') IS NOT NULL

DROP TABLE #BasePeriods

CREATE TABLE #BasePeriods (

[ID] INT IDENTITY(1,1) NOT NULL,

[StartDate] DATETIME NULL,

[EndDate] DATETIME NULL,

[StatusID] INT NULL,

PRIMARY KEY (ID))

INSERT INTO #BasePeriods

SELECT '2012-02-01','2012-02-15',1 UNION ALL

SELECT '2012-02-16','2012-02-28',2 UNION ALL

SELECT '2012-03-01','2012-03-15',1 UNION ALL

SELECT '2012-03-16','2012-03-31',2 UNION ALL

SELECT '2012-04-01','2012-04-15',1 UNION ALL

SELECT '2012-04-16','2012-04-30',2

SELECT * FROM #SalesOrders

SELECT * FROM #BasePeriods

Then find the row in the BasePeriod table for each SalesOrder OrderDate. I've done some date conversions to ignore the time part of the date so the BETWEEN operator will return reliable results.

SELECT

so.OrderID

,CAST(CONVERT(CHAR(8),so.OrderDate,112) AS DATE) AS OrderDate

,bp.ID AS Period

,CAST(CONVERT(CHAR(8),bp.StartDate,112) AS DATE) AS StartDate

,CAST(CONVERT(CHAR(8),bp.EndDate,112) AS DATE) AS EndDate

,bp.StatusID

FROM

#SalesOrders AS so

CROSS APPLY

#BasePeriods AS bp

WHERE

CAST(CONVERT(CHAR(8),so.OrderDate,112) AS DATE)

BETWEEN

CAST(CONVERT(CHAR(8),StartDate,112) AS DATE)

AND CAST(CONVERT(CHAR(8),EndDate,112) AS DATE)

susan.swaffield SSC Rookie Points: 32 More actions · Answer 4

Steven thanks for that it is similar to what i have ended up with. Now all I have to do is work out how to mark this as closed

Select OrderDate, q.Period, b.Status

FROM (SELECT OrderDate,

(SELECT Period From DW.dbo.vw_base_Periods WHERE OrderDate BETWEEN StartDate and EndDATE) as Period

FROM Mpack.dbo.SalesOrders

WHERE OrderDate Is Not Null)q

Inner JOIN DW.dbo.vw_base_Periods b ON q.Period = b.Period

WHere q.Period is not null