|
Hello everybody,
I try to show you the output but when I look from the preview part, the design is get out of order. I want to ask you how can I make the "null" parts gone? There are now empty spaces instead of nulls. But I want namesurname one after another without no spaces.
The output of this code is ;
MacType DAYS MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY
B(+) NIGHT BIRCAN AR
B(+) NIGHT BIRCAN AR
B(+) NIGHT HASAN DEM HASAN DEM
B(+) NIGHT HAVVA SUK HAVVA SUK
B(+) NIGHT HAVVA SUK
B(+) NIGHT MEHDIYE GÖR
B(+) NIGHT KEREM GOK
Here is my second table's view as I want.
What should I do to get this output?
MacType DAYS MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY
B(+) NIGHT BIRCAN AR BIRCAN AR HAVVA SUK HAVVA SUK
B(+) NIGHT HASAN DEM HASAN DEM MEHDIYE GÖR
B(+) NIGHT HAVVA SUK KEREM GOK
This my code;
select ISNULL(MAX(MachineType),''),
ISNULL(MAX(DAYS),''),
ISNULL(MAX(CASE WHEN DIALYSISDAY= 'MONDAY'THEN NAMESURNAME END),'') AS MONDAY,
ISNULL(MAX(CASE WHEN DIALYSISDAY= 'TUESDAY' THEN NAMESURNAME END),'') AS TUESDAY,
ISNULL(MAX(CASE WHEN DIALYSISDAY= 'WEDNESDAY' THEN NAMESURNAME END),'') AS WEDNESDAY,
ISNULL(MAX(CASE WHEN DIALYSISDAY= 'THURSDAY' THEN NAMESURNAME END),'') AS THURSDAY,
ISNULL(MAX(CASE WHEN DIALYSISDAY= 'FRIDAY' THEN NAMESURNAME END),'') AS FRIDAY,
ISNULL(MAX(CASE WHEN DIALYSISDAY= 'SATURDAY' THEN NAMESURNAME END),'') AS SATURDAY
from
(SELECT SICK.MACHINETYPE,SICK.NAMESURNAME ,
CASE WHEN SUBSTRING(DAYPERIOD, DATEPART(dw, SEANCE.SEANCEDATE), 1)= 'N' THEN 'NIGHT' ELSE '' END AS DAYS,
CASE WHEN SICK.MACHINETYPE= 1 THEN 'Negative-'
WHEN SICK.MACHINETYPE= 2 THEN 'B(+)'
WHEN SICK.MACHINETYPE= 3 THEN 'C(+)B(+)'
WHEN SICK.MACHINETYPE= 4 THEN 'C(+)' END AS MachineType,
CASE WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 1 THEN 'MONDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 2 THEN 'TUESDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 3 THEN 'WEDNESDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 4 THEN 'THURSDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 5 THEN 'FRIDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 6 THEN 'SATURDAY' END AS DIALYSISDAY
FROM SEANCE,SICK
WHERE SUBSTRING(DAYPERIOD, DATEPART(dw, SEANCE.SEANCEDATE), 1)= 'N'
and SUBSTRING(SICK.EUCLID,1,5)= '18016'
AND SEANCEDATE BETWEEN '20080301' AND '20080307'
AND SICK.EUCLID=SEANCE.EUCLID )AS T
GROUP BY MachineType,
DAYS,
CASE WHEN DIALYSISDAY= 'MONDAY'THEN NAMESURNAME END,
CASE WHEN DIALYSISDAY= 'TUESDAY' THEN NAMESURNAME END,
CASE WHEN DIALYSISDAY= 'WEDNESDAY' THEN NAMESURNAME END,
CASE WHEN DIALYSISDAY= 'THURSDAY' THEN NAMESURNAME END,
CASE WHEN DIALYSISDAY= 'FRIDAY' THEN NAMESURNAME END,
CASE WHEN DIALYSISDAY= 'SATURDAY' THEN NAMESURNAME END
ORDER BY MachineType
|
|
|
|
|
Ever heard of CAST and TRIM?
If not then google it, and use it!
HTH.
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
Hi,
By default when i install SQL Express Edition i don't havethe connexion named NT AUTHORITY\NetworkService. My problem is than i use integrated security in my connection string to allow instance user.
Integrated security need NT AUTHORITY\NetworkService to allow connexion to database.
How to create this account?
Who have a idea?
Thanks
|
|
|
|
|
Which OS you are using?
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
Windows Server 2003 Standard edition.
|
|
|
|
|
Can you please put more light on exactly what you are doing and how/where are you finding that account?
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
hi 
i want to look for feature start with leter 'b'
can i write
select * from mytable where feature like 'b%'
i want to make sure before starting so please help me
|
|
|
|
|
1. Please check your English. Your message title makes no sense.
2. Part of programming is making mistakes and learning from them. We are not here to hold your hand so you can always get it right the first time.
|
|
|
|
|
you can write that.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
Donate to help Conquer Cancer[ ^]
|
|
|
|
|
|
If you want to get all values which starts with letter b then use that query.
I Love T-SQL
Don't torture yourself,let the life do it for you.
|
|
|
|
|
|
Why would you ask this and not just try it ?
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
hi
because my program not ready
|
|
|
|
|
Are you coding Management Studio? 
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
OK - so why worry about SQL if you're incapable of running any ? I guess you need to wait until you can test your code. Or use a database with client tools.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
May be because its complex and time taking 
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
i want to download adventureworks database but i have slow internet connection. I tried to download from codeplex site but when i click on the link a dialogbox appears asking "i agree" and "i disagree". i want to use flashget for downloading it but that requires direct link and in presence of that link i can't download that. Can somebody tell me that is there any other site from where i can download this with direct link using any download manager.
|
|
|
|
|
You should make a google search...[^] , and you will find any direct link to download it.
I Love T-SQL
Don't torture yourself,let the life do it for you.
|
|
|
|
|
Hello,
I try to implement a WSE 3.0(webservice) in database in SQL server 2005 Standard. The scenario is :
- 1) Winform that call a webservice(send a token to webservice).
- 2) The Webservice call a Custom UsernameTokenManager (using the protected override string AuthenticateToken( UsernameToken token ))
- 3)Sql Server version is Standard version on my Server, but this database is configured by aspnet_regsql.exe to allow Membership functionnalities.
All works correctly to create user thru the webservice, but when i try to validate the user with (Membership.ValidateUser(login,password)i have this error:
The indicator opening session of instant user is not supported in this version of SQL Server.
Can you help me??
Thanks
|
|
|
|
|
hi
I tried to add my db to the server mssql136.win-servers.com ,
and I didn't know how do it ?
jooooo
|
|
|
|
|
Your host should be the best bet contact for this query. Wouldn't it be the case?
Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips
A pessimist sees only the dark side of the clouds, and mopes; a philosopher sees both sides, and shrugs; an optimist doesn't see the clouds at all - he's walking on them. --Leonard Louis Levinson
|
|
|
|
|
Hi,
I am trying to shred values of ModuleTorulemapid,selecteditemid and customvalue from following xml in SQL server 2005. For this purpose, I have written following code, but not able to shred it properly
----------------------------------------------------------------------
EXEC sp_xml_preparedocument @IDoc OUTPUT, @RollForwardRuleXML
SELECT DISTINCT
ModuleToRuleMapId 'ModuleToRuleMapGUID',SelectedItemId 'SelectedItemGUID',[UniqueIdentifier] 'OperationGUID',
CustomValue
FROM
OPENXML(@IDOC,'/ROOT/RuleBuilderModule/RuleBuilderRule/RuleBuilderOperation /RuleBuilderItem ',2)
WITH(ModuleToRuleMapId UNIQUEIDENTIFIER '/ROOT/RuleBuilderModule/RuleBuilderRule/@ModuleToRuleMapId',
SelectedItemId UNIQUEIDENTIFIER '../@SelectedItemId',
[UniqueIdentifier] UNIQUEIDENTIFIER '../@UniqueIdentifier',
CustomValue NVARCHAR(50) '@CustomValue'
)
EXEC sp_xml_removedocument @IDOC
-----------------------------------------------------------------------
Following is the XML which I am using
-----------------------------------------------------------------------
<ROOT>
<RuleBuilderModule Name="RollForwardMethod" UniqueIdentifier="6d6800a6-9006-4d65-a579-731872b16004" Timestamp="0">
<RuleBuilderRule Expression="Which method, automatic or manual, do you want to use for roll forward? <38fc8c42-c1ba-4843-81a4-69d8b9b21b98>" LockedStatus="3" ModuleToRuleMapId="93d552ea-5d8b-4cbd-b936-030ea4fca60a" RuleType="Question" IsBubbleHelpRequired="False" UniqueIdentifier="b88e94f3-48dc-4ce8-ae0c-be17c1133486" Timestamp="0">
<RuleBuilderOperation Name="RBuildMthdAutoManual" ValueSource="Internal" DisplayStyle="Multiple" SelectedItemId="45d72595-c7b7-41e4-bea3-1ffefa9bbfae" SelectedItem="Cch.WorkFlow.BusinessEntities.RuleBuilder.RuleBuilderItem" DialogType="ComboStyle" UniqueIdentifier="38fc8c42-c1ba-4843-81a4-69d8b9b21b98" Timestamp="0">
<RuleBuilderItem Name="Automatic" IsDefault="True" CustomValue="" UniqueIdentifier="45d72595-c7b7-41e4-bea3-1ffefa9bbfae" Timestamp="0" />
<RuleBuilderItem Name="Manual" IsDefault="False" CustomValue="" UniqueIdentifier="b0e4538f-8dc0-46d0-925e-f4a52e0ffdc6" Timestamp="0" />
</RuleBuilderOperation>
</RuleBuilderRule>
<RuleBuilderRule Expression="Rule: Automatically roll the project <363afba8-df04-4af2-a661-dc458bb55f5d>" LockedStatus="3" ModuleToRuleMapId="d5aeeaa7-09d9-4b33-aff9-74cedef43814" RuleType="Rule" IsBubbleHelpRequired="True" UniqueIdentifier="6ee47614-4632-498e-a619-7cacf3c547c4" Timestamp="0">
<RuleBuilderOperation Name="RBuildMthdAutoDuration" ValueSource="Internal" DisplayStyle="Link" SelectedItemId="8cc2752a-575f-484e-be83-1aa79fe1b45d" SelectedItem="Cch.WorkFlow.BusinessEntities.RuleBuilder.RuleBuilderItem" DialogType="ComboStyle" UniqueIdentifier="363afba8-df04-4af2-a661-dc458bb55f5d" Timestamp="0">
<RuleBuilderItem Name="after the client's fiscal year end" IsDefault="False" CustomValue="" UniqueIdentifier="38c5f62d-4859-4f1d-bcd2-807c9d81bf53" Timestamp="0" />
<RuleBuilderItem Name="when the project is completed" IsDefault="False" CustomValue="" UniqueIdentifier="3c66e057-baf2-4c81-a85e-1d4120ab8da2" Timestamp="0" />
<RuleBuilderItem Name="when the first linked work object is rolled" IsDefault="True" CustomValue="" UniqueIdentifier="8cc2752a-575f-484e-be83-1aa79fe1b45d" Timestamp="0" />
</RuleBuilderOperation>
</RuleBuilderRule>
<RuleBuilderRule Expression="How do you want to define who should approve the roll forward? <39d49d0f-6811-4286-8b7e-e75ee9abbdb5>" LockedStatus="3" ModuleToRuleMapId="992f66bb-d84e-4e4c-ba54-c8684b7aa4ae" RuleType="Question" IsBubbleHelpRequired="False" UniqueIdentifier="06b80a58-a700-4f2d-8d8d-e3ce1b752eaa" Timestamp="0">
<RuleBuilderOperation Name="RBuildMthdApprover" ValueSource="Internal" DisplayStyle="Multiple" SelectedItemId="18dca662-6713-4a5e-a3ea-375d48a68afc" SelectedItem="Cch.WorkFlow.BusinessEntities.RuleBuilder.RuleBuilderItem" DialogType="ComboStyle" UniqueIdentifier="39d49d0f-6811-4286-8b7e-e75ee9abbdb5" Timestamp="0">
<RuleBuilderItem Name="Individual" IsDefault="True" CustomValue="" UniqueIdentifier="18dca662-6713-4a5e-a3ea-375d48a68afc" Timestamp="0" />
<RuleBuilderItem Name="Role" IsDefault="False" CustomValue="" UniqueIdentifier="93aa037c-efb0-4038-b4ab-8d3f0da43f2d" Timestamp="0" />
<RuleBuilderItem Name="Team" IsDefault="False" CustomValue="" UniqueIdentifier="7984fc62-ad68-47ea-9b25-4ea9e97945b2" Timestamp="0" />
<RuleBuilderItem Name="Client Responsible Staff" IsDefault="False" CustomValue="" UniqueIdentifier="367fc7ee-f547-4fb2-bd8e-18b126e7da27" Timestamp="0" />
</RuleBuilderOperation>
</RuleBuilderRule>
<RuleBuilderRule Expression="Rule: <2346cf95-6281-4710-9a0a-ef743b50960d> should approve the automatic roll forward." LockedStatus="3" ModuleToRuleMapId="175b4326-eb2e-4a96-8930-06081070d471" RuleType="Rule" IsBubbleHelpRequired="False" UniqueIdentifier="f3b9104f-994c-46a7-82d4-7b85b71e4a23" Timestamp="0">
<RuleBuilderOperation Name="RBuildIndividgual" ValueSource="External" DisplayStyle="Link" SelectedItemId="739f5023-62f2-4ac0-a977-c6d074b4b0fd" SelectedItem="Cch.WorkFlow.BusinessEntities.RuleBuilder.RuleBuilderItem" DialogType="ComboStyle" UniqueIdentifier="2346cf95-6281-4710-9a0a-ef743b50960d" Timestamp="0">
<RuleBuilderItem Name="Unassigned" IsDefault="True" UniqueIdentifier="739f5023-62f2-4ac0-a977-c6d074b4b0fd" Timestamp="0" />
<RuleBuilderItem Name="Roger Fedrer" IsDefault="False" UniqueIdentifier="b5c3d0d2-d58e-4211-abdd-361ad729e309" Timestamp="0" />
<RuleBuilderItem Name="Martina Hinggis" IsDefault="False" UniqueIdentifier="0866c3b8-58ba-45fe-9135-3823f84ded31" Timestamp="0" />
<RuleBuilderItem Name="Jennifer Justin" IsDefault="False" UniqueIdentifier="ebac3d11-0b31-4266-a6c5-a04494397312" Timestamp="0" />
<RuleBuilderItem Name="Wilson Danial" IsDefault="False" UniqueIdentifier="c9b17eb4-7915-47ff-801d-90c7ae7abd91" Timestamp="0" />
<RuleBuilderItem Name="John Thomas" IsDefault="False" UniqueIdentifier="48876e2e-899e-4d1a-be00-eeadc744c481" Timestamp="0" />
</RuleBuilderOperation>
</RuleBuilderRule>
</RuleBuilderModule>
</ROOT>
------------------------------------------------------------------
Could anyone please help me out to shred those values properly?
Thanks In Advance
OmPrakash
|
|
|
|
|
I have created the following assembly:
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Server;
namespace ClrBusinessObjects
{
public class Person
{
[SqlProcedure]
public static string GetPersonName()
{
return "John Smith";
}
}
}
-------------------------------------------
I have attached the assembly in SQL Server as follows:
CREATE ASSEMBLY
ClrBusinessObjects
FROM 'C:\DLL_TEST\ClrBusinessObjects.dll'
WITH PERMISSION_SET = UNSAFE;
GO
--------------------------------------------
I have tried to associate the assembly to a stored proc as follows:
CREATE PROCEDURE TestSqlClrExample
AS EXTERNAL NAME ClrBusinessObjects.Person.GetPersonName
but the SQL Server compiler returns:
Msg 6505, Level 16, State 1, Procedure TestSqlClrExample, Line 1
Could not find Type 'Person' in assembly 'ClrBusinessObjects'.
--------------------------------------------------------------------
What am I doing wrong?
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
