Recently Indexed Messages:-



Transact SQL :: Aggregate Last 12 Months

Sep 2, 2015

I have a table with sample data as shown in the table below. for each month end date, I need to aggregate (sum) all the quantities for each product and customer and include the current month plus the past 12 months (including current month). 

For example, for customer C123, P123 and for 7/31/2015, the query should add 7/31/2015 quantity and the quantity for 1/31/2015. 

DECLARE @TEMP TABLE (Customer VARCHAR (10), Product VARCHAR(10), Month_end_date DATE, Quantity INT)
INSERT INTO @TEMP VALUES ('C123','P123','1/31/2015',10)
INSERT INTO @TEMP VALUES ('C124','P124','2/28/2015',20)
INSERT INTO @TEMP VALUES ('C125','P125','3/31/2015',30)
INSERT INTO @TEMP VALUES ('C126','P126','4/30/2015',40)

[Code] ....

the output for the above example should be
C123 P123 7/31/2015
 70+10=80

is this possible?

View 10 Replies

SQL Server 2008 :: Splitting A Field In The Middle?

Mar 19, 2015

I have a field which looks like "LastName, FirstName (DOB: 01/01/1900)"

How do I get the "01/01/1900" between ":" and ")"

View 6 Replies

SQL Security :: System Administrator Password Reset

Aug 7, 2015

I have inherited a sql server and no one knows the SA password.  I cannot login with windows authentication even if the account is administrator of the machine.

View 4 Replies

T-SQL (SS2K8) :: Convert Number Of Month In Integer Into How Many Year And Month

Sep 10, 2014

This is my table and data

CVID | WorkExperience
--------------------------------
2838736
68181101
96568122
1135484

I need to convert into this result

CVID | WorkExperience
--------------------------------
283873 years
681818 years 5 months
9656812 years 2 months
1135484 months

View 5 Replies

SQL Server 2012 :: Load Folder Name And Images Into Tables

Dec 8, 2014

I need to load images from folders into a SQL Server table and I have done it successfully for individual images, however I need to load all the names of the folders and sub folders names in separate columns + load all images.

So the folders look like as in the screenshot and the final result of the table in SQL Server should look like the second screenshot.

View 9 Replies

Transact SQL :: Identifying First Ones

May 4, 2015

Have a table showing info like:

Region_ID
Branch_ID
Customer_ID
Transaction_ID

In this regard need to create a view to reflect the info in the order

Region_ID (Ascending)
Branch_ID (Asc)
Customer_ID (Asc)
Transaction_ID (Asc)

But, at the same time, also want to include two additional columns identifying or even inserting 1's against each of the first Customer_ID considering the first two columns, and the second one on the single column itself?In other words consider the following sample, how to add 1 to the last two columns where either the Customer appears for the first time in context with the Region and Branch Fields or to be appearing on it's own for the first time:

Region
Branch
Customer
Transaction
Required_A
Required_B

[code]....

View 3 Replies

SQL 2012 :: Cannot Truncate Table Because It Is Published For Replication Or Enabled For Change Data Capture

Jul 8, 2015

I get the following error message when a job calls a Stored Procedure that TRUNCATES a Table:

Cannot truncate table 'CombinedSurveyData' because it is published for replication or enabled for Change Data Capture

Is my only option to change the TRUNCATE to DELETE?

[URL]

View 2 Replies

DB Engine :: Change Default Trace Value

Sep 3, 2014

I am trying to change the default trace value from 2 to 6, to incorporate rollover and server shutdown.  I am using this code to create a new trace (with id: 2) with the value of 6 and use that to overwrite the default trace (with id: 1):

    DECLARE @new_trace_id INT;
    EXECUTE master.dbo.sp_trace_create
      @someinteger = @new_trace_id OUTPUT,
      @someinteger = 6,
      @someinteger = N'C: raceTestTrace';

Then I disable the default trace.  I am then using this code to overwrite the default trace (with id: 1) and replace with new value default trace (with id: 2):

       -- get trace status 
       SELECT * FROM ::fn_trace_getinfo(NULL)
       -- stop trace
       EXEC sp_trace_setstatus @traceid = 1 
                    , @status = 0  
       go 
        -- delete trace
       EXEC sp_trace_setstatus @traceid = 1 
                    , @status = 2

Then I enable the default trace.  It works perfectly (default trace with id 1 is showing value 6) until I restart.  Upon restart no default traces are enabled, once I run the script to enable the default trace then the values for the default trace (with id: 1) are back, and my value for rollover/server restart is back to 2.

View 12 Replies

SQL 2012 :: Check Query Execution Plan Of A Store Procedure From Create Script?

Jun 17, 2015

Is it possible to check query execution plan of a store procedure from create script (before creating it)?

Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?

View 1 Replies

T-SQL (SS2K8) :: Way To Add A Counter At End Of Each Row

Aug 18, 2015

My client wants IN_PUNCH to be the first in punch of the day and the OUT_PUNCH to be the last out punch of the day based on the TransactionDate as it is showing in the desired results table for 07/29 and 07/30 (Changes are highlighted in Bold).

I tried using MIN and MAX at different areas but to no avail.Also, is there a way to add a counter at the end of each row so in case if there were two shifts in a day then it be a '1' for row 1 and a '2' for row 2?

WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS,PAYCODE,SHIFT_LABEL,DOW) AS
(
SELECT 1234,'07/27/2015','07/27/2015','07/27/2015', '12:00','12:00','8', 'Hol', 'NULL', 'Monday' UNION ALL
SELECT 1234,'07/28/2015','07/28/2015','07/28/2015', '08:00','','4.00', 'Absent', 'Batax 1st','Tuesday' UNION ALL
SELECT 1234,'07/28/2015','07/28/2015','07/28/2015', '12:15','14:00','3.75', 'Regular', 'Batax 1st','Tuesday' UNION ALL

[code]...

View 2 Replies

SQL Server 2008 :: Finding Column Within All Tables In DB

May 1, 2015

I am trying to find a way where I can search for a column that is associated in all tables of the database. I have created a query but is not executing correctly.

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t8
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Status%'
ORDER BY schema_name, table_name;

View 3 Replies

Reporting Services :: SSRS 2012 Setup Parameter Value

Apr 22, 2015

In an existing ssrs 2012 report, I want to basically add the following logic to a new dataset that will be used by a new parameter called 'Inventory'.

SELECT  DISTINCT Inv_number as value,  Inv_name as label
FROM s.dbo.fnUser( @endYear, 0, 0, 0)
WHERE (@report is not null)
order by  label,value

In this ssrs report, there are 15 reports that can automatically be generated. I basically want this new parameter called 'Inventory' to only be active when the 4th report is selected to be generated. Thus in the query I listed above, how can I change the query to only be active when the 4th report is selected?

I tried to change the where clause to (@report(4) is not null), but that did not work.

View 5 Replies

DB Engine :: Retrieving Table And Index Object Definitions In Server

Jul 20, 2015

I am currently investigating the best way to handle partition swapping where the partition scheme is built on several file groups.  In order to swap partitions, both partitions need to be on the same file group.  In addition, the file groups and partitions are automatically updated once a month meaning that there is a scheduled job that adds a new file group, file group files, and updates the related partition objects.If I am forced to create a new non-partition table on the same file group as the partition of the target table, what would be the best way to create the target table without hard coding the full table definition and clustered index?  I know that I can query the systables and syscolumns views to reconstruct a basic SQL statement to build the table definition and views  Is there a SQL Server function that I can use to just give me the object definitions? Unfortunately, the OBJECT_ DEFINITION function doesn't apply to tables or indexes. URL....

View 4 Replies

SQL Server 2012 :: Query To Produce Permutation Combination

Jun 4, 2015

I need a query to produce permutation combination.

declare @t2 as table (tab varchar(100))
insert into @t2 values ('V')
insert into @t2 values ('VL')
insert into @t2 values ('1099')
insert into @t2 values ('VOI')

declare @t1 as table (tab varchar(100))
insert into @t1 values ('I')
insert into @t1 values ('U') from the above I need following output (attached output),

View 3 Replies

SQL 2012 :: MSBI - Power View For Multidimensional Models?

Jun 19, 2014

Currently I am working with MSBI. My issue is unable create power view dashboards with Multidiamentional Model(Cubes).

I got some information like, which will work with with SQL Server 2012 Service Pack 1 Cumulative Update (CU) 4.

Got this information from this link : [URL] But I am unable to find the correct download link for the same.

View 0 Replies



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