How to get active records for each date in SQL

Question

Post reply

How to get active records for each date in SQL

Akshit T Shah

SSC Enthusiast

Points: 133
More actions
March 1, 2016 at 2:50 am

#311738

I have a table like below
ID FID MDate Active
--------------------------
1 1 2009-05-25 1
1 2 2009-05-25 1
1 1 2010-02-04 0
1 3 2010-02-04 1
1 1 2009-04-01 0
1 1 2009-03-01 1
How to get active FId for each date?
I was trying like below
SELECT DISTINCT
ID, MDate,
STUFF ((SELECT DISTINCT ',' + CAST(FID AS VARCHAR)
FROM
(SELECT ID, MDate, FID
FROM Table1
WHERE IsActive = 1) t
WHERE t.MDate = a.MDate
FOR XML PATH('')), 1, 1, '') colb
FROM
(SELECT ID, MDate, FID
FROM Table1
WHERE IsActive = 1) a
Somehow, it is giving partial results.
I want output like below
ID MDate FID
-------------------------
1 2009-03-01 1
1 2009-05-25 1,2
1 2010-02-04 2,3
How to get this in SQL?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

tony28 SSCertifiable Points: 7112 More actions · Answer 1

If I good understand you can try this..

I tried for table with 90000 rows. time was about 52second...and got result with 68rows it means 68days ..

select distinct MDate,

STUFF ((SELECT ',' + CAST(FID AS VARCHAR)

FROM Table1 A

WHERE A.MDate = B.MDate AND IsActive =1

ORDER BY FID

FOR XML PATH('')), 1, 1, '')

FROM Table1 B

Scan count 73, logical reads 7871,

but still not sure about performance,.. if i will have idea how to optimize or some another solution, I will post...

Akshit T Shah SSC Enthusiast Points: 133 More actions · Answer 2

thanks for the reply. i may have asked question in incorrect way. i will try explain again.

i want to get those FID which are active, if any one FID is active since long time, it should be getting selected with all dates. in my case FID 2 is active since 2009-05-25 so it should get selected on date 2010-02-04 with FID 3.

Hope i am able to explain.

tony28 SSCertifiable Points: 7112 More actions · Answer 3

So now you have Date and FID , based on date.

I dont understand how can I recognize what is long time for you .

And next , it means that this 2 will be two times in result ?

I dont understand what is purpose of this..

try to post again based on one of these articles..

http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Akshit T Shah SSC Enthusiast Points: 133 More actions · Answer 4

I have a DB table like below

------------------------------------------------

ID FID MDate IsActive

------------------------------------------------

1 1 2009-05-25 1

1 2 2009-05-25 1

1 1 2010-02-04 0

1 3 2010-02-04 1

1 1 2009-04-01 0

1 1 2009-03-01 1

-------------------------------------------------

Script for creating table and inserting above data

CREATE TABLE [dbo].[Table1](

[ID] [int] NULL,

[FID] [int] NULL,

[MDate] [datetime] NULL,

[IsActive] [bit] NULL

) ON [PRIMARY]

GO

INSERT INTO Table1(ID,FID,MDate,IsActive)

SELECT 1,1,'2009-05-25 00:00:00.000',1

UNION

SELECT 1,2,'2009-05-25 00:00:00.000',1

UNION

SELECT 1,1,'2010-02-04 00:00:00.000',0

UNION

SELECT 1,3,'2010-02-04 00:00:00.000',1

UNION

SELECT 1,1,'2009-04-01 00:00:00.000',0

UNION

SELECT 1,1,'2009-03-01 00:00:00.000',1

what i want is which all FIDs are active on each date in the table.

I was trying like below

SELECT DISTINCT

ID, MDate,

STUFF ((SELECT DISTINCT ',' + CAST(FID AS VARCHAR)

FROM

(SELECT ID, MDate, FID

FROM Table1

WHERE IsActive = 1) t

WHERE t.MDate = a.MDate

FOR XML PATH('')), 1, 1, '') colb

FROM

(SELECT ID, MDate, FID

FROM Table1

WHERE IsActive = 1) a

Somehow, it is giving partial results. above query is not adding FID 2 on 2010-02-04 date.

I want output like below

-------------------------------

ID MDate FID

-------------------------------

1 2009-03-01 1

1 2009-05-25 1,2

1 2010-02-04 2,3

--------------------------------

i want to show the report like for each ID which all changes done in FID based on MDate

How to get this in SQL?

tony28 SSCertifiable Points: 7112 More actions · Answer 5

hm I see only 2009-05-25 00:00:00.000 with FID 2, so how can you ensure that FID 2 will be ALSO in 2010-02-04 00:00:00.000.. you have in query some WHERE t.MDate = a.MDate

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

The two rows with date '2010-02-04' have FID of 1 and 3. Your desired output lists FID of 2 and 3 for this date.

Can you explain how FID of 2 is associated with date of '2010-02-04'?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

tony28 SSCertifiable Points: 7112 More actions · Answer 7

ChrisM@Work (3/1/2016)
The two rows with date '2010-02-04' have FID of 1 and 3. Your desired output lists FID of 2 and 3 for this date.
Can you explain how FID of 2 is associated with date of '2010-02-04'?

Right like ChrisM@Work wrote...

when I inserted to table this row..

INSERT INTO Table1(ID,FID,MDate,IsActive)

SELECT 1,2,'2010-02-04 00:00:00.000',1

this is working like you want.