SQL 2012 :: Count Records From Each Separate Data Table Corresponding To Feed

Nov 5, 2014

I have 1 table that is just a list of feeds. A, B, C, D etc (15 rows in total) and each feed has other information attached to it such as Full name, location etc etc. I am only interested in the Feed column.

Each feed then has a corresponding data table which contains a list of records. Eg Feed A data is contained in TableA, Feed B data is contains in TableB and so on.

Basically what I need is a combined table that shows the list of Feeds in the 1st Column ( So A, B, C, D…..) and then a second column which counts the records from each separate data table corresponding to that feed.

So the end result would look something like this:

Feed------No of Records
A----------4 (from TableA)
B----------7 (from TableB)
C----------8 (from TableC)
D----------1 (from TableD)

Possible?

View 2 Replies


ADVERTISEMENT

Selecting Count Of Records Among Two Separate Databases

Jan 11, 2007

Hey all,
I want to run a query that returns the count of records returned by two other queries. Having much trouble with this... I'm sure it's just a triviality. Thanks in advance...

View 2 Replies View Related

SQL 2012 :: Split CSV Records To Separate Columns

Jun 12, 2014

I've a table as below

custid,companyname,phone,address
2,AAAAA,(222) 222-2222,address 2
3,cust 3,(333) 333-3333,address 3
5,BBBBB,(333) 333-3333,DDDDD
6,cust 6,(222) 222-2222,address 6
7,cust 7,(222) 222-2222,address 7

How to split csv values to new fields. so that the desired output should be as below

custidcompanynamephone address
2 AAAAA (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
5 BBBBB (333) 333-3333 DDDDD
6 cust 6 (222) 222-2222 address 6
7 cust 7 (222) 222-2222 address 7

View 9 Replies View Related

Splitting One Data Field With Hyphens To Separate Records

Feb 16, 2004

I receive a file that will have hyphens between data items such as
123-aed-edr-45r-ui9
1-ed3-45r-rrr-98u
I need to split the values to load into a table that will hold the 5 separate data itens. The fields will always have the hyphens but could be different lengths. Any idea on a best approach to split this in tsql

View 2 Replies View Related

Integration Services :: Purge Data In Transaction Table Or Delete Some Data And Store In Separate Table

Aug 18, 2015

How to purge data  in transaction table or we can delete some data and store in separate table in data warehouse?

View 7 Replies View Related

SQL Server 2012 :: Inserted / Updated Records Count For Logging?

Feb 13, 2015

Initially I had designed ETL using Dataflow task in SSIS , No I have converted into Store procedure using merge statement.

I am getting new records inserted, source records and deleted row count when I am running sqp manually.

In ssis simple I have used the rowcount transformation to capture the records.

below variable i have declared in SSIS Package.

User::etlArchiveLogId
User::sourcerecords
User::newrecords
User::changerecord

now I am incremental loading using Stored procedure below is the sp which is executing when task success and logs the records.

ALTER PROCEDURE [dbo].[usp_LogArchiveBBxEndTime]
@EtlArchiveLog_Id int,
@RowsSource int,
@RowsNew int,
@RowsChanged int,
@Sucessful bit,
@Description varchar(500)

[code]....

Now I want to log the variable records which I have declared in my stored procedure .how can I log it in table?

View 1 Replies View Related

SQL Server 2012 :: Extracting Data From Row And Inserting Into Separate Columns In Different Tables?

Mar 19, 2015

From my query I am getting results like below in one of the column:

'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'

this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.

I need something that shows Date on one side representing particular amount on the other

For Immediate Due it will be current date and the amount besides it.

how can I achieve this.

View 8 Replies View Related

SQL Server 2012 :: Select Query To XLS Output - Export Data In Columns To Separate Tabs In Excel

Apr 21, 2015

Using below script to export the select statement result to .xls

declare @sql varchar(8000)
select @sql = 'bcp "select * from Databases..Table" queryout c:bcpTom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.

how to export the data in columns to separate tabs in excel.

View 2 Replies View Related

Display Count Of Records From The Same Table &> 1

Dec 27, 2007

How would I list the users in the users table that have duplicate IDs or count of IDs > 1?The UserName field is unique.
State   UserName     First          Last         ID         City         CountTX       Kkeaton         Kathryn     Keaton     1001     Dallas      2TX       KakiKeaton    Kathryn     Keaton     1001     Dallas      2I think I have to use a subselect? If I use group by then it won't show both records. It shows only one of them.Thanks
Craig

View 4 Replies View Related

Count Distinct In Large Table With 30M Records?

Jul 7, 2015

I have a task to count distinct records in a big table with roughly 30M records, performance is an important factor. Query is to be written to calculate weekly stats, weekly record number could be as high as 1M.

The actual result is like:
ID Policy
350235744Credit Cards
350235744PCI
350235744PCI Audit

So the final number for this particular Policy is 3

I can write the query like:

select count(distinct Incident_id) policy_name
from Reporting_DailyDlpDetail
Where (year(INSERT_DETECT_TS)=2015) and (month(INSERT_DETECT_TS) =6) and (day(INSERT_DETECT_TS) between 2 and 9)
This returns 526254 and costs 11 seconds to complete

or a query like:
Select distinct Incident_id, policy_name
from Reporting_DailyDlpDetail
Where (year(INSERT_DETECT_TS)=2015) and (month(INSERT_DETECT_TS) =6) and (day(INSERT_DETECT_TS) between 2 and 9)
This returns 749687 and costs roughly 1 minute to complete.

Result is different from the two queries, I believe the later gives correct number. How can I count the distinct based on a combo?Considering the size of data, what is the best and most efficient way to run the stats calculation against over 30 different scenarios (different policies and alert types) and not timeout?

View 9 Replies View Related

TSQL - Count The Number Of Records In A Table

Sep 24, 2007

Hi guys,
Is there any function to get the total number of records in a specific table?
(SQL SERVER 2000).
Thanks in advance,
Aldo.

View 3 Replies View Related

Simple Count Function - Show All Records For SSN In A Table

Jul 9, 2013

I want my query to list all SSNS that have more than one record in the table. I have this query:

Code:

SELECT SSN, name4, count(*) from [1099_PER]
group by SSN, name4
having count(SSN) > 1

It does retrieve the right SSNS and tells me how many times the SSN occurs in the table. However, I want my query results to display their full records.

For example

SSN NAME4 COUNT
123445555 WALTER - 4

I want the query to show me all four records for this SSN. I thought removing the count field would do this, but it still gives me only one instance of each SSN.

View 6 Replies View Related

COUNT From Two Separate Tables

Apr 29, 2014

I have two tables

Books
-BookID
-CustomerID

Magazines
-MagazineID
-CustomerID

How would i write a single sql statement where i can get that counts how many bookIDs are listed for each custoemrID and how many magzaineIDs are listed for each customerID and have it return one table that looks like this:

CustomerID, BookCount, MagazineCount

View 3 Replies View Related

MDX: Count Records In Table A That Are Not In Table B

May 23, 2007

Hello all,



I would like to use MDX to identify "contracts" which are in a table A and not in a table B, and count them to have a total per "company".

It should be something like:



Contract / Flag contracts not in table B (1=yes, 0=not)

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

Contract 1 1

Contract 2 0

Contract 3 1

Contract 4 1

Contract 5 0

Contract 6 0

TOTAL 3



So far I know how to distinguish at contract level which records belong to table A and not table B, but I do not have the total for the company.



In SQL it should be something like that:

SELECT Company,count(*)

FROM tableA

left join tableB

on tableA.Contract = tableB.Contract

WHERE tableB.Contract is null

GROUP BY Company



Thanks,

Guillaume

View 3 Replies View Related

SQL 2012 :: Count Rows When Making New Table

Jan 8, 2015

How can I count the rows when I make new table. Here is my code:

SELECT EMPLOYEE_ID, LAST_NAME, SALARY, JOB_ID, COMMISSION_PCT, DEPARTMENT_ID, COUNT(TO_CHAR(HIRE_DATE, 'DAY')) AS NUM_EMPLOYEES
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'Day') = 'Monday'
GROUP BY EMPLOYEE_ID, LAST_NAME, SALARY, JOB_ID, COMMISSION_PCT, DEPARTMENT_ID;

Here is the table: [URL] ....

I have 10 rows, so I would like under NUM_EMPLOYEES to be displayed 10 on each row.

View 5 Replies View Related

SQL Server 2012 :: Join To Find All Records From One Table That Do Not Exist In Other Table

Apr 29, 2014

I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.

What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:

'allstores' 'Stores'
storeid doc article storeid doc article
ALL 0010 001 101 0010 001
ALL 0010 002 101 0010 002
ALL 0011 001 102 0011 002
ALL 0011 002

So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".

View 7 Replies View Related

What Is Data Feed?

Mar 12, 2004

Hello Friends,

I would like to know what Data Feed means while working as an Administrator on Ms SQL Server 2000?
Also what do you mean by integrating feed of data?
I would appreciate it if anyone could help me out with this queries...
Also I was wondering which would be best book to refer while learning about ms sql server 2000.

View 6 Replies View Related

XML Data Feed

Dec 29, 2005

Hello,I have an XML data feed that I would like to use to create tables inSQL Server. The xml data feed consists of a large amount ofinformation that changes on a regualar basis. Is there a way toautomatically create SQL Server tables using the data feed?ThanksBilly

View 7 Replies View Related

SQL 2012 :: How To Count Instances Of Each Word Throughout Comments Field Across The Table

Oct 21, 2015

I have to count the number of instances of each word in the Comments column throughout the table using SQL Server. Eg:

Row# Comments
1 I like working on SQL
2 I enjoy sleeping and like watching TV

So the output should be :

Word Count
I 2
like 2
working 1
on 1
SQL 1
.......

Any way to get this count using MS SQL?

View 5 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

Data Feed From DB2/AS400 To SQL Server 2000

Jun 13, 2001

I need to have an automated process to read data from DB2/AS400 and feed it to SQL Server 2000. Has anyone done this before? Any suggestions how it may be done? I know my company doesn't want to spend a lot to do this.

Thanks for your time.

View 2 Replies View Related

Data Feed / Make / Model Catalog

Mar 23, 2007

Does anyone know where to start if I want an exported copy of homeappliances, cars, cell phones etc.I just need to populate my database and I want to get some data feedfrom all the different types of manufactures.I think there are services out there. But I have not seen one.I want a CSV file that has all the make/models for lets say: TVs

View 1 Replies View Related

Use Query Results To Feed Data Flow

Sep 24, 2007

Greetings,

I need some help determining the best way to accomplish my task. The workflow starts by generating a list of unique ID's from a local table. Then take that list of unique ID's and query an Oracle table for all matching records.

My thought was to first use an Execute SQL task with the following SQL:

select projectid from projectlist group by projectid

with Result Set configured as follows:

Result Name = projectid
Variable Name = varProjectIDList

Then in the Data Flow Task add a DataReader Source to pull the matching data. Here's where I'm getting hung up. I'd like to pass the result set from the Execute SQL task. I tried the following SQL but it doesn't work.

select * from masterlist where projectid = @[User::varProjectIDList]

I'm open to any suggestion on the best way to take my unique list and use it as input for a query against my Oracle DB.

Thank you for your ideas.

Rob


View 4 Replies View Related

SQL 2012 :: Aggregation Of 200 Mil Data Records?

Jul 7, 2015

We are having a requirement to Aggregate the data and create LY, CY data across 5 Metrics.

the Volume of Data will be 200 Mil - 250 Mil

Server Configuration:
Memory: 32 GB
Processors: 16

understand the bench mark configuration needed for the server & any hints on better aggregation methods & LY / CY data

View 5 Replies View Related

SQL 2012 :: How Many Records Can Insert Into A Temp Table

Mar 25, 2014

I use code below to insert data into a temp table.

How many records can insert into a temp table?

Select * into #temp from ORDER

View 3 Replies View Related

SQL 2012 :: 1.5 Million Records Into Temp Table

Sep 23, 2014

I come from a web based world were loading 1.5 million records into a temp table is suicide. I’m doing more data warehouse stuff now and I was looking into optimizing a buddies proc and noticed he was loading 1.5 million records into a temp table. We had a discussion about it because being from a web world I was drastically against it. He on the other hand didn’t feel it was an issue being it gets called once maybe twice a day. The tempdb is set to autogrow and it is on a different drive than all the other databases on the box. It has one ldf and mdf. He’s creating an index on the table after load. Why we shouldn’t be loading 1.5 million recs into temp table?

View 5 Replies View Related

SQL 2012 :: Deleting Repeated Records In Table

Sep 24, 2015

I have one table having three columns.This table contains lot of repeated records. I want to delete this records .

In this below example i want to delete all the records which columns id and no columns contains same values.

id no sequence
------------------------------------
35 35432 1
35 35432 2
35 35432 3
36 35432 1
35 45623 1

First three records the columns id and no contains same value. I want to delete this three records.

But in last record for id =35 and no column =45623.it is not repeated so it should not be deleted.

View 8 Replies View Related

Get Latest Records When Date And Time Are Separate Columns?

Mar 26, 2012

I have 2 tables:

TransactionsImport (which is the destination table)
TransactionsImportDelta

I need to do the following:

Get the records with the latest date and time in the destination table TransactionsImport
Get the records with the latest date and time in the destination table TransactionsImportDelta table
Insert the records from the TransactionsImportDelta table into TransactionsImport that have a greater date & time than the current records in TransactionsImport table.

Problem is date & time are in separate columns:

Table structure:

Date Time ID
2011121305154107142201008300100
2011121305154122B1L13ZY0000A07YD
2011121304504735142201090002600
2011121304504737142201095008300
2011121304504737142201090002600

View 2 Replies View Related

Dynamic Feed Of Table Name To Transfer SQL Server Object Task

Jan 10, 2007

Hi
I would like to be able to feed the List of tables to the Transfer SQL Server Object Task dynamically.
I have got a foreachloop container which it feeds the table names into a variable @table_name (string).

Transfer SQL Server Object Task is with in foreachloop container

I did add an expression into the property of Transfer SQL Server Object Task and assign the tablelist property to @table_name


I would be grateful if you can give me any hint.
Thanks
S

View 19 Replies View Related

T-SQL (SS2K8) :: Import Data From XML Feed To Get Daily Exchange Rates

Aug 26, 2015

I'm trying to find a way to import data from this data xml feed to get daily exchange rate. I' tried:

select *
from
openrowset(bulk 'http://www.bankofcanada.ca/stats/assets/xml/fx-noon.xml',single_blob) as x

Which is a feeble attempt at a start; however, am getting this error message:

Cannot bulk load because the file "http://www.bankofcanada.ca/stats/assets/xml/fx-noon.xml" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).

How to parse this file using SQL.

View 1 Replies View Related

Reporting Services :: SSRS Multi-Value Parameter Feed To Table Value Function

Jul 24, 2015

I have an SSRS report with several variables, some of which are multi-valued.  The main query that feeds the report uses some table value functions.  For example, the query looks something like this:

select tv1.a, tv1.b, tv2.x, tv2.y, tbl.m, tbl.n
from tableValueFunction1(@MultiValueParam) as tv1
inner join tableValueFunction2(@MultiValueParam) as tv2 on tv1.xxx = tv2.xxx
inner join regularTable as tbl on tv1.xxx = tbl.xxx

where tbl.abc in (@MultiValueParam).When I try to run this, I get an error saying that I'm feeding the wrong number of parameters into the table value functions.  I've fed multi-valued parameters to stored procedures before and used a splitter function to split out the comma separated values, so I was planning to do the same thing in this scenario.  However, this is different.  When I look at the query in SQL Profiler, it shows discrete values for the multi value parameter.  For example, the same query above looks like this when viewed through the profiler (using 1,2,3 as my multi-value selection):

select tv1.a, tv1.b, tv2.x, tv3.y, tbl.m, tbl.n
from tableValueFunction1(1,2,3) as tv1
inner join tableValueFunction2(1,2,3) as tv2 on tv1.xxx = tv2.xxx
inner join regularTable as tbl on tv1.xxx = tbl.xxx
where tbl.abc in (1,2,3)

In other words, SSRS isn't converting the multi-value parameter to a comma separated string, like it normally does.

View 2 Replies View Related

SQL Server 2012 :: Combining Master And Detail Records In New Table

Sep 4, 2014

CREATE TABLE DHS(CUSTOMERNBR VARCHAR(20), CONTRACT VARCHAR(20), SUBCONTRACT VARCHAR(20) , STARTDATE DATETIME, ENDDATE DATETIME, EFLAG VARCHAR(20), HFLAG VARCHAR(20))

The data which will be going into this table is from two table which have a 1 to many relationship:

Here is the 1 side and data:

CREATE TABLE CUSTOMERS(
CUSTOMERNBR VARCHAR(20),
CONTRACT VARCHAR(20),
SUBCONTRACT VARCHAR(20),
STARTDATE DATETIME,
ENDDATE DATETIME DEFAULT '12/31/2099')

[Code] ....

Here is the Many side and data:

CREATE TABLE FLAGS(CUSTOMERNBR VARCHAR(20), FLAGCODE VARCHAR(20), STARTDATE DATETIME, ENDDATE DATETIME DEFAULT '12/31/2099')
INSERT INTO FLAGS(CUSTOMERNBR, FLAGCODE, STARTDATE, ENDDATE) VALUES('10001000101', 'H', '02/01/2014', '03/31/2014')

[Code] ....

The CUSTOMERS table holds the record date span into which the FLAGS table records have to "fit". In no case will the date spans from the FLAGS table fall outside the STARTDATE - ENDDATE span in the CUSTOMERS table for any CUSTOMERNBR. Here is an example of the final expected output in the DHS table from combining records in the CUSTOMERS and FLAGS tables:

CUSTOMERNBRCONTRACTSUBCONTRACTSTARTDATEENDDATEEFLAGHFLAG
10001000101A910400801/01/201401/31/2014
10001000101A910400802/01/201403/21/2014H
10001000101A910400804/01/201404/30/2014
10001000101A910400805/01/201405/31/2014H
10001000101A910400806/01/201412/31/2099E
10001000102A555500101/01/201403/31/2014E
10001000102A555500104/01/201404/30/2014EH
10001000102A555500105/01/201406/30/2014E
10001000102A555500107/01/201412/31/2099
10001000103A666600401/01/201410/01/2014

View 1 Replies View Related

SQL Server 2012 :: Delete Unmatching Records In Child Table

Feb 24, 2015

I've 2 tables ResumeSkill (Child table) and Skill (Parent table), There are duplicates in the parent table and after removing the foreign key constraint in child table deleted all duplicate values from Parent table. But those deleted duplicate values has references in child table which need to be deleted now.

ResumeSkill Skill

Id SkillId
SkillId Name

I want to delete all the records from ResumeSkill that dont have matching skillId in Skill table.

View 2 Replies View Related







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