Recently Indexed Messages:-
Transact SQL :: Aggregate Last 12 Months
Sep 2, 2015I 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?
SQL Server 2008 :: Splitting A Field In The Middle?
Mar 19, 2015I have a field which looks like "LastName, FirstName (DOB: 01/01/1900)"
How do I get the "01/01/1900" between ":" and ")"
SQL Security :: System Administrator Password Reset
Aug 7, 2015I 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 RepliesT-SQL (SS2K8) :: Convert Number Of Month In Integer Into How Many Year And Month
Sep 10, 2014This 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
SQL Server 2012 :: Load Folder Name And Images Into Tables
Dec 8, 2014I 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.
Transact SQL :: Identifying First Ones
May 4, 2015Have 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]....
SQL 2012 :: Cannot Truncate Table Because It Is Published For Replication Or Enabled For Change Data Capture
Jul 8, 2015I 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]
DB Engine :: Change Default Trace Value
Sep 3, 2014I 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.
SQL 2012 :: Check Query Execution Plan Of A Store Procedure From Create Script?
Jun 17, 2015Is 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?
T-SQL (SS2K8) :: Way To Add A Counter At End Of Each Row
Aug 18, 2015My 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]...
SQL Server 2008 :: Finding Column Within All Tables In DB
May 1, 2015I 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;
Reporting Services :: SSRS 2012 Setup Parameter Value
Apr 22, 2015In 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.
DB Engine :: Retrieving Table And Index Object Definitions In Server
Jul 20, 2015I 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 RepliesSQL Server 2012 :: Query To Produce Permutation Combination
Jun 4, 2015I 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),
SQL 2012 :: MSBI - Power View For Multidimensional Models?
Jun 19, 2014Currently 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.