SQL Help

[simran1]

Hi,
I am trying to get this SQL.
I have a table with the following fields
ID, Date, day1, day2 Day3, day4, day5
The values of the fields day1, day2, day3, day4, day5 can be the following: "c", "e", "k", "o"
If i have two records with values
Rec 1 Rec 2
ID 1 2
Date 11/11/2002 11/11/2002
Day1 c k
Day2 e o
Day3 c o
Day4 k o
Day5 o o

I want a sql wiich will give the records as
ID, date, TotalC, TotalE, TotalK, TotalO
REC1 = 1,11/11/2002,2,1,1,1
Rec2 = 2,11/11/2002,0,0,1,4

Any ideas how can i design this SQL? or are there any other alternatives.

 

[CmPaiva]

That's It, but it only workes in access databases, cos it uses iif() construction, not avaiable in SQL Server.

SELECT Id, Date, IIf([Day1]='c',1,0)+IIf([Day2]='c',1,0)+IIf([Day3]='c',1,0)+IIf([Day4]='c',1,0)+IIf([Day5]='c',1,0) AS TotalC, IIf([Day1]='e',1,0)+IIf([Day2]='e',1,0)+IIf([Day3]='e',1,0)+IIf([Day4]='e',1,0)+IIf([Day5]='e',1,0) AS TotalE, IIf([Day1]='k',1,0)+IIf([Day2]='k',1,0)+IIf([Day3]='k',1,0)+IIf([Day4]='k',1,0)+IIf([Day5]='k',1,0) AS TotalK, IIf([Day1]='o',1,0)+IIf([Day2]='o',1,0)+IIf([Day3]='o',1,0)+IIf([Day4]='o',1,0)+IIf([Day5]='o',1,0) AS TotalO
FROM <Yourtable>

Tell if is what you need.
Carlos Paiva

 

[simran1]

hi,
Thank you for the SQl, but i am using SQL server. Are there any other alternatives i can use.

 

[SemperFiDownUnda]

You might have to do something with a TSQL &quot;CASE&quot; statement in your QUERY.....example

SELECT RecID, RecDate, 
       ( SELECT Value = CASE Day1 WHEN 'C' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) +
       ( SELECT Value = CASE Day2 WHEN 'C' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) +
       ( SELECT Value = CASE Day3 WHEN 'C' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) +
       ( SELECT Value = CASE Day4 WHEN 'C' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) +
       ( SELECT Value = CASE Day5 WHEN 'C' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) AS CTotal,
       ( SELECT Value = CASE Day1 WHEN 'E' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) +
       ( SELECT Value = CASE Day2 WHEN 'E' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) +
       ( SELECT Value = CASE Day3 WHEN 'E' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) +
       ( SELECT Value = CASE Day4 WHEN 'E' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) +
       ( SELECT Value = CASE Day5 WHEN 'E' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) AS ETotal, 
       ( SELECT Value = CASE Day1 WHEN 'K' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) +
       ( SELECT Value = CASE Day2 WHEN 'K' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) +
       ( SELECT Value = CASE Day3 WHEN 'K' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) +
       ( SELECT Value = CASE Day4 WHEN 'K' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) +
       ( SELECT Value = CASE Day5 WHEN 'K' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) AS KTotal,
       ( SELECT Value = CASE Day1 WHEN 'O' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) +
       ( SELECT Value = CASE Day2 WHEN 'O' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) +
       ( SELECT Value = CASE Day3 WHEN 'O' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) +
       ( SELECT Value = CASE Day4 WHEN 'O' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) +
       ( SELECT Value = CASE Day5 WHEN 'O' THEN 1 ELSE 0 END FROM #tmpHold WHERE RecID = T1.RecID ) AS OTotal
  FROM #tmpHold T1

Replace #tmpHold with your table name.
and....sorry bad table design if you need this type of functionality. Should be a parent child relationship.