January 5, 2016 at 9:18 am
sartis (1/5/2016)
Sean, Thanks for trying to help. So the page in my web app where I want to display this timeline is called details.cfm . It's a page where I show all details about a stakeholder, in a tab I have a timeline where I show when a stakeholder received/got sent a note for example or an asset, like the details I provided here. I have four different queries currently1 for notes (my current query for notes)
select * from stakeholder_notes
where note_stakeholder_id = '#URL.id#'
1 for assets - similar query for assets
1 for emails - similar query for emails
1 for events attended - similar query for events_attended
So I know the stakeholder id already and each table has an "id" that links back to my stakeholder table.
The problem with this method is it displays all notes together, all assets together when I output these queries. I would like one query that selects the data for all four of these tables and output would not be grouped together. Hope that makes sense.
So I just asked if there is a way to select data from multiple tables that all have a date column in one query. I'm new to posting to forums like this so I didn't know all the rules. I only posted part of the data/query to try to keep things simple.
Thanks again.
I understand your are new to this kind of thing. No problem but you keep going back and forth. It is still not at all clear what you are really trying to do or even what you need help with. Do you want a single query that will return all assets, events, notes and emails for a given stakeholder? This is very different from a query for each group.
Here is my shot in the dark. This will return all of the 4 types of things in a single query. I made a LOT of assumptions due to lacking information.
declare @StakeholderID nvarchar(50) = '1234'
select sh.*
, x.*
from stakeholder sh
cross apply
(
select 'Note'
, note_date as ActionDate
from stakeholder_notes
where note_stakeholderid = @StakeHolderID
UNION ALL
select 'Asset'
, sh_asset_date
from sh_assets
where sh_id = @StakeHolderID
UNION ALL
select 'Event'
, EventDate
from StakeholderEvents
where StakeHolderID = @StakeHolderID
UNION ALL
select 'Email'
from StakeholderEmails
where StakeHolderID = @StakeHolderID
) x
where StakeHolderID = @StakeHolderID
order by x.ActionDate
_______________________________________________________________
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/
January 5, 2016 at 11:52 am
You provided sample data and the tables structure. Can you also provide what you want the final output to look like? We may be able to fill in the gap from there.
----------------------------------------------------
January 5, 2016 at 12:51 pm
MMartin1 (1/5/2016)
You provided sample data and the tables structure. Can you also provide what you want the final output to look like? We may be able to fill in the gap from there.
ok, so lets say a stakeholder has received the following:
2 emails first one dated 1/20/15 second one 2.11/15
1 asset dated 1/26/15
1 event attended dated 3/12/15
I would want my timeline to show
Event 3/12/15
email 2/11/15
asset 1/26/15
email 1/20/15
Thanks,
January 5, 2016 at 12:52 pm
Sean Lange (1/5/2016)
sartis (1/5/2016)
Sean, Thanks for trying to help. So the page in my web app where I want to display this timeline is called details.cfm . It's a page where I show all details about a stakeholder, in a tab I have a timeline where I show when a stakeholder received/got sent a note for example or an asset, like the details I provided here. I have four different queries currently1 for notes (my current query for notes)
select * from stakeholder_notes
where note_stakeholder_id = '#URL.id#'
1 for assets - similar query for assets
1 for emails - similar query for emails
1 for events attended - similar query for events_attended
So I know the stakeholder id already and each table has an "id" that links back to my stakeholder table.
The problem with this method is it displays all notes together, all assets together when I output these queries. I would like one query that selects the data for all four of these tables and output would not be grouped together. Hope that makes sense.
So I just asked if there is a way to select data from multiple tables that all have a date column in one query. I'm new to posting to forums like this so I didn't know all the rules. I only posted part of the data/query to try to keep things simple.
Thanks again.
I understand your are new to this kind of thing. No problem but you keep going back and forth. It is still not at all clear what you are really trying to do or even what you need help with. Do you want a single query that will return all assets, events, notes and emails for a given stakeholder? This is very different from a query for each group.
Here is my shot in the dark. This will return all of the 4 types of things in a single query. I made a LOT of assumptions due to lacking information.
declare @StakeholderID nvarchar(50) = '1234'
select sh.*
, x.*
from stakeholder sh
cross apply
(
select 'Note'
, note_date as ActionDate
from stakeholder_notes
where note_stakeholderid = @StakeHolderID
UNION ALL
select 'Asset'
, sh_asset_date
from sh_assets
where sh_id = @StakeHolderID
UNION ALL
select 'Event'
, EventDate
from StakeholderEvents
where StakeHolderID = @StakeHolderID
UNION ALL
select 'Email'
from StakeholderEmails
where StakeHolderID = @StakeHolderID
) x
where StakeHolderID = @StakeHolderID
order by x.ActionDate
Thanks, I will give this a shot. Yes I want one query that will return all assets, events, notes and emails.
January 5, 2016 at 12:54 pm
sartis (1/5/2016)
Sean Lange (1/5/2016)
sartis (1/5/2016)
Sean, Thanks for trying to help. So the page in my web app where I want to display this timeline is called details.cfm . It's a page where I show all details about a stakeholder, in a tab I have a timeline where I show when a stakeholder received/got sent a note for example or an asset, like the details I provided here. I have four different queries currently1 for notes (my current query for notes)
select * from stakeholder_notes
where note_stakeholder_id = '#URL.id#'
1 for assets - similar query for assets
1 for emails - similar query for emails
1 for events attended - similar query for events_attended
So I know the stakeholder id already and each table has an "id" that links back to my stakeholder table.
The problem with this method is it displays all notes together, all assets together when I output these queries. I would like one query that selects the data for all four of these tables and output would not be grouped together. Hope that makes sense.
So I just asked if there is a way to select data from multiple tables that all have a date column in one query. I'm new to posting to forums like this so I didn't know all the rules. I only posted part of the data/query to try to keep things simple.
Thanks again.
I understand your are new to this kind of thing. No problem but you keep going back and forth. It is still not at all clear what you are really trying to do or even what you need help with. Do you want a single query that will return all assets, events, notes and emails for a given stakeholder? This is very different from a query for each group.
Here is my shot in the dark. This will return all of the 4 types of things in a single query. I made a LOT of assumptions due to lacking information.
declare @StakeholderID nvarchar(50) = '1234'
select sh.*
, x.*
from stakeholder sh
cross apply
(
select 'Note'
, note_date as ActionDate
from stakeholder_notes
where note_stakeholderid = @StakeHolderID
UNION ALL
select 'Asset'
, sh_asset_date
from sh_assets
where sh_id = @StakeHolderID
UNION ALL
select 'Event'
, EventDate
from StakeholderEvents
where StakeHolderID = @StakeHolderID
UNION ALL
select 'Email'
from StakeholderEmails
where StakeHolderID = @StakeHolderID
) x
where StakeHolderID = @StakeHolderID
order by x.ActionDate
Thanks, I will give this a shot. Yes I want one query that will return all assets, events, notes and emails.
Given you latest post you should add DESC to the order by so the most recent value will be earliest in the list. 😉
_______________________________________________________________
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/
January 5, 2016 at 12:58 pm
understood.
January 5, 2016 at 1:01 pm
sartis (1/5/2016)
MMartin1 (1/5/2016)
You provided sample data and the tables structure. Can you also provide what you want the final output to look like? We may be able to fill in the gap from there.ok, so lets say a stakeholder has received the following:
2 emails first one dated 1/20/15 second one 2.11/15
1 asset dated 1/26/15
1 event attended dated 3/12/15
I would want my timeline to show
Event 3/12/15
email 2/11/15
asset 1/26/15
email 1/20/15
Thanks,
Okay, do you want to show only data for one stakeholder. or all of them in one view? How would you sort the stakeholders?
Ex// like this or only one stakeholder at a time ?
Stakeholder, item, date
------------------------
SH1 Event 3/12/15
SH1 email 2/11/15
SH1 asset 1/26/15
SH1 email 1/20/15
SH2 Event 2/28/15
SH2 email 2/10/15
SH2 asset 1/24/15
SH2 asset 1/15/15
.
.
.
----------------------------------------------------
January 5, 2016 at 1:03 pm
MMartin1 (1/5/2016)
sartis (1/5/2016)
MMartin1 (1/5/2016)
You provided sample data and the tables structure. Can you also provide what you want the final output to look like? We may be able to fill in the gap from there.ok, so lets say a stakeholder has received the following:
2 emails first one dated 1/20/15 second one 2.11/15
1 asset dated 1/26/15
1 event attended dated 3/12/15
I would want my timeline to show
Event 3/12/15
email 2/11/15
asset 1/26/15
email 1/20/15
Thanks,
Okay, do you want to show only data for one stakeholder. or all of them in one view? How would you sort the stakeholders?
Ex// like this or only one stakeholder at a time ?
Stakeholder, item, date
------------------------
SH1 Event 3/12/15
SH1 email 2/11/15
SH1 asset 1/26/15
SH1 email 1/20/15
SH2 Event 2/28/15
SH2 email 2/10/15
SH2 asset 1/24/15
SH2 asset 1/15/15
My page is showing data for just one stakeholder at a time.
.
.
.
January 5, 2016 at 1:19 pm
Great, Sean's example solves this. You can refer to his last post.
----------------------------------------------------
January 5, 2016 at 1:39 pm
Or if you want to make this look more to what you may be familiar with, drawing off of your example ,
USE tempDB;
GO
/* ----------------------------- */
CREATE TABLE #stakeholder_notes(
note_id INTEGER NOT NULL PRIMARY KEY
,note_stakeholder_id NVARCHAR(50) NOT NULL
,note_date DATETIME NOT NULL
,note VARCHAR(11) NOT NULL
);
INSERT INTO #stakeholder_notes(note_id,note_stakeholder_id,note_date,note) VALUES (36,N'5003','2010-07-15 00:00:00.000','Sample Note');
INSERT INTO #stakeholder_notes(note_id,note_stakeholder_id,note_date,note) VALUES (37,N'5004','2010-07-15 00:00:00.000','sample note');
INSERT INTO #stakeholder_notes(note_id,note_stakeholder_id,note_date,note) VALUES (38,N'5004','2010-07-21 00:00:00.000','sample note');
CREATE TABLE #sh_assets(
sh_asset_id INTEGER NOT NULL PRIMARY KEY
,sh_asset_name VARCHAR(500) NOT NULL
,sh_id VARCHAR(50) NOT NULL
,sh_asset_date DATETIME NOT NULL
);
INSERT INTO #sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (6,'12-1814 Final.docx','6247','2013-09-06 00:00:00.000');
INSERT INTO #sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (7,'COTNS FL10.pdf','6253','2013-09-06 00:00:00.000');
INSERT INTO #sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (8,'aa_ww_quote.pdf','5004','2010-07-20 00:00:00.000');
INSERT INTO #sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (9,'AD_certificate.pdf','5004','2010-07-10 00:00:00.000');
INSERT INTO #sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (11,'ComEd Logo.bmp','5003','1900-01-01 00:00:00.000');
INSERT INTO #sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (12,'MicroBook Read Me.pdf','7255','2013-12-03 00:00:00.000');
INSERT INTO #sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (13,'3828EL3010F-1.pdf','5003','1900-01-01 00:00:00.000');
INSERT INTO #sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (15,'bob.txt','7259','2014-03-11 00:00:00.000');
INSERT INTO #sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (16,'badlands.jpg','7260','1900-01-01 00:00:00.000');
/* --------- your construct would look as follows --------*/
declare @stakeHolderID varchar(30);
set@stakeHolderID = '5004';
SELECT 'Note' as item, note_date as itemDate
From#stakeholder_notes
WHEREnote_stakeholder_id= @stakeHolderID
UNION ALL
SELECT 'Asset' as item, sh_asset_Date
From#sh_assets
WHEREsh_id= @stakeHolderID
Order by itemDate DESC
--DROP TABLE #stakeholder_notes;
--DROP TABLE #sh_assets;
You can add UNION ALL to represent your other item tables before the ORDER BY
----------------------------------------------------
January 5, 2016 at 1:55 pm
MMartin1 (1/5/2016)
Or if you want to make this look more to what you may be familiar with, drawing off of your example ,
USE tempDB;
GO
/* ----------------------------- */
CREATE TABLE #stakeholder_notes(
note_id INTEGER NOT NULL PRIMARY KEY
,note_stakeholder_id NVARCHAR(50) NOT NULL
,note_date DATETIME NOT NULL
,note VARCHAR(11) NOT NULL
);
INSERT INTO #stakeholder_notes(note_id,note_stakeholder_id,note_date,note) VALUES (36,N'5003','2010-07-15 00:00:00.000','Sample Note');
INSERT INTO #stakeholder_notes(note_id,note_stakeholder_id,note_date,note) VALUES (37,N'5004','2010-07-15 00:00:00.000','sample note');
INSERT INTO #stakeholder_notes(note_id,note_stakeholder_id,note_date,note) VALUES (38,N'5004','2010-07-21 00:00:00.000','sample note');
CREATE TABLE #sh_assets(
sh_asset_id INTEGER NOT NULL PRIMARY KEY
,sh_asset_name VARCHAR(500) NOT NULL
,sh_id VARCHAR(50) NOT NULL
,sh_asset_date DATETIME NOT NULL
);
INSERT INTO #sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (6,'12-1814 Final.docx','6247','2013-09-06 00:00:00.000');
INSERT INTO #sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (7,'COTNS FL10.pdf','6253','2013-09-06 00:00:00.000');
INSERT INTO #sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (8,'aa_ww_quote.pdf','5004','2010-07-20 00:00:00.000');
INSERT INTO #sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (9,'AD_certificate.pdf','5004','2010-07-10 00:00:00.000');
INSERT INTO #sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (11,'ComEd Logo.bmp','5003','1900-01-01 00:00:00.000');
INSERT INTO #sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (12,'MicroBook Read Me.pdf','7255','2013-12-03 00:00:00.000');
INSERT INTO #sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (13,'3828EL3010F-1.pdf','5003','1900-01-01 00:00:00.000');
INSERT INTO #sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (15,'bob.txt','7259','2014-03-11 00:00:00.000');
INSERT INTO #sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (16,'badlands.jpg','7260','1900-01-01 00:00:00.000');
/* --------- your construct would look as follows --------*/
declare @stakeHolderID varchar(30);
set@stakeHolderID = '5004';
SELECT 'Note' as item, note_date as itemDate
From#stakeholder_notes
WHEREnote_stakeholder_id= @stakeHolderID
UNION ALL
SELECT 'Asset' as item, sh_asset_Date
From#sh_assets
WHEREsh_id= @stakeHolderID
Order by itemDate DESC
--DROP TABLE #stakeholder_notes;
--DROP TABLE #sh_assets;
You can add UNION ALL to represent your other item tables before the ORDER BY
Thanks MMartin1 and sean, I plugged this into SQl Server management studio and it appears to be kicking back results in the correct manner. Sorry for all the headaches! I will plug it into my page and let you know and send a screenshot.
January 5, 2016 at 3:38 pm
sartis (1/5/2016)
Thanks MMartin1 and sean, I plugged this into SQl Server management studio and it appears to be kicking back results in the correct manner. Sorry for all the headaches! I will plug it into my page and let you know and send a screenshot.
Please don't post a screenshot if it contains any personal information other than your own. This is a public forum and we really don't want to see anyone get in trouble by publishing actual data. Data structures, sample data, code, etc. is one thing, but actual data is completely different.
January 5, 2016 at 3:42 pm
Ed Wagner (1/5/2016)
sartis (1/5/2016)
Thanks MMartin1 and sean, I plugged this into SQl Server management studio and it appears to be kicking back results in the correct manner. Sorry for all the headaches! I will plug it into my page and let you know and send a screenshot.Please don't post a screenshot if it contains any personal information other than your own. This is a public forum and we really don't want to see anyone get in trouble by publishing actual data. Data structures, sample data, code, etc. is one thing, but actual data is completely different.
I would never do that, all of my data in my dev db is from a fake name generator.
January 5, 2016 at 3:45 pm
sartis (1/5/2016)
Ed Wagner (1/5/2016)
sartis (1/5/2016)
Thanks MMartin1 and sean, I plugged this into SQl Server management studio and it appears to be kicking back results in the correct manner. Sorry for all the headaches! I will plug it into my page and let you know and send a screenshot.Please don't post a screenshot if it contains any personal information other than your own. This is a public forum and we really don't want to see anyone get in trouble by publishing actual data. Data structures, sample data, code, etc. is one thing, but actual data is completely different.
I would never do that, all of my data in my dev db is from a fake name generator.
Excellent - glad to hear it. I just don't want to see anyone get in trouble for asking for help on something.
Viewing 14 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply