How to get the total time of the records in a table

Hello,

I have one table that has a column called CallDuration. This columns has always the format "1/01/2000 12:01:38 AM". The date part "1/01/2000" I want to discard, and sum the time part to get a total time in my query. How can I do that?

Thxselect datediff(hour,'1/01/2000 12:01:38 AM', '1/01/2000 11:10:38 AM') Hours,
datediff(minute,'1/01/2000 12:01:38 AM', '1/01/2000 11:10:38 AM') % 60 Minutes,
datediff(minute,'1/01/2000 12:01:38 AM', '1/01/2000 11:10:38 AM') TotalMinutes|||select datediff(hour,min(callduration), max(callduration)) Hours,
datediff(minute,min(callduration), max(callduration)) % 60 Minutes,
datediff(minute,min(callduration), max(callduration)) TotalMinutes

How to get the status?

I have a table with columns c1, c2, c3, c4.

if all nulls or blanks. Status = 0
if c1 assigned but no c2, c3, and c4, then status = 1
if c2 assigned but no c3 and c4, then status = 2
if c3 .. then ..
if c4 .. then ..

I want to have one SQL to get the status like (ignored checking for
blanks here for demo)

SELECT Status = (if not c4 is null then 4
else not c3 is null then 3
else not c2 is null then 2
else not c1 is null then 1
else 0)
FROM mytable.

Thought of using CASE ... WHEN ... but it is only on one colum.

Any better idea.

Thanks

JohnI'm not sure why you believe that CASE can only reference a single
column (admittedly, Books Online shows only single-column examples):

select case
when coalesce(c1,c2,c3,c4) is null then 0
when c1 is not null and c2 is null and c3 is null and c4 is null then
1
when c1 is null and c2 is not null and c3 is null and c4 is null then
2
/* Add other combinations here */
else null end as 'Status'
from
(select null as 'c1', null as 'c2', null as 'c3', null as 'c4'
union all
select 1, null, null, null
union all
select null, 2, null, null) dt

Simonsql

How to get the max value of duplicate item

Hi Every Body,

I have one table it's called -Access.It contains two columns name of that is Door,Emp_Id.

example Door Emp_Id

10 1

10 2

10 3

11 4

12 5

11 1

this is the value in that table.......

I want get Which Door number maximum Access from the table.....

Thanks & Regards,

S.Sajan

It's unclear exactly what are you asking for or what are you trying to achieve. Can you share a small code snippet or give more information? how is the data stored? what do you mean by "maximum access"? etc.

|||I'll take a guess that you're trying to find the highest door number than a particular employee has access to.

To do this you need:

Code Snippet

Select max(Door) from [-Access] where Emp_id = @.Emp_id

If instead you're trying to work out which door the most employees have access to you need:

Code Snippet

Select top 1 Door, count(Door) from [-Access] group by Door order by count(Door) desc

There are probably better ways to do the latter, but I tried something similar on a pretty big table, 90,000 records, and it's pretty quick.

Sean

Edit: I tried it on Sql Server, but I think it's the same for Access.|||

hi Sean Fowler,

That is exactly correct....in that query...............Thanks..

I Have one more doubte....

Select Top 1 Door,Count(Door) from [Access] group by Door order by Count(Door)desc

Above query disply Door Number,Which is maximum access Door Number by Employee...

Suppose two Door has access same number of time ...In that time i want display the two Door Number

..this is my requirement....

thanks in advance..

Thanks & Regards,

S.Sajan

|||

Here it is,

Code Snippet

Create Table #dooraccess (

[Door] Varchar(100) ,

[Emp_Id] Varchar(100)

);

Insert Into #dooraccess Values('10','1');

Insert Into #dooraccess Values('10','2');

Insert Into #dooraccess Values('10','3');

Insert Into #dooraccess Values('11','4');

Insert Into #dooraccess Values('12','5');

Insert Into #dooraccess Values('11','1');

On SQL Server 2000/2005,

Code Snippet

Select Main.* from

(SelectDoor, Count([Emp_Id]) [AccessCount] from #dooraccess Group By Door) as Main

Join

(

Select Max(AccessCount) AccessCount from

(

SelectDoor, Count([Emp_Id]) [AccessCount] from #dooraccess Group By Door

) as Data

) as MaxAccess

On Main.AccessCount = MaxAccess.AccessCount

--or

Code Snippet

Select Top 1 Door, Count([Emp_Id]) [AccessCount] from #dooraccess Group By Door Order By [AccessCount] Desc

On SQL Server 2005,

Code Snippet

;With CTE

as

(

Select Door, Count([Emp_Id]) Over(partition By Door) [AccessCount] from #dooraccess

)

, CTE2

as

(

Select * , Max([AccessCount]) Over() MaxAccessCount from CTE

)

Select * from CTE2 Where AccessCount = MaxAccessCount

--or

Code Snippet

Select Top 1 Door, Count([Emp_Id]) Over(partition By Door) [AccessCount] from #dooraccess order By [AccessCount] Desc

|||All good suggestions. You can also do this:

Code Snippet

Select Door, count(Door)
from [#dooraccess]
group by Door
having count(Door) = (select top 1 count(Door) from #dooraccess group by Door order by count(Door) desc)

Seansql

How to get the Columns list to the end user?

Hello Nilay here,

How to display the coulumn list of the reports to the end user ?

Thanks,I don't understand the question. Is it

a) How to prompt the user for the columns he/she wants to display, or
b) How to display a column containing a list of reports?|||Hi, I want to show the All coumns, which are in the report, to the end user. So user can select his usefull columns and the selected coulmn should only show on the report and other should be hide.

Regards,
Nilay|||

In SQL Server 2005, you can use a multi-value parameter to prompt the user. Then you can write an expression/custom function to parse the output of the parameter. Call that expression/custom function in the visibility expression for the column in your table/matrix.

You'll need to make sure the choices in your parameter list are are kept in sync with your fields list, that's something that we don't provide by default.

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

How to get the Columns list to the end user?

Hello Nilay here,

How to display the coulumn list of the reports to the end user ?

Thanks,I don't understand the question. Is it

a) How to prompt the user for the columns he/she wants to display, or
b) How to display a column containing a list of reports?|||Hi, I want to show the All coumns, which are in the report, to the end user. So user can select his usefull columns and the selected coulmn should only show on the report and other should be hide.

Regards,
Nilay|||

In SQL Server 2005, you can use a multi-value parameter to prompt the user. Then you can write an expression/custom function to parse the output of the parameter. Call that expression/custom function in the visibility expression for the column in your table/matrix.

You'll need to make sure the choices in your parameter list are are kept in sync with your fields list, that's something that we don't provide by default.

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

sql

How to get results back from a Stored procedure.

I have a stored procedure1 calling stored procedure 2. Stored
procedure 2 when it is finished returns a single row with 2 columns.
Is there a way to grab the return of column 1 from stored procedure 2
inside stored procedure 1?
Thank you in advance
http://www.sommarskog.se/share_data.html
"TheVillageCodingIdiot" <whosyodaddy1019@.hotmail.com> wrote in message
news:1651d138-de4e-49ac-928c-c2558468ef55@.s50g2000hsb.googlegroups.com...
>I have a stored procedure1 calling stored procedure 2. Stored
> procedure 2 when it is finished returns a single row with 2 columns.
> Is there a way to grab the return of column 1 from stored procedure 2
> inside stored procedure 1?
> Thank you in advance

How to get results back from a Stored procedure.

I have a stored procedure1 calling stored procedure 2. Stored
procedure 2 when it is finished returns a single row with 2 columns.
Is there a way to grab the return of column 1 from stored procedure 2
inside stored procedure 1?
Thank you in advancehttp://www.sommarskog.se/share_data.html
"TheVillageCodingIdiot" <whosyodaddy1019@.hotmail.com> wrote in message
news:1651d138-de4e-49ac-928c-c2558468ef55@.s50g2000hsb.googlegroups.com...
>I have a stored procedure1 calling stored procedure 2. Stored
> procedure 2 when it is finished returns a single row with 2 columns.
> Is there a way to grab the return of column 1 from stored procedure 2
> inside stored procedure 1?
> Thank you in advance

How to get Primary Key (Columns) of a Table?

I want to get the Primary Key Columns in Arrays by sending a table
name. I am using SQL Server 2000 and I want to make a find utility in VB.net which
will work for all the forms; I have tables with one Primary key and some tables with composite Primary keys.

I used to do this in VB 6 by making a function which fills the Primary Keys in
List Box (I require to fill in list box), now I need to get in array.

Can some one tell me the migration of the following VB 6 Code?

This was written for the MS Access, I need same for SQL Server, I can
not find Table Def and Index Object in VB.net 2003.

Public Sub GetFieldsFromDatabase (ldbDatabase As Database, lsTableName As
String)

Dim lttabDef As TableDef
Dim liCounter As Integer
Dim liLoop As Integer
Dim idxLoop As Index
Dim fldLoop As Field

With ldbDatabase
For Each lttabDef In .TableDefs
If lttabDef.Name = lsTableName Then
liCounter = lttabDef.Fields.Count
For liLoop = 0 To liCounter - 1
cboFieldLists.List(liLoop) = lttabDef.Fields(liLoop).Name
Next liLoop
For Each idxLoop In lttabDef.Indexes
With idxLoop
lblIndexName = .Name
If .Primary Then
liCounter = 0
For Each fldLoop In .Fields
cboPrimaryKeys.List(liCounter) = fldLoop.Name
liCounter = liCounter + 1
Next fldLoop
End If
End With
Next
cboFieldLists.ListIndex = 0
If cboPrimaryKeys.ListCount > 0 Then
cboPrimaryKeys.ListIndex = 0
End If
Exit For
End If
Next
End With
End Sub

SQL Server has a built-in system stored procedure calledsp_primarykeys whichwill return you the primary key information for the table you specify.

USE master
EXEC sp_primarykeys @.table_server = N'LONDON1',
@.table_name = N'Customers',
@.table_catalog = N'Northwind',
@.table_schema = N'dbo'

|||Thanks Tmorton

How to get output of sql command in columns

Hi,
I am working with Informix db in Digital Unix.
When I try to give any select commands and try to retrieve more than 5 columns in the same sql command, the output comes in rows instead of columns.
Is there a way to force it to come in columns?

i just use a simple format,
select column1 ,column2 ,column3 ,column4 ,column5 from tableyou should be getting 5 columns per record in the DB

column1 ,column2 ,column3 ,column4 ,column5
column1 ,column2 ,column3 ,column4 ,column5
column1 ,column2 ,column3 ,column4 ,column5
column1 ,column2 ,column3 ,column4 ,column5
column1 ,column2 ,column3 ,column4 ,column5

how do you want the layout and why?

how to get one columns back ground color to red

Hi all
In attendance report i need to show the back ground color as red if the column is sundayRight click the field, choose format field, select the Border tab, press the 'x-2' button to the right of the background boxes, enter a formula like
if {table.field} = 'Sunday' then crRed else crNoColor

How to get most used data from table

I have a table where there are two columns like beginsite and endsite.Now,i
want to see which are the most entered 100 endsites for a particular
beginsite.Any
pointers on the query ?
regards,
ani
I am not sure if I understand your request but try this:
SELECT TOP 100 EndSite, COUNT(*) AS Totals
FROM YourTable
WHERE BEginSite = xxx
GROUP BY EndSite
ORDER BY COUNT(*) DESC
Andrew J. Kelly SQL MVP
"c_ani" <cani@.discussions.microsoft.com> wrote in message
news:88D1830B-18EB-4AEF-B4E5-9AEF499A3CE6@.microsoft.com...
>I have a table where there are two columns like beginsite and endsite.Now,i
> want to see which are the most entered 100 endsites for a particular
> beginsite.Any
> pointers on the query ?
> regards,
> ani

How to get most used data from table

I have a table where there are two columns like beginsite and endsite.Now,i
want to see which are the most entered 100 endsites for a particular
beginsite.Any
pointers on the query ?
regards,
aniI am not sure if I understand your request but try this:
SELECT TOP 100 EndSite, COUNT(*) AS Totals
FROM YourTable
WHERE BEginSite = xxx
GROUP BY EndSite
ORDER BY COUNT(*) DESC
Andrew J. Kelly SQL MVP
"c_ani" <cani@.discussions.microsoft.com> wrote in message
news:88D1830B-18EB-4AEF-B4E5-9AEF499A3CE6@.microsoft.com...
>I have a table where there are two columns like beginsite and endsite.Now,i
> want to see which are the most entered 100 endsites for a particular
> beginsite.Any
> pointers on the query ?
> regards,
> ani

How to get most used data from table

I have a table where there are two columns like beginsite and endsite.Now,i
want to see which are the most entered 100 endsites for a particular
beginsite.Any
pointers on the query ?
regards,
aniI am not sure if I understand your request but try this:
SELECT TOP 100 EndSite, COUNT(*) AS Totals
FROM YourTable
WHERE BEginSite = xxx
GROUP BY EndSite
ORDER BY COUNT(*) DESC
Andrew J. Kelly SQL MVP
"c_ani" <cani@.discussions.microsoft.com> wrote in message
news:88D1830B-18EB-4AEF-B4E5-9AEF499A3CE6@.microsoft.com...
>I have a table where there are two columns like beginsite and endsite.Now,i
> want to see which are the most entered 100 endsites for a particular
> beginsite.Any
> pointers on the query ?
> regards,
> ani

How to get list (text) of all tables and columns?

Is there a way using MS SQL Server and Enterprise Manager to get a text
document (or perhaps even a Word document) listing all table names,
column names, etc of a database?

--
Sugapablo
-----------
http://www.sugapablo.com <--music
http://www.sugapablo.net <--personal"Sugapablo" <russREMOVE@.sugapablo.com> wrote in message
news:vmu7b03j5uos68@.corp.supernews.com...
> Is there a way using MS SQL Server and Enterprise Manager to get a text
> document (or perhaps even a Word document) listing all table names,
> column names, etc of a database?
> --
> Sugapablo
> -----------
> http://www.sugapablo.com <--music
> http://www.sugapablo.net <--personal

It would probably be easier to use Query Analyzer instead, and get the
details you need from the INFORMATION_SCHEMA views (assuming you have SQL7
or 2000), eg.:

select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
order by TABLE_NAME, ORDINAL_POSITION

If it's a one-off task, then you can just cut and paste the results, but if
you need to do it regularly, then you could consider using SQLDMO from a
client application. That way you can create the whole Word document using VB
or something similar.

Simon|||Sugapablo <russREMOVE@.sugapablo.com> wrote in message news:<vmu7b03j5uos68@.corp.supernews.com>...
> Is there a way using MS SQL Server and Enterprise Manager to get a text
> document (or perhaps even a Word document) listing all table names,
> column names, etc of a database?

Hello,

To get a list of user tables use this query:

select name from ssysobjects where type = 'u'

And for a list of columns related for all objects (tables,views,sps)

select name, object_name(id) from sysindexes

The results could be pushed to a text document or any other ODBC
compliant datasource using DTS.

Regards,
-Manoj

How to get largest 3 values from 5 columns

Hi,

I wondered if someone could help me, I'm new to defining my own SQL statements!

I am looking to extract 4 columns of data from a Access database, these columns I am pasting into a table. That I can do using the select query, and set them AS variable1 to 4.

(I am putting these variables into an ASP page)

My problem comes when I need to create an extra column which shows the best 3 results from the 4 returned data points. In excel I would use something such as =Large(CellA1:E1, 1) to give me the largest value and then so on for the largest 3, and then summate these three values.

I have pasted my code so far below:

I'd be very grateful if someone could enlighten me.

Many thanks!
__________________________________________________ ____

ResultsSQL = "SELECT *, (Round1Pts) AS Round1Points, " & _
"(Round2Pts) AS Round2Points, " & _
"(Round3Pts) AS Round3Points, " & _
"(Round4Pts) AS Round4Points, " & _
"(????) AS Best3Points " & _
"FROM " & ResultsTable & " ORDER BY (Best3Points) DESC, (DriverName) DESC"Did you try using the MAX function?

MAX(Round1Pts,MAX(Round2Pts,MAX(Round3Pts,MAX(Roun d4Pts)))) AS Best3Points
...etc...
:cool:

How to get hierarchical xml based of multiple tables xml columns

Hi,
I am currently working in SQL Server 2005.
we have three tables all these tables have an xml type columns.The XML in
these XML columns are related to each other.
EX.
Table "PLY" contains Rows As
<dsplaylist>
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
</ply>
</dsplaylist>
<dsplaylist>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
<ply_grp_info grp_ref_id="3de9cf11-34C8-53fc-6ae8-3335f2cd58fe" />
</ply>
</dsplaylist>
Table "GRP" Contains Rows As
<dsgrp>
<grp id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" slug="grp4">
<grp_inst_info inst_ref_id="d7a02503-8186-4380-a9d3-16aeedb7fa08" />
<grp_inst_info inst_ref_id="dca02503-8186-3480-a9d3-16aeedb7fa23" />
<grp_inst_info inst_ref_id="aba02503-8186-4380-a9d3-16aeedb7fa23" />
</grp>
</dsgrp>
<dsgrp>
<grp id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="grp3">
<grp_inst_info inst_ref_id="d7a02503-8186-4380-a9d3-16aeedb7fa08" />
</grp>
</dsgrp>
Table "INSTANCE" Contains Rows As
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
<instance id="dca02503-8186-3480-a9d3-16aeedb7fa23" slug="inst4" />
<instance id="aba02503-8186-4380-a9d3-16aeedb7fa23" slug="inst3" />
i want to get a hierachical relational xml out of these TABLES columns XML.
In the Following XML FORMAT
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<grp id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" slug="grp4">
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
<instance id="dca02503-8186-3480-a9d3-16aeedb7fa23" slug="inst4" />
<instance id="aba02503-8186-4380-a9d3-16aeedb7fa23" slug="inst3" />
</grp>
</ply>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<grp id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="grp3">
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
</grp>
</ply>
How can i achieve this using FLWOR Expression or any other way.
Thanks,
CarolI have done this for one table
try this
create table ply (ply_col xml)
insert into ply values ('<dsplaylist>
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
</ply>
</dsplaylist>')
insert into ply values ('<dsplaylist>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
<ply_grp_info grp_ref_id="3de9cf11-34C8-53fc-6ae8-3335f2cd58fe" />
</ply>
</dsplaylist>')
This is the query
with CTE_PLY as
(
SELECT T1.ply_id.query('.') as ply_id
FROM ply
CROSS APPLY ply_col.nodes('/dsplaylist/ply') as T1(ply_id)
)
select ply_id.value('/ply[1]/@.id[1]','varchar(100)') as [id],
T2.ply_GRP.value('@.grp_ref_id','varchar(100)') as ply_grp_ref_id
FROM CTE_PLY
CROSS APPLY PLY_ID.nodes('/ply/ply_grp_info') as T2(ply_GRP)
If you can apply the same logic on all three tables then its a normal join
query :)
Hope this helps..

How to get distinct columns using COALESCE

Hi guys, can you please help me to solve this problem. I have to get distinct row from offering column of xyz table.

I have to get offering1, offering2 from xyz table. But I am getting only offering1. I should not get duplicate rows from XYZ table.

SELECTDISTINCT @.Staging_Off=COALESCE(@.Staging_Off+',','')+ Offering

FROM xyz

WHERE xyz.OfferingNOTIN

(SELECTDISTINCT Offering.Offering

FROM OfferingJoin xyz

ON Offering.Offering= xyz.Offering

AND Offering.SourceSystem= @.SourceSystem

)

That's probably because your nested query (SELECT DISTINCT) is not working on the same row as your outer query. You must make sure your inner query join with a value of the outer query, otherwise they won't be related. Something like this:

SELECT DISTINCT @.Staging_Off=COALESCE(@.Staging_Off +',','')+ OfferingFROM xyzAS X1WHERE X1.OfferingNOT IN(SELECT DISTINCT O.OfferingFROM OfferingAS O, xyzAS X2WHERE O.Offering = X2.OfferingAND X2.SomeId = X1.SomeIdAND O.SourceSystem= @.SourceSystem)

Here I assume that there are some kind of unique id in xyz table that can be used

I think this query could be rewritten in a more clean manner, but I can't do it from the tip of my head. Would need source data and do some trial and erroring ;-) Good luck!