I have two queries from two different tables ex ABC and BCD. For table ABC, according to my query, I got 11 records ; for table BCD I only got 9 records.
Bottom line: I would like to see only 11 records from Table ABC including certain data from table BCD after I joined this two tables.
However, no matter what I did I always got 99 records when I joined.
I have 3 tables as per following:
orddet
OrderProductQtyOrd
1 Item1 20
2 Item1 10
3 Item2 10
4 Item1 5
4 Item2 5
ordhead
OrderDate
110/06/2015
205/07/2015
307/06/2015
415/08/2015
product
ProductdescMinQty
Item1This is 110
Item2This is 220
I want to pull only the 1 line for minqty for an item as follows
OrderProductQtyOrddate minqty
1 Item1 20 10/06/2015 10
2 Item1 10 05/07/2015
3 Item2 10 07/06/2015 20
4 Item1 5 15/08/2015
4 Item2 5 15/08/2015
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
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?
I have documentation in the form of extended properties for tables which are subscribers in a replication scheme. The documentation describes the tables in reference to their replication scheme. I don't want to apply them to the source and have them published.I can't apply the extended properties receiving the error, 'don't have permission' yet I am DB creator on all systems. The theory is that I can't modify the subscription. Which makes sense.Can I turn off the replication, apply the extended properties, then turn on replication without causing harm?
View 0 Replies View RelatedI am new in SQL and i need do a query where I need sum values from 2 tables, when i do it the Sum values are not correct. this is my query
SELECT D.Line AS Line, D.ProductionLine AS ProductionLine, D.Shift AS Shift, SUM(CAST(D.DownTime AS INT)) AS DownTime,
R.Category, SUM(Cast(R.Downtime AS INT)) AS AssignedDowntime,
CONVERT(VARCHAR(10), D.DatePacked,101) AS DatePacked
FROM Production.DownTimeReason R
left JOIN Production.DownTimeHistory D
[Code] .....
I have a ssis package, which runs on date parameter. If we dont specify the date it always load data for yesterday's date. And if we give any specific date like '2015-05-10', It should load for that date. How can we achieve this dynamically (using package configuration)? Once we load for any specific date, package should be set for yesterday's date dynamically. How to achieve this as I am new to SSIS.
View 2 Replies View RelatedRefer to my query thread on msdn SQL site, how I can achieve the result.
[URL] .....
I have a XML data passed on to the stored proc in the following format, and within the stored proc I am accessing the data of xml using the nodes() method
Here is an example of what i am doing
DECLARE @Participants XML
SET @Participants = '<ArrayOfEmployees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Employees EmpID="1" EmpName="abcd" />
<Employees EmpID="2" EmpName="efgh" />
</ArrayOfEmployees >'
SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,
Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName
FROM
@Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node)
the above query produces the following result
EmployeeID EmployeeName
--------------- -----------
1 abcd
2 efgh
How do I join the data coming out from the above query with another table in the database, for example Employee in this case
i.e. somewhat like this (note the following query does not perform the join correctly)
SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,
Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName
FROM
@Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node)
INNER JOIN
Employee EMP ON EmployeeID = EMP .EmployeeID
My desired output after the join would be
EmployeeID EmployeeName Email Home Address
--------------- ----------- --------------- -----------
1 abcd abcd@abcd.com New York
2 efgh efgh@efgh.com Austin
I have a query which wants to union join the data. no matter how many times I tried, I got an error. How to change my union query?
select distinct b.lev5 AS "LEVEL 1",b.lev5NAME, C.lev7 "FUND", C.lev7NAME,round (sum(a.data),2) AS AMOUNT
(Select distinct b.lev5
from bf_data a
inner join bf_orgn_cnsl_tbl b
on a.bf_orgn_cd = b.bf_orgn_cd
[Code] .....
There are 2 tables
1. Table Name : TRNNUM Field Name : TRNID
2. Table Name : TRNPRD Field Name : TRNID, PRDID
Sample of tables
Table TRNNUM
TRNID
-----
1234
2565
3458
Table TRNPRD
TRNID PRDID
----- -----
1234 AA
1234 BB
1234 CC
2565 CC
2565 EE
2565 FF
2565 HH
3458 AA
3458 BB
Desired output is
TempTable
TRNID PRDID PRDSEQ
----- ----- ------
1234 AA START
1234 BB START:AA
1234 CC START:AA:BB
2565 CC START
2565 EE START:CC
2565 FF START:CC:EE
2565 HH START:CC:EE:FF
3458 AA START
3458 BB START:AA
SQL 2008 R2
I have a partitioned table in which one of the partitions is on the Primary filegroup. I want to move the data off of that Primary filegroup, and and on to a new filegroup named RTFG6.
Scheme and function currently defined as:
CREATE PARTITION SCHEME [PS1_Left_id] AS PARTITION [PF1_Left_id] TO ([RTFG1], [RTFG2], [RTFG3], [RTFG4], [RTFG5], [PRIMARY])
CREATE PARTITION FUNCTION [PF1_Left_id](int) AS RANGE LEFT FOR VALUES (10, 15, 35, 48, 53)
I've tried split and merge, and for whatever reason, always end up with the Primary filegroup holding data.
How do i get it off of Primary completely, and onto RTFG1 to RTFG6?
I don't want to export to a holding table and re-create the table if i can avoid it, due to identity columns and relationships with multiple tables.
I am developing the SSIS and stuck on copying specific files.
1. We receive CSV file to our drive on a daily basis.
2. The csv file name has the last 8 digits formatted with the yyyymmdd. For example, the file name might be abcdef_20150226.csv This means it will be our CSV file for today, Thursday, February 26, 2015.
3. There are a lot of files in this directory.
[URL]
What we would like to do:
Add the constraints (or expression) that will copy the files from this directory to another directory that have the date equivalent to Monday only. For example, the file abcdef_20150226.csv will not be copied because it is Thursday file. But the abcdec_20150223 will be copied to a new Directory because it is Monday.
Hi friends,I need to select columns for specific data from all tables in DataBasePls give me reply asap Regards,M.Raj
View 4 Replies View RelatedI have a ssis package which runs daily. This ssis package has couple of execute sql tasks which load data for yesterday's transaction. Ex.
INSERT INTO Shipped (Div_Code, shipment_value, ship_l_id, shipped_qty, shipped_date, whse_code,
ord_id, ship_id, ship_l_ord_l_id, Created_date) select ord.DIV_CODE as div_code, ship.SHIPMENT_VALUE as shipment_value, ship_l.SHIP_L_ID as ship_l_id, ship_l.SHIPPED_QTY as shipped_qty, ship.SHIPPED_DATE as shipped_date, ship.WHSE_CODE as whse_code, ord.ORD_ID as ord_id, ship.SHIP_ID as ship_id, ship_l.ord_l_id as ship_l_ord_l_id, Getdate() as Created_date from SHIP ship, ORD ord, SHIP_L ship_l where ship.SHIPPED_DATE=(dateadd(day, -1, CONVERT(VARCHAR(10),GETDATE(),120))) and ship.WHSE_CODE='WPP' and ord.ORD_ID=ship.ORD_ID and ship.SHIP_ID=ship_l.SHIP_ID
All execute sql task has query like above query. and in some query we have date filter which loads data for yesterday. Ex. one query has ship.SHIPPED_DATE=(dateadd(day, -1, CONVERT(VARCHAR(10),GETDATE(),120))). some other query has ord.trans_date=(dateadd(day, -1, CONVERT(VARCHAR(10),GETDATE(),120))). this package runs daily through sql server job, so It loads data for yesterday. Now If i want to run for any particular date, How could we achieve from ssis?
I would like to know about the DML process (Insert/update /delete) in a particular table .. it is like change tracking but I also want to know the modification date
I know CDC ( Change data capture ) but unfortunately it needs SQL 2008 developer/enterprise edition and my SQL server is SQL 2008 STANDARD edition.
I know very little about Extended Events, but I know it is supposed to be more powerful than Profiler. The SQL Instance involved is 2008R2. I was asked whether we could capture when a stored proc was called with a certain parameter. So for example, if dbo.usp_mystoredproc is called and is passed a value of '12345a' for @customer, can that be captured? I would want to know the time it was called, the parameter and parameter value. Probably would be interested in the SPID or LoginName as well.
View 4 Replies View RelatedHere's what my table looks like;
UniqID | Code |
1 | ABC
1 | 123
2 | ABC
3 | ABC
4 | ABC
4 | ABC
I only want to UniqIds that only have the CODE of ABC... and if it contains ANYTHING other than ABC then It doesnt return that UniqID... Now keep in mind there's multiple different codes.. I'm just looking for a bit of code that drops any ID's that don't have my criteria.
We have a database in SQL Server 2008 R2 with mirroring and want that replication is done by dedicated network.We stop the endpoint and when we try to run the following command, syntax error occurred:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '192.168.1.14'.
What is the correct syntax of the command line below?
ALTER ENDPOINT Endpoint_Mirroring AS TCP (LISTENER_IP = '192.168.1.14')
I want to delete all backup files from a folder older than a specific date. But if I use the beklow query, I need to pass how many days of older backup files I need to delete whereas in my case, I dont know how many days/month/syears of old backup files are there in the backup folder.
EXEC xp_cmdshell 'FORFILES /p c:BACKUP /s /m *.sql /d -30 /c "CMD /C del /Q /F @FILE"'
I have 2 tables , one is Broker and Other is Booking, Booking table has 4 brokers codes as foreign keys with other columns, like
Booking
id Broker1 Broker2 Broker3 Broker4
1 100 101 102 101
2 101 102
brokers can bu null in booking (means no broker)
Brokers
BrokersCode BrokerName
100 John
101 muller
102 ABC
Now my requirement is to show broker Name (John, Muller, ABC) against their codes in table Booking.
i tried with left outer join, but this is not working after join in result it shows more rows in booking table as original.
(No of records in booking table should be same after join as before, the thing is only to show brokers names) .
Any help?
Thanks
I have a problem...I have two tables, patient_tran and patient_plan. Patient_tran has case_entry_date and patient_id,patient_plan has patient_id, plan_Id, plan_eff_date and plan_term_date. I need to join these two tables and get the patient_id, plan_id but the plan_id should be the plan_id where the entry_date falls between plan_eff_date and plan_term_date. If doesn't match the criteria then pick up the plan_id where the plan_term_date is null, if there's no null plan_term_date then pick up the plan_id with the most recent plan_term_date. In patient_plan table, there's could be more than one plan per patient. How can I do this? Can anyone please help, will be most appreciated.
Thanks in advance!!
I have Two tables @master and @child
Master Table :
MasterID EntryNumber BranchId IsstockIn
1 1 1 1
2 1 1 0
Child Table:
CEntryNumber CBranchID EntryQty
1 1 10
1 1 20
1 1 -5
1 1 -4
My Query:
Select SEC.EntryQty from Item.StockEntryChild SEC
where SEC.CEntryNo =
(
select SEM.EntryNumber from item.StockEntryMaster SEM
where SEC.CBranchID=SEM.BranchID and SEC.CEntryNo=SEM.EntryNumber and SEM.MasterID=1 and SEM.isStockIn=1
)
My Result:
EntryQty
10
20
-5
-4
Expected Result:
10
20
I am looking for a query where in I can have a conditional statement within inner join after ON statement as shown below.
Declare @roleid int
select @roleid = roleid from Role where Name ='Admin'
select empid,empName,deptName from employee em
inner department dm on CASE when @roleid>0 then 1=dm.RoleId else em.RoleId=dm.RoleId end
I have a SQL database that has multiple tables I need to combine for an analytics presentation. The tables are PRODUCTS (contains product information and UPC), STORES (Store number, name and address), DATES (List of dates that data is published on), and DATA (quantity, by store, by UPC, by date). I am trying to combine these so that i have one table with a row for each UPC on each day at each store. Also, I would like to lookup values for year ago quantities at the same store for the same UPC. Is there a way to structure a query to provide all of this information? I know it will be a lot of rows, but I will filter by product prior to pulling the data.
I think the solution requires a subquery in the FROM portion of the statement, but I am not sure how to structure it. Here are the Fields:
Products.UPC, Data.UPC, Data.Store_Num, Stores.StoreNum, Data.StartDate, Dates.DateKey, and finally the reason we are here Data.Qty
I am using an Azure SQL Database.
Selecting the data from these two tables? The columns 'host_ext_id' have the same data BUT in 'adrmst' all data is preceeded by A0000.
table is 'adrmst'
Host External IDAddress NameAddress Line 1Address Line 2City StatePostal Code
A000042401 T-3803VC 1530401 00 WENGER STTOPEKA KS66609
A000042402 CO INC 960 PP TOMLIN MILL RDSTATESVILLENC286258332
A000042403 CO INC 1420 PP BLVD GARYSBURGNC278319748
A000042405 CO INC 1419 PP BROWN RD KISSIMMEEFL347463415
A000042405 CO INC 962 PP COMMERCE DRVALDOSTAGA316011206
table is 'shipment'
Shipment IDHost External IDcar_move_idP_DEST_LOC_ID
42401 42401
42402 42402 SDQD_00862TAGSDQD
42403 42403 SDQD_00863TAGSDQD
42404 42404 SDQD_00863TAGSDQD
42405 42405 SDQD_00863TAGSDQD
I have this sql....
Select
DISTINCT p.dbPatID, p.dbpatfirstname, p.dbPatLastName,
s.dbSchTypeCnt as SchDetailType, t.dbSchTypeCnt as SchTypeType,
ISNULL(r.dbStatusDesc, 'No Ref') AS dbStatusDesc,
ISNULL(t.dbSchTypeCode, 'No Ref') AS dbSchTypeCode,
ISNULL(t.dbSchTypeDesc, 'No Ref') AS dbSchTypeDesc,
[code]....
however, I only want the lastest a.dbPatApptTime and only when a.dbPFStatus = 1 and a.ClientRef = 'EPS'
So the stand alone sql could be....
Select Top(1) dbPatApptTime as LastVisitDate, dbSchTypeDesc as LastVisitDesc
from appointments
where dbPFStatus = 1 and clientref = 'EPS'
order by dbPatApptTime desc
I'm just not sure how to incorporate that into my sql or whether there is a better way,
I'm creating a sql stored procedure inside this proc it returns some information about the user, i.e location, logged in, last logged in, etc I need to join this on to the photos table and return the photo which has been set as the profile picture, if it hasn't been set then return the first top 1 if that makes sense?
The user has the option to upload photos so there might be no photos for a particular user, which I believe I can fix by using a left join
My photos table is constructed as follows:
CREATE TABLE [User].[User_Photos](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[UserId] [bigint] NOT NULL,
[PhotoId] [varchar](100) NOT NULL,
[IsProfilePic] [bit] NULL,
[Code] ....
Currently as it stands the proc runs but it doesn't return a particular user because they have uploaded a photo so I need to some how tweak the above to return null if a photo isn't present which is where I'm stuck.
I have data that I want at multiple granularities, 5,15,30 and 60 minutes. To reduce repetition, I have put them all in the same table, so that there is a column for 5,15,30 and 60 minutes, with a filtered index on each of the columns that removes the nulls. This means that each day will have 288 slots, but only 24 of the slots are filled in for 60 min data, and all of them are filled for 5 minute data.
I have another column that specifies the interval granularity, and my first thought was to access my data through a join, where I can use a CASE statement, and depending on the data granularity necessary, it will look at a different column:
INNER JOIN Data d ON
AND d.settlement_key =
CASE st.interval_granularity
WHEN 5 THEN [5_min_settlement_key]
WHEN 15 THEN [15_min_settlement_key]
WHEN 60 THEN [60_min_settlement_key]
ELSE NULL END
Despite the presence of the indexes on the columns, then the process seems to be quite slow, I think probably due to the fact that any query plan isn't going to know beforehand which of the columns it is going to use for any given dataset, until it actually starts to run, so it may not be optimised.
How I could optimise this based on the given structure? Maybe there are hints to be added to the join, or maybe I can clear the query plan each time the SQL is run? My other option for dealing with the data of different granularity was to use one column and repeat the data multiple times, each at the different granularity, but this makes my data, row and table sizes much higher, as we are adding just a column for each additional granularity. Would this work any better in future versions of SQL server, maybe with column store indexes?
I have an inline query that I am trying to convert it into JOIN, results are not coming out the same:
Original query:
SELECT distinct
(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '3') As DcountMy query:
[code]....
I am trying to join two tables together, on the same field except they have different data types, see the properties below
Code:
TableCOLUMN_NAMEDATA_TYPECHARACTER MAXIMUM LENGTHCHARACTER OCTET LENGTHCHARACTER SET NAMECOLLATION NAME
1itemClassnvarchar 512 1024 UNICODE Latin1_General_CI_AI
2PGCode varchar 3 3 iso_1 Latin1_General_CI_AS
in the code for the join,
Code:
left join common.dbo.qryPRDGroupDets on CAST(qryData_GB1_ByColumn.itemclass as varchar(3)) = Cast(common.dbo.qryPRDGroupDets.PGCode as varchar(3))
I have tried using the CAST function on one side of the join then on both, to no avail...
I have sql pulling back data from 2 tables (Ticket, Assignment) matching on an ID.
however the Assignment table can have more than 1 record for a matching ID in the Ticket table so is bringing back rows for each of these entries HOWEVER i only want 1 row returned matching on the FIRST record in Assignment table (on earliest DateAssigned field)
Here's my code
SELECT t.TicketID, CreatedByUserID, CreatedForUserID, DateCreated, a.DateAssigned FROM Ticket t
INNER JOIN Assignment a ON (SELECT TOP 1 TicketID FROM Assignment
WHERE [TicketID] = t.TicketID
ORDER BY DateAssigned ASC) = t.TicketID
WHERE (CreatedByUserID NOT IN (SELECT u.UserID FROM Users u
INNER JOIN Profiles p ON u.UserID = p.UserID
[Code] ....
I am tying to join tables to get the result but it is not showing any data,i have shipping address column in both tables I want to show data in single column I don't know how to display.
select r1.ProductID,r1.ProductName,r1.PMNO ,r.ShippingInfo,r.ShippingAddress ,rs.ShippingAddress from R2InventoryTable r1 inner join RecycleComponents1Table r on r1.ProductID=r.ProductID
inner join
ReSaleorReStock1Table rs on r1.ProductID=rs.ProductID
where r1.HazMat='No' order by ProductID
If I join two tables it is showing data
select r1.ProductID,r1.ProductName,r1.PMNO ,r.ShippingInfo,r.ShippingAddress from R2InventoryTable r1 inner join RecycleComponents1Table r on r1.ProductID=r.ProductID
where r1.HazMat='No' order by ProductID