July 23, 2012 at 5:33 am
Hi,
I have a scenario where I have a version table that holds multiple versions. One or more versions can be active at a time. Each version has a start and end date and it is possible that versions start and end dates could over lap each other or there could be gaps in the start and end dates.
Is it possible to filter out the records without using something like a cursor in the WHERE Clause?
I have tried to simulate the scenario with these temp tables;
--Create Sample of tables
CREATE TABLE [dbo].[#Version](
[VersionID] int NOT NULL,
[VersionName] [varchar](50) NULL,
[StartMonth] [varchar](50) NULL,
[EndMonth] [varchar](50) NULL,
[IsActive] BIT NULL
)
CREATE TABLE [dbo].[#TransactionDetail]
([VersionID] varchar(3) NULL,
[PERIOD] varchar(10) NULL,
VALUE decimal(10,2)NULL
)
GO
--Insert sample data
INSERT INTO [#Version] (VersionID,VersionName ,StartMonth,EndMonth ,IsActive)
VALUES (1,'RE2','007.2012','012.2012',1)
INSERT INTO [#Version] (VersionID,VersionName ,StartMonth,EndMonth ,IsActive)
VALUES (2,'RE2','007.2013','012.2013',0)
INSERT INTO [#Version] (VersionID,VersionName ,StartMonth,EndMonth ,IsActive)
VALUES (3,'BUD','001.2012','012.2012',1)
INSERT INTO [#Version] (VersionID,VersionName ,StartMonth,EndMonth ,IsActive)
VALUES (4,'BUD','001.2013','012.2013',0)
--Transactions
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE2','007.2012',24.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE2','008.2012',24.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE2','007.2012',28.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE2','008.2012',28.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE2','008.2012',28.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE2','009.2012',28.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE2','009.2012',28.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE2','010.2012',28.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE2','011.2012',28.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE2','002.2012',18.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE2','001.2012',15.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE2','007.2012',26.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE2','007.2012',24.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE2','007.2013',24.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE2','008.2013',24.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE2','004.2013',29.00)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE2','012.2013',23.80)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE2','012.2013',25.30)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('BUD','008.2012',24.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('BUD','007.2012',25.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('BUD','006.2012',22.50)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('BUD','002.2012',21.00)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('BUD','008.2013',44.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('BUD','007.2013',34.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('BUD','006.2013',84.50)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('BUD','002.2013',25.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE1','002.2013',25.90)
INSERT INTO [#TransactionDetail] (VersionID,PERIOD,VALUE )
VALUES('RE1','001.2013',25.90)
GO
SELECT *
FROM [#TransactionDetail]
WHERE VersionID IN (SELECT VersionName FROM [#Version]WHERE IsActive = 1)
--I need to filter out the period for only active versions start and end date
--Show only active versions
SELECT *
FROM [#Version]
WHERE IsActive = 1
DROP TABLE [#Version]
DROP TABLE [#TransactionDetail]
Any ideas would be greatly appreciated.
July 23, 2012 at 6:08 am
Hi Michael,
Your Schema structure definition is perfect. Can you please brief your requirement clearly. So that i can try my best.
Regards,
Karthik.
SQL Developer.
July 23, 2012 at 6:21 am
Something like this?
SELECT
TD.*
FROM
[#TransactionDetail] TD
INNER JOIN
#Version V
ON
TD.VersionID = V.VersionName
WHERE
TD.PERIOD >= V.StartMonth
AND
TD.PERIOD < V.EndMonth
AND
V.IsActive = 1
If not, based on the sample data you provided, what should be the expected outcome?
July 24, 2012 at 2:43 am
Hi,
Thank you for the response. What I am looking for is to return only transactions that fall between the start and end of each active version. But these are not set dates and do change. There could be two or more active versions.
RE2Start:007.2012 End:012.2012
BUDStart:001.2012 End:012.2012
If I use the query provided by anthony I will get values outside this range, for example;
BUD008.201344.90
July 24, 2012 at 5:01 am
This should work for you:
Select b.* From #Version As a JOIN #TransactionDetail As b ON a.VersionName = b.VersionID And IsActive = 1
Where (PARSENAME(b.PERIOD, 1) >= PARSENAME(a.StartMonth, 1) )
And (PARSENAME(b.PERIOD, 1) <= PARSENAME(a.EndMonth, 1))
Edit1: Well it actually won't.....sorry bout that ...I'll be back with the correction.
Edit2: Here is the Correction:
Select * From
(
Select VersionId, VersionName,
Cast(Replace(PARSENAME(StartMonth, 2), '0', '') + '/' + '01' + '/' + PARSENAME(StartMonth, 1) As Date) As StartMonth,
Cast(Replace(PARSENAME(EndMonth, 2), '0', '') + '/' + '01' + '/' + PARSENAME(EndMonth, 1) As Date) As EndMonth,
IsActive
From #Version
) As a
JOIN
(
Select VersionID, Cast(Replace(PARSENAME(PERIOD, 2), '0', '') + '/' + '01' + '/' + PARSENAME(PERIOD, 1) As Date) As Period, VALUE
From #TransactionDetail
) As b
ON a.VersionName = b.VersionID And IsActive = 1
Where b.Period > a.StartMonth AND b.Period < a.EndMonth
Hope this works for you. You'll have to change the Months from Varchar to Date.
July 25, 2012 at 1:12 am
Hi,
Thank you Vinu ! That is excatly what I was looking for:-)
July 25, 2012 at 2:52 am
August 14, 2012 at 11:15 am
Hi,
Just wanted to give a short update. Thanks for the help once again but I did pick up an issue with period 10 conversion. So I modified the code to this and it works, just in case some else needs this one day.
Issue was with the replace of the 0;
This works for me;
cast(cast(cast(PARSENAME(PeriodN.Name,2) as int) as varchar(2)) + '/' + '01' + '/' + PARSENAME( PeriodN.Name , 1) As Date)
August 15, 2012 at 9:57 pm
The solution is fine...but with so many Cast and Parsename function calls, I think that if you are working on a big table with lots of data then the performance would be affected to some extent.
I would rather advise that you leave the formatting to the Front End in this case.
On second thought, the advise would be to check the query with a higher load of data and then decide for yourself. 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply