Database

15-Jul-07 18:37

I try this code, and it's work.

select name,date=max(date),
sum(case when type='A' then value else 0 end) as 'A',
sum(case when type='B' then value else 0 end) as 'B',
sum(case when type='C' then value else 0 end) as 'C'
from table1
group by name

But after few days, my database is update with more than one record in the same name, separate by column name 'date',
Regarding I just want to display data depend on last date,
Example my table is below:

name type value date
-----------------------------------------------------
a A 1 07/10/2007
a B 1 07/10/2007
a C 1 07/10/2007
a A 2 07/13/2007
a B 2 07/13/2007
a C 2 07/13/2007

but the result is:

the result is:
name date A B C
-----------------------------------------
a 06/13/2007 3 3 3

I'm expecting like this:
name date A B C
-----------------------------------------
a 06/13/2007 2 2 2

I try to modified the code:
select name,
sum(case when type='A' and date=max(date) then....
but found error

So, any suggestion, thanks a lot.

Paul Conrad15-Jul-07 18:57

15-Jul-07 18:57

Have you tried:

<code>     select name,date,
         sum(case when type='A' then value else 0 end) as 'A',
         sum(case when type='B' then value else 0 end) as 'B',
         sum(case when type='C' then value else 0 end) as 'C'
     from table1 where date=max(date)
     group by name </code>

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

Krish - KP15-Jul-07 19:08

15-Jul-07 19:08

Are your sure and Have you checked is the query wroks.

Regards
KP

Paul Conrad15-Jul-07 19:23

15-Jul-07 19:23

Krish - KP,

This one should do it. I created the same table you had, ran this one and it gives the right results Big Grin | :-D

<code>select name,max(date),
         sum(case when type='A' then value else 0 end) as 'A',
         sum(case when type='B' then value else 0 end) as 'B',
         sum(case when type='C' then value else 0 end) as 'C'
     from table_1 where date=(select max(date) from table_1)
     group by name </code>

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

Krish - KP15-Jul-07 23:25

15-Jul-07 23:25

Now syntax is right.

what about logic. is this what required Smile | :)

Regards
KP

masyarial16-Jul-07 0:00

16-Jul-07 0:00

About logic is OK, I already use convert.
And about your code below:

SELECT a.name, MAX(a.dt) AS [date],
SUM(CASE WHEN type = 'A' THEN valu ELSE 0 END) AS A,
SUM(CASE WHEN type = 'B' THEN valu ELSE 0 END) AS B,
SUM(CASE WHEN type = 'C' THEN valu ELSE 0 END) AS C
FROM TblB a
INNER JOIN (SELECT name, MAX(dt) AS dt FROM tblB GROUP by name) b
ON a.name = b.name and a.dt = b.dt
GROUP BY a.name

this only show each name, with maximum value
not each name, each type with maximum value.

I have been tried to modified inside inner join code but, still don't get.
Pls help mee.....

Krish - KP16-Jul-07 1:36

16-Jul-07 1:36

example given gives max with name. that's why in inner join table (subquery)
i've not used type.

include type also in subquery's group by clause.

Regards
KP

masyarial16-Jul-07 22:03

16-Jul-07 22:03

OK Krish, I done with your code, it's very2 work thanks.
But how I write sql syntax if I want to select another table
(for example tbl2) instead of a.tbl1 INNER JOIN b.tbl1 ???

Thanks a lot
B.regards

masyarial15-Jul-07 20:31

15-Jul-07 20:31

Hi Jonh, I have error on your code
Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

I try to modified the code below:
select name,date,
sum(case when type='A' then value else 0 end) as 'A',
sum(case when type='B' then value else 0 end) as 'B',
sum(case when type='C' then value else 0 end) as 'C'
from table1 where date=(select max(date) from table1)
group by name

it's work but, this code will only show maximum date on whole table,
not show each name which have maximum date.

Need your suggestion. thanks

Krish - KP15-Jul-07 19:09

15-Jul-07 19:09

SELECT a.name, MAX(a.dt) AS [date], <br />
    SUM(CASE WHEN type = 'A' THEN valu ELSE 0 END) AS A, <br />
    SUM(CASE WHEN type = 'B' THEN valu ELSE 0 END) AS B, <br />
    SUM(CASE WHEN type = 'C' THEN valu ELSE 0 END) AS C<br />
FROM TblB a <br />
   INNER JOIN (SELECT name, MAX(dt) AS dt FROM  tblB GROUP by name) b <br />
                                 ON a.name = b.name and a.dt = b.dt<br />
GROUP BY a.name

Regards
KP

Paul Conrad15-Jul-07 19:17

15-Jul-07 19:17

Does that one do what you need it to?

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

masyarial15-Jul-07 20:55

15-Jul-07 20:55

Yes, you all great !!!

Thanks a lot.

B.regards

masyarial15-Jul-07 22:15

15-Jul-07 22:15

Hi All, sorry to bothering you again, need your help.
my format date is complete like "7/12/2007 10:40:49 AM"
so, concern that time/date is different each row, example (i write date as integer)

name type value date
-----------------------------------------------
a A 1 1
a B 1 2
a C 1 3
a A 2 4
a B 2 5
a C 2 6

when I run the code the result is:
name date A B C
----------------------------------------
a 6 0 0 2

so, the code show value each name with maximum date,
not show value each name, each type with maximum date.

so, i very confuse, realy need your help, thanks

SQL SERVER

magedhv15-Jul-07 0:27

magedhv

15-Jul-07 0:27

how do i make a backup plan that can backup the database to another location automaticly

Re: SQL SERVER

Paul Conrad15-Jul-07 5:14