SQL Server 2012 :: Excluding Records Whose Values From 2 Different Fields Match

Aug 31, 2015

Using MSSQL 2012

I have a simple select query and I need to eliminate records whose values from 2 different fields match. I thought I had this working, but if one of those fields in my data IS NULL it filters out those records. If I comment out my last line then my number record shows, if I include that statement that record drops. The only thing I see in my data is the Name and PName are both NULL in the data for that particular number. Just need to filter out any records where it finds those 3 Names that also have "Default" as the PName, then include everything else even if Name or Pname is NULL.

Below is my where clause.

WHERE [DETERMINATION] <> 'Denied'
AND [Number] ='A150731000039'

---- Removes incorrect records where these names match----
AND ([Name] NOT IN ('GLASSMAN','NANCY','LUDEMANN') AND [PName] = 'DEFAULT')

View 4 Replies


ADVERTISEMENT

SQL Server 2012 :: How To Match Values From A PIVOT

Jan 31, 2014

I'd like to get a extract table result, with a reference id primary key, showing the maximum dates for events and who was responsible for them. I can get the max(date) field in columns using PIVOT, but can't see a way to get the 'who' field without lots of LEFT JOINs.

Here's some test data and code which shows the principle:

CREATE TABLE #t
(
ref INT ,
id INT ,
who VARCHAR(10) ,
checkin DATE

[Code] ....

The result set is:

ref 1 who1 2 who2 3 who3 4 who4
123 2014-01-18 carol 2014-01-18 andy 2014-01-16 bill 2014-01-17 carol
456 NULL NULL 2014-01-17 NULL NULL NULL NULL NULL

Is there some way to avoid all the LEFT JOINs, maybe by using another PIVOT, to produce the same result?

View 4 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

SQL Server 2012 :: Select Records Where Combination Of Two Values Are In Subquery Result?

Dec 12, 2014

I have some data in the following format;

MYTABLE

DOC_NO // REV_NO // FILE_NAME
ABC123 // A // abc123.pdf
ABC123 // B // abc123_2.docx
ABC124 // A // abc124.xlsx
ABC124 // A // -
ABC125 // A // abc125.docx
ABC125 // C // abc125.jpg
ABC125 // C // abc125.docx
ABC125 // C // -
ABC126 // 0 // -
ABC127 // A1 // abc127.xlsx
ABC127 // A1 // abc127.pdf

I'm looking to select all rows where the DOC_NO and REV_NO appear only once.(i.e. the combination of the two values together, not any distinct value in a column)

I have written the sub query to filter the correct results;

SELECT DOC_NO, REV_NO FROM [MYTABLE]
GROUP BY DOC_NO, REV_NO
HAVING COUNT(*) =1

I now need to strip out the records which have no file (represented as "-" in the FILE_NAME field) and select the other fields (same table - for example, lets just say "ADD1", "ADD2" and "ADD3")

I was looking to put together a query like;

SELECT DOC_NO, REV_NO, FILE_NAME, ADD1, ADD2, ADD3 FROM [MYTABLE]
WHERE FILE_NAME NOT LIKE '-' AND DOC_NO IN
(SELECT DOC_NO, REV_NO FROM [MYTABLE]
GROUP BY DOC_NO, REV_NO
HAVING COUNT(*) =1)

But of course, DOC_NO alone being in the subquery select is not sufficient, as (ABC125 /A) is a unique combination, but (ABC125 /C) is not, but these results would be pulled in.

I also cannot simply add an additional "AND" clause on its own to make sure the REV_NO value appears in the subquery, because it is highly repetitive and would have to specifically match the DOC_NO)

What is the easiest way of ensuring that I only pull in the records where both the DOC_NO and REV_NO (combination) are unique, or is there a better way of putting this select together altogether?

View 9 Replies View Related

SQL Server 2012 :: Difference Between Two Dates (Excluding Weekends)

May 19, 2014

I have a table with a list of jobs along with their start and end datetime values.

I am looking for a function which will return the time taken to process a job using a start date and an end date. If the date range covers a Saturday or Sunday I want the time to ignore the weekends.

Example

Start Date=2014-05-15 12:00:00.000
End Date=2014-05-19 13:00:00.000

Total Time should be: 2 Days, 1 Hour and 0 Minutes

View 5 Replies View Related

SQL Server 2008 :: Finding Only Fields That Don't Match In Two Different Tables

Feb 12, 2015

I have two table People and Employee, both have firstname and lastname as fields

I want to display only the names that don't match on firstname and lastname

View 3 Replies View Related

Excluding Fields In SELECT Statement

Oct 23, 2005

Hi,Is there a way to exclude fields in a query other than just includingthe ones you want. If there are 20 fields and you want to see all but3, it would be a lot easier to exclude the 3.Thanks

View 8 Replies View Related

Excluding Records

Apr 24, 2008

Hello,

I'm trying to figure out the best way to approach this seemingly simple task in Reporting Services 2005, but have come up with nothing but frustration. Any help would be appreciated!

Here's the basic idea:

SELECT Server.Database1.Employee, Server.Database1.HomeDept, Server.Database2.WorkedDept
FROM Server.Database1, Server.Database2

I need to exclude certain combinations of HomeDept/WorkedDept (about 11)...such as 1015/2223, 1015/2226, 2002/4422, 2002/8742, 2002/2342, etc. I want any other combinations of HomeDept 1015 & 2002 to be selected (which would be hundreds).

I've tried:

WHERE (Server.Database1.HomeDept <> '1015' AND Server.Database2.WorkedDept NOT IN ('2223','2226')) AND Server.Database1.HomeDept <> '2002' AND Server.Database2.WorkedDept NOT IN ('4422','8742','2342'))

(and all combinations of NOT IN, <>, and parenthesis placement...I even tried to do each exception separately)

What happens is that all instances of HomeDept 1015 and 2002 and all instances of WorkedDept 2223, 2226, 4422, 8742, and 2342 are excluded.

The SQL Query Designer will remove parenthesis so that the HomeDept exclusion is processed separately from the WorkedDept.

I've tried to do the SQL in the Generic Query Designer, so the SQL Query Designer doesn't reformat it, and it still returns the same results.

This behavior only seems to happen when using <> for both, NOT IN for both, or a combo of NOT and <>. If I used = for the HomeDept, the exclusion of the WorkedDept worked fine.

Any suggestions?

View 1 Replies View Related

SQL Server 2008 :: Records That Don't Match From Two Database

Sep 24, 2015

I am using the following views from two db's to find records that don't match.

My question is can I have output of fields from the second table

SELECT distinct T1.[last name],t1.[first name],ENum
FROM ECLINICIAN_Info T1
WHERE NOT EXISTS(SELECT *
FROM ACLINICIAN_Info T2
WHERE t1.Enum = t2.Anum
and t1.[last name] = t2.lname and t1.[first name] = t2.Fname)

I would like to include t2.Anum in my select ...

View 5 Replies View Related

Excluding Duplicate Records

Apr 18, 2008

and generating a report from an SQL table, and need to know how to exclude records that are "duplicates". Not duplicates in a sense that every field is identical, but duplicates in a sense where everything except the unique identifier is identical. Is there a quick and easy way to do this?

View 5 Replies View Related

Excluding Repeated Values In A Sum

May 21, 2007

The scenario is as follows:

I have rows coming from the db including:
Contract Number, Contract Name, owner and Actions associated with each contract.

SQL statement brings back:
Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
1234 123453 $50 Bob y
534232 5464634211 $30 Harry z

The problem is that each contract can have multiple actions associated with it...
There ideal output would be:

Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
Bob y
534232 5464634211 $30 Harry z

Total: $80

Basically I need to hide and not include repeated items based on a contract number... one idea I had was creating a group based on contract number and then display info in the header and then only owner and actions in the detail section.. The problem is Totals... how can I can it to avoid count the duplicated values..

Any help would be greatly appreciated.

Thanks,
Neil

View 4 Replies View Related

XL Source Editor Excluding Numeric Records

Dec 11, 2007

Hi All,

I have a XL source file which contains the 1st column value is some of Numeric and alpha numeric (ex. 1,1A,1B,2,2A,2B),
i have fired the select statement (select * from [sch 1 a$A9155] where f1 is not null ) in XL source editor, its not showing the numeric records means 1 and 2 were excluding from the above select statement i think it was due to combining numeric and alpha numeric.

I need both records should select , please anyone can help me to sort out the issue.

Thanks in advance

Thanks,
syed

View 6 Replies View Related

SQL Server 2012 :: Get OrderIDs That Only Match The Criteria

Aug 8, 2015

I am stuck with a query that I am working on. I have data like below.

DECLARE @Input TABLE
(
OrderID INT,
AccountID INT,
StatusID INT,
Value INT
)

INSERT INTO @Input VALUES (1,1,1,15), (2,1,1,20), (3,2,1,5), (4,2,2,40), (5,3,1,20), (6,1,2,40), (7,1,2,40)

If an Account's value reaches 20 for StatusID = 1 and 40 for StatusID = 2, that is a called "Good". I want to find out which order made the Account become "Good".

By looking at the data, it is understandble that AccountID 1 crossed Status ID 1's limit of 20 with order 2, but the status ID 2's limit was only crossed after the 6th order was placed. So my output should show 6 for AccountID 1.

For AccountID 2, value of statusID 1 was 5 with orderid 3, but it reached the limit for status id 2 of 40 with order 4. But the first condition was not met. so it shouldn't be seen in the output.

Same with AccountID 3 as well, It reached the limit of status id 1 with order 5 but the limit for order 2 wasn't reached so it should be ignored as well.

I wrote the code as below, its working fine but I still know there are better ways to write since I will be working with atleast a million records.

;WITH CTE AS
(
SELECT OrderID,AccountID, StatusID, SUM(Value) OVER(Partition By AccountID, StatusID ORDER BY OrderID) AS RunningTotal
FROM @Input

[Code] ....

View 2 Replies View Related

SQL Server 2008 :: Loop Through Date Time Records To Find A Match From Multiple Other Date Time Records?

Aug 5, 2015

I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.

E.g.

Query 1

Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'

Table

Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'

Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.

I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.

View 5 Replies View Related

SQL Server 2012 :: Match Name In Different Tables Where Name Order Is Reversed

Aug 6, 2015

CREATE TABLE #NAME1(FULLNAME VARCHAR (100))
INSERT INTO #NAME1(FULLNAME) VALUES('JOHN X. DOE')
INSERT INTO #NAME1(FULLNAME) VALUES('FITZGERALD F. SCOTT')

CREATE TABLE #NAME2(LASTNAME VARCHAR(25), MI VARCHAR(2), FIRSTNAME VARCHAR(25))
INSERT INTO #NAME2(LASTNAME, MI, FIRSTNAME) VALUES('DOE', 'X', 'JOHN')
INSERT INTO #NAME2(LASTNAME, FIRSTNAME) VALUES('FITZGERALD', 'F SCOTT')

My task is to find matches between these two tables on "name."

View 1 Replies View Related

Query To Match Some Fields Out Of Many - HELP...

May 12, 2008

I have a table with 6 fields, and I will have all 6 parameters passed in - is there any way to write a query to give me rows based on matching ANY combination of 4 fields out of the 6 parameters passed in ? This is driving me crazy... short of doing an OR statement for all the different combinations - I have no idea how to do this....




This is what I have so far -

SELECT @nodeMachineType = nodeMachineType,
@nodePKID = NodePKID,
@biosVar = Bios,
@computerNameVar = ComputerName,
@diskVolumeVar = DiskVolume,
@guidVar = Guid,
@macAddressVar = MacAddress,
@motherboardVar = motherboard
FROM Nodes_Active
WHERE
case when Bios = @bios then 1 else 0 end +
case when ComputerName = @computerName then 1 else 0 end +
case when DiskVolume = @diskVolume then 1 else 0 end +
case when guid = @guid then 1 else 0 end +
case when macAddress = @macAddress then 1 else 0 end +
case when MotherBoard = @motherboard then 1 else 0 end >= 4

View 10 Replies View Related

SQL Server 2012 :: Generate Flag To Check Whether Join Condition Match Or Not

Oct 12, 2015

I want to join 2 tables, table a and table b where b is a lookup table by left outer join. my question is how can i generate a flag that show whether match or not match the join condition ?

**The lookup table b for column id and country are always not null values, and both of them are the keys to join table a. This is because same id and country can have multiples rows in table a due to update date and posting date fields.

example table a
id country area
1 China Asia
2 Thailand Asia
3 Jamaica SouthAmerica
4 Japan Asia

example table b
id country area
1 China Asia
2 Thailand SouthEastAsia
3 Jamaica SouthAmerica
5 USA America

Expected output
id country area Match
1 China Asia Y
2 Thailand SouthEastAsia Y
3 Jamaica SouthAmerica Y
4 Japan Asia N

View 3 Replies View Related

Using LIKE Operator To Match Large Text Fields

Apr 5, 2001

I have an idea to use LIKEW opeartor (with te wildcards) to match large (>10Kb) text fields of 'text' and 'ntext' types. Are there known problems here?

Thanks

View 1 Replies View Related

Unable To Match Fields With Identical Contents

Apr 2, 2008

I am having trouble with a select statement where i try to use a variety of methods on two columns in different tables that, hypothetically, have exactly the same contents.

Below are the three Create Table statements, so you can see how everything is set up. The columns zone_monthly.specific and #temp_dec0607c.specifics come from almost the same original source, so I can't fathom how they could actually be different in their contents - I know some of the individual lines are the same.


Create Table category_specifics (ID integer, segment nvarchar(Max), specifics nvarchar(max))

BULK INSERT category_specifics

FROM 'c:USERSTEALDOCUMENTShonda_chartsmonthly_filescategory_list.txt'





Create Table zone_monthly (ID Integer, specific nvarchar(max), zone integer, dec06 money, dec07 money)

INSERT INTO zone_monthly (specific, zone)

SELECT specifics, '12' AS zone

From category_specifics




Create Table #temp_dec0607c (ID integer, specifics nvarchar(max), zone integer, dec07 money, dec06 money)

Bulk Insert #temp_dec0607c

FROM 'c:USERSTEALDOCUMENTShonda_chartsmonthly datazonedec0607.txt'


Now here is the SELECT statement that I am having trouble with - It returns no results, and, as I said before, I am rather certain it should return something. I have tried it with an inner join as well (code included), and again, no results.



SELECT #temp_dec0607c.zone, #temp_dec0607c.dec07, #temp_dec0607c.dec06

FROM #temp_dec0607c

WHERE

#temp_dec0607c.specifics=zone_monthly.specific


SELECT #temp_dec0607c.zone, #temp_dec0607c.dec07, #temp_dec0607c.dec06

FROM #temp_dec0607c

INNER JOIN zone_monthly ON

#temp_dec0607c.specifics=zone_monthly.specific

What on earth is going on?

View 5 Replies View Related

SQL Server 2012 :: Replace All Values In String With Values From Look Up Table

Mar 19, 2014

I have a table that lists math Calculations with "User Friendly Names" that look like the following:

([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])

I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)

The new formulas need to look like the following:

([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])

I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.

How can I accomplish this?

Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.

DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )

[Code] .....

View 3 Replies View Related

Delete Records Are Match On Different Tables

Feb 19, 2007

Hello, Everyone

I had 2 tables contain data:
1) old_data: 601,195 records
firstname
lastname
address
city
state
zip
zip4

2) current_data: 410,185 records
firstname
lastname
address
city
state
zip
zip4
3)Questions: How I write a query make to delete records? From table 'old_data' delete match records reference to table 'current_data' and keep the remain good records are from 'current_data' table.

****Here's below my test query that came up with if there are any error and please provide me any suggestion or new query. Very important task ....Thanks you to all.

/*******
Delete current_data
From old_data Cross Join current_data
Where old_data.FirstName = current_data.FirstName and old_data.LastName = current_data.LastName
and old_data.Address = current_data.Address
********/


RV

View 8 Replies View Related

How To Match Records On Several Substring Functions

Dec 13, 2006

Hello All,I have a sql statement that I'm using to match records on. But i needto use a substring to get the nuber that I need, and I have to useseveral substrings to get the number since its embedded in other text.Here's my sql:use fto_upsselect distinct o.Shipper#, o.InvoiceDate, o.InvoiceNumber, o.ChgType,o.Lading, o.BillTo, o.Name2, o.Address1, o.Address2, o.City, o.State,o.Zip,o.ShipFromName1, o.ShipFromeName2, o.ShipFromAddress1,o.ShipFromAddess2, o.ShipFromCity, o.ShipFromState, o.ShipFromZip,o.PickupDate,o.PickUpRecord, o.TrackingNumber,o.BOL, o.Reference2, o.ServiceType, o.ServiceZip, o.Zone, o.Weight,o.PublishedCharge, o.IncentiveCredit, o.InsuredCharge, o.BilledCharge,o.DelAreaSurcharge,m.FRRDSSTrackingNbr,m.FRRDSSShipmentNbr, m.FRRDSSDivision,m.FRRDSSDeptNbr, m.FRRDSSVendorNbr, m.FRRDSSChargeBackNbr,m.FRRDSSShipmentCreateDate,m.[UPS Units],m.[UPS % Units to Total]into dbo.tempOutBoundTablefrom upsOutbound o left join msoutboundhistory m on right(o.bol,7) =m.FRRDSSShipmentNbr and o.bol = m.FRRDSSShipmentNbr********How do I select the records using substring(o.bol,4,7) to add to theabove query? How do I inner join the records based on adding thesubstring extracted o.bol?

View 3 Replies View Related

Query To Get Rows Which Match With All Given Values

Sep 10, 2007

Hi all,

I would like have your help about a query.
In fact, I have a query to retrieve the rows for specific ID.
Like that:

SELECT *
FROM TblUser u
WHERE EXISTS

(

SELECT *

FROM TblScore s

WHERE s.FKIDUser = PKIDUser

)

With this query, I retrieve all users for which ones there are some scores.
Now, I need to get only users with specific score.
In the table TblScore, there is a column ScoreValue.
This column contains a value between 1 and 15

I would like to retrieve the users having score equal to 2,4 and 6
I could add a where clause like that: "and scorevalue in (2,4,6)"
But I want only users having these and only these scores, not less, not more.

So if an user has the following scores: 2,4,6,8, I don't want to retrieve it
If an user has the following scores: 2;4, I don't want to retrieve it.
If an user has the following scores: 2,4,6, I want it.

Someboy would have an idea at my problem ?

Thanks in advance
Jerome

View 7 Replies View Related

Transact SQL :: Get Row If All Column Values Match

Nov 10, 2015

Please don’t look for table design satisfies NF, this is just for example

Student table
Id            StudentId
1                    10
2                    20
3                    30

Student_Class
Id            ClassId
1              100
1              101
1              102
1              103
2              100
2              101

When I give studentId 10 and class ids = 100, 101, 102, 103 then result should be get row from student table only if all given class ids matched.

Result:
Id            Student ID          ClassId
1              10                          100, 101, 102, 103

Case 2: Student Id: 10    class Ids = 100, 101 the no results since all the class ids for student 10 in Student_Class are not matching with the given class Ids parameter.

View 11 Replies View Related

SQL Server 2012 :: How To Match Two Different Date Columns In Same Table And Update Third Date Column

May 30, 2015

I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.

I have table containing 5 columns.

caseid
referenceid
startdate
enddate
caseopendate

[code]...

View 4 Replies View Related

Aggregating Values That Match Aggregate Result

Sep 23, 2015

The actual schema I'm working against is proprietary and also adds more complication to the problem I'm trying to solve. So to solve this problem, I created a mock schema that is hopefully representative. See below for the mock schema, test data, my initial attempts at the query and the expected results.

-- greatly simplified schema that makes as much sense as the real schema
CREATE TABLE main (keyvalue INT NOT NULL PRIMARY KEY, otherdata VARCHAR(10));
CREATE TABLE dates (datekeyvalue INT NOT NULL, keyvalue INT NOT NULL, datevalue DATE NULL, PRIMARY KEY(datekeyvalue, keyvalue));
CREATE TABLE payments (datekeyvalue INT NOT NULL, keyvalue INT NOT NULL, paymentvalue INT NULL, PRIMARY KEY(datekeyvalue, keyvalue));

[Code] ....

Desired results:

SELECT 1 AS keyvalue, 'first row' AS otherdata, '2015-09-25' AS nextdate, 30 AS next_payment
UNION ALL
SELECT 2, 'second row', '2015-10-11', 150
UNION ALL
SELECT 3, 'third row', NULL, NULL

I know I'm doing something wrong in the last query and I believe another sub-query is needed?

Let me answer a few questions in advance:

Q: This schema looks horrible!
A: You don't know the half of it. I cleaned it up considerably for this question.

Q: Why is this schema designed like this?
A: Because it's a 3rd-party mainframe file dump being passed off as a relational database. And, no, I can't change it.

Q: I hope this isn't a frequently-run query against a large, high-activity database in which performance is mission-critical.
A: Yes, it is, and I left out the part where both the date and the amount are actually characters and have to pass through TRY_CONVERT (because I know how to do that part).

View 2 Replies View Related

Left Outer Join Losing Records Without A Match

Nov 30, 2004

I have 2 tables GLSUMMARY and GLBUDGET, they are identical.
I am joining them together with a left outer join from the SUMMARY to the BUDGET but when I dont have a matching BUDGET record on the join the SUMMARY gets dropped as well :eek:

Any help will be appreciated!

Here is the query!
SELECT s.conu as CoNu, s.deptnu as DeptNu,
s.fundnu as FundNu, s.acctnu as AcctNu,
Sum(isNull(Amt01,0)) as Amt01,
Sum(isNull(Amt02,0)) as Amt02,
Sum(isNull(Amt03,0)) as Amt03,
Sum(isNull(Amt04,0)) as Amt04,
Sum(isNull(Amt05,0)) as Amt05,
Sum(isNull(Amt06,0)) as Amt06,
Sum(isNull(Amt07,0)) as Amt07,
Sum(isNull(Amt08,0)) as Amt08,
Sum(isNull(Amt09,0)) as Amt09,
Sum(isNull(Amt10,0)) as Amt10,
Sum(isNull(Amt11,0)) as Amt11,
Sum(isNull(Amt12,0)) as Amt12,
Sum(isNull(Amt13,0)) as Amt13,
Sum(isNull(Bud01,0)) as Bud01,
Sum(isNull(Bud02,0)) as Bud02,
Sum(isNull(Bud03,0)) as Bud03,
Sum(isNull(Bud04,0)) as Bud04,
Sum(isNull(Bud05,0)) as Bud05,
Sum(isNull(Bud06,0)) as Bud06,
Sum(isNull(Bud07,0)) as Bud07,
Sum(isNull(Bud08,0)) as Bud08,
Sum(isNull(Bud09,0)) as Bud09,
Sum(isNull(Bud10,0)) as Bud10,
Sum(isNull(Bud11,0)) as Bud11,
Sum(isNull(Bud12,0)) as Bud12,
Sum(isNull(Bud13,0)) as Bud13
FROM shelbydb.shelby.GLSummary S
left OUTER JOIN shelbydb.shelby.GLBudget B
on
(s.begindate = b.begindate)
and (s.acctnu = b.acctnu)
and (s.conu = b.conu)
and (s.deptnu = b.deptnu)
and (s.fundNu = b.fundNu)
WHERE
(s.begindate = '1/1/2004'
and b.begindate = '1/1/2004')
group by
S.conu, S.deptnu, S.fundnu, S.acctnu,
b.conu, b.deptnu, b.fundnu, b.acctnu

View 3 Replies View Related

Displaying The Values That Dont Match And Replacing By NULL

Feb 6, 2003

Hi,

Im having a couple of problems with SQL.

I have this query...

SELECT Band.Name, Member.Name
FROM Member
JOIN MemberOf ON Member.Mid = MemberOf.Mid
JOIN Band On MemberOf.Bid = Band.Bid
WHERE MemberOf.Instrument = 'keyboards';

which basically shows me the name of the bands who had keyboard players. I would like it to also display the names of the band who didnt have keyboard players, replacing the keyboard players name with "NULL".

so... my questions are :)

how do u get it to display the records that do not match the condition, and how do u get it to replace the keyboard players name with "NULL" when they do not match the condition.

PS. The three tables are

Band. Which has Bid as a primary key.
Member. Which has Mid as a primary key.
MemberOf. Which links these through its two foreign keys Mid and Bid.

Thanks for your help! (assuming someone does)

View 2 Replies View Related

SQL Server 2012 :: How To Update Fields Having Same Names And DOB

Apr 11, 2014

Create Table #Temp (ID int not null primary Key, Name varchar(25) not null, DOB datetime not null, Sex char(1), Race char(1), Height int, Weight int)

insert #Temp
select 1, 'Kenneth', '1963-02-26 00:00:00.000', 'M','C', 516, 160
union
select 2, 'Kenneth', '1963-02-26 00:00:00.000', NULL,NULL, NULL, NULL
union
select 3, 'William', '1962-06-28 00:00:00.000', 'M','C', 600, 223

[Code] .....

/* Expecting Output */

select 1 as ID, 'Kenneth' as Name, '1963-02-26 00:00:00.000' as DOB, 'M' as Sex,'C' as Race, 516 as Height, 160 as Weight
union
select 2, 'Kenneth', '1963-02-26 00:00:00.000', 'M','C', 516, 160
union
select 3, 'William', '1962-06-28 00:00:00.000', 'M','C', 600, 223
union

[Code] .....

View 5 Replies View Related

SQL Server 2012 :: XML - Updating Parts Of Fields

May 30, 2014

I have a table where one of the fields contains XML as in the following. Is there an SQL update statement that can change any occurrences of timezoneIdfrom 0 to 1, and timezone from America/New_York to America/Chicago and time to time - 1 hour?

<scheduleitem><schedule><frequency><weekly weeklyInterval="1"><WED/></weekly></frequency><startDate>2008-08-05</startDate><time>22:30:00</time><timezoneId>0</timezoneId><timezone>America/New_York</timezone></schedule></scheduleitem>

After the query is run, rows like the above would be changed to

<scheduleitem><schedule><frequency><weekly weeklyInterval="1"><WED/></weekly></frequency><startDate>2008-08-05</startDate><time>21:30:00</time><timezoneId>1</timezoneId><timezone>America/Chicago</timezone></schedule></scheduleitem>

View 6 Replies View Related

SQL Server 2012 :: Max Count Grouped By All The Fields

Sep 15, 2015

Trying to get the max count grouped by all the fields. All the fields are the same, but trying to get the location for each physician that has the largest number of patients.

if the output for the sql below is:

101, 10, Jon, Smith, MD, Ortho, OR, 15
101, 10, Jon, Smith, MD Ortho, 1, 12
101, 10, Jon, Smith, MD, Ortho, 2, 10
24, 3, Mike, Jones, MD, Neuro, OR, 21
24, 3, Mike, Jones, MD, Neuro, 2, 43

I'd like to have the query rewritten so the results are as:

101, 10, Jon, Smith, MD, Ortho, OR, 15
24, 3, Mike, Jones, MD, Neuro, 2, 43

SELECT
a.attendingmdkey,e.[provider id],e.[first name],e.[last name],e.title,e.specialty,l.locationname,count(a.accountid) as Count
FROM accounts a
left outer join location l on l.locationid=a.locationid
left outer join providers e on e.[ProviderID]=a.attendingmdkey
where a.dischargedate>='2014-12-01' and a.dischargedate<'2015-01-01'
and a.divisioncode in ('1','2','$')

group by a.AttendingMdKey,e.[provider id],e.[first name],e.[last name],e.title,e.Specialty,l.locationname
order by a.AttendingMdKey

View 3 Replies View Related

ODBC Driver (for SQL Server 2005) Unable To Retrive CRecorset's Fields Values

Jan 24, 2007

I am using MFC ODBC classes in my VC++ application for database accesss (on a SQL Server 2005 Database).
After opening a Recordset I have to use the CRecordset::GetFieldValue( LPCTSTR lpszName, CDBVariant& varValue, short nFieldType = DEFAULT_FIELD_TYPE ) method of the CRecordset object to obtain field values. The above method works fine with ODBC drivers for Jet but when I use ODBC for SQL Server it throws the following exception.

State:S1002,Native:0,Origin:[Microsoft][
ODBC SQL Server Driver] Invalid
Descriptor Index

I don't understand why the Microsoft SQL Server ODBC Driver is throwing this
exception.

Please help me!

Michael.

View 4 Replies View Related

Search Query - Analysis On Duplicate Records Based Off Of Several Match Keys

Jun 7, 2014

I'm trying to do some analysis on duplicate records based off of several match keys. I have a data set of approximately 30,000 people and the goal is to determine how many duplicate matches are in the system.

How would I write an SQL statement that looks for the following pieces of information. (I'm not using one person as an example; I need to do an analysis on the entire data set)

First name (exact match)
Last name (exact match)
Address line 1 (exact match)
Postal code/zip (exact match)

First Initial (exact match)
Last name (exact match)
DOB exact match
Postal code/zip (exact match)

View 1 Replies View Related







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