SQL Server 2014 :: How To Sum And Group Data

Jan 20, 2015

I want to group the following data attached by CUSTACCT, YEAR, PERIOD(month). As you can see in the attached example, I see duplicates for late fees and Exchange fees but none for FLIGHT_HRS. I want to see the data grouped by CUSTACCT by YEAR then by PERIOD for each of these: FLIGHTHRSSUM, LATEFEESUM, EXCHFEESUM without duplicates.

Is this possible?

This is my current SQL

SELECT dbo.VIEW_FLIGHT_HRS_TOT.YEAR AS FLTHRSYEAR, dbo.VIEW_FLIGHT_HRS_TOT.PERIOD AS FLTHRSPERIOD, dbo.VIEW_FLIGHT_HRS_TOT.FLIGHTHRSSUM,
dbo.VIEW_LATE_FEES_TOT.PERIOD AS LATEFEEPERIOD, dbo.VIEW_LATE_FEES_TOT.YEAR AS LATEFEEYEAR, dbo.VIEW_LATE_FEES_TOT.LATEFEESUM,

[Code] ....

View 9 Replies


ADVERTISEMENT

SQL Server 2014 :: Un-Group Data From Report

May 28, 2015

We have an application that can spit out our Facility Structure into the following format. However, we have a need to take that data and feed it into another system. However, as you can see it is organized in a PARENT / CHILD structure.

Indent Level Key:
1System
2Facility
3Service Line
4Division
5Department

If you notice, each additional row is the child row to its parent above it as long as the Indent Level Key continues to increment. Once we start going back up the structure we essentially have a new Division/Service Line/or Facility depending on how far back we jump on the Indent Level for that next row. Here is some sample data.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Test_Data','U') IS NOT NULL
DROP TABLE #Test_Data

--===== Create the test table with
SET ANSI_NULLS ON

[Code] .....

View 3 Replies View Related

SQL Server Admin 2014 :: Re-balancing Blob And Lob Data In Multiple Files On Primary File Group

May 7, 2014

A little background on what I am trying to achieve first. We are moving to Azure virtual machines and we will have 8 disks on the SQL Server box. I am adding more files to the primary file group and each file will go on its own drive. I am then rebalancing data across these files by rebuilding all of the indexes on the tables which is working fine. No problems so far all is good.

I now have an additional problem. If there is a lob or blob column on the table, rebuilding the clustered index and all the non clustered indexes doesn't rebalance the blob or lob data across the disks such as it does with in row data.

I cannot find any articles on rebalancing lob or blob data because all the articles say to move to a new file group. I do not want a new file group, I just want to use the primary file group where the data already resides, and just redistribute it evenly in the same way that I can in row data which is working fine.

One solution I thought about was to BCP data out of the table, truncate the table and then BCP back into the table which I imagine would have the desired effect of distributing the data evenly over the files.

View 2 Replies View Related

SQL Server Admin 2014 :: Creating Additional Data File For A Particular File Group?

Jul 6, 2015

For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.

Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?

View 5 Replies View Related

SQL Server Admin 2014 :: Can Delete A Data-file Or File-group

Apr 27, 2015

In a server we had File Growth,And then We had to Add New Hard Drive And New File On It.And Now We have New server with a Huge Hard Drive.But all files remaind.Can I Reduce This files to One data file or not ?

View 3 Replies View Related

SQL Server 2014 :: Need A Query On Group By Condition

Oct 11, 2013

I have one table which contines 3 columns they are

1.emp
2.monthyear
3.amount

in that table i am having 6 rows like below.

emp monthyear amount
1 102013 1000
2 102013 1000
1 112013 1000
1 112013 1000
2 122013 1000
2 122013 0000

i want a total on group by condition on each employee. which will have the data from NOV to dec 2013 only.

out put should be like this
1 2000
2 1000

View 9 Replies View Related

SQL Server 2014 :: Order By Month Number In A Group By

Dec 19, 2014

Sample Data
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE CheckRegistry (
CheckNumber smallint,

[code]...

How can I get the result orderd by the month number?

View 3 Replies View Related

SQL Server Admin 2014 :: Separate One Availability Group To Several AVs?

Jul 3, 2015

We have 4 servers : Server 1 , Server 2 and HA , DR servers.

I designed 2 Plans to get HA support for my databases .

Which of them are better , And is there any problem in my design ?

View 4 Replies View Related

SQL Server Admin 2014 :: Notification On Availability Group Failover

Nov 10, 2014

Is there any way to trigger an event (ie. call a procedure/job/etc.) when/if an AG fails over?

Not looking to use agent alerts.

View 0 Replies View Related

SQL Server Admin 2014 :: Manual Failover Availability Group

Dec 24, 2014

Recently after turning on trace I restarted the sql services on a box which is configured for automatic failover availability groups. The ag has not failed over to other node. The other node was in resolving state. When the restarted server is back, the AG went back to that server. I checked the sys.availability groups field for failover property failure condition level, it's set to 1 which means service restarts should initiate the failover.

View 3 Replies View Related

SQL Server Admin 2014 :: AlwaysOn Availability Group Configuration

Jun 17, 2015

What I asked for: Three Windows Server 2012 R2 machines with independent storage running a SQL Server 2014 AlwaysOn Availability Group. DB1 would be the primary, DB2 would be a synchronous replica, and DB3 would be a remote asynchronous replica.

What I was given: a two-node Windows Server 2012 R2 WSFC to run SQL Server 2014 Enterprise with shared storage and a third (remote) Windows Server 2012 R2 machine with independent storage, also with SQL Server 2014 Enterprise, to host an AlwaysOn Availability Groups asynchronous replica.

DB1 and DB2 (as Cluster1) share an E: drive. The remote DB3 has its own E: drive. Initially, DB3’s E: drive was claimed as a cluster resource and I couldn’t even see it. I’ve had several ugly days trying to make this work and have temporarily given up, installing DB3 as a standalone SQL Server that is no longer part of the WSFC and pointing everything towards that (it was originally a third node in the WSFC).

Is it possible to create an AlwaysOn Availability Group with nested clusters (i.e. create the AOAG with Cluster1 and DB3 and somehow ignore the individual nodes that comprise Cluster1)?

View 6 Replies View Related

SQL Server 2014 :: Group Up Records Randomly Into N Number Of Batches

Jul 6, 2015

I need to group up the records randomly into ‘n’ number of batches. That can be done by NTILE, but I want group up similar records in single group.

Say for example, following is the list of records I have in my table which I want to group into 5 batches

A123
A124
A124
A123
A127

After Ntile I will get the below,

Desired output is, Need output like Ntile but all same id should reside in single batch

Even if I n=5, maximum possibility of batches are 3 only.

View 2 Replies View Related

SQL Server 2014 :: Recursive Family - Group All Related Members Under Same FamilyID

Jun 24, 2014

I have a family table and would like to group all related members under the same familyID. This is a replication of existing business data, 14,000 rows. The familyID can be randomly assigned to any group, its sole purpose is to group the names:

declare @tv table (member varchar(255), relatedTo varchar(255))
insert into @tv
select 'John', 'Mary'union all
select 'Mary', 'Jessica' union all
select 'Peter', 'Albert' union all

[Code] ....

I would like my result to look like this:

familyID Name
1 John
1 Mary
1 Jessica
1 Fred
2 Peter
2 Albert
2 Nancy
3 Abby
4 Joe
4 Frank

View 3 Replies View Related

SQL Server Admin 2014 :: Group Policy User Denied Access

Sep 15, 2014

I have a user, who is trying log into the server, but everytime he gets this error saying something about the Group policy denies him access.

This user needs access and i'm trying to understand how to grant it to him.

I have been looking into how i can access the group policy editor, but the farthest i can get is the Local group policy editor. How do i make sure this specific user has access?

View 1 Replies View Related

SQL Server Admin 2014 :: Where To Find Availability Group Fail Over Occurrences

Nov 14, 2014

Where can I find dates and times to when an availability group was moved outside of the SQL error log?

View 1 Replies View Related

SQL Server Admin 2014 :: Exit From Not Synchronizing Primary Database In Availability Group

Jan 27, 2015

I Create an availibility Group with a primary and a secondary. (For test)

Then run Planed Failover.

Switched to secondary. That's ok.

After that I update some tables on secondary (That now is primary)

I Run Again Planed Failover on server 2.

Switched. OK

But primary database Get (Not synchronizing) Status.

And in primary I don't have that updates.

How to sync these databases and exit from Not synchronizing.

View 5 Replies View Related

SQL Server Admin 2014 :: Disconnected From Listener In Availability Group (When Primary Failed)

Jun 27, 2015

I setup an availability Group. (Only 2 servers - Primary And secondary) -- 21 , 22

I also define an listener . IP .. 23

1- In First step I connected To Listener (23) And in a while I inserted A record to a table .

While 1=1
insert into Tbl_T1(f1,f2) Values (1,2)

2- in second, I Stop the primary .

- I expected this while whitout disconnect, continue.

3- The while code stopped whit this message :

Msg 64, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

4- I execute again the script, And it worked in new primary.

My questions :

1- is the listener disconnected between switched primary and secondary ? OR have we data loss between switching?

2- I did some huge update on Primary that fill the Log fiel space. And in last Update I got this error :

Msg 9002, Level 17, State 2, Line 27

The transaction log for database 'Your_DB' is full due to 'LOG_BACKUP'.

Is this (Fill All space) a reason to switch primary? Or not ?

View 2 Replies View Related

SQL Server Admin 2014 :: Availability Group Handshaking Fails After Network Outage

Aug 14, 2015

I'm running a primary and secondary on sql server 2012 enterprise edition on windows server 2012, and it runs fine except when a network outage occurs.

Then the handshaking keeps failing, the databases on the replica show as not synchronizing and the only way to fix this is to reboot both primary and secondary.

We keep getting 3520's, etc. on the DR error log

How to eliminate all these prod reboots?

I increased query connection timeouts to 60, but saw no change.

View 0 Replies View Related

SQL Server Admin 2014 :: Change Data Capture(CDC) For Data Warehouse / Reporting?

Aug 12, 2015

I have a requirement to implement CDC for 50+ tables to implement incremental data changes warehouse/reporting rather than exporting the whole table data. The largest table is having more than half a billion records.

The warehouse use a daily copy of OLTP db (daily DB refresh). How can I accomplish this. Is there a downside in implementing CDC just for the sake of taking incremental changes on the tables?

Is there any performance impact if we enable CDC on OLTP db?

Can we make use of the CDC tables on the environment we do daily db refresh so that the queries don't hit OLTP database?

What is the best way to implement CDC to take incremental changes for reporting.

View 0 Replies View Related

SQL Server 2014 :: How To Insert CSV Data Into DB Where Some Data Don't Have Double Quotes

Aug 11, 2015

Example of data in CSV are as follows:

"XXX","0001",-990039739 ,0 ,0 ,0 ,0 ,0 ,0
"ABC"," ",-3422054702 ,0 ,481385 ,0 ,0 ,0 ,0
"JJZ","0001",0 ,0 ,0 ,0 ,0 ,0 ,0Here's my format:
12.0
10
1 SQLCHAR 0 0 """ 0 "" ""
2 SQLCHAR 0 5 "","" 1 OKCCY SQL_Latin1_General_CP1_CI_AS

[Code] ....

View 5 Replies View Related

SQL Server 2014 :: Update Old Data With New Data

Jun 5, 2014

I got this script but all I get are the errors:

Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'PartNrFabrikant'.
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'omschrijving'.
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'verkoopprijs'.
Msg 116, Level 16, State 1, Line 13

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

script:
UPDATE artikelen
SET omschrijving = [Hofstede].[dbo].[sparepartsupdate].[omschrijving] ,
verkoopprijs = [Hofstede].[dbo].[sparepartsupdate].[verkoopprijs] ,
gewijzigd = getDate()

[code]...

View 8 Replies View Related

SQL Server Admin 2014 :: Error While Updating Data Using Oracle Linked Server

Sep 11, 2015

We have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server . Previously its working fine but we are suddenly facing below issue.

Below error occurred during process .

OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "<linked server name>".

View 7 Replies View Related

SQL Server 2014 :: How To Copy Data Rows From Hosting To Local Server

Mar 11, 2015

I'm a web developer who writes transact-SQL to make my web applications run properly. I'm not real strong in other areas of SQL. Let me explain our set-up and then I'll explain what I want to do:

We have an ecommerce web site and all sales are saved in a SQL Server 2008 R2 database at our hosting company. We also have a local Windows 2012 network that has SQL Server 2014 Express installed.

Here is what I want to do:

I want to copy sales rows from the SQL Server 2008 database at our hosting company and save them in the SQL Server 2014 Express database on our local Windows 2012 server. I'd like to automate this if possible so that it happens each night perhaps. I know there is a way to schedule SQL jobs but I've never actually done this. I also would need to know how to attach to our hosting company DB as well as our local network DB.

View 2 Replies View Related

SQL Server 2014 :: How To Import Data To Server From Access Database Using Script

Sep 22, 2015

I want to import the data from specific Access Database and Table to SQL Server, using SQL Script. I am trying to implement the solution as given in this link. URL....Here is the code that I have tried -

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO

[code]...

The access database file path is - 'C:SQL ProjectTestDB1001.mdb'.The Table from which I want to import the data is - [Table1001]. but when I run this script, I get this error -9 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MSAccessConnect" reported an error. Authentication failed.

Secondly I need to make 2 more changes to the code posted above.

1.If some access database .mdb, has got password protection, then how to include the password in the script, so that one does not have to manually feed in the password during data import work.

2. How to limit the data that is to be imported from the table [Table1001] by including a WHERE Clause, like for example - SELECT * FROM [Table1001] WHERE xdate = '2015-9-16 00:00:00.000')

View 7 Replies View Related

SQL Server 2014 :: Cursor To Delete Data?

Nov 5, 2013

i have to delete data from a table which is older than 2 weeks, how can i use a cursor to do it.

I will have to place the query in a SQL job and run that weekly once

View 9 Replies View Related

SQL Server 2014 :: Transform Data Into Column

Jan 8, 2014

I have data like this in the table :

IntRecpieID strName intMealtypeID Total
100 ‘A’ 1 20
101 'B' 2 30
100 'A' 3 40

Desired Output required:

IntRecpieID StrName 1 2 3

100 'A' 20 Null 40

101 'B' Null 30 Null

View 5 Replies View Related

SQL Server 2014 :: Insert Data From PDF Files

Mar 23, 2014

We are facing a problem with loading data from .pdf files from vendor..pdf files have data in tabular format and we would like to insert those fields into a SQL table.We do not want to insert the physical location of the file but, we need to insert the data within the file.How can we read a pdf file?

View 6 Replies View Related

SQL Server 2014 :: One To Many Relationship In Data Processing

Jun 24, 2014

I have a scenario where one partner can have multiple competency like Grade A,Grade B or Grade C and Grade AB,Grade BC etc.

I need to pull the records of competency of higher level also there is a chance of changing this competency level in feature like Grade A of Grade AA like this.

What would be the best option to select this kind of records.

View 9 Replies View Related

SQL Server 2014 :: Extracting Data From Database?

Nov 5, 2015

I am running an hotel application for all the bookings.

I would like to get all the data of all the guests that stayed in the hotel (name, stay duration and dates, payment, etc).However, the GUI would make me go over one booking at a time, which would take me forever.

The application has an SQL database locally on my computer.

Will I be able to use SQL server and extract all the guests data from the last year and export it into a file, e.g. A CSV file?

View 8 Replies View Related

SQL Server Admin 2014 :: Setup Performance Monitor Collector On Workstation Collecting Remote Server Data?

Mar 31, 2015

I set up the collector, and specify the Run As as my AD account in the Collector Set - Properties - General screen. My AD account is the local admin of the remote server.

However, the collector does not seem to work. Although the collecting set is shown as running, the The blg file stays at 64K. If I open it, there is nothing inside (no counter at the bottom). What did I miss?

View 1 Replies View Related

SQL Server 2014 :: Data Profiling Tables Within Server

Jun 18, 2015

I have been giving the task of profiling databases and all the tables within it. Right now I'm collecting the following:

Null_Count
Null_Percentage
Total_Record_Count

I'm looking to capture Mean_value, Min_value, and Max_value and the unique_count.

View 2 Replies View Related

SQL Server Admin 2014 :: Cannot Export All Data From Database

Dec 4, 2013

And have chosen the destination - unstructered (flat) file. But the wizard proposes to export only one table (dbo.Acocount) and all the others from the list are not exported. How can I export ALL the data into one file.I need to do this to edit the syntax in the editor and then import this data and database structure into Postgresql

View 4 Replies View Related

SQL Server 2014 :: Enquiry On Retrieving Data From Database

Jul 18, 2014

I have a drop down menu for transmission. Inside the drop down menu, there are 1.Any 2.Auto 3.Manual options

Now I want to retrieve from my database, if the client select '1.Any' option. Meaning the output I want it to display all the products which have both auto and manual for transmission.

I have no problem retrieving all the products which either only have auto option or manual option.

View 2 Replies View Related







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