Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Average Date (Multiple Columns)


Hi

SQL Server 2000
I have four columns of date in a table say Date1, Date2, Date3, Date4 in table Tab_A.
I want to get average date of four columns for each row.
Please advise how?

Thanks
J a w a d


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Average Multiple Columns In A Table
Is this possible with the following scenario?

 

I have 5 groups and 1 details group.  The details group is the lowest level of granularity.  I have two columns that have averages in them.  I have a third column called GPA.  Is it possible to average the averages in columns 1 & 2 and show that product in the third column (GPA)?

 

 









  Column 1 Avg
  Column 2 Avg
    GPA

Group 1


    

Group 2




Group 3




Group 4




Group 5




Details 1




 

 

So it looks something like the above

View Replies !   View Related
Select With Grouping For Multiple Date Columns
Hi All,

Thanks for dropping by my post.

I have a table which is of this form.










ID
MS030_A
MS030_F
MS036_A
MS036_F
MS040_A
MS040_F

ZZ0023
2/16/06
2/16/06
8/10/07
8/10/07
11/21/05
11/21/05

ZZ0031
8/10/07
4/5/07
8/9/07
8/9/07
3/22/07
3/22/07

ZZ0077
8/9/07
9/7/07
8/10/07
8/10/07
8/10/07
9/7/07

ZZ0078
8/10/07
9/7/07
8/9/07
8/9/07
8/9/07
9/7/07

ZZ0079
8/9/07
8/10/07
10/26/05
10/26/05
8/10/07
8/10/07

ZZ1030
3/31/05
8/10/07
9/1/05
9/1/05
8/9/07
 

ZZ1033
3/24/06
8/9/07
8/9/07
8/9/07
3/31/05
 

ZZ1034
8/10/07
8/10/07
8/9/07
8/9/07
3/24/06
 

ZZ1037
8/9/07
8/9/07
9/24/07
 
9/24/07
 

ZZ1040
10/26/05
10/26/05
9/24/07
 
9/24/07
 

ZZ1041
9/1/05
9/1/05
9/24/07
 
9/24/07
 

ZZ1042
8/9/07
8/9/07
9/24/07
 
9/24/07
11/21/05

The goal is to group all this transactions by Month and Year.

Something like this....







MS030_A
MS030_F
MS036_A
MS036_F
MS040_A
MS040_F
Month
 Year

3
2
2
2
2
2
1
2006

4
4
7
9
8
9
2
2006

10
10
6
8
8
 
3
2006

4
4
5
5
3
2
4
2006

5
6
8
3
7
1
5
2006
For just one date column it is pretty straight forward i.e., just do a select count and group by DATEPART ( Mm, DateField)
but for multiple columns i am in a total fix....

can please someone help me out...

appreciate your help

View Replies !   View Related
Average By Columns
Here's my sample formula:

SELECT dbo.TechphonesCalibQA.ID, dbo.TechphonesCalibQA.EmpID, dbo.TechphonesCalibQA.EmpName, dbo.TechphonesCalibQA.[Level],
CASE dbo.TechphonesCalibQA.G_Branded WHEN dbo.TechphonesCalibClient.G_Branded THEN 1 ELSE 0 END AS G_Branded
FROM dbo.TechPhonesCalibClient INNER JOIN
dbo.TechphonesCalibQA ON dbo.TechPhonesCalibClient.SesID = dbo.TechphonesCalibQA.SesID

I would like to get the average of column G_branded (Result from the above formula) Any idea?

Thanks

View Replies !   View Related
Average Date Of Birth
Hi, folks.
I have a table with a column Date_of_birth.
I want to evaluate average date_of_birth by grouping on depts.
AVG function doesn't seem to work with datetime col. Plz help.

View Replies !   View Related
3 Month To Date Average
Hello Friends

Actually i posted this problem earlier , hoping that i got the result , did not look in depth untill i realized the values i got was getting was wrong.

The Scenario is still the same.

3 MTD should be the past two completed months as well as the dates in the current month as well. So taking the example above – if the reporting period for Feb was 01/30/2007 to 02/27/2007, and March was 02/28/2007 to 03/27/2007, the 3 MTD should then be the average from 01/30/2007 to 04/10/2007.

I am creating a Calculated Member as measure.

Basically if i select the average on 76th day of the year, it should basically be the average of 76th Day ( well offcourse whould ignore the empty cells )

For Example the result set should be very similar , It is basically doing MTD

Running balance is basically adding up , where as 3 month to date average should be average of 3 month previous average

suppose today is 75th day , so it should be average of 30days(1st Month ) + 31days(2nd month)+ 14days(of this month)

I have used the lag function

AVG(Hirearchy.Currentmember.lag(2):Hirearchy.Curre ntmember, Measure.abc)

where hirerachy is Year->Month-date

The problem i have here is which i am coming close to conclusion

When i use month level it gives me average of month level , the result on month level is fine. But my requirment is to have it on date level. but how do i have rolling average of 3 month in a date level, if i do a date level with 90 days lag which is not correct which is average of 90 days from current day.

AVG([Tbl Date Key].[Report Hirerachy].CurrentMember.Lag(2):[Tbl Date Key].[Report Hirerachy].CurrentMember,[Measures].[Ab1_Avg]

)


When i drill down to date level , which would be assumed

AVG([Tbl Date Key].[Report Hirerachy].date.Lag(2):[Tbl Date Key].[Report Hirerachy].date,[Measures].[Ab1_Avg]

)





its doing a lag on 3 days lag as appose to , I need the lag on 3 months on a day which would be 90 days

should i be doing a lag on days.



The problem at 90 days lag would be every it would lag 90 days average , but what i am looking for it is when it is on the middle of the month it should be

suppose today is 15th day of month, so it should be average of 30days(1st Month ) + 31days(2nd month)+ 14days(of this month)


i am confused , please help

View Replies !   View Related
3 Month To Date Average
Hello Friends

Actually i posted this problem earlier , hoping that i got the result , did not look in depth untill i realized the values i got was getting was wrong.

The Scenario is still the same.

3 MTD should be the past two completed months as well as the dates in the current month as well. So taking the example above – if the reporting period for Feb was 01/30/2007 to 02/27/2007, and March was 02/28/2007 to 03/27/2007, the 3 MTD should then be the average from 01/30/2007 to 04/10/2007.

I am creating a Calculated Member as measure.

Basically if i select the average on 76th day of the year, it should basically be the average of 76th Day ( well offcourse whould ignore the empty cells )

For Example the result set should be very similar , It is basically doing MTD

Running balance is basically adding up , where as 3 month to date average should be average of 3 month previous average

suppose today is 75th day , so it should be average of 30days(1st Month ) + 31days(2nd month)+ 14days(of this month)

I have used the lag function

AVG(Hirearchy.Currentmember.lag(2):Hirearchy.Currentmember, Measure.abc)

where hirerachy is Year->Month-date

The problem i have here is which i am coming close to conclusion

When i use month level it gives me average of month level , the result on month level is fine. But my requirment is to have it on date level. but how do i have rolling average of 3 month in a date level, if i do a date level with 90 days lag which is not correct which is average of 90 days from current day.

AVG([Tbl Date Key].[Report Hirerachy].CurrentMember.Lag(2):[Tbl Date Key].[Report Hirerachy].CurrentMember,[Measures].[Ab1_Avg]

)


When i drill down to date level , which would be assumed

AVG([Tbl Date Key].[Report Hirerachy].date.Lag(2):[Tbl Date Key].[Report Hirerachy].date,[Measures].[Ab1_Avg]

)





its doing a lag on 3 days lag as appose to , I need the lag on 3 months on a day which would be 90 days

should i be doing a lag on days.



The problem at 90 days lag would be every it would lag 90 days average , but what i am looking for it is when it is on the middle of the month it should be

suppose today is 15th day of month, so it should be average of 30days(1st Month ) + 31days(2nd month)+ 14days(of this month)


i am confused , please help

View Replies !   View Related
Returning Average Of Multiple Rows In A Table Join
Hi,I'm am looking for a little help. I need to create a SQL view whichjoins a few tables, and I need to return an average for a particularcolumn where a second duplicate ID exists...Heres an example of how the results could be returned...ID | Name | Order No. | Value---+------+-----------+---------5 | test | 1234 | 35 | test2| 1234 | 45 | test3| 1234 | 35 | void | 1235 | 55 | void2| 1235 | 65 | void3| 1235 | 55 | void4| 1235 | 7ID is my main join which joins the tablesName is a unique nameOrder No is the same for the different names, I only need to return onerow with this order no, and the first name (the rest are irrelevant)Value is the field which I wish to return as an average of all 3, 4 orhowever many rows is returned and share the same order no. This iswhere I get totally lost as I am pretty new to SQL. Can anyone provideany help on how I would go about limiting this query to the uniqueorder no's and returning the average of the value field, and I can takeit from there with my own tables.Thanks for your helpstr8

View Replies !   View Related
User-Defined Functions To Calculate Average And Relative Percent Difference(RPD):How To Designate The Variables Of Average/RPD
Hi all,


This is my first time to do T-SQL User-Defined Functions programming. I just learned some basic DDL and DML statements. But I do not know how to designate the variables of the User-Defined Function.

I have 3 dbo tables in SQL Server Management Studio Express (SSMSE):

dbo.Projects:
ProjectID     ProjectName     LabName
1                   Blueriver           ALSI
2                   Greentree         GPL
3                   Redrock          STL-NJ
NULL             NULL              NULL

dbo.Samples:
SampleID       SampleName    Matrix    SampleType     Chemical      ProjectID
1                     Blueriver01      Water       Primary            VOCs            1
2                     Blueriver02      Water       Duplicate          VOCs            1
3                     Blueriverr03     Water       QA                   VOCs            1
4                     Greentree11    Soil          Primary             VOCs            2
5                     Greentree12    Soil          Duplicate           VOCs            2
6                     Greentree13    Soil          QA                    VOCs            2
NULL               NULL              NULL       NULL                 NULL         NULL

dbo.LabTests:
AnalyteID     AnalyteName       Result     Unit     SampleID
1                 Acetone              120.80     ug/L          1
2                 Benzene               25.60     ug/L          1
3                 Trichloroethene      13.00     ug/L          1
4                 Xylenes                   0.00     ug/L         1
5                 Acetone                 90.70     ug/L         2
6                 Benzene                 31.40    ug/L         2
7                 Trichloroethene       19.20     ug/L         2
8                 Xylenes                    2.00     ug/L         2
9                 Acetone                140.30     ug/L        3
10               Benzene                 21.50     ug/L        3
11               Trichloroethene        22.20     ug/L        3
12               Xylenes                     0.00     ug/L       3
13               Acetone                 222.10     ug/Kg     4
14               Benzene                  10.30     ug/Kg     4
15               Trichloroethene         30.20     ug/Kg     4
16               Xylenes                    50.70     ug/Kg     4
17               Acetone                  211.90     ug/Kg     5
18               Benzene                   16.40     ug/Kg     5
19               Trichloroethene          34.70     ug/Kg     5
20                Xylenes                    60.00     ug/Kg     5
21                Acetone                  220.30     ug/Kg     6
22                Benzene                   13.20     ug/Kg     6
23                Trichloroethene          32.00     ug/Kg     6
24                Xylenes                     55.50     ug/Kg     6
NULL            NULL                        NULL     NULL    NULL

The Average of chemical/analyte in 2 samples is defined as:
Average = Abs(the result of primary sample + the result of duplicate sample)/2.
Average = Abs(the result of primary sample + the result of QA sample)/2.
Average = Abs(the result of duplicate sample + the result of QA sample)/2.

The RPD of chemical/analyte in 2 samples is defined as:
RPD = Abs(the result of primary sample - the result of duplicate sample)/
Average
RPD = Abs(the result of primary sample - the result of QA sample)/
Average
RPD = Abs(the result of Duplicate sample - the result of QA sample)/
Average

I want to calculate the average and RPD of each chemical/analyte for the following 3 pairs of the related samples:
(i) Between the primary and duplucate samples
(ii) Between the primary and QA samples
(iii) Between the duplucate and QA samples.

In the Table "dbo.Projects", ProjectID is the primary key.
In the Table "dbo.Samples", SampleID is the primary key and ProjectID is the foreign key.
In the Table "dbo.LabTests", AnalyteID is the primary key and SampleID is the foreign key.

I do not know how to designate the variable of each result of analyte in a sample and use it to calucalte the Average and RPD for each pair (i.e. for (i), (ii), or (iii)).
For example: I do not know how to designate the following 2 results:
dbo.LabTests:
AnalyteID        AnalyteName      Result        Unit            SampleID
1                    Acetone             120.80        ug/L                 1
5                    Acetone               90.70        ug/L                 2
 
Please help and advise me how to designate the 2 results in T-SQL User Defined Functions to calculate the Average and RPD.

Thanks in advance,
Scott Chang

View Replies !   View Related
Latches - Average Waits, Average Duration?
I have been monitoring the average number of latch waits and the average duration of each latch wait on my primary SQL box. On average, I see around 30 latch waits per second with an average duration of under 1 second.

What type of average are you seeing on your production machines? What is a normal average?

I have tried researching for suggested values on these two counters, but I haven't turned up any information.

Thanks,

MV

View Replies !   View Related
Identity Columns And Date Columns On Transactional Replication
Hi,

I am planning to use transacational replication (instead of merge replication) on my SQL server 2000. My application is already live and is being used by real users.

How can I ensure that replicated data on different server would have exact same values of identity columns and date columns (where every I set default date to getdate())?

It is very important for me to have a mirror image of data (without using clustering servers).

Any help would be appreciated.

Thanks,

-Niraj

 

View Replies !   View Related
How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns
 

Please can anyone help me for the following?
 
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
 
for eg:
data

Date           Shift  Reading
01-MAR-08     1     879.880
01-MAR-08     2     854.858
01-MAR-08     3     833.836
02-MAR-08     1     809.810
02-MAR-08     2     785.784
02-MAR-08     3     761.760
 
i want output for the above as:

Date              Shift1         Shift2         Shift3
01-MAR-08     879.880       854.858       833.836
02-MAR-08     809.810       785.784       761.760
 Please help me.

View Replies !   View Related
Multiple Columns With Different Values OR Single Column With Multiple Criteria?
Hi,

I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as

SELECT

TBL.col2,TBL.col3
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col2,'engine') TBL1
ON

TBL.col1=TBL1.[key]
INNER JOIN

CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON

TBL.col1=TBL2.[key]

Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.

I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]
Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA

SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]

Result=0 Row
Any idea how i can write second query to get result?

View Replies !   View Related
Obtaining Data To Be Displayed In Multiple Columns From Multiple Rows
 

Hello All,
 
I am rather new to reporting on SQL Server 2005 so please be patient with me.
 
I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.
 
For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I  to list each system part as a column in a report
 
The table (System Info) looks like:-
 
ID | System part |
1  | RAM
2  | Disk Drive
10| CPU
11| CD ROM |
 
Which
 
 
So basically I need it to look like this.
 
Name | IP | RAM | Disk Drive|
----------------------------------------------
A | 127.0.0.1 | 512MB | Floppy
 
So Far my SQL code looks like this for 1 item
SELECT SYSTEM  PART
FROM System Info
WHERE System.ID = 1
 
How would I go about displaying the other system parts as columns with info
 
Any help is much appreciated!
 
 
 

View Replies !   View Related
Search In Fulltextindexes For Multiple Searchterms In Multiple Columns
I want to search in fulltextindexes for multiple searchterms in multiple columns. The difficulty is:
I don't want only the records with columns that contains both searchterms.
I also want the records of which one column contains one of the searchterm ans another column contains one of the searchterms.

For example I search for NETWORK and PERFORMANCE in two columns.
Jobdescr_________________________|Jobtext
Bad NETWORK PERFORMANCE________|Slow NETWORK browsing in Windows XP
Bad application PERFORMANCE_______|Because of slow NETWORK browsing, the application runs slow.

I only get the first record because JobDescr contains both searchterms
I don't get the second record because none of the columns contains both searchterms

I managed to find a workaround:

SELECT T3.jobid, T3.jobdescr
FROM (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'network*') or CONTAINS(jobtext, 'network*') ) T1
INNER JOIN (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'performance*') or CONTAINS(jobtext, 'performance*')) T2 ON T2.Jobid = T1.Jobid
INNER JOIN (SELECT jobid, jobdescr FROM dba.job) T3 ON T3.Jobid = T1.Jobid OR T3.Jobid = T2.JobId
It works but i guess this will result in a heavy database load when the number of searchterms and columns will increase.

Does anyone know a better solution?

Thanks in advance Bart Rouw

View Replies !   View Related
Processing Multiple Rows And Multiple Columns In SSIS
 I previously posted a problem with result set bindings but I have not been able to resolve my problem.  I guess all this comes with being new to programming in this environment!  Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns.  I have to read and manipulate each row.  I was originally looking at using a foreach loop but have not been able to get it to work.  One reply to my previous thought I should be using a data task to accomplish this.  Could someone tell me the best way to handle this situation?  As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me!  Thanks in advance!

View Replies !   View Related
Need An Average By Year Of An Average By Month
I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged...
I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25.
The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement.
?confused?

Here's the original query.
accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): '

SELECT name, country_cd from weather_station where platformId=&&platformId;

SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS
FROM daily
WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime
GROUP BY to_char(datetime,'MM')
ORDER BY to_char(datetime,'MM');

with a result of:

NAME_________________CO
-------------------- --
OFFUTT AFB___________US

MO______MAX_T _____AVG_T__MIN_TEMTP_TOTAL_YEARS
-- ---------- ---------- ---------- -----------
01_________21______-5.31________-30__________60
02_________26______-2.19______-28.3__________61
03_______31.1_______3.61______-26.1__________60
04_______35.6______11.07______-12.2__________60
05_______37.2_______17.2_______-3.3__________60
06_______41.1______22.44__________5__________60
07_______43.3______24.92________7.2__________60
08_______40.6______23.71________5.6__________60
09_________40______18.84_______-2.2__________59
10_______34.4_______12.5_______-8.9__________59
11_________29_______4.13______-23.9__________60
12_________21______-2.52______-28.3__________60

View Replies !   View Related
Deleting Multiple Columns From Multiple Objects
Is there a way to delete from multiple tables/views a column with a specificname? For example, a database has 50 tables and 25 views all have a columnnamed ColumnA. Is it possible to write a simple script that will deleteevery column named ColumnA from the database?Seems to be it would be possible and I can somewhat vision it usingsysobjects but without wanting to spend too much time generating the script(when I could in shorter time manually delete) thought I'd pose the question.Thanks.

View Replies !   View Related
Convert Multiple Rows Into Multiple Columns
I need to push multiple rows of data from one table to one record in another table. This can be achieved using cursors but I need to do them without using cursors.
Source Table:
fld_name fld_value
fld1 val1
fld2 val2
fld3 val3
fld4 val4

Destination should be like this:
fld1 fld2 fld3 fld4
val1 val2 val3 val4

Thanks.

View Replies !   View Related
Multiple Rows To One Row And Multiple Columns
 

Hi,
I want to convert multiple rows to one row and multiple columns. I saw some examples with PIVOT but i could not get them to work.
Heres what i want to do:
 
 
This is the how the table is:
EmpID Designation




678
CFA

679
CFA

680
CFA

685
CFP

685
CIMA

685
IMCA

 
I want it to display as:
EmpID Designation1 Designation2 Designation3
678    CFA
679    CFA
680    CFA
685    CFP     CIMA       IMCA
 
could anyone provide some help on this?
 
Thanks

View Replies !   View Related
SQL 'Having' On Multiple Columns?
Can I write a having statement for multiple columns? Here's my situation: I want to select duplicates from a table based off of 3 fields:Normally one would use HAVING COUNT(*) > 1however, I need to INSERT INTO my table based on duplicates of the 3 fields but also insert the key from the first table, ie:insert into #TempTable (key, field1, field2, field3)select key, field1, field2, field3 from Table1 order by field1, field2, field3 Having COUNT(field1)>1 and COUNT(field2)>1 and COUNT(field3)>1 My question is this: Will this having statement compare all three fields of this row to all three fields of the other rows, or does it do each column independantly?for example:row 1: a   b   g                  row 2: a   c   k                  row 3: j    c   k These rows aren't the same, even though there are 2 a's, I want it to look at the entire row, and not return all three of these. 

View Replies !   View Related
Multiple Columns From WHERE?
Hey all,
I was curious if this was possible...
I basically have 2 queries I'd like to combine into one. The only difference in the queries is one clause in the WHERE statement

so here is an idea of what I'm talking about

SELECT COUNT(*) as HighStock FROM products WHERE qty > 100

now lets say I needed to do one for low I would have to run that query 2 times with different alias's and change the qty...

is there a way to get all that in one result set? Something like

SELECT COUNT(*) as HighStock, COUNT(*) as LowStock FROM products WHERE qty > 100 AND LowStock = qty < 20

so then my result would be
HighStock LowStock
50 10

anyone have any clues on that? thanks! :)

View Replies !   View Related
Max Of Multiple Columns
hi guys,

I have a table which has 4 columns A,B,C,D. it is somewhat like this:

A B C

S U 8
S U 10
S U 3
s V 14
S V 36
T U 25
T U 34
T U 9
T V 5
T V 1

This is the way i have in my database. I need to check which is the max in column C for values in Column A and Column B. First i need to start from column A. If it is S, then i need to see column B, if it is U then max[Column C] for only U & S. then when it becomes V, I need to get max [Column C] for only V & S. So on and so forth. So finally my output should be:

S U 10
S V 36
T U 34
T V 5

How can i do this?

regards,
David

View Replies !   View Related
MIN/MAX Across Multiple Columns
I have seen questions posted a number of times where someone wants to find the maximum or minimum value from a set of columns in a single row of a table.

This script demonstrates two methods for finding the maximum value in a row across a set of columns in the row when any or all of the columns are allowed to be null or equal.

Method 1 uses a UNION ALL sub query for all the columns with a MAX. It is much simpler to code and test, especially when you get much past 4 columns. Adding another column is as simple as adding one more SELECT to the subquery.

Method 2 uses a CASE statement to determine the MAX. It is much more complex to code (and test), and gets exponentially harder to code as the number of columns goes up. I think 5 or 6 columns may be about the limit of complexity of coding that you would want to take on. One advantage of this script is that you can use the simpler to code Method 1 to test the more complex code for the Method 2 if you choose to implement it as a CASE statement.


If you have another method you would like to contribute, feel free. Also, if anyone wants to post performance test results, that would be nice.



print 'Create table to hold test data'
create table #t (
number int not null primary key clustered,
Val1 int,
Val2 int,
Val3 int,
Val4 int
)
GO
print 'Load test data'
insert into #t
select
number,
-- Generate random numbers
-- with about 1/7th null
case
when abs(checksum(newid()))%7 = 0
then null
else checksum(newid())%1000000
end,
case
when abs(checksum(newid()))%7 = 0
then null
else checksum(newid())%1000000
end,
case
when abs(checksum(newid()))%7 = 0
then null
else checksum(newid())%1000000
end,
case
when abs(checksum(newid()))%7 = 0
then null
else checksum(newid())%1000000
end
from
-- Load one million rows of test data.
-- Number table function here
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
dbo.F_TABLE_NUMBER_RANGE(1,1000000)
go
print 'Find rows that do not match for Method 1 and Method 2'
select
out1.*,
out2.*
from
(
-- Method 1, using a subquery with a max
select
a.number,
a.Val1,
a.Val2,
a.Val3,
a.Val4,
[Max_of_Val1_to_Val4] =
(
select
X1= max(bb.xx)
from
(
select xx = a.Val1 where a.Val1 is not null union all
select xx = a.Val2 where a.Val2 is not null union all
select xx = a.Val3 where a.Val3 is not null union all
select xx = a.Val4 where a.Val4 is not null
) bb
)

from
#t a
) out1
join
(
-- Method 2, using a case
select
a.number,
a.Val1,
a.Val2,
a.Val3,
a.Val4,
[Max_of_Val1_to_Val4] =
case
when a.Val1 is not null and
(a.Val1 >= a.Val2 or a.Val2 is null) and
(a.Val1 >= a.Val3 or a.Val3 is null) and
(a.Val1 >= a.Val4 or a.Val4 is null)
then a.Val1
when a.Val2 is not null and
(a.Val2 >= a.Val1 or a.Val1 is null) and
(a.Val2 >= a.Val3 or a.Val3 is null) and
(a.Val2 >= a.Val4 or a.Val4 is null)
then a.Val2
when a.Val3 is not null and
(a.Val3 >= a.Val1 or a.Val1 is null) and
(a.Val3 >= a.Val2 or a.Val2 is null) and
(a.Val3 >= a.Val4 or a.Val4 is null)
then a.Val3
when a.Val4 is not null and
(a.Val4 >= a.Val1 or a.Val1 is null) and
(a.Val4 >= a.Val2 or a.Val2 is null) and
(a.Val4 >= a.Val3 or a.Val3 is null)
then a.Val4
else null
end
from
#t a
) out2
on out1.number = out2.number
where
-- Look for results that do not match
(out1.[Max_of_Val1_to_Val4] is null and out2.[Max_of_Val1_to_Val4] is not null) or
(out1.[Max_of_Val1_to_Val4] is not null and out2.[Max_of_Val1_to_Val4] is null) or
out1.[Max_of_Val1_to_Val4] <> out2.[Max_of_Val1_to_Val4]
go
print 'Find count of rows with different columns null'
print 'Should have a rowcount of 16 to test all conditions'
select
Null_Column_Conditions =
case when Val1 is null then 0 else 1000 end+
case when Val2 is null then 0 else 0100 end+
case when Val3 is null then 0 else 0010 end+
case when Val4 is null then 0 else 0001 end,
count(*)
from
#t
group by
case when Val1 is null then 0 else 1000 end+
case when Val2 is null then 0 else 0100 end+
case when Val3 is null then 0 else 0010 end+
case when Val4 is null then 0 else 0001 end
order by
1
go
drop table #t




Results:


Create table to hold test data
Load test data

(1000000 row(s) affected)

Find rows that do not match for Method 1 and Method 2

(0 row(s) affected)

Find count of rows with different columns null
Should have a rowcount of 16 to test all conditions
Null_Column_Conditions
---------------------- -----------
0 395
1 2444
10 2560
11 14760
100 2400
101 14955
110 14843
111 90206
1000 2518
1001 14857
1010 14989
1011 90256
1100 15100
1101 89659
1110 89783
1111 540275

(16 row(s) affected)






CODO ERGO SUM

View Replies !   View Related
CSV To Multiple Columns
If the table is denormalised and has Comma Seperate Values in a column, this code will copy it to Multiple columns of a Normalised table




declare @DeNormalisedTable table(data varchar(8000))

insert into @DeNormalisedTable
select '1,Davolio,Nancy' union all
select '2,Fuller,Andrew' union all
select '3,Leverling,Janet' union all
select '4,Peacock,Margaret' union all
select '5,Buchanan,Steven' union all
select '6,Suyama,Michael' union all
select '7,King,Robert' union all
select '8,Callahan,Laura' union all
select '9,Dodsworth,Anne'

select * from @DeNormalisedTable -- Comma Seperated Values

declare @s varchar(8000), @data varchar(8000)
Create table #NormalisedTable (Code int, FirstName varchar(100), LastName varchar(100))

select @s=''

while exists (Select * from @DeNormalisedTable where data>@s)
Begin
Select @s=min(data) from @DeNormalisedTable where data>@s
select @data=''''+replace(@s,',',''',''')+''''
insert into #NormalisedTable
exec('select '+@data)
End

select * from #NormalisedTable -- Data in Normalised Table

drop table #NormalisedTable


Madhivanan

Failing to plan is Planning to fail

View Replies !   View Related
Not IN With Multiple Columns
 

Is it possible to use multiple columns for a not in?
something like this;
 
Select  *  From MyTable
Where  X , Y
Not In (Select X , Y  FROM MyOtherTable )

 
 
If not, how would I accomplish this?

View Replies !   View Related
Multiple Key Columns?
I've been working on this project for a few months now. This is my first SS2005 project.  I'm using SSIS, SSAS, and SSRS.  I deal with volume statistics on labratory tests.  The three main identifying columns of a test are: Bill_Item_Id, Mnemonic, and [Test Name].  My main dimension table uses the primary key column: Bill_Item_ID.  I recently learned that I can't use this column as a primary key because there are a couple tests that have the same Bill_Item_Id, but different Test Names.  I know that I can't have multiple primary keys, but is there a way I could say that no two rows in this table will have the same Bill_Item_ID, Mnemonic, AND [Test Name]? Then my fact table would have all three columns and match on those three instead of just one.  I realize this will take a lot of work to rework SSAS and SSIS to accomadate for this, but unfortunately no one brought this tidbit of information to my attention. 

 

Thank you very much.

View Replies !   View Related
Alter Multiple Columns In SQL
This does not work, incorrect syntax... Please help: ALTER TABLE dbo.classifieds_HotelAds ALTER COLUMN (
HasValetParking varchar(1) NULL,
HasContinentalBreakfast varchar(1) NULL,
HasInRoomMovies varchar(1) NULL,
HasSauna varchar(1) NULL,
HasWhirlpool varchar(1) NULL,
HasVoiceMail varchar(1) NULL,
Has24HourSecurity varchar(1) NULL,
HasParkingGarage varchar(1) NULL,
HasElectronicRoomKeys varchar(1) NULL,
HasCoffeeTeaMaker varchar(1) NULL,
HasSafe varchar(1) NULL,
HasVideoCheckOut varchar(1) NULL,
HasRestrictedAccess varchar(1) NULL,
HasInteriorRoomEntrance varchar(1) NULL,
HasExteriorRoomEntrance varchar(1) NULL,
HasCombination varchar(1) NULL,
HasFitnessFacility varchar(1) NULL,
HasGameRoom varchar(1) NULL,
HasTennisCourt varchar(1) NULL,
HasGolfCourse varchar(1) NULL,
HasInHouseDining varchar(1) NULL,
HasInHouseBar varchar(1) NULL,
HasHandicapAccessible varchar(1) NULL,
HasChildrenAllowed varchar(1) NULL,
HasPetsAllowed varchar(1) NULL,
HasTVInRoom varchar(1) NULL,
HasDataPorts varchar(1) NULL,
HasMeetingRooms varchar(1) NULL,
HasBusinessCenter varchar(1) NULL,
HasDryCleaning varchar(1) NULL,
HasIndoorPool varchar(1) NULL,
HasOutdoorPool varchar(1) NULL,
HasNonSmokingRooms varchar(1) NULL,
HasAirportTransportation varchar(1) NULL,
HasAirconditioning varchar(1) NULL,
HasClothingAiron varchar(1) NULL,
HasWakeupService varchar(1) NULL,
HasMiniBarInRoom varchar(1) NULL,
HasRoomService varchar(1) NULL,
HasHairDryer varchar(1) NULL,
HasCarRentDesk varchar(1) NULL,
HasFamilyRooms varchar(1) NULL,
HasKitchen varchar(1) NULL,
HasMap varchar(1) NULL,
HasWiFi varchar(1) NULL,
GDSChainCode nvarvarchar(1) NULL,
GDSChainCodeName nvarchar(50) NULL,
DestinationID nvarchar(50) NULL,
DrivingDirections nvarchar(MAX) NULL,
NearbyAttractions nvarchar(MAX) NULL,
IANHotelID int NULL,HasClothingIron bit NULL
)
GO
COMMIT

View Replies !   View Related
Getting Back Multiple Columns
Hi, I have an asp.net app that essentially access, updates data in an sql server 2000.Instead of writing a stored procedure for each call to a table is there a way to write one stored procedure that grabs all the data from a table, then my vb code could parse through and find the data....Is this a stupid way, or should I stick to my original way... the problem is that I have hundreds of stored procedures....HUNDREDS... I don't think it's the way I am supposed to do this.redhanz. 

View Replies !   View Related
Joing Multiple Columns
Hi All,Bit of a newbie question i'm afraid, so sorry if this is a reallystupid question,I am not sure if I am trying to do this in the right place as I am sureSQL Server has a far better way of doing thisWhat I am trying to do is join the contents of several columns andpresent the entry into a new column. the entries of all the columns Iam trying to join are different types such as dates and numbers. I wantto join all columns as a text string. To make it just a even moretricky I wish to insert a # between all fields.Just in case my poor description has got you thinking What the F*&Khere is an example of what I am trying to doC1 C2 C3 C4 C5Ian 1234 22/02/2006 123456789 Ian#1234#22/02/2006#1234567890I have been trying to do this in the Formula in the design veiw of thetable.Any Advice would be fantastic.Many ThankIan

View Replies !   View Related
Multiple Rows And Columns MAX
Hi All,I'm using the sample table:ID | CODE | V1 | V2 | V3----------------------------------------------------------------1 3 10 3 431 4 9 8 221 2 6 2 551 5 57 12 6I want to get for a given ID the MAX(V1), MAX(V2), MAX(V3)and their respective CODEs.For the above table the returned record for ID=1 should be:v1=57, v2=12, v3=55, code_v1=5, code_v2=5, code_v3=2currently I've got a very messy solution, I'mlooking for an elegant way to do this.10x,Assaf.

View Replies !   View Related
INNER JOIN On On Multiple Columns
I'm trying to eliminate all records that do not have one of two conditions. I'm using INNER JOIN on a derived "table", not a table in my database. The code below summarizes what I'm trying to do. Please note that this is an extremely simplified query.

---------------------------

SELECT * FROM jobs
INNER JOIN
(
SELECT contact_id FROM contacts WHERE deleted = 0
)AS ValidContacts
ON (jobs.owner = ValidContacts.contact_id OR jobs.assignee = ValidContacts.contact_id)

---------------------------

This works fine when the the "SELECT contact_id FROM contacts WHERE deleted = 0" part returns a small number of records, however when that part returns a very large number of records, the query hangs and never completes. If I remove one of the conditions for the JOIN, it works fine, but I need both. Why doesn't this work?

Another possible solution is if I were to use "WHERE/IN" like this:

---------------------------

SELECT * FROM jobs
WHERE owner IN (SELECT contact_id FROM contacts WHERE deleted = 0)
OR assignee IN (SELECT contact_id FROM contacts WHERE deleted = 0)

---------------------------

This would work fine, but I don't want to have to run the "SELECT contact_id FROM contacts WHERE deleted = 0" part twice (since in my real code, it is much more complicated and performance is a big issue". Any help would be greatly appreceated.

I'm using SQL Server 2000 on Windows XP Pro.

View Replies !   View Related
Columns With Multiple Values ??
Hi,
The values I need to store in the table are

Student ID
Student Name
Subjects

The "Student ID" is the primary key.

A student can take more than 1 subject.

For example:
Student ID: 100
Student Name: Kelly Preston
Subjects: Geography, History, Math

How can I store these values in a database table?
I know the normal "INSERT" statement, but how would I store the multiple subjects for a single student ID?

My "Student ID" is auto generated. If I create a new row for each subject, the Student ID will be different for each subject, which I dont want.

Or I can create a new field called "RowNumber" and keep that the primary key..
For example:

Row Number StudentID StudentName Subject
1 100 Kelly Geography
2 100 Kelly History
3 100 Kelly Math

If this is the only way to store the multiple sibjects, then for a given student ID (say 100), how can I retreieve the associated name and subjects? What is the query for that?

View Replies !   View Related
Add Multiple Columns In One Shot
I have a table and want to add 20 more columns to it. I'd like to name these newly added columns such as fname1,fname2....
I don't want to type them mannully. I tried to use loop. But when I use "alter table add" in the loop, I couldn't find a way to substitute the column name in the loop.
Is there any other good solution to this?
Thanks

View Replies !   View Related
Multiple Columns Constraint
I need to setup a constraint to 2 columns(combine keys). Is there a way to accomplish this without using trigger?

View Replies !   View Related
Add Multiple Columns In One Shot
I have a table and want to add 20 more columns to it. I'd like to name these newly added columns such as fname1,fname2....
I don't want to type them mannully. I tried to use loop. But when I use "alter table add" in the loop, I couldn't find a way to substitute the column name in the loop.
Is there any other good solution to this?
Thanks

View Replies !   View Related
Syntax For Multiple Columns In Contains
I was trying to find out if there is a way to check multiple
columns when using the CONTAINS condition in SQL 7.0.

I know i can do one column and I can do all the columns that
are full-text indexed, how do i do more than one but not all!

select * from table where contains (table.column1,'"foo1" and "foo2"')
and
select * from table where contains (table.column.*,'"foo1" and "foo2"')
do work...
but i need to do something like
select * from table where contains(column1,column2, '"foo"')

View Replies !   View Related
Add Multiple Columns In One Shot
I have a table and want to add 20 more columns to it. I'd like to name these newly added columns such as fname1,fname2....
I don't want to type them mannully. I tried to use loop. But when I use "alter table add" in the loop, I couldn't find a way to substitute the column name in the loop.
Is there any other good solution to this?
Thanks

View Replies !   View Related
Multiple Columns From 1 Query
Hi, I have a query that gets the data for a specific week (starting Monday).

The below statement returns the data for the whole week mon-fri

What I would like to do is to have the data come back in a table with columns Mon,Tue,Wed etc breaking down the data.

How would I make the data come back by splitting it into columns mon,Subtot1,tue,Subtot1,wed,Subtot1,thur,Subtot1,fri,Subtot1

SELECT
dbo.People.FirstName,
dbo.People.LastName,
dbo.RequestTypes.Title,
dbo.Companies.CompanyName,
dbo.People.PersonId,
dbo.Actions.RequestId,
dbo.Actions.ActionDate,
dbo.Actions.TimeUsed As Subtot1

FROM
dbo.Actions
INNER JOIN dbo.Requests
INNER JOIN dbo.Companies ON
dbo.Requests.CompanyId = dbo.Companies.CompanyId ON
dbo.Actions.RequestId = dbo.Requests.RequestId
INNER JOIN dbo.RequestTypes ON
dbo.RequestTypes.RequestTypeId = dbo.Actions.RequestTypeId
INNER JOIN dbo.People ON
dbo.People.PersonId = dbo.Actions.ActionedById
WHERE
(dbo.People.PersonId = 'JO'
dbo.Actions.ActionDate > DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) AND
dbo.Actions.ActionDate < DATEADD(wk, DATEDIFF(wk,0,getdate()), 5) )
GROUP BY
dbo.RequestTypes.Title,
dbo.People.FirstName,
dbo.People.LastName,
dbo.Companies.CompanyName,
dbo.People.PersonId,
dbo.Actions.RequestId,
dbo.Actions.ActionDate,
dbo.Actions.TimeUsed

Thanks in advance,
Tugsy

View Replies !   View Related
Updating Multiple Columns
Hi guys.........i'm tryign to update 2 tables in one stored procedure. However i'm getting errors. heres the code i have:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[snow_ors_additionalInfoUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[snow_ors_additionalInfoUpdate]
GO
CREATE PROCEDURE dbo.snow_ors_additionalInfoUpdate
@Reference int,
@CanTravel int,
@SEEmployee varchar(100),
@SE_EMPLOYEE_FROM datetime,
@SE_EMPLOYEE_TO datetime,
@WorkHours Varchar(100),
@DrivingLicence varchar(100),
@CriminalConvictions bit,
@CriminalConvictionsDetails1 text,
@CriminalConvictionsDate1 datetime,
@CriminalConvictionsDetails2 text,
@CriminalConvictionsDate2 datetime,
@CriminalConvictionsDetails3 text,
@CriminalConvictionsDate3 datetime,
@VacancyMonitoring Varchar(100),
@VacancyMonitoringDetails Varchar(50),

AS
UPDATE Account
SET CanTravel= @CanTravel,
SEEmployee = @SEEmployee,
WorkHours= @WorkHours,
DrivingLicence = @DrivingLicence,
CriminalConvictions = @CriminalConvictions,
CriminalConvictionsDetails1 = @CriminalConvictionsDetails1,
CriminalConvictionsDate1 = @CriminalConvictionsDate1,
CriminalConvictionsDetails2 = @CriminalConvictionsDetails2,
CriminalConvictionsDate2 = @CriminalConvictionsDate2,
CriminalConvictionsDetails3 = @CriminalConvictionsDetails3,
CriminalConvictionsDate3 = @CriminalConvictionsDate3
WHERE Account.Reference = @Reference

UPDATE Application
SETVacancyMonitoring = @VacancyMonitoring,
VacancyMonitoringDetails = @VacancyMonitoringDetails,
WHERE Application.Reference = @Reference

GO

and here are the errors i'm getting:

Server: Msg 156, Level 15, State 1, Procedure snow_ors_additionalInfoUpdate, Line 19
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Procedure snow_ors_additionalInfoUpdate, Line 37
Incorrect syntax near the keyword 'WHERE'.


thanks again guys!

View Replies !   View Related
Select Where LIKE From Multiple Columns
I am using mySQL and the following query works fine:

SELECT * from listings where name LIKE "%$trimmed%" order by name";

and so does this query:

SELECT * from listings where keywords LIKE "%$trimmed%" order by name";

however, I can't seem to combine the two with an OR statement as this query only returns the results from the first LIKE column

select * from listings where name LIKE "%$trimmed%" or keywords LIKE "%$trimmed%" order by name

I want to be able to search both columns and return a row if the NAME column or the KEYWORDS columns contains a string.

View Replies !   View Related
Search Multiple Columns
Hi

I have a table with 9 fields that I need to check. I want to check if "text1" and "text2" is in any of thoose 9 fields. But I dont want to check the combination of "text1" and "text2" in the same column. I want for example be able to check if "text1" is in column 1 to 9 and if "text2" is in column 1 to 9. Can someone give me a suggestion on how to do this?

View Replies !   View Related
Selecting Multiple Columns Into One
I have a product table that has a productID column and a productName column. Then I have a productCategory table that associates productIDs with categoryIDs. And lastly I have a category table containing a categoryID and categoryName. I want to write a query that will return a table with three columns...productID, productName, and and calculated column containing the categories the product belongs to separated by a comma. A sample output would be...

ProductID____ProductName____Categories
1____________Green Flats____Footwear, Accessories, Women's Apparel
2____________Purple Belt____Accessories, Women's Apparel
3____________Pink Tunic_____Women's Apparel

If any of this isn't clear please feel free to write with questions.
So far I have this query -
SELECT Products.ProductID, ProductName, CategoryName FROM Products, ProductCategory, Categories WHERE Product.ProductID = ProductCategory.ProductID AND Categories.CategoryID = ProductCategory.CategoryID

This returns....
ProductID_____ProductName____CategoryName
1_____________Green Flats____Shoes
1_____________Green Flats____Accessories
1_____________Green Flats____Women's Apparel
2_____________Purple Belt____Accessories
2_____________Purple Belt____Women's Apparel
etc....

Any suggestions on how to remedy this???

View Replies !   View Related
Combine Multiple Columns
Hi,

I have the following query :

select uname, count(ID) from tbh_Axis
group by uname

which works fine and displays

Admin3
User18

How can i display the result as :

Admin(3)
User1(8)

When I do this:

select uname + '(' + count(ID) + ')' from tbh_Axis
group by uname

It doesnt work.

View Replies !   View Related
Multiple Autonumbering Columns
Greetings All,

I am moving from Foxpro to SQL. I am little confused
about one aspect dealing with multiple autonumbering
columns.

In foxpro it will allow you to set your fields to "autonumber"
so you can achieve having 3 or 4 fields for example with unique
numbers, depending on the application function.

When I try to do this in SQL I can setup my first identity
and PK and it autonumbers just fine, when I try to setup
a second autonumber column with a unique I cant seem to
get control of the identity to set the starting seed, Unless
I select that column in the identity to yes, but it seems to
disable the the first identity column I setup.

Can anyone tell what I missed or doing wrong ?

One last note, I am still using the Studio Manager
Still learning the programming portion..

Thanks

View Replies !   View Related
Computing The Sum Of Multiple Columns
Hi,

I want to create a view where I can calculate the sum of a couple of bit value columns,
aswell as keeping track of the total number of bits set to true.

Here is an example:





dbo.Band


BandID    int
Name    nvarchar(50)
Country    nvarchar(50)




dbo.Record


ID    int
Name    nvarchar(50)
BandID    int
Label    nvarchar(50)
InProduction    bit
InSkodne    bit
From these tables I created this view:





dbo.TestView


SELECT     dbo.Band.Name, dbo.Band.Country, dbo.Record.Name AS Recordname, dbo.Record.Label, CONVERT(int, dbo.Record.InProduction) AS InProduction,
                      CONVERT(int, dbo.Record.InSkodne) AS InSkodne, CONVERT(int, dbo.Record.InProduction) + CONVERT(int, dbo.Record.InSkodne) AS Total
FROM         dbo.Band INNER JOIN
                      dbo.Record ON dbo.Band.BandID = dbo.Record.BandID
I use the convert function to be able to use SUM() across my bit columns, which works fine. Problems is I´m not sure that the way I´m creating the TOTAL column is the best way to go. Any other ideas?
I´m having some problems using this view and the TOTAL column in particular when referencing this view from applications outside SQL Server...

View Replies !   View Related
Multiple Columns As The Pivot Key
Hello,
 
Here is a sample of the data that I am trying to pivot;
 
rec_id    sequence   field_name    value
1             1                  cat_nbr           Granrier
1             1                  cat_page        pg 21
1             2                  cat_nbr           H&S
1             2                  cat_page         pg234
2             1                  cat_nbr           Ford
2             1                  cat_page        pg5
 
I need to pivot on rec_id and sequence to get an output like this:
 
rec_id      sequence         cat_nbr           cat_page
1               1                        Granrier           pg21
1               2                        H&S                pg234
2               1                        Ford                pg5
 
All I seem to be able to get thoug is this:
rec_id      sequence         cat_nbr           cat_page
1               1                        Granrier          
1               1                                                 pg21
1               2                        H&S                
1               2                                                 pg234
2               1                        Ford                pg5

 
 
It seems to me that the pivot transform can only pivot around one key value column.  What am I missing?
 
Thanks.

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved