December 28, 2006 at 10:22 am
Hi all, I hope that this is the right place for this, apologies if not.
I am wanting to run two queries, one that utilises the results of the other to seek more information. I'll try and explain clearly:
Query 1) The query I have written for this currently looks thus:
SELECT [db1],[dc1],
COUNT([dc1])
FROM [Database].[Live].[T1] t1
WHERE (t1.[date1] IN ('2006-09-01','2006-10-01','2006-11-01','2006-12-01'))
AND (t1.[dc1] IN ('A1', 'b1','C1','D1','E1'))
GROUP BY [db1],[dc1]
ORDER BY [db1],[dc1]
This outputs a primary key listing of db1's that match the criteria.
What I then want is for each db1 code that gets output'ed by the query, have it go back to the database and report back all other entries for that db1 code that occurred within 5 days of the original date1 date.
Hopefully that makes sense!!
I am really new to T-SQL and am not sure how best to go about doing this. Any help would be very gratefully received!
December 28, 2006 at 11:59 am
Can you please post table definition and some sample data so we have more information to answer your question.
With out this, it is a stab in the dark
select *
From [Database].[Live].[T1] t1
join (SELECT [db1],[dc1], OUNT([dc1])
FROM [Database].[Live].[T1] t1
WHERE (t1.[date1] IN ('2006-09-01','2006-10-01','2006-11-01','2006-12-01'))
AND (t1.[dc1] IN ('A1', 'b1','C1','D1','E1')
GROUP BY [db1],[dc1]
ORDER BY [db1],[dc1])) as DerivedTable on DerivedTable.[db1]= t1.[db1]
where date1 between dateadd(dd,-4,getdate()) and getdate()
December 29, 2006 at 4:01 am
Hi Ray, thank you very much for having a look into this.
The database structure can be created thus:
CREATE TABLE [Live].[T1](
[db1] [varchar](12) COLLATE Latin1_General_CI_AS NULL,
[tc1] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[date1] [smalldatetime] NULL,
[time1] [varchar](8) COLLATE Latin1_General_CI_AS NULL,
[reason] [int] NULL,
[cc] [varchar](12) COLLATE Latin1_General_CI_AS NULL,
[dc1] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
)
I have stripped out a lot of unecessary columns for clarity. Some goofy sample data is:
db1 tc1 date1 time1 reason cc dc1
------------ -------------------- ----------------------- -------- ----------- ------------ --------------------
00000000001 AB1000 2006-11-14 00:00:00 17:16:32 0 ABC1 A1
00000000001 DA1300 2006-11-15 00:00:00 17:16:24 0 ABC1
00000000002 MF1009 2006-11-14 00:00:00 20:51:28 0 ABC1
00000000003 MF1009 2006-11-14 00:00:00 20:52:03 0 ABC1
00000000004 MF1001 2006-11-14 00:00:00 20:51:22 0 ABC1
00000000006 MF1019 2006-11-14 00:00:00 20:50:50 0 ABC1
00000000007 MF1004 2006-11-14 00:00:00 20:50:31 0 ABC1
00000000008 MF1005 2006-11-14 00:00:00 09:52:00 0 ABC1 B1
00000000008 MF1001 2006-11-15 00:00:00 20:50:44 0 ABC1
00000000008 AB1000 2006-11-16 00:00:00 09:51:39 0 ABC1
00000000009 MF1054 2006-11-14 00:00:00 20:50:10 0 ABC1
00000000010 MF1019 2006-11-14 00:00:00 20:50:57 0 ABC1
00000000012 MF1019 2006-11-14 00:00:00 20:52:06 0 ABC1
00000000013 MF1019 2006-11-14 00:00:00 20:50:29 0 ABC1
00000000016 MF1019 2006-11-14 00:00:00 20:50:30 0 ABC1
I hope this helps to clarify.
By the way, I noticed that I left a COUNT command in my example query, that shouldn't be there.
Best regards,
Hugh
December 29, 2006 at 7:46 am
one way to approach this would be to create the first query as a "View"
December 29, 2006 at 8:24 am
A couple ways to do this.
With CTE...
WITH ReturnDetailsFor (db1, dc1, OriginalDate)
AS
(
SELECT DISTINCT
[db1],
[dc1],
[date1]
FROM [T1]
WHERE ([date1] IN ('2006-09-01','2006-10-01','2006-11-01','2006-12-01'))
AND ([dc1] IN ('A1', 'b1','C1','D1','E1'))
)
SELECT DISTINCT
t1.*
FROM [T1] t1
JOIN ReturnDetailsFor RDF ON t1.[db1] = RDF.[db1] AND
DATEDIFF(day,t1.[Date1], RDF.OriginalDate) BETWEEN -5 AND 5
ORDER BY [db1],[dc1]
With Derived Table...
SELECT DISTINCT
t1.*
FROM [T1] t1
JOIN ( SELECT DISTINCT
[db1],
[dc1],
[date1] as OriginalDate
FROM [T1]
WHERE ([date1] IN ('2006-09-01','2006-10-01','2006-11-01','2006-12-01'))
AND ([dc1] IN ('A1', 'b1','C1','D1','E1')) ) RDF
ON t1.[db1] = RDF.[db1] AND
DATEDIFF(day,t1.[Date1], RDF.OriginalDate) BETWEEN -5 AND 5
ORDER BY [db1],[dc1]
you Could also use table variables, views, etc etc
January 2, 2007 at 9:44 am
That's brilliant, thanks Joel! I had discovered the derived table functionality and was stumbling my way through it! You however have helped clarify it for me. Thank you!
And thank you to everyone else who took the time to look and think about this issue.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply