code in sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

code in sql

Author

Topic

Vithi
Starting Member

19 Posts

Posted - 2008-12-18 : 08:58:54

Table_1

DOCID ID
12345 99999
12345 99998
12345 99997
12345 99996
23456 99995
23456 99994
23456 99993
34567 99992
34567 99991
45678 99990
56789 88889
56789 88884
56789 88883
56789 88882
56789 88881

Table_2
ID Value Date (yyyy/m/d)
99999 x 2008/01/05
99998 x 2008/01/09
99997 y 2008/01/10
99996 t 2008/05/03
99995 y 2008/05/06
99994 m 2006/04/06
99993 d 2006/04/23
99992 x 2006/11/22
99991 m 2006/11/23
99990 m 2007/05/02
88889 m 2008/09/25
88884 y 2008/09/29
88883 y 2008/08/01
88882 x 2008/01/06
88881 m 2008/09/27

Should look like this:
DOCID Value X Latest Date Value Y Latest Date Value M Latest Date
12345 2008/01/09 2008/01/10 -
23456 - 2008/05/06 2006/04/06
34567 2006/11/22 - 2006/11/23
45678 - - 2007/05/02
56789 2008/01/06 2008/09/29 2008/09/27

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 09:20:36

Subreports? All you need is a crosstab/pivot report.

SELECT		t1.DOCID,
		MAX(CASE WHEN t2.Value = 'X' THEN t2.Date ELSE NULL END) AS [Val X Latest Date],
		MAX(CASE WHEN t2.Value = 'Y' THEN t2.Date ELSE NULL END) AS [Val Y Latest Date],
		MAX(CASE WHEN t2.Value = 'M' THEN t2.Date ELSE NULL END) AS [Val M Latest Date]
FROM		Table_1 AS t1
INNER JOIN	Table_2 AS t2 ON t2.ID = t1.ID
GROUP BY	t1.DOCID
ORDER BY	t1.DOCID

E 12°55'05.63"
N 56°04'39.26"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-12-18 : 09:37:19

moved from script library

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 09:53:35

[code]SELECT t1.DOCID,
MAX(CASE WHEN t2.Value = 'X' THEN t2.Date ELSE NULL END) AS [Val X Latest Date],
MAX(CASE WHEN t2.Value = 'Y' THEN t2.Date ELSE NULL END) AS [Val Y Latest Date],
MAX(CASE WHEN t2.Value = 'M' THEN t2.Date ELSE NULL END) AS [Val M Latest Date]
FROM Table_1 AS t1
INNER JOIN Table_2 AS t2 ON t2.ID = t1.ID
WHERE t2.Value IN ('X', 'Y', 'M')
GROUP BY t1.DOCID
ORDER BY t1.DOCID[/code]

E 12°55'05.63"
N 56°04'39.26"

Vithi
Starting Member

19 Posts

Posted - 2008-12-18 : 09:57:31

LATEST MAX FUNCTIONALITY STILL DOES NOT WORK :(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 09:59:57

[code]SELECT t1.DOCID,
MAX(CASE WHEN t2.Value = 'X' THEN t2.Date ELSE NULL END) AS [Val X Latest Date],
MAX(CASE WHEN t2.Value = 'Y' THEN t2.Date ELSE NULL END) AS [Val Y Latest Date],
MAX(CASE WHEN t2.Value = 'Z' THEN t2.Date ELSE NULL END) AS [Val Z Latest Date]
FROM Table_1 AS t1
INNER JOIN Table_2 AS t2 ON t2.ID = t1.ID
WHERE t2.Value IN ('X', 'Y', 'Z')
GROUP BY t1.DOCID
ORDER BY t1.DOCID[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 10:27:02

The your dates are NOT dates. I believe you have them stored as varchar, am I right?

SELECT		t1.DOCID,
		MAX(CASE WHEN t2.Value = 'X' THEN CAST(t2.Date AS DATETIME) ELSE NULL END) AS [Val X Latest Date],
		MAX(CASE WHEN t2.Value = 'Y' THEN CAST(t2.Date AS DATETIME) ELSE NULL END) AS [Val Y Latest Date],
		MAX(CASE WHEN t2.Value = 'Z' THEN CAST(t2.Date AS DATETIME) ELSE NULL END) AS [Val Z Latest Date]
FROM		Table_1 AS t1
INNER JOIN	Table_2 AS t2 ON t2.ID = t1.ID
WHERE		t2.Value IN ('X', 'Y', 'Z')
GROUP BY	t1.DOCID
ORDER BY	t1.DOCID

E 12°55'05.63"
N 56°04'39.26"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-18 : 10:27:09

Peso's query should work for you. Do you need M latest date as well? Then you need to add to crosstab result and put like this in Where clause:
WHERE t2.Value IN ('X', 'Y', 'Z','M')

Vithi
Starting Member

19 Posts

Posted - 2008-12-18 : 10:32:51

no. just x, y and m. it is stored as 'date'.

I tried it in diff table and it seems to work so far. i am guessing that if users entered the data themselves, it does not work. it only works for the system generated data. I have to resolve that problem. IT WORKS!!!!!! :)

Thank you guys so much. I've been at this for a while now. I can do simple sql. I should start doing some exercise on this.

THank you thank you thank you.

You guys have been a GREAT HELP :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 11:04:13

quote:
Originally posted by Vithi

no. just x, y and m. it is stored as 'date'.

Which RDBMS are you using? Date datatype is available in SQL Server 2008 and on.
And now you want x, y and m only, again as in original post?

Before you wanted

quote:
Originally posted by Vithi

MY MISTAKE. BUT STILL THE LATEST X OR Y OR Z DOES NOT COME UP WHEN IT HAS MORE THAN 1 VALUE. I HAVE 12/16/2008 AND 12/11/2008 FOR M, AND 12/11/2008 COMES UP.

Btw, there is no need for shouting.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 11:14:46

quote:
Originally posted by Vithi

MY MISTAKE AGAIN. X, Y, M NOT Z.

SORRY IF THERE WERE ANY RUDE SHOUTING...I DIDN'T MEANT TO. ONLY HAPPY SHOUTING..I'LL RESTRAIN MYSELF FROM THAT TOO. :)

Keep your pants on

E 12°55'05.63"
N 56°04'39.26"

Vithi
Starting Member

19 Posts

Posted - 2008-12-19 : 09:39:16

Yes.

Vithi
Starting Member

19 Posts

Posted - 2008-12-23 : 10:34:14

Table_1

DOCID ID
12345 99999
12345 99998
12345 99997
12345 99996
23456 99995
23456 99994
23456 99993
34567 99992
34567 99991
45678 99990
56789 88889
56789 88884
56789 88883
56789 88882
56789 88881

Table_2
ID Value Date (yyyy/m/d) Updated
99999 x 2008/01/05 Y
99998 x 2008/01/09 N
99997 y 2008/01/10
99996 t 2008/05/03
99995 y 2008/05/06
99994 m 2006/04/06
99993 d 2006/04/23
99992 x 2006/11/22 N
99991 m 2006/11/23
99990 m 2007/05/02
88889 m 2008/09/25
88884 y 2008/09/29
88883 y 2008/08/01
88882 x 2008/01/06 Y
88881 m 2008/09/27

Should look like this:
DOCID Val X Latest Date Value Y Latest Date Value M Latest Date
12345 2008/01/05 2008/01/10 -
23456 - 2008/05/06 2006/04/06
34567 - - 2006/11/23
45678 - - 2007/05/02
56789 2008/01/06 2008/09/29 2008/09/27

Another condition needs to be included, so that only the maximum date of x should come up and it the 'Updated' in Table_2 must be 'y'.


SELECT		t1.DOCID,
		MAX(CASE WHEN (t2.Value = 'X' and t2.updated = 'y') THEN t2.Date ELSE NULL END) AS [Val X Latest Date],
		MAX(CASE WHEN t2.Value = 'Y' THEN t2.Date ELSE NULL END) AS [Val Y Latest Date],
		MAX(CASE WHEN t2.Value = 'M' THEN t2.Date ELSE NULL END) AS [Val Z Latest Date]
FROM		Table_1 AS t1
INNER JOIN	Table_2 AS t2 ON t2.ID = t1.ID
WHERE		t2.Value IN ('X', 'Y', 'M')
GROUP BY	t1.DOCID
ORDER BY	t1.DOCID

by altering the code that way, i am receiving the correct values for x when it is max date and the updated value 'y' from table_2.
But i don't want the rest of it to show up with the id. i need to exclude the blank ones. i am not sure if i have to do an if statement. if i were to include it in the code so that t2.updated = 'y', it would be general and the rest of the values will not show up either. i want it to be specific. if it is possible, let me know. thank you.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-23 : 14:23:48

quote:
Originally posted by Vithi

1

Why did you edit/delete original Post? Now you will not get any help from experts over here.

Vithi
Starting Member

19 Posts

Posted - 2008-12-23 : 14:43:09

that was by mistake. my bad.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources