February 3, 2009 at 8:42 am
Hello All Experts,
If I have 3 tables, and each table has 3 columns such as
Table BO
BODDS BOCLEARED BOCLEAREDDATE
BO 3 1/1/2009
BO 1 1/2/2009
BO 5 1/3/2009
Table CT
CTDDS CTCLEARED CTCLEAREDDATE
CT 1 1/1/2009
CT 2 1/3/2009
CT 4 1/5/2009
Table NH
NHDDS NHCLEARED NHCLEAREDDATE
NH 1 1/1/2009
NH 5 1/5/2009
NH 2 1/6/2009
I wish to know the number of cases that all three offices (NH, BO, and CT) have been cleared, and I wish to display the outlook likes below:
add all three tables into one table and calls it a new name: Total
The outlook below is the result that I wish to have
Table Total
DDS TotalCleared ClearedDate
BO,NH,CT 5 1/1/2009
BO 1 1/2/2009
BO, CT 7 1/3/2009
CT, NH 9 1/5/2009
NH 2 1/6/2009
Please help me ...help me
Thank you very very very much
February 3, 2009 at 8:50 am
February 3, 2009 at 9:34 am
Hello Mike,
I'm sorry I don't know how to put in the right format in this SQL.
I apolozige for the inconvenient.
Thank you
February 3, 2009 at 9:34 am
Please help me experts out there
February 3, 2009 at 9:37 am
If I have 3 tables, and each table has 3 columns such as
Table BO
BODDS BOCLEARED BOCLEAREDDATE
BO 3 1/1/2009
BO 1 1/2/2009
BO 5 1/3/2009
Table CT
CTDDS CTCLEARED CTCLEAREDDATE
CT 1 1/1/2009
CT 2 1/3/2009
CT 4 1/5/2009
Table NH
NHDDS NHCLEARED NHCLEAREDDATE
NH 1 1/1/2009
NH 5 1/5/2009
NH 2 1/6/2009
I wish to know the number of cases that all three offices (NH, BO, and CT) have been cleared, and I wish to display the outlook likes below:
add all three tables into one table and calls it a new name: Total
The outlook below is the result that I wish to have
Table Total
DDS TotalCleared ClearedDate
BO,NH,CT 5 1/1/2009
BO 1 1/2/2009
BO, CT 7 1/3/2009
CT, NH 9 1/5/2009
NH 2 1/6/2009
Please help me ...help me
Thank you very very very much
February 3, 2009 at 10:07 am
josephptran2002 (2/3/2009)
Hello Mike,I'm sorry I don't know how to put in the right format in this SQL.
I apolozige for the inconvenient.
Thank you
Read the link that Michael provided (also in my signature) and it tells you how to provide data in a usable format.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 3, 2009 at 10:13 am
Hello All,
I am new to this SQL. Please help me
February 3, 2009 at 12:56 pm
Hello Experts,
WOuld you please help me to solve this question that I have posted today?
Thank you very much
February 3, 2009 at 1:03 pm
Have you read the article that several of us have recommended that you read? If you follow the steps in that article, you can post everything we need to help you. So, help us help you, read the article first, then follow the steps it provides.
We're more than willing to help, but you have to give us what we need. we aren't paid to do this, we do it because we want to do it.
February 3, 2009 at 1:51 pm
This sounds like homework but here you go anyway:
SELECT ClearedDate, TotalCleared,
STUFF(DDS,1,1,'') AS DDS
FROM
(
SELECT ClearedDate, SUM(Cleared) AS TotalCleared
, CASE WHEN SUM( case when DDS = 'BO' then 1 end) IS NOT NULL THEN ',BO' ELSE '' END +
CASE WHEN SUM( case when DDS = 'CT' then 1 end) IS NOT NULL THEN ',CT' ELSE '' END +
CASE WHEN SUM( case when DDS = 'NH' then 1 end) IS NOT NULL THEN ',NH' ELSE '' END As DDS
FROM
(
SELECT BODDS As DDS, BOCLEARED As Cleared,BOCLEAREDDATE AS ClearedDate FROM BO
UNION ALL SELECT CTDDS, CTCLEARED, CTCLEAREDDATE FROM CT
UNION ALL SELECT NHDDS, NHCLEARED, NHCLEAREDDATE FROM NH
) T
GROUP BY ClearedDate
)A
* Noel
April 2, 2009 at 9:13 am
josephptran2002,
We are happy to help, but we want you to do some work yourself and show some effort. People are posting links and hints, and you need to make an effort. We are not a service to do your job/homework for you.
April 2, 2009 at 10:01 am
Hi Steve,
I try to put some sample data as it requires in this forum and their format. But I could not, would you please take a look at the way I post for my sample data to see if something is wrong with the way I posted. and show me the right way to post.
This is what I did to post the sample data, on the IFCODE Shortcuts then I click on and start typing code of table and sample data in between of code brackets. But it won't show a table with sample data in the table
Thank you
Joe
April 2, 2009 at 10:30 am
Hello NOELD
How are you doing? First, thank you so much for your codes, I am greatly appreciated your helps and this is a homework that I try to do. So I can understand it. Second, But let me explain my situation for you, so you can help me out.
I have a main table where to store the data,
I have mutliple columns and those are
SSN, DECLEAREDDATE, SOMATICMCCLEAREDDATE, PSYCMCCLEAREDDATE, DESECONDCLEAREDDATE, SOMATICMCCLEAREDDATE, PSYCMCSECONDCLEAREDDATE, DDS.
SSN INT, DDS VARCHAR, and the rests are Datetime.
If there is a case (SSN), which has been assigned to one of three columns (only one column) such as DECLEAREDDATE OR SOMATICMCCLEAREDDATE, OR PSYCMCCLEAREDDATE. For example,
SSN is 111223333 is assigned to DECLEAREDDATE then the rests of two columns such as SOMATICMCCLEAREDDATE, and PSYCMCLEAREDDATE should be blanked. If a different case is assigned to SOMATICMCCLEAREDDATE then DECLEAREDDATE and PSYCMCCLEAREDDATE should be blanked. If the column has been assigned to determine the outcome of case, for example like case 111223333 is assigned to DECLEAREDDATE then this column will have a date such as 1/1/2009.
But if there is a case 222334444 has been assigned to SomaticMCClearedDate to determine the outcome, somehow that case returned for the second time. Because the first determination was wrong and second time reviews should be SOMATICMCSECONDCLEAREDDATE, then DESECONDCLEAREDDATE & PSYCMCSECONDCLEAREDDATE should be emptied. But this case won't count as a cleared case with no returns (this case is a return case).
However, if a case likes 111223333 is a cleared case with no returns then it should have the office name where this case was cleared by what office. So DDS is an office name where contains the office variable BO, CT, NH, VT, ME, RI.
I wish to count and display the number of cases cleared with no returns. And the outlook that I wish to have:
Create Table [dbo][WeeklyActualClearedCasewithNoReturns]
(
[SSN][INT]NULL,[DDS][Varchar](3)NULL,[DECLEAREDDATE][Datetime]NULL,[SOMATICMCCLEAREDDATE][Datetime]NULL,[PSYCMCCLEAREDDATE][DATETIME]NULL,[DESECONDCLEAREDDATE][DATETIME]NULL,[SOMATICMCSECONDCLEAREDDATE][DATETIME]NULL, [PSYCMCSECONDCLEAREDDATE][DATETIME]NULL
)ON [PRIMARY]
INSERT INTO WeeklyActualClearedCasewithNoReturns Values('000112222', 'CT', '1/1/2009', ' ', ' ', ' ', ' ', ' ')
INSERT INTO WeeklyActualClearedCasewithNoReturns Values ('111335555', 'ME', ' ', ' ', '1/4/2009', ' ', ' ', ' ')
INSERT INTO WeeklyActualClearedCasewithNoReturns Values ('666223333', 'CT', ' ', '1/6/2009', ' ', ' ', ' ', ' ') INSERT INTO WeeklyActualClearedCasewithNoReturns Values ('999228888', 'RI', ' ', ' ', '1/11/2009', ' ', ' ', ' ')
I have written the codes for this other reports, and they count all cases. But this report I get stuck because of the conditions and that condition are
Count cases cleared with no returns, and SSN is count only once time (SSN is a primary key, so there is no way that SSN can be duplicated or count twice). So I ignore SSN condition in this factor.
Thank you so so so much for all efforts that you help me.
May god bless your helps to new SQL developer like me. and try to learn as fast as possible.
ALTER PROCEDURE [dbo].[WklyClearances]
-- Add the parameters for the stored procedure here
@Start Datetime,
@End Datetime,
@Parameter varchar(3) = 'ALL'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
Select @Start = COALESCE( @Start, '01-Jan-2000'),
@End = COALESCE( @End, GETDATE() ),
@Parameter = COALESCE( @Parameter, 'ALL')
;WITH AllDDS
AS
(
SELECT DDS, DEClearedDate AS ClearedDate, DECleared AS Cleared
FROM dbo.DECleared
WHERE (DEClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, SomaticMCClearedDate AS ClearedDate, SomaticMCCleared AS Cleared
FROM dbo.SomaticMCCleared
WHERE (SomaticMCClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, PsycMCClearedDate AS ClearedDate, PsycMCCleared AS Cleared
FROM dbo.PsycMCCleared
WHERE (PsycMCClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, DESecondClearedDate AS ClearedDate, DESecondCleared AS Cleared
FROM dbo.DESecondCleared
WHERE (DESecondClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, SomaticMCSecondClearedDate AS ClearedDate, SomaticMCSecondCleared AS Cleared
FROM dbo.SomaticMCSecondCleared
WHERE (SomaticMCSecondClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, PsycMCSecondClearedDate AS ClearedDate, PsycMCSecondCleared AS Cleared
FROM dbo.PsycMCSecondCleared
WHERE (PsycMCSecondClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, DEThirdClearedDate AS ClearedDate, DEThirdCleared AS Cleared
FROM dbo.DEThirdCleared
WHERE (DEThirdClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, SomaticMCThirdClearedDate AS ClearedDate, SomaticMCThirdCleared AS Cleared
FROM dbo.SomaticMCThirdCleared
WHERE (SomaticMCThirdClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, PsycMCThirdClearedDate AS ClearedDate, PsycMCThirdCleared AS Cleared
FROM dbo.PsycMCThirdCleared
WHERE (PsycMCThirdClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, DEFourthClearedDate AS ClearedDate, DEFourthCleared AS Cleared
FROM dbo.DEFourthCleared
WHERE (DEFourthClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, SomaticMCFourthClearedDate AS ClearedDate, SomaticMCFourthCleared AS Cleared
FROM dbo.SomaticMCFourthCleared
WHERE (SomaticMCFourthClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, PsycMCFourthClearedDate AS ClearedDate, PsycMCFourthCleared AS Cleared
FROM dbo.PsycMCFourthCleared
WHERE (PsycMCFourthClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
),
PivotDDS
AS
(
SELECT ClearedDate,
ISNULL( SUM( ISNULL( [BO], 0 ) ), 0 ) AS [BO],
ISNULL( SUM( ISNULL( [CT], 0 ) ), 0 ) AS [CT],
ISNULL( SUM( ISNULL( [NH], 0 ) ), 0 ) AS [NH],
ISNULL( SUM( ISNULL( [ME], 0 ) ), 0 ) AS [ME],
ISNULL( SUM( ISNULL( [RI], 0 ) ), 0 ) AS [RI],
ISNULL( SUM( ISNULL( [VT], 0 ) ), 0 ) AS [VT],
ISNULL( SUM( ISNULL( [WO], 0 ) ), 0 ) AS [WO]
FROM AllDDS
PIVOT
(
SUM( Cleared ) FOR DDS IN( [BO], [CT], [NH], [ME], [RI], [VT], [WO] )
) P
GROUP BY ClearedDate
),
FinalDDS
AS
(
SELECT ClearedDate, [BO] AS BOCleared, [CT] AS CTCleared, [NH] AS NHCleared,
[ME] AS MECleared, [RI] AS RICleared, [VT] AS VTCleared, [WO] AS WOCleared,
[BO] + [CT] + [NH] + [ME] + [RI] + [VT] + [WO] AS TotalCleared,
( CASE WHEN [BO] > 0 THEN ', BO' ELSE ' ' END )
+ ( CASE WHEN [CT] > 0 THEN ', CT' ELSE ' ' END )
+ ( CASE WHEN [NH] > 0 THEN ', NH' ELSE ' ' END )
+ ( CASE WHEN [ME] > 0 THEN ', ME' ELSE ' ' END )
+ ( CASE WHEN [RI] > 0 THEN ', RI' ELSE ' ' END )
+ ( CASE WHEN [VT] > 0 THEN ', VT' ELSE ' ' END )
+ ( CASE WHEN [WO] > 0 THEN ', WO' ELSE ' ' END ) AS DDS
FROM PivotDDS
)
SELECT *
From (
Select Sum(BOCleared) as BOCleared, Sum(CTCleared) as CTCleared, Sum(NHCleared) as NHCleared, Sum(MECleared) as MECleared, Sum(RICleared) as RICleared, Sum(VTCleared) as VTCleared, Sum(WOCleared) as WOCleared,
Sum(TotalCleared) AS TotalCleared,ClearedDate AS ClearedDate, SUBSTRING( DDS, 3, 1000 ) AS DDS
FROM FinalDDS
GROUP BY ClearedDate, SUBSTRING(DDS, 3, 1000) WITH ROLLUP
)D
Where (ClearedDate IS NULL AND DDS IS NULL) OR (ClearedDate IS NOT NULL AND DDS IS NOT NULL)
Order BY ISNULL( ClearedDate, '31-Dec-2090')
END
April 2, 2009 at 1:40 pm
Hello josephptran2002
Maybe you better understand what the people want to tell you with your data.
Your posted sample data:
BODDS BOCLEARED BOCLEAREDDATE
BO 3 1/1/2009
BO 1 1/2/2009
BO 5 1/3/2009
Problem
Everybody who wants to help has first try to discover your table structure and than format your data to get it into a test table.
Better way for this:
CREATE TABLE #BO
(
BODDS VARCHAR(10),
BOCLEARED INT,
BOCLEAREDDATE DATETIME
)
INSERT INTO #BO
SELECT 'BO', 3, '1/1/2009'
UNION SELECT 'BO', 1, '1/2/2009'
UNION SELECT 'BO', 5, '1/3/2009'
Formatting
As you see in my previous example I used the SQL formatting feature of this forum. I just had a look to your previously posted procedure (by clicking the "Quote" button) and saw that your stored procedure is well formatted, so you should post it as it is. Just use the IFCode shortcuts on left side of the text area when you write a post. To format SQL you just have to write a "[ code ]" (without the spaces) at the beginning and a "[ /code ]" at the end of your SQL. If you just use these two words it looks like this:
ALTER PROCEDURE [dbo].[WklyClearances]
-- Add the parameters for the stored procedure here
@Start Datetime,
@End Datetime,
@Parameter varchar(3) = 'ALL'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
Select @Start = COALESCE( @Start, '01-Jan-2000'),
@End = COALESCE( @End, GETDATE() ),
@Parameter = COALESCE( @Parameter, 'ALL')
;WITH AllDDS
AS
(
SELECT DDS, DEClearedDate AS ClearedDate, DECleared AS Cleared
FROM dbo.DECleared
WHERE (DEClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, SomaticMCClearedDate AS ClearedDate, SomaticMCCleared AS Cleared
FROM dbo.SomaticMCCleared
WHERE (SomaticMCClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, PsycMCClearedDate AS ClearedDate, PsycMCCleared AS Cleared
FROM dbo.PsycMCCleared
WHERE (PsycMCClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, DESecondClearedDate AS ClearedDate, DESecondCleared AS Cleared
FROM dbo.DESecondCleared
WHERE (DESecondClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, SomaticMCSecondClearedDate AS ClearedDate, SomaticMCSecondCleared AS Cleared
FROM dbo.SomaticMCSecondCleared
WHERE (SomaticMCSecondClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, PsycMCSecondClearedDate AS ClearedDate, PsycMCSecondCleared AS Cleared
FROM dbo.PsycMCSecondCleared
WHERE (PsycMCSecondClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, DEThirdClearedDate AS ClearedDate, DEThirdCleared AS Cleared
FROM dbo.DEThirdCleared
WHERE (DEThirdClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, SomaticMCThirdClearedDate AS ClearedDate, SomaticMCThirdCleared AS Cleared
FROM dbo.SomaticMCThirdCleared
WHERE (SomaticMCThirdClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, PsycMCThirdClearedDate AS ClearedDate, PsycMCThirdCleared AS Cleared
FROM dbo.PsycMCThirdCleared
WHERE (PsycMCThirdClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, DEFourthClearedDate AS ClearedDate, DEFourthCleared AS Cleared
FROM dbo.DEFourthCleared
WHERE (DEFourthClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, SomaticMCFourthClearedDate AS ClearedDate, SomaticMCFourthCleared AS Cleared
FROM dbo.SomaticMCFourthCleared
WHERE (SomaticMCFourthClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, PsycMCFourthClearedDate AS ClearedDate, PsycMCFourthCleared AS Cleared
FROM dbo.PsycMCFourthCleared
WHERE (PsycMCFourthClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
),
PivotDDS
AS
(
SELECT ClearedDate,
ISNULL( SUM( ISNULL( [BO], 0 ) ), 0 ) AS [BO],
ISNULL( SUM( ISNULL( [CT], 0 ) ), 0 ) AS [CT],
ISNULL( SUM( ISNULL( [NH], 0 ) ), 0 ) AS [NH],
ISNULL( SUM( ISNULL( [ME], 0 ) ), 0 ) AS [ME],
ISNULL( SUM( ISNULL( [RI], 0 ) ), 0 ) AS [RI],
ISNULL( SUM( ISNULL( [VT], 0 ) ), 0 ) AS [VT],
ISNULL( SUM( ISNULL( [WO], 0 ) ), 0 ) AS [WO]
FROM AllDDS
PIVOT
(
SUM( Cleared ) FOR DDS IN( [BO], [CT], [NH], [ME], [RI], [VT], [WO] )
) P
GROUP BY ClearedDate
),
FinalDDS
AS
(
SELECT ClearedDate, [BO] AS BOCleared, [CT] AS CTCleared, [NH] AS NHCleared,
[ME] AS MECleared, [RI] AS RICleared, [VT] AS VTCleared, [WO] AS WOCleared,
[BO] + [CT] + [NH] + [ME] + [RI] + [VT] + [WO] AS TotalCleared,
( CASE WHEN [BO] > 0 THEN ', BO' ELSE ' ' END )
+ ( CASE WHEN [CT] > 0 THEN ', CT' ELSE ' ' END )
+ ( CASE WHEN [NH] > 0 THEN ', NH' ELSE ' ' END )
+ ( CASE WHEN [ME] > 0 THEN ', ME' ELSE ' ' END )
+ ( CASE WHEN [RI] > 0 THEN ', RI' ELSE ' ' END )
+ ( CASE WHEN [VT] > 0 THEN ', VT' ELSE ' ' END )
+ ( CASE WHEN [WO] > 0 THEN ', WO' ELSE ' ' END ) AS DDS
FROM PivotDDS
)
SELECT *
From (
Select Sum(BOCleared) as BOCleared, Sum(CTCleared) as CTCleared, Sum(NHCleared) as NHCleared, Sum(MECleared) as MECleared, Sum(RICleared) as RICleared, Sum(VTCleared) as VTCleared, Sum(WOCleared) as WOCleared,
Sum(TotalCleared) AS TotalCleared,ClearedDate AS ClearedDate, SUBSTRING( DDS, 3, 1000 ) AS DDS
FROM FinalDDS
GROUP BY ClearedDate, SUBSTRING(DDS, 3, 1000) WITH ROLLUP
)D
Where (ClearedDate IS NULL AND DDS IS NULL) OR (ClearedDate IS NOT NULL AND DDS IS NOT NULL)
Order BY ISNULL( ClearedDate, '31-Dec-2090')
END
As you see it is much easier to read. 😉
Greets
Flo
April 2, 2009 at 1:52 pm
Hello there,
Thank you so much for showing me how to post SQL code, would you please tell me how to create a table with the sample data in table?
Thank you
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply