I have two tables tbl1 and tbl2, which I do a full outer join between tbl1 and tbl2 on recordId field. The recordId field is not a key in either of the tables.
If there is one row each for a recordId 123 in both tables, the select query would return one combined row.
If tble1 had two rows for recordId 123, and tbl2 had one row for the same, it would return to rows repeating the data in tbl2.
If tbl2 had two rows and bl1 had one row, it would return two rows in output repeating the data in tbl1.
Is the above correct? Would the result be different if it was an inner join instead of full outer join?
Is it ever possible that one of the two records with recordId 123 will be dropped from the result?
create table dbo.#Status( ID varchar(50), Status varchar(50), EffectiveStartDate datetime, EffectiveEndDate datetime, Is_Current bit
[code].....
I want result as the attached image.
Create table query for result is: CREATE TABLE dbo.#Result( ID varchar(50), Fee varchar(100), Bill varchar(50), A_Date date, B_Date date, Status VARCHAR(50), EffectiveStartDate datetime, EffectiveEndDate datetime )
I have a table of "applicants" with unique applicant id and another table "reviews" with reviews which has unique id and Emplid and contains general program name like Math and then may contain 3 addition rows for specific program like Calculus, algebra, geometry etc.
There may or may not be a record for each applicant id in table reviews or there can be 1 or more than one record in reviews based on level of review( General or Specific).
All the general reviews has “Math” as Program_code but if there are more reviews, they can have Program_code like “Cal” , “Abr”, “Geo”
I want to join the tables so I can get all the records from both the tables where Program_code in reviews table is “Math” only.
That is I want to join the table and get all the records from reviews table where the program_code is “Math” only How can I do that?
insert into SEL values(194,'6/1/2006 12:00:00 AM',730,11) insert into SEL values(194,'6/1/2006 12:00:00 AM',930,4) insert into SEL values(194,'6/1/2006 12:00:00 AM',1830,10) insert into SEL values(194,'6/1/2006 12:00:00 AM',1930,20) insert into SEL values(194,'6/1/2006 12:00:00 AM',2130,14) insert into SEL values(194,'6/1/2006 12:00:00 AM',2230,0) insert into SEL values(195,'6/1/2006 12:00:00 AM',730,22) insert into SEL values(195,'6/1/2006 12:00:00 AM',930,43) insert into SEL values(195,'6/1/2006 12:00:00 AM',1830,0) insert into SEL values(195,'6/1/2006 12:00:00 AM',1930,54) insert into SEL values(195,'6/1/2006 12:00:00 AM',2130,21) insert into SEL values(195,'6/1/2006 12:00:00 AM',2230,6)
CREATE TABLE [dbo].[station_info]( [STATE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [STATION_NAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MAIN_ID] [int] NOT NULL ) ON [PRIMARY]
insert into station_info values('SEL','PUCHONG',196) insert into station_info values('JHR','BulohKasap',5)
Above script showing table as follow SEL MAIN_ID | DATE_TAKEN | TIME | DAILY_RAINFALL -------------------------------------------------------- 194 | 6/1/2006 12:00:00 AM | 730 | 11 194 | 6/1/2006 12:00:00 AM | 930 | 4 .......... .......... 202 | 6/1/2006 12:00:00 AM | 450 | 23 .......... .......... *This table storing DAILY_RAINFALL everyday from time to time for each MAIN_ID.
station_info STATE | STATION_NAME | MAIN_ID --------------------------------------- SEL | Puchong | 194 JHR | BulohKasap | 5 ......... ......... *This table storing MAIN_ID description. Main_ID is a primary key.
1. 1 day having many TIME. So, we only take which time having MAX(DAILY_RAINFALL) per day per MAIN_ID to do the SUM(DAILY_RAINFALL) for that month.
So far, i've this query SELECT CAST(CAST(m.Month AS varchar(2)) + '/1/' + CAST(m.Year AS varchar(4)) AS datetime) AS DATE_TAKEN,m.MAIN_ID,m.DAILY_RAINFALL FROM ( SELECT MONTH(t.Date_Taken) AS Month, YEAR(t.Date_Taken) AS Year, t.Main_ID, SUM(t.Daily_Rainfall) As Daily_Rainfall FROM ( SELECT t1.MAIN_ID,t1.DATE_TAKEN,t1.TIME, t1.DAILY_RAINFALL FROM (SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TAKEN,MAIN_ID ORDER BY DAILY_RAINFALL DESC) AS RowNo,* FROM dbo.SEL)t1 INNER JOIN dbo.STATION_INFO t2 ON t2.MAIN_ID=t1.MAIN_ID AND t1.RowNo=1 AND t1.DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-12,GETDATE()),101) AND t1.DATE_TAKEN<=CONVERT(VARCHAR(10), GETDATE(), 101) WHERE t2.STATE='SEL')t GROUP BY MONTH(t.Date_Taken),YEAR(t.Date_Taken),t.Main_ID)m ORDER BY Main_ID,Date_Taken *Assume GETDATE()=6/10/2007
I'm stuck when joint table below. The purpose of joint is to pickup [cumrf1year] column (if DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-12,GETDATE()),101) AND t1.DATE_TAKEN<=CONVERT(VARCHAR(10), GETDATE(), 101)) and pickup [month_year] value depend on DATE_TAKEN (if DATE_TAKEN=6/1/2006, [month_year]=6/30/2006, if DATE_TAKEN=7/1/2006, [month_year]=7/31/2006). MAIN_ID is foreign key to relate [longterm_rf_temp] table below.
insert into longterm_rf_temp values(194,'6/30/2006',207.94,550.7,850.7,1150.7,1450.7) insert into longterm_rf_temp values(194,'7/31/2006',200.64,590.4,858.7,1260.7,1550.7) insert into longterm_rf_temp values(194,'8/30/2006',222.64,390.4,958.7,1460.7,1750.7) insert into longterm_rf_temp values(195,'6/30/2006',217.94,550.7,840.7,1150.7,1324.7) insert into longterm_rf_temp values(195,'7/31/2006',202.64,590.4,858.7,1260.7,1659.7) insert into longterm_rf_temp values(195,'8/30/2006',222.64,490.4,958.7,1460.7,1733.7)
Am new to sql, and I wold appreciate help with optimising the folloing example. The result of the example should be to list a result with details of the Column names:
OPBal| Receipt| IssTrns| Transfer| ClBal
SELECT dbo.inventory.location, dbo.inventory.itemnum, (select sum(dbo.matrectrans.linecost) where dbo.matrectrans.issuetype LIKE 'RECEIPT' ) As Receipt, ( select sum(dbo.matrectrans.linecost)where dbo.matrectrans.issuetype LIKE 'TRANSFER' ) As Transfer, ( select(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost)where dbo.IST_ITEMDETAIL.logdate='2006-07-20' ) As OpBal, ( select (IST_ITEMDETAIL.curbal*IST_ITEMDETAIL.avgcost)where IST_ITEMDETAIL.logdate='2006-08-21' ) As ClBal, ( sum(matusetrans.linecost) ) As IssTrnf FROM dbo.matrectrans, dbo.matusetrans, dbo.IST_ITEMDETAIL , ( dbo.inventory inner JOIN dbo.item ON dbo.inventory.itemnum = dbo.item.itemnum AND dbo.inventory.orgid = dbo.item.orgid )
WHERE dbo.inventory.location = dbo.matusetrans.storeloc AND dbo.inventory.itemnum = dbo.matrectrans.itemnum AND dbo.inventory.siteid = dbo.matrectrans.siteid
OR dbo.inventory.location = dbo.matrectrans.tostoreloc AND dbo.inventory.itemnum = dbo.matusetrans.itemnum AND dbo.inventory.siteid = dbo.matusetrans.siteid OR dbo.inventory.location = dbo.matrectrans.fromstoreloc
OR dbo.inventory.location = dbo.ist_itemdetail.location AND dbo.inventory.itemnum = dbo.ist_itemdetail.itemnum GROUP BY dbo.inventory.location, dbo.inventory.itemnum,dbo.matrectrans.issuetype,(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost), dbo.IST_ITEMDETAIL.logdate,dbo.IST_ITEMDETAIL.curbal,dbo.IST_ITEMDETAIL.avgcost
I need to divide amount in joint account. So if joint account has 2 account holders & amount is 35622.15 then one person should have 17811.08 and other person should have 17811.07
If I used below query it just give me 17811.08 for both account holders so when we sum it it's one penny extra.
I need some help.I am trying to write a query which does the followingSELECT * from table1 where field1=(SELECT distinct field1 FROM table1WHERE field2='2005' or field2='2010')I need all the values from table1 which match any value from field 1from the subquery.Any help is appreciated.thanks
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
I want to learn if its possible to get ID in trigger and set it to another field.
I can do it by using scope_identity and using update command but I dont want to run another update command.
This is a very heavy loaded system that runs maybe 10-30 transactions on every second and I cannot afford that. I come up with a solution by using sequences but if I could use ID would be better.
this is the Select statment I am trying to use so that I can update the Asset in Assets table and the ESN number in the ESN table. But using ESNId and AssetId.
This is my query that returns the ones are not assigned to and Asset
SELECT DISTINCT EsnId, EsnNumber FROM dbo.ESNTracking WHERE (EsnId NOT IN (SELECT EsnId FROM dbo.EsnAsset))
I have two tables I need to join but there are 2 fields which theycould be joined on.Using the example Tablles, TableA and TableB below;TableAID1 ID2 Qty1 Null 42 A 5Null B 6TableBID1 ID2 QtyNull A 63 B 64 Null 7Null C 8I want to create TableC which will look like this;ID1 ID2 TableA.Qty Tableb>Qty1 Null 4 Null2 A 5 63 B 6 64 Null Null 7Null C Null 8Any ideas?Regards,Ciarn
I imported data into a database and the first character in an ID Field starts with %. This is causing many problems for the application. Unfortunately, this field exists in 72 of 128 tables in the database. Is there a way to LTRIM every ID field where the first character is %? This is easy in 1 Table but how do I apply it to all 72 tables at once? Thanks for for your assistance
I have a Function where I want to put a character in a particular position in a Destination string. For example, I have a 5 character field containing, say, "12345". I would like to put the letter "A" in the third position resulting in "12A45". I'm trying the following
DECLARE @RetVal as varchar(5) SET SUBSTRING(@RetVal,3,1) = 'A'
I'm getting a compiler error
"incorrect syntax near '@RetVal'. Expecting '(' or SELECT"
Am I using the wrong Function to do this? If so, what should I be using?
I have a query that displays a bunch of fields, specifically a createdon field and closedate field.I need to find the diff in days between date1 and date2 and only disiplay those results.For example: where the NumDays = a certain value. For example, 81.I have the NumDays displaying in the query, but I don't know how to reference these values in the where clause.
Hi,I have a 2 tables called 1.tblRisk which consists of Ref(pk), subject, status, staff & Dept(fk)2.tblDept which has Ref(Pk) & DepartmentHow do i get it to populate Department, when tblRisk Ref's Dept matches the Ref in tblDept i am using SQL Server 2000best regards
I'm managing an amature online university and I've been charged with creating a deans list. I have a table for exam results for each course.. currently totaling 5. I have an employeeID column and a total_points column in each table. Sooooo I need to join all the tables and get an average for total_points where the employeeID matches across tables. I have no idea how to write this select.. any help?
So for years I was using the int identity(1,1) primary_key for all the tables I created, and then in this project I decided, you know, I like the uniqueidentifier using newsequentialid() to ensure a distinctly unique primary key.
then, after working with the php sqlsrv driver, I realized huh, no matter what, i am unable to retrieve the scope_identity() of the insert
So of course I cruised back to the MSSMS and realized crap, I can't even make the uniqueidentifier an identity.
So now I'm wondering 2 things...
1: Can I short cut and pull the uniqueidentifier of a newly inserted record, even though the scope_identity() will return null or 2: do I now have to add a column to each table, keep the uniqueidentifier (as all my tables are unified by that relationship) and also add a pk field as an int identity(1,1) primary_key, in order to be able to pull the scope_identity() on insert...
I am trying to update records based on the results of a query with a subquery.
The result set being produced shows the record of an item number. This result produces the correct ItemNo which I need to update. The field I am looking to update is an integer named Block.
When I run the update statement all records are updated and not the result set when I run the query by itself.
Below is the code I am using in an attempt to update the block column but it updates all records and not the ones which I need to have the Blocked field set to 1.
Update #items set Blocked = 1 Where Exists ( SELECT ItemNo=MAX(CASE rn WHEN 1 THEN ItemNo END) --,SearchNo --,COUNT(*)
[Code] ...
Why is the update changing each record? How can I change the update to choose the correct records?
How do I reliably symmetrically encrypt a single node in an XML field in my SQL Server 2012 database? I know I cannot use SQL Server encryption to encrypt an XML field and I have been getting unpredictable results with a home grown solution.
I have a list of movies that show throughout the year. I would like to assign a unique numeric identifier to each text field.
I have provided some sample data with the output I would like. The Movie_ID in the sample data is just made up, feel free to assign any numeric identifier, preferably of the same length but not a necessity.
I have created table in which there are four columns (id, date, parcelname, parcelnumber) and 10 rows. I want to count record of the column parcelnumber but condition that, in between two different dates the record should be counted.
I have some data where a bit value changes over time and I want to rank it by the repeating groups, how do I write the SQL so that I get the result below?
I want to sort by create date, and every time the bit changes value we start the rank over.
If I partition by the bit field, it will just group the entire data set by the bit field.
I am needing to combine the Notes field where Number and date are the same...For example
for Number 0000000003 I need notes to Read ('CHK # 2452 FOR $122.49 REJECTED AS NSF ON 2/25/15') the note counter is different for each row, and is combination of special char, 0-Z and looks like the (!) depicts the start of a new Number.
