March 13, 2013 at 1:56 pm
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.:-)
March 13, 2013 at 2:14 pm
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/
March 13, 2013 at 2:18 pm
Thanks for the heads up, I will work through the details
March 13, 2013 at 2:49 pm
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)
March 13, 2013 at 4:06 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply