Database

16-Feb-09 23:40

Merci Ashfield.
I used your solution :

SELECT     ID_STUDENT, CASE WHEN (CODE) = 1 THEN 'abs' ELSE CONVERT(varchar, [121]) END AS note1,<br />
 CASE WHEN (CODE) = 1 THEN 'ASD' ELSE CONVERT(varchar,<br />
[122]) END AS note3,<br />
 CASE WHEN (CODE) = 1 THEN 'ASD' ELSE CONVERT(varchar, [123])  END AS note3<br />
<br />
FROM         (SELECT     ID_FIELD, ID_STUDENT, NOTE, CODE<br />
                       FROM          EXAMEN) p PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt<br />
<br />
<br />

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

foryou17-Feb-09 1:02

17-Feb-09 1:02

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!

Wendelius17-Feb-09 7:57

17-Feb-09 7:57

I don't if I follow the logic but if you drop away the code in pivot like this:

SELECT *
FROM (SELECT ID_FIELD, 
             ID_STUDENT, 
             NOTE
      FROM EXAMEN) p 
      PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt

you will get a result as wanted where note is 0 for the rows where code is 1 (is this always true?):

ID_STUDENT  121  122  123
----------  ---  ---  ---
9306        0    4    12
9307        3    0    9
9308        12   4    9

Now if you replace the zeros like:

SELECT ID_STUDENT, 
       CASE WHEN ([121]) = 0 THEN 'abs' ELSE CONVERT(varchar, [121]) END AS note1,
       CASE WHEN ([122]) = 0 THEN 'ASD' ELSE CONVERT(varchar, [122]) END AS note2,
       CASE WHEN ([123]) = 0 THEN 'ASD' ELSE CONVERT(varchar, [123]) END AS note3
FROM (SELECT ID_FIELD, 
             ID_STUDENT, 
             NOTE
      FROM EXAMEN) p 
      PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt

you will get:

ID_STUDENT  note1   note2   note3
----------  -----   -----   -----
9306        abs     4       12
9307        3       ASD     9
9308        12      4       9

Is this getting you any closer to the result?

The need to optimize rises from a bad design.My articles[^]

foryou17-Feb-09 22:47

17-Feb-09 22:47

Thank you very much Mika Wendelius you helped me a lot in this site Smile | :)

.
for me code = 1 means that the student is abcent so his note= 0 and I have to replace 0 by 'abc' to differentiate between a student absent and another who is present and received 0.why I used code code_absence.

I do not know if I come to explain my problem.
Thnaks.

Wendelius18-Feb-09 7:37

18-Feb-09 7:37

It didn't come out very pretty Smile | :)

but could you use this for somekind of starting point. The idea is that the first query gets the records where the student is absent and the second gets those where the student isn't absent. then these are joined and columns are combined:

SELECT coalesce(a.ID_STUDENT, b.ID_STUDENT) as id_student,
		coalesce(a.note1, b.note1) as note1,
		coalesce(a.note2, b.note2) as note2,
		coalesce(a.note3, b.note3) as note3
FROM 
(SELECT ID_STUDENT, 
        CASE WHEN ([121]) = 0 THEN 'Abs' ELSE CONVERT(varchar, [121]) END AS note1,       
        CASE WHEN ([122]) = 0 THEN 'Abs' ELSE CONVERT(varchar, [122]) END AS note2,       
        CASE WHEN ([123]) = 0 THEN 'Abs' ELSE CONVERT(varchar, [123]) END AS note3
FROM (SELECT ID_FIELD, ID_STUDENT, NOTE
FROM EXAMEN WHERE code = 1) p 
PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt) a
full outer join 
(SELECT ID_STUDENT, 
       CONVERT(varchar, [121]) AS note1,
       CONVERT(varchar, [122]) AS note2,
       CONVERT(varchar, [123]) AS note3
FROM (SELECT ID_FIELD, ID_STUDENT, NOTE
FROM EXAMEN WHERE code = 0) p 
PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt) b
on a.ID_STUDENT = b.ID_STUDENT

The result is:

id_student  note1  note2  note3
----------  -----  -----  -----
9306        Abs    4      12
9307        3      Abs    9
9308        12     4      9

The need to optimize rises from a bad design.My articles[^]

foryou19-Feb-09 1:55

19-Feb-09 1:55

Hi.
Thank you a lot ,merci beaucoup Mika Wendelius it works Smile | :)

Wendelius19-Feb-09 5:53

Formulas in SQL Server Reporting Services

19-Feb-09 5:53

You're welcome Smile | :)

The need to optimize rises from a bad design.My articles[^]

dboy22116-Feb-09 11:04

dboy221

16-Feb-09 11:04

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.

Re: Formulas in SQL Server Reporting Services

Diving Flo17-Feb-09 23:44

Diving Flo

17-Feb-09 23:44

Just some hints as I don't have the chance to test it. Try the following:

=IIF(Format(Fields!seStartDate.Value, "MM/d/yyyy") = '1/1/1900', '', Format(Fields!seStartDate.Value, "MM/d/yyyy")

May be you want to try this as well:

=IIF(Fields!seStartDate.Value.ToString() = '1/1/1900', '', Format(Fields!seStartDate.Value, "MM/d/yyyy")

I am not sure whether you should us the double quotes (") within the format function or the single quotes for = '1/1/1900'.

Good luck,
Florian

In search of a better solution...

Jörgen Sigvardsson16-Feb-09 6:50

Re: In search of a better solution... [modified]

16-Feb-09 6:50

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 like

SELECT 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

Wendelius16-Feb-09 7:04

16-Feb-09 7:04

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.

The need to optimize rises from a bad design.My articles[^]

modified on Monday, February 16, 2009 1:27 PM

Jörgen Sigvardsson16-Feb-09 8:35

16-Feb-09 8:35

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... Frown | :(

Wendelius17-Feb-09 1:26

17-Feb-09 1:26

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

The need to optimize rises from a bad design.My articles[^]

Jörgen Sigvardsson17-Feb-09 1:34

17-Feb-09 1:34

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

Wendelius17-Feb-09 2:10

17-Feb-09 2:10

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.

The need to optimize rises from a bad design.My articles[^]

Jörgen Andersson17-Feb-09 2:53

Jörgen Andersson

17-Feb-09 2:53

This version also works with an empty table, but it still fails if you remove the first slot.

SELECT Coalesce(Min(s1.Slotnumber) + 1, 1) 
FROM slots s1 LEFT JOIN slots s2 
ON s1.Slotnumber = s2.Slotnumber - 1 
WHERE s2.Slotnumber IS NULL
AND s1.Slotnumber<999

Jörgen Sigvardsson17-Feb-09 1:34

17-Feb-09 1:34

I forgot the most important thing: thank you for your effort! Much appreciated!

--
Kein Mitleid Für Die Mehrheit

Wendelius17-Feb-09 2:10

17-Feb-09 2:10

You're welcome Smile | :)

The need to optimize rises from a bad design.My articles[^]

Ennis Ray Lynch, Jr.17-Feb-09 5:15

Ennis Ray Lynch, Jr.

17-Feb-09 5:15

CREATE TABLE slot(
    id int not null,
    slot1 bit,
    slot2 bit,
    slot... bit,
    slotn bit,
   ...
)

J/k. I would use the associate table approach that contains the list of available slots exactly as you have mentioned. Its not fancy, but it is efficient and easy to read.

Need custom software developed? I do C# development and consulting all over the United States.

If you don't ask questions the answers won't stand in your way.

Doing a job is like selecting a mule, you can't choose just the front half xor the back half so when you ask me to do a job don't expect me to do it half-assed.

PIEBALDconsult17-Feb-09 16:15

PIEBALDconsult

17-Feb-09 16:15

How about having a "Reserved" bit to set or clear as the slot is used and freed up?

A third-party product my former employer used did that, there was a table that always contained 8000 rows, and the system used them to hold values until it needed them, then freed them afterward.

Jörgen Sigvardsson17-Feb-09 20:58