April 27, 2010 at 10:27 pm
Hi,
I am trying to output to a view in a certail format. I think I may be able to use something like the PIVOT command but am very unsure of the syntax.
The tables are as follows:
tblDeliverableNames
ID Name
1 Instruction
2 Meeting Held
3 Site Visit
4 Report Submitted
tblProject
ID Name
1 Project1
2 Project2
3 Project3
4 Project4
tblDeliverables
ID ProjectID DeliverableNameID ForecastDate CompleteDate
1 1 1
2 1 2
3 1 3
4 1 4
5 2 1
6 2 3
7 2 4
8 3 1
9 3 2
10 3 4
11 4 1
12 4 2
13 4 3
14 4 4
I would like to output this to a view with the following format:
Headings: ProjectName, InstructionForecastDate, InstructionCompleteDate, MeetingForecastDate, MeetingCompleteDate, SiteVisitInstructionDate, SiteVisitCompleteDate, ReportForecastDate, ReportCompleteDate
with the dates populated from tblDeliverables under each heading.
You will note that a 'meeting' is not always a deliverable for a project. This is the case for Project2 and Project3. I would like to put 'N/A' (or anything else to signify that is doesn't exist) in the date field for the view if there is no such deliverable for the project.
The number of projects will be much larger than this and the number of deliverables will also increase.
This will eventually be output to excel. I hope I have provided enough information. Please let me know if you require anything further.
Thanks
April 27, 2010 at 11:52 pm
Hi Buddy,how about you going through this following article and helping us help you?? 🙂
CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN[/url]
When u do so, i am sure a lot of us will help u instantly...
So please post
1. Table Structures - CREATE TABLE scripts
2. Constraints or Indexes in the table, - CREATE SCRIPTS
3. SAMPLE DATA - INSERT INTO TABLE scripts
4. Desired output - some visual representation of this.
I know how to do your request, but without the above said things, i cant proceed further buddy...
April 28, 2010 at 12:30 am
My poor soul, got lot of free time these days and i am utilizing it to LEARN..:w00t:
Now that said, i have given my time in scripting the code for you.
First let us look at the table DDLs ..Wellington, look at how i have made up the data, so that you make the people who want to help you , will really feel easy with what-is-what and they in-turn give out the BEST and TESTED code..
IF OBJECT_ID('TEMPDB..#tblDeliverableNames') IS NOT NULL
DROP TABLE #tblDeliverableNames
CREATE TABLE #tblDeliverableNames
(
ID INT,
[DELIName] VARCHAR(100)
)
INSERT INTO #tblDeliverableNames
SELECT 1 , 'Instruction'
UNION ALL
SELECT 2 ,'Meeting Held'
UNION ALL
SELECT 3 ,'Site Visit'
UNION ALL
SELECT 4 ,'Report Submitted'
IF OBJECT_ID('TEMPDB..#tblProject') IS NOT NULL
DROP TABLE #tblProject
CREATE TABLE #tblProject
(
ID INT,
[PROJName] VARCHAR(100)
)
INSERT INTO #tblProject
SELECT 1 , 'Project1'
UNION ALL
SELECT 2 ,'Project2'
UNION ALL
SELECT 3 ,'Project3'
UNION ALL
SELECT 4 ,'Project4'
IF OBJECT_ID('TEMPDB..#tblDeliverables') IS NOT NULL
DROP TABLE #tblDeliverables
CREATE TABLE #tblDeliverables
(
ID SMALLINT ,--IDENTITY(1,1),
ProjectID INT ,
DeliverableNameID INT,
ForecastDate DATETIME,
CompleteDate DATETIME
)
INSERT INTO #tblDeliverables
SELECT 1 , 1 , 1 , GETDATE() , DATEADD(DD,1,GETDATE())
UNION ALL
SELECT 2, 1 ,2 , GETDATE() , DATEADD(DD,1,GETDATE())
UNION ALL
SELECT 3 ,1, 3, GETDATE() , DATEADD(DD,1,GETDATE())
UNION ALL
SELECT 4 ,1, 4, GETDATE() , DATEADD(DD,1,GETDATE())
UNION ALL
SELECT 5, 2 ,1, GETDATE() , DATEADD(DD,1,GETDATE())
UNION ALL
SELECT 6, 2 ,3, GETDATE() , DATEADD(DD,1,GETDATE())
UNION ALL
SELECT 7 ,2, 4, GETDATE() , DATEADD(DD,1,GETDATE())
UNION ALL
SELECT 8 ,3, 1, GETDATE() , DATEADD(DD,1,GETDATE())
UNION ALL
SELECT 9 ,3 ,2, GETDATE() , DATEADD(DD,1,GETDATE())
UNION ALL
SELECT 10, 3 ,4, GETDATE() , DATEADD(DD,1,GETDATE())
UNION ALL
SELECT 11 ,4 ,1, GETDATE() , DATEADD(DD,1,GETDATE())
UNION ALL
SELECT 12, 4, 2, GETDATE() , DATEADD(DD,1,GETDATE())
UNION ALL
SELECT 13 ,4 ,3, GETDATE() , DATEADD(DD,1,GETDATE())
UNION ALL
SELECT 14 ,4, 4, GETDATE() , DATEADD(DD,1,GETDATE())
Now, the CODE that will perform what we wanted it to and how you wished!
SELECT
P.[PROJName] ProjectName,
MAX(CASE d.DeliverableNameID WHEN 1 THEN d.ForecastDate END) AS InstructionForecastDate ,
MAX(CASE d.DeliverableNameID WHEN 1 THEN d.CompleteDate END) AS InstructionCompleteDate ,
MAX(CASE d.DeliverableNameID WHEN 2 THEN d.ForecastDate END) AS MeetingForecastDate ,
MAX(CASE d.DeliverableNameID WHEN 2 THEN d.CompleteDate END) AS MeetingCompleteDate ,
MAX(CASE d.DeliverableNameID WHEN 3 THEN d.ForecastDate END) AS SiteVisitForecastDate ,
MAX(CASE d.DeliverableNameID WHEN 3 THEN d.CompleteDate END) AS SiteVisitCompleteDate ,
MAX(CASE d.DeliverableNameID WHEN 4 THEN d.ForecastDate END) AS ReportForecastDate ,
MAX(CASE d.DeliverableNameID WHEN 4 THEN d.CompleteDate END) AS ReportCompleteDate
FROM
#tblDeliverables d
JOIN
#tblDeliverableNames n
ON d.DeliverableNameID = n.ID
LEFT JOIN
#tblProject P
ON (P.ID = d.ProjectID)
GROUP BY
P.[PROJName]
Now, did the above give u what you wanted ?? 🙂 Pro'ly there may be other best methods for this , but as a starter, you can have this script!
If thats what u wanted, then today's beer is on you 😛
Cheers!!
April 28, 2010 at 12:55 am
Hi ColdCoffee
Thanks for all your efforts. I'll have to get used to posting stuff like this and will pay more attention next time. Apologies for the hassle.
Your answer did get part of the way, but what I need is the detail you have provided as headings to the columns in the view, and then to populate the forecast dates and complete dates within the view under each heading.
In addition, if there are no such deliverable dates for a project (such as meeting in Project2), then I need to populate with something like 'N/A' to show that it is not applicable to that project.
Does this make sense. Please let me know if there is anything else I can provide. I am still new to this stuff and appreciate you steering me in the right direction.
Thanks again
April 28, 2010 at 1:54 am
jwellington (4/28/2010)
Your answer did get part of the way, but what I need is the detail you have provided as headings to the columns in the view, and then to populate the forecast dates and complete dates within the view under each heading.
I perfectly DINT (:() get what your required output is. I need to see some visual samples of your desired output
In addition, if there are no such deliverable dates for a project (such as meeting in Project2), then I need to populate with something like 'N/A' to show that it is not applicable to that project.
We can do this, but we will be loosing the date format that you are seeing now. We will have to convert NULLS into VARCHAR which will cost us the format of the date what u are using in the ForecastDate and CompleteDate columns
Please let me know if there is anything else I can provide. I am still new to this stuff and appreciate you steering me in the right direction.
Yes, i need to see visually how your desired output will be;
Like this
ProjectNameInstructionForecastDateInstructionCompleteDate
Project12010-04-28 13:18:08.3802010-04-29 13:18:08.380
Project22010-04-28 13:18:08.3802010-04-29 13:18:08.380
Project32010-04-28 13:18:08.3802010-04-29 13:18:08.380
Project42010-04-28 13:18:08.3802010-04-29 13:18:08.380
To get above, wrap your desired output in code="plain" /code "IFCode" you see to the right of the typing area.
Hope this helps you...
Cheers!
April 28, 2010 at 5:47 pm
Hi ColdCoffee,
Works great thanks, except for putting the N/A in if the deliverable doesn't exist for that project such as below:
ProjectNameMeetingHeldForecastDateMeetingHeldCompleteDate
Project12010-04-28 13:18:08.3802010-04-29 13:18:08.380
Project2N/AN/A
Project32010-04-28 13:18:08.380NULL
Project4NULLNULL
You will notice that there are still nulls in there when the date has not been filled in (and the deliverable exists).
I am willing to forgo the date format for this view, and also to convert the NULLs to ''.
I have tried using CONVERT and CASE statements within the view but cannot seem to get it to work as required.
Thanks for your assistance so far.
April 28, 2010 at 11:27 pm
Ok, as you have decided to forgo the date fomat, it becomes my duty to provide you the code that will replace NULL dates with 'N/A'.
Here is that final (presumably ;-)) CODE..
SELECT
P.[PROJName] ProjectName,
ISNULL(CONVERT(VARCHAR,(MAX(CASE d.DeliverableNameID WHEN 1 THEN d.ForecastDate END)),20),'N/A') AS InstructionForecastDate ,
ISNULL(CONVERT(VARCHAR,(MAX(CASE d.DeliverableNameID WHEN 1 THEN d.CompleteDate END)),20),'N/A') AS InstructionCompleteDate ,
ISNULL(CONVERT(VARCHAR,(MAX(CASE d.DeliverableNameID WHEN 2 THEN d.ForecastDate END)),20),'N/A') AS MeetingForecastDate ,
ISNULL(CONVERT(VARCHAR,(MAX(CASE d.DeliverableNameID WHEN 2 THEN d.CompleteDate END)),20),'N/A') AS MeetingCompleteDate ,
ISNULL(CONVERT(VARCHAR,(MAX(CASE d.DeliverableNameID WHEN 3 THEN d.ForecastDate END)),20),'N/A') AS SiteVisitForecastDate ,
ISNULL(CONVERT(VARCHAR,(MAX(CASE d.DeliverableNameID WHEN 3 THEN d.CompleteDate END)),20),'N/A') AS SiteVisitCompleteDate ,
ISNULL(CONVERT(VARCHAR,(MAX(CASE d.DeliverableNameID WHEN 4 THEN d.ForecastDate END)),20),'N/A') AS ReportForecastDate ,
ISNULL(CONVERT(VARCHAR,(MAX(CASE d.DeliverableNameID WHEN 4 THEN d.CompleteDate END)),20),'N/A') AS ReportCompleteDate
FROM
#tblDeliverables d
JOIN
#tblDeliverableNames n
ON d.DeliverableNameID = n.ID
LEFT JOIN
#tblProject P
ON (P.ID = d.ProjectID)
GROUP BY
P.[PROJName]
Tell us back here if that piece worked your needs!!
Cheers!
April 28, 2010 at 11:51 pm
Hi ColdCoffee,
Thanks for the code, but unfortunately it is not exactly what I was looking for. I don't actually want to replace all NULL dates directly with N/A.
You will notice in my previous post (and the original one that was difficult to understand) that for Project 2 there was no 'Meeting Held' deliverable. In this case, there will be no date values for that project milestone (ie. they don't exist). Your code would still state this as Null, which is understandable, but then there is no differentiation between a milestone date that does not exist and one that has not yet been populated.
I would like to differentiate between existing date fields that have not yet been populated (shown with NULL values) and project milestone dates that do not actually exist for the specific project (shown with N/A).
Does this make more sense. I hope so and appreciate all your efforts to help so far. I find this forum so helpful. Hopefully on future posts I can get all that syntax correct to start with.
Thanks again
April 29, 2010 at 12:39 am
Wellington, as i had already asked, i need to see the records in your table (i dont want your orginal data, you may very well cook-up data that looks similar to your original data) and what u expect your result should be..i have given you a head-start and have given how to give sample data.. if you could edit them and send me, i can give you what you want.. without that, my hands are tied, buddy..
April 29, 2010 at 1:08 am
Hi ColdCoffee,
Thanks for your post. I understand your frustration. The code you put in your post with the table structures is suitable for this question (as it contains the missing 'Meeting Held' deliverable on Project2), but if it is not suitable for you to answer then I'm afraid you have me beaten. Thanks so much for the information you have helped me with so far. It has certainly put me on the path to finding a solution.
Thanks again
April 29, 2010 at 1:46 am
jwellington (4/29/2010)
Hi ColdCoffee,Thanks for your post. I understand your frustration. Thanks so much for the information you have helped me with so far. It has certainly put me on the path to finding a solution.
Wellington, i never frustrated mate :-)! I was attending another issue for my colleague and i replied your hurriedly.. thats why my tone in the reply would be different. had my post hurt you, i apologize, sire!
I hope that you find a solution to your problem soon, and once u do it, pls post your final code so that others may benefit from that !
It was my pleasure solving your issues, Thanks!
May 3, 2010 at 10:24 pm
Hi,
I believe I have solved this one. It involves a fair bit of manual coding for the entire view, but below is the solution for one line:
CASE WHEN EXISTS(SELECT * FROM tblDeliverables WHERE tblDeliverables.ProjectID= tblProject.id AND tblDeliverables.DeliverableNameID = 1) THEN CONVERT(nvarchar(10), tblDeliverables.ForecastDate) ELSE 'N/A' END AS InstructionCompleteForecastDate
All columns in the view use Group By, and there is definitely only on record possible for a single projectID and DeliverableNameID combination.
Hope this helps anyone looking in future.
Thanks for all your prior help ColdCoffee.
May 4, 2010 at 2:46 am
You're welcome, Wellington! 🙂
Good to see that your code is fixed!
Cheers!!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply