Recently Indexed Messages:-



SQL Server Admin 2014 :: Can Have Secondary Replica As Read / Write Mode?

Oct 14, 2014

We have a reporting database which is refreshed daily from prod backup and later creating new tables/views/indexes as part of the refresh job. Is there a better approach we can implement in sql 2012/2014 for this scenario since we are planning to migrate to sql2014.

View 5 Replies

SQL Server Admin 2014 :: Transactional Log Backup With Frequency Of 1 Month

Jan 6, 2015

I am planning to take one full backup and Transactional Log backups for every month ..as i will be making the changes in database only once in a month .

And I am aware of that in case of disaster i need to restore database with all the Transactional Log backups . My Plan is to have Transactional log backups for 5 Years and after 5 years i would be taking a full backup .

So should I need to take any other precautions or concerns with this approach.?

View 9 Replies

Transact SQL :: Adding Case When Statement With Group By Query Doesn't Aggregate Records

Aug 28, 2015

I have a a Group By query which is working fine aggregating records by city.  Now I have a requirement to focus on one city and then group the other cities to 'Other'.  Here is the query which works:

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Kansas City 800
Columbia 700
Jefferson City 650
Joplin 300

When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Other Missouri City 800
Other Missouri City 700
Other Missouri City 650
Other Missouri City 300

What I would like to see is a result like:

St. Louis 1000
Other Missouri City 2450

View 5 Replies

SQL 2012 :: Include Zero String And Null Value In Search

Aug 22, 2014

I need to search on varchar column including zero length string and null value.What is the best way to do it?

View 5 Replies

SQL Server 2014 :: Get Latest Per One Column

Oct 9, 2014

I have the following table

DECLARE @TABLE1 TABLE (D_ID int, C_ID int, C_HIST_ID int)
INSERT INTO @TABLE1
VALUES(1000016,71,77),
(1000017,71,547),
(1000017,71,553),
(1000017,71,564),
(1000013,71,565),
(1000017,71,3329),
(1000017,71,3330)

SELECT * FROM @TABLE1 ORDER BY C_HIST_ID

D_ID C_IDC_HIST_ID
10000167177
100001771547
100001771553
100001771564
100001371565
1000017713329
1000017713330

I was able to get the following result

SELECT D_ID,C_ID,C_HIST_ID
FROM @TABLE1 ACH
WHERE ACH.C_ID= 71
AND ACH.C_HIST_ID IN (SELECT MAX(ACH1.C_HIST_ID) FROM @TABLE1 ACH1
WHERE ACH1.C_ID = ACH.C_ID
AND ACH.D_ID = ACH1.D_ID )
ORDER BY C_HIST_ID

D_ID C_IDC_HIST_ID
10000167177
100001371565
1000017713330

But the result I need is

D_ID C_IDC_HIST_ID
10000167177
100001771564
100001371565
1000017713330

C_HIST_ID = 564, which I'm unable to get it through my query...

View 4 Replies

SQL Server 2008 :: Join And SUM Values From 2 Tables

Jan 29, 2015

I 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] .....

View 3 Replies

SQL 2012 :: Tool To Compare Result Sets

Jun 11, 2014

I am looking for a tool to compare the result sets.

Is there any free tool or Microsoft built in tool that we can use to compare the result sets. Suppose if I change one thing in code and needs to test the am I getting the same result set or not? Instead of doing manually is there any way to compare both result set.

View 5 Replies

SQL Server 2012 :: Compare Row Count Between Two Tables With 10k Rows?

Dec 18, 2014

I run the script below once a day to keep track of row count over time. I would like to compare the results from today and yesterday to see if anyone deleted more than 20% of data from any given table. How would I do this? I really don't need the data anymore than a day just to compare the results.

Mon - Run script to collect row count
Tues - Run script to collect current row into temp table
,compare all row count in both tables
,purge records from Monday and insert current
Wed - Run script to collect current row into temp table
,compare all row count in both tables

[code]....

View 4 Replies

Selecting Records Where Part-field Is Unique

Aug 10, 2015

I have a very large CSV file containing name-and-address information which I am reading in a Visual Basic project using the Microsoft.JetOLEDB.4.0 provider.

The key field on which the CSV file is to be filtered is the PostCode field.  This is a UK-format PostCode "XXnn Nxx" where "XX" is one or more letters denoting a geographical area within the UK and "nn" is one or more characters (starting with at least one numeric digit) which when combined with the area code denotes a specific district within the geographical area.  My aim is to identify all the unique UK postal districts held within my address CSV file.

Because I do not know how to use SQL to filter on the partial contents of a database field I am presently reduced to extracting unique full PostCodes using "SELECT DISTINCT PostCode,City,County FROM [ADDRESSES.csv]" into a DataTable object, then sequentially reading that DataTable using the operation of a dictionary object to identify unique PostCode areas, to finally construct the DataTable I need.

Is it possible in SQL to select records where the value of a varying number of characters before a space character in a given (PostCode) field is unique?

View 12 Replies

T-SQL (SS2K8) :: Summing - Adding A Negative And A Positive

Mar 20, 2014

I have a query that brings in a result from a table that I have to display as a negative, so I use ABS (tbname.fieldname) *-1

This works fine. (Learnt it here from a previous post.)

I have another query that brings in a result from the same table but as a postive. So that works fine too.

Now, I need to sum them together in a new query. If the total value is a positive, fine. If the total value is a negative, it displays as a positive.

My question. Is it actually possible to achieve what I'm trying to do?

Output example below - narrow result for testing.

idpt2 Value2SumQty
N1141307-80970 3.80 2
N1141S2G00009D070 26.16 2

The 3.80 is correct, it is a positive.

The 26.16 is incorrect as it is the output from sum 235.44 -261.6 so I need it to display as -26.6

View 9 Replies

Transact SQL :: How To Find Duplicates For Different Fields

Oct 27, 2015

I have a table that has Serial numbers and MSDSID numbers and many other fields in the table.

What I need to figure out is if the table contains a distinct Serial number with different MSDSIDs.

So If I have in the table

Serial          MSDSID          Date          Size...
001                 20            1/1/2015        5
002                 21            1/1/2015        3
001                 22            2/1/2015        1
003                 21            3/1/2015        1
004                 23            1/15/2015      5
003                 22            1/20/2015      6
004                 23            2/21/2015      5
002                 21            4/25/2015      4

I would like the results to show:

Serial            Count
001                  2           the count is 2 because Serial 001 has an MSDSID of 20 and 22
002                  1           the count is 1 because Serial 002 only has MSDSID 21
003                  2           the count is 2 because Serial 003 has an MSDSID of 21 and 22
004                  1           the count is 1 because Serial 002 only has MSDSID 23

It would be even better if the results just showed where the count is greater than 1.

View 5 Replies

Transact SQL :: Difference Between Data With Group By

Jul 28, 2015

I have a SQL table like this

Events time endTime
Tram 2014-11-28 12:35:50.390 2014-11-28 12:43:19.120
Re-Entry 2014-11-28 12:43:19.120 2014-11-28 12:56:07.040
Tram 2014-11-28 12:56:07.040 2014-11-28 13:15:25.060 // EndDate Before dump
Dump 2014-11-28 13:15:25.060 2014-11-28 13:50:07.233
Tram 2014-11-28 13:50:07.233 2014-11-28 13:55:17.473
Load 2014-11-28 13:55:17.473 2014-11-28 14:06:55.063
Tram 2014-11-28 14:06:55.063 2014-11-28 14:37:12.100
Dump 2014-11-28 14:37:12.100 2014-11-28 14:37:12.100

I want to calculate the Difference between 2 dates like endtime before Dump -time..I am expecting output like this

ROW1   ROW2
 1    00:39:34
 2    23:12:55

You can find the details in SQL Fiddle here.

View 4 Replies

SQL 2012 :: Services On A Cluster - Full Text Filter Daemon

Sep 30, 2015

So the cluster install forced us to install Full-Text on our cluster (which we don't want to use, but I guess we don't have a choice about installing it). Now I'm going through to update the services from Manual to Automatic (on the appropriate nodes) and I see SQL Full-text Filter Daemon Launcher running.

What is the harm in turning it off if we're not using Full-Text capabilities?

View 2 Replies

Round Off Sum Values

Mar 19, 2014

I have already summed the values and this below query sum the values and returns zero if there are no values. How can I roundoff the values for these columns.

isnull(sum([Old_Values]),0) [Old Values],
[New_Value] = isnull(sum([Yearly_Value]-[Previous_Year_Value]),0)

Even this query I use for calculation and I need to round off the values

[Product_Price] = [Product_Profit]/4,
[Product_Profit] = [ActualValue] * 0.75,
[GrossValue]

View 1 Replies

Transact SQL :: To Display Days Hours Mins Format Based On Business Hours

Apr 22, 2015

I want to display Days Hours Mins Format.

I am Having two columns Like below,

Col1 (in days)    col2 (In Hours : Mins)
3days  4:5 

In this first have to  add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5

From this 31.5 I should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.

View 6 Replies



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