December 4, 2008 at 9:22 am
Hi Bob,
Do you know why you try to save the view and it won't allow to save it? and they have the message "you must declare the scalar variable @start? I have already declared @start.
Thanks
December 4, 2008 at 9:46 am
What if I have a table which has multiple columns and those columns contain the dates and I wish to merge those dates columns into one new column? Do I still have to use pivot table?
Joe, @pivotTable is just a sample I created to show you how the UNPIVOT works. It has multiple date columns (Date1-Date4) and the code I showed you merges them into one column. It is up to you to put your table name and column names in place of the ones I made up.
Why you have to be declared the pivottable? Because you wish to use pivottable right? and my next question is why getdate()-365, getdate()+2, getdate()+3 ? why you substract 365 (one year), add 2, add 3? then union all select getdate()+3, getdate()+4, getdate()+7, getdate()+2? why you add 3, 4, 7, and 2? and add 5, 4, 1, and 2?
Again, I declared the table variable just as an example. The getdate()+x were just used to populate my table with sample data so that you could see the results when you ran the code. If you haven't done so yet, please cut and paste my example and run it. Getdate()-365 was used to get at least one date with a previous year to illustrate why you want to order by the datetime column and not on the character string that results from doing a CONVERT of the datetime column.
P.S. The @start variable does not appear in my code. I just cut and paste it into a new window on my system and it ran just fine.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 4, 2008 at 2:00 pm
Hi Bob,
How are you doing? you might say I'm very dumb on SQL. But I have to ask you straight question which relates to my project. I think without your helps or someone consider expert like you then I will get stuck forever.
Here is my question
In View of SQL Server (not in table's query), I get two view
dbo.DECleared--> it contains two columns
DECleared DEClearedDate
1 1/1/2008 12:00:00 AM
1 1/2/2008 12:00:00 AM
1 1/6/2008 12:00:00 AM
1 1/9/2008 12:00:00 AM
1 1/15/2008 12:00:00 AM
dbo.SomaticCleared--> it also contains two columns
SomaticCleared SomaticClearedDate
1 1/1/2008 12:00:00 AM
1 1/2/2008 12:00:00 AM
1 1/4/2008 12:00:00 AM
1 1/7/2008 12:00:00 AM
1 1/16/2008 12:00:00 AM
How can add these columns together and result that I wish to show as below
Total Cleared Total Cleared Date
2 1/1/2008
2 1/2/2008
1 1/4/2008
1 1/6/2008
1 1/7/2008
1 1/9/2008
1 1/15/2008
1 1/16/2008
Sorry to ask you too much or anyone out there
Thank You Very Much
Jo
December 4, 2008 at 2:02 pm
Hi Bob,
regard to previous question,
How can I eliminate the time after I added two columns together ?
Thank You Bob,
Joe
December 4, 2008 at 2:44 pm
Dear Joe,
Two different views or tables make this a totally different problem with a totally different solution. Are you doing this project as a class assignment by any chance? I hope not. In any event, this will be my last post. Good luck with the rest of it.
Bob
------------------------------------------------------------------------------------------------------------------
declare @DeCleared table (DeCleared int, DeCleareddate datetime)
declare @SomaticCleared table (SomaticCleared int, SomaticCleareddate datetime)
insert into @DeCleared
select 1,'1/1/2008 12:00:00 AM' union all
select 1,'1/2/2008 12:00:00 AM' union all
select 1,'1/6/2008 12:00:00 AM' union all
select 1,'1/9/2008 12:00:00 AM' union all
select 1,'1/15/2008 12:00:00 AM'
insert into @SomaticCleared
select 1,'1/1/2008 12:00:00 AM' union all
select 1,'1/2/2008 12:00:00 AM' union all
select 1,'1/4/2008 12:00:00 AM' union all
select 1,'1/7/2008 12:00:00 AM' union all
select 1,'1/16/2008 12:00:00 AM'
;with CTE as
(SELECT isnull(DeCleared,0)+isnull(SomaticCleared,0) as TotalCleared,isnull(DeClearedDate,SomaticClearedDate) as ClearedDate
from @DeCleared
full outer join @SomaticCleared on DeClearedDate = SomaticClearedDate
)
select TotalCleared,convert(char(10),ClearedDate,101)as ClearedDate
from CTE
order by ClearedDate
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 5, 2008 at 9:38 am
Joseph, for your first post's question, I would simply use a UNION
Select A
UNION
Select B
UNION
SELECT C
etc
you can use the date to string conversions mentioned in other posts...
December 5, 2008 at 10:01 am
josephptran2002 (12/4/2008)
Hi Bob,How are you doing? you might say I'm very dumb on SQL. But I have to ask you straight question which relates to my project. I think without your helps or someone consider expert like you then I will get stuck forever.
Here is my question
In View of SQL Server (not in table's query), I get two view
dbo.DECleared--> it contains two columns
DECleared DEClearedDate
1 1/1/2008 12:00:00 AM
1 1/2/2008 12:00:00 AM
1 1/6/2008 12:00:00 AM
1 1/9/2008 12:00:00 AM
1 1/15/2008 12:00:00 AM
dbo.SomaticCleared--> it also contains two columns
SomaticCleared SomaticClearedDate
1 1/1/2008 12:00:00 AM
1 1/2/2008 12:00:00 AM
1 1/4/2008 12:00:00 AM
1 1/7/2008 12:00:00 AM
1 1/16/2008 12:00:00 AM
How can add these columns together and result that I wish to show as below
Total Cleared Total Cleared Date
2 1/1/2008
2 1/2/2008
1 1/4/2008
1 1/6/2008
1 1/7/2008
1 1/9/2008
1 1/15/2008
1 1/16/2008
Sorry to ask you too much or anyone out there
Thank You Very Much
Jo
select count(*), ClearedDate from
(select DECleared, convert(varchar(10),DEClearedDate,101) as ClearedDate from dbo.DECleared
UNION ALL
select SomaticCleared, convert(varchar(10),SomaticClearedDate,101)
as ClearedDate from dbo.SomaticCleared) A
group by ClearedDate
December 5, 2008 at 12:06 pm
Hi David,
Thank You Very Much for your scripts,
If I wish to show the total (sum) at the bottom of the column "TotalCleared", how do I do? would you please show me ?
Thank You
December 5, 2008 at 12:33 pm
Hi David,
when I ran the scripts you wrote, then the column "ClearedDate" won't show up. It only shows column "TotalCleared"
Thanks David
December 5, 2008 at 1:05 pm
Not sure, it shoed both columns for me... paste your exact code here I bet you just copied mine wrong 🙂
for totals... read this
http://www.sqlserverandxml.com/2008/09/tsql-lab-4-how-to-add-total-line-to.html
January 30, 2009 at 1:14 pm
Hello Bob,
I have a question which needs your helps because you are so good on this subject.
How are you doing? you might say I'm very dumb on SQL. But I have to ask you straight question which relates to my project. I think without your helps or someone consider expert like you then I will get stuck forever.
Here is my question
In View of SQL Server (not in table's query), I get two view
dbo.DECleared--> it contains two columns
DECleared DEClearedDate DEDDS
1 1/1/2008 12:00:00 AM BO
1 1/2/2008 12:00:00 AM BO
1 1/6/2008 12:00:00 AM BO
1 1/9/2008 12:00:00 AM BO
1 1/15/2008 12:00:00 AM BO
dbo.SomaticCleared--> it also contains two columns
SomaticCleared SomaticClearedDate SomaticDDS
1 1/1/2008 12:00:00 AM CT
1 1/2/2008 12:00:00 AM CT
1 1/4/2008 12:00:00 AM CT
1 1/7/2008 12:00:00 AM CT
1 1/16/2008 12:00:00 AM CT
How can add these columns together and result that I wish to show as below
Total Cleared Total Cleared Date DDS
2 1/1/2008 BO,CT
2 1/2/2008 BO,CT
1 1/4/2008 CT
1 1/6/2008 BO
1 1/7/2008 CT
1 1/9/2008 BO
1 1/15/2008 BO
1 1/16/2008 CT
AS I know that I should use CTE Command
with CTE as
(SELECT isnull(DeCleared,0)+isnull(SomaticCleared,0) as TotalCleared,isnull(DeClearedDate,SomaticClearedDate) as ClearedDate
from @DeCleared
full outer join @SomaticCleared on DeClearedDate = SomaticClearedDate
)
select TotalCleared,convert(char(10),ClearedDate,101)as ClearedDate
from CTE
order by ClearedDate
But this command gives me the result that I wish to have but It's missing the column "DDS" where it tells me which office has been cleared the case.
Would you please show me how to add more codes in this code? So it can give me the result that I wish to display.
Thank you so much Bob
Very respectful
January 30, 2009 at 2:13 pm
Hey Joseph,
The answer to your question follows, but a little discussion is in order. Please take a moment to look at how I set up the problem by creating a couple of table variables (or temporary tables) and putting data into them. This way it is easy for anyone else online to play with the data and test solutions before they post back a reply. I know you always ask for help very politely, so please be polite enough to set up the data like this in future questions. Starting out with a script that defines the tables and populates them with data saves the people who are trying to help you a great deal of time, and actually encourages more people to look at your problem. Consider it a way of saying "thank you" in advance. It is appreciated much more than any compliments.
You DID do an excellent job of showing exactly what you wanted your output to look like, so let's move on to your question. The solution was to add the following expression to your final SELECT
isnull(DEDDS,'')+(case when isnull(DeCleared,0)+isnull(SomaticCleared,0)= 2 then ',' else ''end)+isnull(SOMATICDDS,'') as DDS
The ISNULL statement checks to see if there is a null value and if so replaces it with an empty blank (''). This is necessary because any time you add NULL to anything, the result is always NULL. Your full outer join produces a result from each table, but if one table is missing a certain date, a null is returned. Therefore we use ISNULL to make sure both DEDDS and SomaticDDS are blanks.
The CASE statement is used to test whether or not we are going to have two elements. If so, we add a comma between them. There may be other ways to place the comma, but that will work for your needs with this problem. If you ever need to produce a list with more than two elements separated by columns, search this site for the word CONCATENATE or CONCATENATION.
Be warned that I periodically unsubscribe from threads that I've been following. If I had already done that, I would never have seen your note, and other people would have assumed that I was still helping you. When a thread has been idle for a long time and you have an additional question, you would do well to post it as a new question. You have a much better chance of getting help quickly from somebody. Believe me, there are REAL experts on here whose SQL knowledge far surpasses mine.
Good luck to you.
Bob
P.S. The CTE wasn't necessary to the solution. It could all be done with just one query, so I left the CTE out of the code below.
-------------------------------------------------------------------------------------------------
declare @DECleared table (DECLEARED int, DEClearedDate datetime, DEDDS char(2))
declare @SomaticCleared table (SomaticCleared int, SomaticClearedDate datetime, SomaticDDS char(2))
insert into @decleared
select 1 ,'1/1/2008 12:00:00 AM' ,'BO' union all
select 1 ,'1/2/2008 12:00:00 AM' ,'BO' union all
select 1 ,'1/6/2008 12:00:00 AM' ,'BO' union all
select 1 ,'1/9/2008 12:00:00 AM' ,'BO' union all
select 1 ,'1/15/2008 12:00:00 AM' ,'BO'
insert into @somaticCleared
select 1 ,'1/1/2008 12:00:00 AM' ,'CT' union all
select 1 ,'1/2/2008 12:00:00 AM' ,'CT' union all
select 1 ,'1/4/2008 12:00:00 AM' ,'CT' union all
select 1 ,'1/7/2008 12:00:00 AM' ,'CT' union all
select 1 ,'1/16/2008 12:00:00 AM' ,'CT'
SELECT isnull(DeCleared,0)+isnull(SomaticCleared,0) as TotalCleared,isnull(DeClearedDate,SomaticClearedDate) as ClearedDate,
isnull(DEDDS,'')+(case when isnull(DeCleared,0)+isnull(SomaticCleared,0)= 2 then ',' else ''end)+isnull(SOMATICDDS,'') as DDS
from @DeCleared D
full outer join @SomaticCleared S on DeClearedDate = SomaticClearedDate
order by ClearedDate
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply