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 Replies


ADVERTISEMENT

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







Copyrights 2005-15 www.BigResource.com, All rights reserved