MAX and DATEDIFF: group

Showing posts with label group. Show all posts

Monday, March 26, 2012

MCQ Questions

Hi,
I an new to this group. I think this group will be helpful to me. I
have to appear in the test of database. can any one help me by
providing the Multiple chocie question with answer about the databases
or sql server.Have a look at the exam preparation sites - they each have their own sets of
fiarly-comprehensive multiple choice questions:
http://www.transcender.com
http://www.selftestsoftware.com/
http://www.measureup.com/
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

MCQ Questions

Hi,
I an new to this group. I think this group will be helpful to me. I
have to appear in the test of database. can any one help me by
providing the Multiple chocie question with answer about the databases
or sql server.
Have a look at the exam preparation sites - they each have their own sets of
fiarly-comprehensive multiple choice questions:
http://www.transcender.com
http://www.selftestsoftware.com/
http://www.measureup.com/
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
sql

Wednesday, March 7, 2012

Maximum Date in a group of records

I have a table that I need to find the maximum date per Doc_No per Parent_Doccategory. I can get the maximum date per Doc_No but I can't get the information for the next level. The following script is getting the maximum date per airplane but I should get a maximum date each time the parent_doccategory changes. Can anyone help? This is the script so far:

CREATE PROCEDURE [dbo].[sp_Maximum_Change_Date] AS

select Effective_Updated , parent_doc_no, parent_doccategory from gdb_01_4.dbo.aircraft_changes As S
where effective_updated = (select max(effective_updated)
from gdb_01_4_test.dbo.aircraft_changes where
parent_doc_no = S.parent_doc_no) group by parent_doc_no,parent_doccategory, effective_updated, order by parent_doc_no
GO

PARENT_DOC_NOPARENT_DOCCATEGORYEFFECTIVE_UPDATED
129 AC 3/24/2004 1:54:29 PM
129 AC 4/2/2004 1:44:39 PM
129 AE 6/24/2004 8:49:13 AM
129 AU 9/28/2004 12:16:30 PM

I want to get the one record for AC with the 4/2 date, the one for AE and the one for AU. My script is only returning the AU record because it has the maximum date for Parent_Doc_No 129.

Quote:

Originally Posted by crackerbox

PARENT_DOC_NOPARENT_DOCCATEGORYEFFECTIVE_UPDATED
129 AC 3/24/2004 1:54:29 PM
129 AC 4/2/2004 1:44:39 PM
129 AE 6/24/2004 8:49:13 AM
129 AU 9/28/2004 12:16:30 PM

I want to get the one record for AC with the 4/2 date, the one for AE and the one for AU. My script is only returning the AU record because it has the maximum date for Parent_Doc_No 129.

Try adding another argument in the first where clause saying which one you want like example add

and parent_doccategory = 'AE'

before the group by clause|||

Quote:

Originally Posted by Taftheman

Try adding another argument in the first where clause saying which one you want like example add

and parent_doccategory = 'AE'

before the group by clause

That will work but then it eliminates the other two lines that I also need. I basinally need two group levels, one for the Parent_doc_No and the other for the Parent_DocCategory so that it first looks at the Parent_Doc_No and then at the Parent_Doc_Category and gets the most up to date for each category.

Maximum Date in a group of records

hi all,
i got is table:
Id StartDate EndDate
a 19/03/2001 18/03/2002
a 19/03/2002 18/04/2002*
b 13/08/2000 12/08/2001
b 13/08/2001 12/08/2002
b 13/08/2002 10/07/2002*

Sort command and groupins i am ok but i need to select only the records that
has the latest enddate. (See *)
any ideas? thanks in advance
rashidThis query returns row(s) with the latest Enddate for each ID but the row
for ID='B' isn't the one you highlighted.

SELECT id, startdate, enddate
FROM SomeTable AS S
WHERE enddate =
(SELECT MAX(enddate)
FROM SomeTable
WHERE id = S.id)

Hope this helps.

--
David Portas
SQL Server MVP
--

Monday, February 20, 2012

Max value of table column

hello,

i have a table with different columns

my table has 1 group (group per hour)

my last column of my table is a calculation of different values from that line.

in my footer of my column i want to display the max value of that column.

somehow i can't manage to achieve this.

this i an example of my table (how i want i to look like)

hour | ColA | ColB | ColC | ColD

00 15 14 16 16

03 20 21 23 23

....

23 55 16 75 75

90 51 114 75

ColD is the max value of ColA, ColB and ColC.

The footer contains the sumed values for A, B and C

In the footer for ColD i would like to display the max value of ColD (in this case 75).

This may be a stupid question, but i'm stuck on it ..

Vinnie

Here the query,

Code Snippet

Create Table #data (

[hour] Varchar(100) ,

[ColA] int ,

[ColB] int ,

[ColC] int ,

[ColD] int

);

Insert Into #data Values('00','15','14','16',NULL);

Insert Into #data Values('03','20','21','23',NULL);

Insert Into #data Values('23','55','16','75',NULL);

Select

hour,

max(case when colid=1 Then case when hour is not null then data1 else data2 end end) as [cola],

max(case when colid=2 Then case when hour is not null then data1 else data2 end end) as [colb],

max(case when colid=3 Then case when hour is not null then data1 else data2 end end) as [colc],

max(case when colid=4 Then data1 end) as [cold]

from

(

select

hour,

Isnull(id,4) colid,

Max(Case When id=1 Then cola

When id=2 Then colb

When id=3 Then colc End) data1,

Sum(Case When id=1 Then cola

When id=2 Then colb

When id=3 Then colc End) data2

from

#data

cross join

(select 1 id Union Select 2 Union Select 3) as d

Group By

hour,id

With Cube

) as Data

Group by

hour

Order By

isnull(hour,99999)

|||

Hi,

my problem is with a table in reporting services.

my data already comes from a dataset.

so my question is how to retreive the max value of a table column.

also, i work with reporting models and not with query based datasets.

but that has nothing to do with my question.

Greetings

Vinnie

|||

How much of this table have you implemented successfully? Everything but the last value at the bottom right?

Are you using report builder or Business Intelligence Studio?

|||

Hello Greg,

Thanks for the answer.

I am using visual studio to build my reports.

The table itself works fine. The only thing i cannot accomplish is to

have the max value of that specific table column in my footer.

Is what i'm asking impossible to do or am i that stupid that it's right under

my nose and cannot figure it out ?

any help is welcome.

Greetings

Vinnie

|||

VSempoux wrote:

Hello Greg,

Is what i'm asking impossible to do or am i that stupid that it's right under

my nose and cannot figure it out ?

Neither.

VSempoux wrote:

also, i work with reporting models and not with query based datasets

I don't understand this. So there is no query to generate your report? You said you weren't using report builder, so how could you be using reporting models?

We have to understand how you are going about this before we can help you. The only way I know of how to get that max value is to write it into a query....

|||

Okay Greg,

Let me clarify my situation.

I am writing reports for an application that is in dev for the moment.

We want to give our customers the opportunity to also create their own report, based on the data that

we use in our standard reports that are supplied with the app.

So, in order to don't have to do the work twice, i am making report models that i use in my reports.

With VS or BIS you can choose your original datasource, so in my case it is a report model.

My orignal report models are based on queries that i have put into views.

So in my report model data source view i select all the data from the sql view and from that i generate

my model. Afterwards i add some calculated fields in the model itself, if i have the need for them.

Then in the report itself i define datasets.

But for my datasets i can only do drag and drop the fields (enteties) i need in my report.

Of course i can define filters for the datasource.

Does this give you a better insight into my situation ?

If it would make your life easier, i could sent you some screenshots ...

Greetings

Vinnie

|||

VSempoux wrote:

My orignal report models are based on queries that i have put into views.

Then I suppose the answer is you need to create a field in the model with a query that selects the max of the dataset that produces ColA, ColB and ColC.

When I say dataset here, I am not talking about the SQL dataset. I am talking about the collection of data. It really makes no difference whether it comes from a dataset, a view, or a donkey for that matter.

hour | ColA | ColB | ColC | ColD

00 15 14 16 16

03 20 21 23 23

....

23 55 16 75 75

90 51 114 75

|||

Hi,

I already have such a field and it's doing what it's suposed to do.

But this field is set into the table group (remember the group that groups per hour)

So, when my table is displayed after generation, i have 24 lines with in the last column the max values

of several columns.

example :

this is how my table layout looks like

table group= hour(Fields!TimeValue.value) -> this groups all my data per hour

table group header contains 7 colums

column 1 : hour(Fields!TimeValue.value) sorted ascending

column 2-3-4-5-6 contains a calculation like this : (sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value))*100

this returns a ratio value

column 7 contains an expression that gives me max value of column 2-3-4-5. this is the expression i use :

=CInt(math.Max((sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest2.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest3.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest4.Value)/sum(Fields!CountTotalTest.Value)*100), (sum(Fields!CountTest5.Value)/sum(Fields!CountTotalTest.Value)*100))))))

This expression returns the highest ratio value for a specif hour.

At the end i have 24 values in column 7, each time the highest value per hour.

In my table footer i cannot use the expression above, because this will calculate the highest value for the total 24 hours (which is of course different from the highest single value per hour).

Do you see now what i want to do ?

Since i do various other calculations inside my report with fields like Fields!CountTest1.value, .... i cannot do this (i think) in my sql view.

Fields!CountTest1 is also a calculated field within my dataset (=iif(Fields!TestState.Value=101 and Fields!ContactTotalTest.Value=1,1,0))

Soooooo, the only simple thing i want ... the max value, of the generated max values per hour, in my table footer !

I don't know how i can make this more clear.

Greetings

Vinnie

|||See this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2021871&SiteID=1&mode=1

Ian