April 1, 2009 at 8:33 am
Hello ALL,
I have created a query in store procedure and wish to capture a case if that case falls into one of the columns. For example,
Create Table #WeeklyActualCasesClearedWithNoReturns
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTER,
DECLEAREDDATE Datetime,
SOMATICMCCLEAREDDATE Datetime,
PSYCMCCLEARED DATETIME,
DESECONDCLEAREDDATE Datetime,
SOMATICMCSECONDCLEAREDDATE DATETIME,
PSYCMCSECONDCLEAREDDATE DATETIME,
DDS VARCHAR
)
SET Dateformat DMY
Select Identity_Insert #WeeklyActualCasesClearedWithNoReturns ON
Insert Into #WeeklyActualCasesClearedWithNoReturns(ID, DECLEAREDDATE, SOMATICMCCLEAREDDATE, PSYCMCCLEAREDDATE, DESECONDCLEAREDDATE, SOMATICMCSECONDCLEAREDDATE, PSYCMCSECONDCLEAREDDATE, DDS)
Select '001234312', '1/1/2009', ' ', ' ', ' ', ' ', ' ', 'BO' Union ALL Select '012542675',' ', '1/5/2009', ' ', ' ', ' ', ' ', 'CT' Union ALL Select '543124532' , ' ', ' ', '1/7/2009', ' ', ' ', ' ', 'ME' Union ALL Select '111223333', '1/8/2009', ' ', ' ', '1/9/2009', ' ', ' ', 'NH'
Set Identity_Insert #WeeklyActualCasesClearedWithNoReturns OFF
If there is a case and that case names "A" is assigned to SomaticMCClearedDate to determine the outcome of this case. Then DEClearedDate and PsycMCClearedDate Should be empty, because a case can only be assigned to one field name. Or case B is assigned to DEClEAREDDATE to determine, and of course SomaticMCClearedDate and PsycMCClearedDate are empty. I would like to capture the case as single record "1". CASE ID IS PRIMARY KEY.
DDS is an office name, BO and CT are office abbreviation name. There are more than 2 fields name in the table, because there is no space for me to show, so I would like all to execuse me for such inconveniently.
I wish to have an outlook like below:
BOCLEARED CTCLEARED... TOTALCLEARED CLEAREDDATE DDS
1 1 1/1/2009 BO
1 1 1/3/2009 CT
------------------------------------
1 1 2
I wrote the codes for the outlook above, but somehow it can not capture the case falls in either column field name. Please take a look at my codes and point out what I need to add or eliminate in order to capture what I need to display.
ALTER PROCEDURE [dbo].[FYTDClearances]
-- 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
WOULD ALL EXPERTS HELP ME?
Thank you
Joseph
April 1, 2009 at 8:40 am
Without the code for your stored procedure, table structure (CREATE TABLE statements), sample data, and expected results there isn't much we can do to help you.
April 1, 2009 at 8:47 am
I am sorry to forget posting my store procedure.
ALTER PROCEDURE [dbo].[FYTDClearances]
-- 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 1, 2009 at 9:00 am
FYI. Those of us that provide free help usually monitor the Active Threads which shows all threads from all forums. Multiple posts simply fragments the responses you get and actually makes some people upset and degrades the help you are seeking.
Also, to be sure, are you still using SQL Server 2000?
April 1, 2009 at 9:04 am
Lynn Pettis (4/1/2009)
Also, to be sure, are you still using SQL Server 2000?
Nope, that's 2005 or 2008. There's a CTE and a pivot in there. I'm requesting a move to SQL 2005 forum.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2009 at 9:09 am
GilaMonster (4/1/2009)
Lynn Pettis (4/1/2009)
Also, to be sure, are you still using SQL Server 2000?Nope, that's 2005 or 2008. There's a CTE and a pivot in there. I'm requesting a move to SQL 2005 forum.
Good catch. Should have redirected everyone to his 2005 forum, my mistake. But then, his code hadn't been posted at the time.
April 1, 2009 at 9:20 am
Hello,
I am using SQL 2005
Below are the codes that I wrote
ALTER PROCEDURE [dbo].[FYTDClearances]
-- 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 1, 2009 at 9:24 am
Hello There
I did not mean to make someone madding at me, I just try to seek all advices from all experts. Again, I am sorry.
I am using SQL 2005
Thank you in advances
April 1, 2009 at 2:39 pm
You really need to only post on the forum that applies. If you're using SQL 2005, please only post on the 2005 forums.
You won't be "missing" any experts. Those who check the 2005 forums are checking it because they know SQL 2005.
If you post on the 2000 forums, the answers you get will be in 2000 code and will miss out on using 2005 options and might not run on your 2005 server. If you post on the 2008 boards, you will get 2008 code which might not work on your 2005 server.
BUT, psot on the 2005 forums and you will get code and/or suggestions that work with 2005.
Please make your life and our lives easier by only posting on the appropriate boards.
Thank you, this has been a public service announcement.
-- Kit
April 1, 2009 at 3:04 pm
What do you mean with "WOULD ALL EXPERTS HELP ME?" Do you expect the rest of the world to jump up and throw their common knowledge at your specific problem when all you have been doing so far is to repeat the same long-winded and poorly formatted piece of SQL code back at us without listening to the requests to submit DDL statements and some sample data plus what the expected results should be given those samples? Don't expect any help anytime soon buddy.
April 2, 2009 at 6:21 am
Hello There,
How are you doing? Thanks for the respond and would you please show me how to post the sample data on the forum with the example of how to post the sample data? Because I read the instruction of how to post, but when I actually did it then It won't work.
Sorry for the inconvenient and thank you.
Joseph
April 2, 2009 at 7:47 am
josephptran2002 (4/2/2009)
Hello There,How are you doing? Thanks for the respond and would you please show me how to post the sample data on the forum with the example of how to post the sample data? Because I read the instruction of how to post, but when I actually did it then It won't work.
Sorry for the inconvenient and thank you.
Joseph
If you follow the instructions in the first article I have referenced below regard asking for assistance, you shouldn't have any problems providing us with what we have requested. The article is very detailed on how to do it. If you are having problems, I suggest you reread the article, print a copy of it and follow it step by step as you put together the requested information.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply