Query setup

[djj55]

SQL 2008 R2
Hello,

I would like to loop through ever column in my table and find when the last time it was work.
I have four columns that have a date of work so an example table would be:

CREATE TABLE MyTable (
   WorkType1 DATETIME,
   WorkType2 DATETIME,
   WorkType3 DATETIME,
   WorkType4 DATETIME,
   MyColumn1 VARCHAR(10),
   MyColumn2 money)

INSERT INTO MyTable (WorkType1, WorkType2, WorkType3, WorkType4, MyColumn1, MyColumn2)
VALUES 
('10/10/10', '9/10/10',  '8/10/10', '7/10/10',  'Hello',  NULL), 
('4/10/10',  '9/10/10',  '8/10/10', '7/10/10',  'World',  30.20), 
('3/10/10',  '9/10/10',  '8/10/10', '7/10/10',  'GoodBy', 5.50), 
('2/10/10',  '9/10/10',  '8/10/10', '7/10/10',  'Hello',  NULL), 
('10/10/10', '10/12/10', '8/10/10', '7/10/10',  'Hello',  7.25), 
('10/10/10', '9/10/10', '10/13/10', '7/10/10',  'Hello',  90.00), 
('10/10/10', '9/10/10',  '8/10/10', '7/10/10',  'Hello',  NULL), 
('10/10/10', '9/10/10',  '8/10/10', '10/14/10',  NULL,    10.00)

Note that I actually have several columns.

What I want is something like:

SELECT MAX(WorkType1), MAX(WorkType2), MAX(WorkType3), MAX(WorkType4), MAX(MyColumn1) 
FROM MyTable 
WHERE MyColumn1 IS NOT NULL
SELECT MAX(WorkType1), MAX(WorkType2), MAX(WorkType3), MAX(WorkType4), MAX(MyColumn2) 
FROM MyTable 
WHERE MyColumn2 IS NOT NULL

However I would like to only show the maximum date of the four to get:
MyColumn1 2010-10-13 World
MyColumn2 2010-10-14 90.00

I do know how to get the list of columns.

Thanks for any pointers,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

There is going to be several ways to accomplish this. My first reaction (upon reading this question) was, why isn't the database normalized. You really shouldn't similar columns in a table. If the data was properly normalized, this query would be a lot simpler. So I thought, let's make it look normalized and then write the query.

Select	ColumnName, Max(WorkDate), Max(DataColumn)
From    (
        Select 'MyColumn1' As ColumnName, WorkType1 As WorkDate, Convert(VarChar(20), MyColumn1) As DataColumn
        From   MyTable
        WHERE MyColumn1 IS NOT NULL

        UNION ALL

        Select 'MyColumn1',WorkType2, Convert(VarChar(20), MyColumn1)
        From   MyTable
        WHERE MyColumn1 IS NOT NULL

        UNION ALL

        Select 'MyColumn1',WorkType3, Convert(VarChar(20), MyColumn1)
        From   MyTable
        WHERE MyColumn1 IS NOT NULL

        UNION ALL

        Select 'MyColumn1',WorkType4, Convert(VarChar(20), MyColumn1)
        From   MyTable
        WHERE MyColumn1 IS NOT NULL

        UNION ALL

        Select 'MyColumn2', WorkType1, Convert(VarChar(20), MyColumn2)
        From   MyTable
        Where  MyColumn2 Is Not NULL

        UNION ALL

        Select 'MyColumn2', WorkType2, Convert(VarChar(20), MyColumn2)
        From   MyTable
        Where  MyColumn2 Is Not NULL

        UNION ALL

        Select 'MyColumn2', WorkType3, Convert(VarChar(20), MyColumn2)
        From   MyTable
        Where  MyColumn2 Is Not NULL

        UNION ALL

        Select 'MyColumn2', WorkType4, Convert(VarChar(20), MyColumn2)
        From   MyTable
        Where  MyColumn2 Is Not NULL
        ) As A
Group By ColumnName

Then I got to thinking, if you really are stuck with this structure, the normalize & aggregate method may not be the most efficient. You may be better off writing a UDF to return the max, like this:

Create Function dbo.MaxOf4Dates(@Date1 DateTime, @Date2 DateTime, @Date3 DateTime, @Date4 DateTime)
Returns DateTime
AS
Begin
  Return(
    Select Max(TheDate)
    From   (
           Select @Date1 As TheDate 
           Union All 
           Select @Date2 
           Union All 
           Select @Date3 
           Union All 
           Select @Date4) As A	
         )
End

Then your queries would be:

SELECT dbo.MaxOf4Dates(MAX(WorkType1), MAX(WorkType2), MAX(WorkType3), MAX(WorkType4)), MAX(MyColumn1) 
FROM MyTable 
WHERE MyColumn1 IS NOT NULL

SELECT dbo.MaxOf4Dates(MAX(WorkType1), MAX(WorkType2), MAX(WorkType3), MAX(WorkType4)), MAX(MyColumn2) 
FROM MyTable 
WHERE MyColumn2 IS NOT NULL

There are also ways to accomplish this using the unpivot operator in SQL. I'm not too familiar with unpivot, so I will leave it up to you to research that method.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Is it what you wanted?

select MyColumn1, myColumn2, max(WorkType) from

MyTable UNPIVOT (WorkType for ColType in ([WorkType1],[WorkType2],[WorkType3],[WorkType4])) unpvt
group by MyColumn1, MyColumn2

PluralSight Learning Library

 

[markros]

Actually, I see an extra requirement now for MyColumn1 is not null, so,

select MyColumn1, myColumn2, max(WorkType) from

MyTable UNPIVOT (WorkType for ColType in ([WorkType1],[WorkType2],[WorkType3],[WorkType4])) unpvt
where myColumn1 IS NOT NULL
group by MyColumn1, MyColumn2

As you see, in SQL 2005 and up using UNPIVOT operator makes this query to be quite simple.

PluralSight Learning Library

 

[djj55]

George - Old database table one step away from flat.
George, Markros - Thank you for the information, I have a fire to put out right now (old Access database problem) so I will get to these in a little while.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

As you see, in SQL 2005 and up using UNPIVOT operator makes this query to be quite simple.

If it were truly simple, then quickly writing a query would probably return the correct results, right?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Right. I tried it quickly without NULL restriction and it worked fine. I haven't re-tried with adding NULL restriction.

PluralSight Learning Library

 

[djj55]

I am going to try the UNPIVOT method for now, however the udf may come into play later.

This whole exercise is to help me thin down the table for a complete rework of the database. I have been working on this for three years and now that we have developers writing .NET code to replace the Access databases, things are starting to take shape. Still arguing with the developers about some normalization but getting there.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!