 |
 | How to get an answer to your question |  | Chris Maunder | 17:30 10 Nov '05 |
|
|
For those new to message boards please try to follow a few simple rules when posting your question.- Choose the correct forum for your message. Posting a VB.NET question in the C++ forum will end in tears.
- Be specific! Don't ask "can someone send me the code to create an application that does 'X'. Pinpoint exactly what it is you need help with.
- Keep the subject line brief, but descriptive. eg "File Serialization problem"
- Keep the question as brief as possible. If you have to include code, include the smallest snippet of code you can.
- If you are posting source code with your question, place it inside <pre></pre> tags, or click the "Ignore HTML tags in this message" check box.
- Be courteous and DON'T SHOUT. Everyone here helps because they enjoy helping others, not because it's their job.
- Please do not post links to your question in one forum from another, unrelated forum (such as the lounge). It will be deleted.
- Do not be abusive, offensive, inappropriate or harass anyone on the boards. Doing so will get you kicked off and banned. Play nice.
- If you have a school or university assignment, assume that your teacher or lecturer is also reading these forums.
- No advertising or soliciting.
- We reserve the right to move your posts to a more appropriate forum or to delete anything deemed inappropriate or illegal.
cheers,
Chris Maunder
CodeProject.com : C++ MVP
|
|
|
|
|
|
|
i am bit confused about my question only please help me to get the result
i hav 3 tables
table1,table2,table3
table1
uid shiftcode startdate enddate
100 s1 xxxxxx xxxxx
101 s1 xxxxxx xxxxx
102 s1 xxxxxx xxxxx
table2
uid patterncode
103 p1
table3
uid shiftcode patterncode
103 s2 p1
i want shiftcode's of 100 to 103 users
how to get
plzz help me....
icanmakeiteasy
|
|
|
|
|
|
|
I think your question is lacking some information. Could you explain the logic of combining those rows together. Is it for example that when table1 has shiftcode s1 you want shiftcode s2 from table3 or is the idea to go through table2 and join patterncode, but then again how does pattern code relate to shiftcode in table1 etc.
|
|
|
|
|
|
|
thx for reply...
table1's uid >> primary key
that is refernced to table2 and table3
i.e table1 is parent table
table2 and table3 are child tables of table1
table2's patterncode >> primary key
that is refernced to table3
user 103 is on bench so his shift code is s2 or any other
if user got work for some days simple he moves to table1 with his working days and his work code(s1,s2,s3..etc)
just i want work code(shiftcode) of 100 to 103 users between given dates
icanmakeiteasy
|
|
|
|
|
|
|
it is very simple use joins and co related sub queries
|
|
|
|
|
|
|
i am little poor in joins and sub queries
can u plzz give me some idea
icanmakeiteasy
|
|
|
|
|
 | CASE when false query [modified] |  | foryou | 2hrs 34mins ago |
|
|
Merci Ashfield.
I used your solution :
SELECT ID_STUDENT, CASE WHEN (CODE) = 1 THEN 'abs' ELSE CONVERT(varchar, [121]) END AS note1,
CASE WHEN (CODE) = 1 THEN 'ASD' ELSE CONVERT(varchar,
[122]) END AS note3,
CASE WHEN (CODE) = 1 THEN 'ASD' ELSE CONVERT(varchar, [123]) END AS note3
FROM (SELECT ID_FIELD, ID_STUDENT, NOTE, CODE
FROM EXAMEN) p PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt
I have not the problem of data type but I have the data displayed by double
Here are my data TABLE examen
Id_student---- id_feild ---- note----code
9306-------------121 ----------- 0-------1
9306-------------122---- -------4-------0
9306------------123----------- 12----- 0
9307------------121 ------------3------ 0
9307------------122------------ 0------ 1
9307------------123 ------------9------ 0
9308------------121------------ 12------0
9308------------122------------ 4------ 0
9308------------123------------ 9------0
following the requet résultatt
9306----NULL----NULL----NULL
9307----NULL----NULL----NULL
9308----NULL----NULL-----NULL
9306----NULL----4-------12
9307-----3------NULL----9
9308-----12-----4------9
9306-----ASD----ASD-----ASD
9307-----ASD----ASD-----ASD
and this is what I want
id_student---note1----note2---note3
9306---------ASD-------4---------12
9307---------3-------ASD -------9
9308--------12------4---- ------9
I dont know why please help me.
Thanks
modified on Tuesday, February 17, 2009 6:32 AM
|
|
|
|
|
|
|
Hi!
I think I have this result because we must specify
that the ID_FIELD= 121 here:CASE WHEN (CODE) = 1 THEN 'abs' ELSE CONVERT(varchar, [121]) END AS note1,
and ID_FIELD=122 here:CASE WHEN (CODE) = 1 THEN 'abs' ELSE CONVERT(varchar, [122]) END AS note2
how ? the case when accepts two conditions?
thanks!
|
|
|
|
|
 | Formulas in SQL Server Reporting Services | | dboy221 | 15hrs 10mins ago |
|
|
Not sure if this is the location where I should post such a question (if not please direct me to the proper forum), but I am designing a report in VS2005 and need to have a date field display blank or an empty string if the date is 1/1/1900. I have written the IIf statement as such:
=IIF(Fields!seStartDate.Value = '1/1/1900', '', Format(Fields!seStartDate.Value, "MM/d/yyyy")
, however I am getting a syntax error. Please advise as to how this should be written...thanks in advance to all that reply.
|
|
|
|
|
|
|
Suppose we have a table like this:
CREATE TABLE Slots (
SlotNumber INT NOT NULL,
...,
PRIMARY KEY(SlotNumber)
); In this table, a maximum of X slots can be inserted. Given a table with Y rows in it, what is the next available slot?
One solution is to keep a second table with all the slots in it:CREATE TABLE AllSlots (
SlotNumber INT NOT NULL,
PRIMARY KEY(SlotNumber)
); Then I could do something likeSELECT SlotNumber
FROM AllSlots
WHERE SlotNumber NOT IN (SELECT SlotNumber FROM Slots)
Does anybody have a better solution? Note that I have only ANSI SQL in my arsenal (no fancy TSQL/PL1/Whatever constructs are at my disposal)
--
Kein Mitleid Für Die Mehrheit
|
|
|
|
|
|
|
Just wondering, why don't you simply take MAX(SlotNumber) + 1 (if you used that, I wouldn't recommend to use the SlotNumber as primary key, but as a unique key and define a different primary key).
If the MAX + 1 is less than maximum amount of slots, you still have a free slot.
If the slots are not filled in order, I think your solution is fine. Just when querying, you should take top 1 row if you need only one empty slot.
modified on Monday, February 16, 2009 1:27 PM
|
|
|
|
|
|
|
I forgot to mention that there's an upper and lower limit of the slots. The limits may be 1 and 999 for example. Essentially, the query would be "pick any number between 1 and 999, that isn't found in the table".
For small ranges such as [1, 999], it's probably easiest to select everything and order by the primary key (a no op, since the primary key index is sorted already), and then scan for the next pair of adjacent slots X and Y where X + 1 != Y. That means that X + 1 must be available. This is however not feasible if the slot span is large-ish, because it'll degrade to a table scan... 
|
|
|
|
|
|
|
Don't know if you meant this but if you need single slots, could you use the same table and find rows that have gaps between a row and next row in order. Something like
select ...
from slots s1
where not exists (select 1
from slots s2
where s2.slotnumber = s1.slotnumber + 1)
This wouldn't lead to table scan since it's fully an index operation. However the first slot cannot be found using this so you would have to find it differently
|
|
|
|
|
|
|
I (well - my colleague to be honest) found a similar solution:SELECT Slot + 1 AS NextSlot
FROM Slots
WHERE NextSlot NOT IN (SELECT Slot FROM Slots) Here I'll have to distinguish between no slots and all slots unavailable, because these two edge cases will produce an empty result set.
--
Kein Mitleid Für Die Mehrheit
|
|
|
|
|
|
|
That would work also. However there's a slight difference between the variations. I believe your query will perform fine if the dbms is capable of doing and predicting index joins. If it's not, it can lead to table scan to create the intermediate result set from slots.
On the other hand the exists structure may perform well if the execution can be stopped immediately when the first row is found meaning that not exists will never be true (most of dbms's can do this).
So what I'm saying is that I think you should test both variations and get the execution plan to see which one (or perhaps some derived solution) will lead to best result.
|
|
|
|
|
|
|
I forgot the most important thing: thank you for your effort! Much appreciated!
--
Kein Mitleid Für Die Mehrheit
|
|
|
|
|
|
|
You're welcome 
|
|
|
|
|
|
|
I have a table, which has two rows
1.EmailID
2.EmailAddress
I have Duplicate Email Addresses and i want to Delete Duplicate Email Addresses from my table except the max(emailId)
and I have millions of Duplicate EmailAddresses in my table
Plz ans me as soon as possible....
Thanks!
|
|
|
|
|
|
|
Don't double post. It's considered rude.
|
|
|
|
|
|
|
I have been Double posted in last two days but I do not recieve a single ans to help out from this satuation..........yyyyyyyyyyyyy
|
|
|
|
|
|
|
You mean you have been waiting 2 days when it takes 5 secondes to do a search. There are pleanty of answers here clickety[^]
Learn to use the tools you have available instead of whining that no one is helping you.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
|
I use every link . but there is no link to be fruite full
U know
|
|
|
|
|
|
|
if you can't take Mika's reply and apply it to your requirement then you need to do some thinking about what you are doing - he supplied a solution days ago. I give up, some people are just too lazy for words.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
|
U know i find the solution . thanku for your Stupid answers
you are only time wasted...............ok
|
|
|
|
|
|
|
|
News 
Joke 
Rant