SQL Server 2012 :: Convert Hardcoded SP Into Dynamic Merge Statement
Feb 13, 2015
I am working on to convert my static Store procedure to Dynamic.
I have created a Store procedure with Merge statement which is inserting new record and updating existing record.
This SP I will use in SSIS Insted of Data Flow Task I will run in Execute SQL Task.
Now my biggest problem is I dont know how to convert static code toi dynamic
Below is my Store procedure code.
As you can see my Source Query
I have a filemaster table as shown below which consist of Input filename,Source table ,Destination table and BBX expression.
Input_FilenameSourceTableName DestinationTableName BBxKeyDerExpr
CCTFB ImportBBxCctfb ArchiveBBxCctfb SUBSTRING(Col001,1,6)
CEMXR ImportBBxCemxr ArchiveBBxCemxr SUBSTRING(Col001,1,10)
In my source query I want to change the value of Source table ,Destination table and BBX expression dynamically on the basis of input file.
Purpose of making dynamic is that I have created separate sp for all the input, my clients want to have sungle dynamic sp which will execute on the basis of input file.this input file name I wil get fromm variable which i have created in SSIS Package.
Lets consider @File_name is the variable in package which store the file name
if file name is CCTFB then my query should take the Source table ,Destination table and BBX expression value from file master table.
Like that I have 100 of source query and evry query have diffrent number of columns. How can I change the column number in uodate and insert statement dynamically on run time.
CAST(SUBSTRING(Col001,1,6) + SUBSTRING(Col002,1,10) AS varchar(100)) :-It creates a key for comparing, this value i can take it from filemaster
HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002,Col003,Col004) AS varchar(max))) -here numberv of column need to be changed .
(SUBSTRING(SOURCE.Col001,1,6) + SUBSTRING(SOURCE.Col002,1,10)) this condition also i can take it from file master.
[Code] ....
I am able to get inserted and updated rowcount, but not able to get the matching records count.
View 0 Replies
ADVERTISEMENT
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Jul 22, 2015
I have a procedure where Login and Password of the database are to be used and right now i have given a valid hardcoded value for them.
How can I get these values from the actual login (I mean sql server authentication value or windows authentication).
View 8 Replies
View Related
Feb 3, 2015
I have this query:
SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand,
dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number],
dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],
[Code] ....
I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.
This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).
The question is how do I PIVOT and preserve the percentage of ownership?
View 3 Replies
View Related
Mar 18, 2015
I have created mergse statement using SCD2.where I am inserting the data if my BBxkey is not matching with target and updating the rows if the bbxkey is matching and rowchecksum is different.
Working of Store procedure
There are 2 scenario covered in this procedure on the basis of that ETL happening.
There are 2 columns deriving from source table at run time, one is BBxkey which is nothing but a combination of one or more column or part of column and another column is a Rowchecksum column which is nothing but a Hashvalue of all the column of the tables for a row.
Merge case 1:-WHEN NOT MATCH THEN INSERT
If source BBxkey is not there in Archive table that means if BBxKey is null then those records are new and it will directly inserted into Archive table.
Merge case 2:-WHEN MATCH THEN UPDATE
If Source.BBxkey=Target.BBxkey && Source.Rowchecksum<>Target.Rowchecksum then this means source records are available in Archive table but data has been changed, in this case it will update the old record with latestversion 0 and insert the new record with latestversion 1.
my sp failing when source having more than 1 same bbxkey.
error [Execute SQL Task] Error: Executing the query "EXEC dbo.ETL_STAGE_ARCHIVE ?" failed with the following error: "The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.".
Sample store procedure
DECLARE @Merge_Out TABLE (Action_Taken varchar(8),
TimeIn datetime,
BBXKey varchar(100),
RowChecksum nvarchar(4000),Col001 varchar(8000),Col002 varchar(8000),
Col003 varchar(8000),Col004 varchar(8000),Col005 varchar(8000),
[code].....
How Can I avoid such failure of my sp.
I want to handle those case where S.bbxkey=T.bbxkey && s.rowchecksum=t.rowchecksum
View 2 Replies
View Related
Oct 7, 2015
In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.
Merge test.dbo.LockCombination AS LKC1
USING
(select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber
from
[Inputtb] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type
[Code] ....
Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?
View 6 Replies
View Related
Mar 12, 2015
I have created a Dynamic Merge statement SCD2 Store procedure , which insert the records if no matches and if bbxkey matches from source table to destination table thne it updates old record as lateteverion 0 and insert new record with latest version 1.
I am getting below error when I ahve more than 1 bbxkey in my source table. How can I ignore this.
BBXkey is nothing but I am deriving by combining 2 columns.
Msg 8672, Level 16, State 1, Line 6
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
View 4 Replies
View Related
Mar 5, 2015
I have a table which is updated daily using a MERGE statement. As records are insert, updated and deleted, I am saving the OUTPUT from the MERGE statement into a history table with a timestamp and action$ column appended to the record.
Using this history table, I'd like to rebuild the data based on specific past date. I was able to create a stored procedure that inspects each record in the history table and apply it to the data in a temp table. The stored procedure solution uses multiple queries to rebuild the data at a point in time. I was curious if there was an easier and more efficient solution using a table function.
View 2 Replies
View Related
Mar 11, 2015
How to write a Dynamic Pivot Statement to Calculate and Organize Columns like:
CREATE TABLE #mytable
(
Name varchar(50),
GA int,
GB int,
startdate DATETIME,
enddate DATETIME
[Code] ...
Below is Our Sample Table Data.
Name GAGBstartdateenddate
Pavan 261/1/20151/1/2015
Hema 561/1/20151/1/2015
Surya 501/1/20151/1/2015
Pavan 811/2/20151/8/2015
Hema 311/2/20151/8/2015
Surya 121/2/20151/8/2015
Pavan 1041/9/20151/15/2015
Hema 301/9/20151/15/2015
Surya 6131/9/20151/15/2015
How to write Pivot Satement to get Oupt like below:
1/1/2015 Pavan Hema Surya SumTotal
Total 8 11 5 24
GA 2 5 5 12
GB 6 6 0 12
1/8/2015 Pavan Hema Surya SumTotal
Total 9 4 3 16
GA 8 3 1 12
GB 1 1 2 4
1/15/2015 Pavan Hema Surya SumTotal
Total 14 3 19 36
GA 10 3 6 19
GB 4 0 13 17
View 5 Replies
View Related
Dec 11, 2014
We have a SSIS package which loads the data from csv files to DB. It only loads the new entries ie if the row already exists in the tables than it doesn't insert it. For this we load the CSV to temp tables for respective schemas and than those are compared with base tables of respective schemas and inserted new rows. For this we use Merge statement.
View 1 Replies
View Related
May 2, 2007
If anyone could confirm...
SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.
E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.
First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.
However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.
Question: I thought the filters would remain dynamic and be applied on each sync?
I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!
Question: I wonder why if parent records are supplied, why not child records?
If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...
Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
View 4 Replies
View Related
Jan 7, 2014
I have a table with a field called SeqId which is not an identity nor a sequence but a kind of autonumber field (max(SeqId) + 1). Now I have to do a MERGE between 2 tables where the one with SeqId is the target.
How can I get the next SeqId for every row added? I tried this:
MERGE dbo.CRM_MNP_ORIGINAL_NRN AS T
USING dbo.seriesnacionales AS S
ON (T.RANGE1 = S.RANGOINI )
WHEN NOT MATCHED THEN
INSERT (SeqId, Range1, Range2, OPERATORCODE, NRN, StartDate, CreateDate)
VALUES((SELECT dbo.FN_GetNextSeqId4CRM_MNP_ORIGINAL_NRN()), S.RangoIni, S.RangoFin, S.IdOperador,
'TEST_M', Convert(DATETIME, FECASIGNA , 103), SYSDATETIME())
WHEN MATCHED THEN
UPDATE SET T.Range1 = S.RangoIni, T.Range2 = S.RangoFin, T.OPERATORCODE = S.IdOperador
OUTPUT $action, Inserted.*, Deleted.*;
where the function just return Max(SeqId) + 1
but I always get the same value for that field.
View 2 Replies
View Related
Aug 13, 2014
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END
What is the best way to script this
View 1 Replies
View Related
Oct 20, 2014
I have some problem about Merge Replication.i'm trying to merge Database A to Database B in local Server. So Database i want publisher contain this:
Because only Data in Table change therefore so i choosed it 100% Snapshot was generated after that ( Problem not a valid window user i already figured out )After that. I created Local Subscription in same Server ( Pull subscriptions and Subscription type: Client ),now problem is throw out."The schema script 'vwBuyADT_513.sch' could not be propagated to the subscriber."
I tried research so many time in Google but any information i found isn't useful for this problem.This problem still can ignore,Synchronization still running. But after 4-5 Hours running..This messages throw out: "The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation"
My question is:
Is there anyway solve 2 probem? :
1. "The schema script 'vwBuyADT_513.sch' could not be propagated to the subscriber."
2. "The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation"
View 7 Replies
View Related
Feb 3, 2014
Merging table :
--------Dummy TABLE
create table #Tbl1 (date1 date,WSH varchar(10),ITN int,Executions int)
insert into #Tbl1 (date1 , WSH , ITN , Executions)
select '20130202' ,'ABC', 1 , 100
union all
select '20130203' ,'DEF', 1 , 200
[Code] .....
I want Result like this :
date1 WSH ITNExecutionsMCGPositions
2013-02-02 ABC 1 100 2 500
2013-02-03 DEF1 200 NULL NULL
2013-02-05 NULLNULL NULL 2 600
View 2 Replies
View Related
Nov 26, 2014
basically i have data like this
order_key comment
1 A
1 B
1 C
2 B
2 D
the data intends to be like this
order_key comment
1 A,B,C
2 B,D
View 3 Replies
View Related
Dec 24, 2014
I have a table that looks like this ...
idtype_codephone_num
11111-111-1111
12222-222-2222
21111-111-1111
32222-222-2222
I want to merge the data to look like this ...
idphone1 phone2
1111-111-1111222-222-2222
2111-111-1111NULL
3NULL222-222-2222
Basically if the type code is 1 one then move the data to column phone1, if the type is 2 then move it to column phone2.
This would be fairly simple if we always have type codes 1 and 2. But sometimes we can have type 1 and not type 2, or we could have type 2 and not type1.
Right now we only have 2 type codes. But, in the future we could be adding a 3rd type. So that would add a 3rd column (phone3).
Below is my code that I have written. I move the data into a temp table then list it. I am thinking of making this a view to my table. It works just fine. My question is, is there a better and more efficient way of doing this?
CREATE TABLE #Contacts (
id INT PRIMARY KEY,
phone1 VARCHAR(15),
phone2 VARCHAR(15)
)
-- Insert the records for type 1
INSERT INTO #Contacts
SELECT id,
phone_num,
NULL
FROM test1
WHERE type_code = '1'
-- Insert the records for type 2, if the id does not exist for type 1
INSERT INTO #Contacts
SELECT id,
NULL,
phone_num
FROM test1
WHERE NOT EXISTS (
SELECT 1
FROM #Contacts
WHERE #Contacts.id = test1.id
)
AND test1.type_code = '2'
-- if the id has both type 1 and 2, update the phone2 column with the data from type 2
UPDATE #Contacts
SET phone2 = test1.phone_num
FROM #contacts
JOIN test1 ON test1.id = #Contacts.id
WHERE type_code = '2'
SELECT id, phone1, phone2
FROM #Contacts
DROP TABLE #Contacts
View 2 Replies
View Related
Jul 26, 2015
We have a table in an SQL Server 2012 database that stores tree-like structures. Simplified for the purpose of my question, it has the following format:
Id int identity,
ParentId int,
GroupId int
Each record of the table represents an object identified by Id. An object may or may not have a parent in the same table, such that object.ParentId = parentObject.Id. A root object has ParentId = NULL. There are multiple root objects, so the table in fact stores multiple trees. What’s important is that the tree depth is not fixed, i.e. theoretically there can be any number of ancestor generations for an object. GroupId is a property of a root object; in theory none of the children of a root object has to have GroupId <> NULL; it can be assumed that any child has the same GroupId value as its root object.
A sample table having two roots (one grandparent and one parent), one non-root parent/child and 4 child roots:
Id ParentId GroupId
----------------------------------------------------------
1 NULL 200 root grandparent
2 1 NULL non-root parent/child
3 2 NULL child
4 2 NULL child
5 NULL 300 root parent
6 5 NULL child
7 5 NULL child
The table is not normalised, i.e. there’s no separate {root_object : group} table. However I don’t think normalising the table would solve the problem.
Now the problem. We need to set up merge replication from the table above (Master table) to the table of the same format in another DB. We need to replicate only those rows of the Master table that have a certain fixed GroupId value, e.g. 200 in the example above. If we ensure that GroupId in all descendant objects of a root object has the same value in the table as the root object itself that would be trivial. The table would look like this:
Id ParentId GroupId
----------------------------------------------------------
1 NULL 200 root grandparent
2 1 200 non-root parent/child
3 2 200 child
4 2 200 child
5 NULL 300 root parent
6 5 300 child
7 5 300 child
And the filter would look like this:
WHERE GroupId = 200
However out of performance considerations, we would like to avoid if possible filling GroupId for the descendant objects, because as it must be clear from the above, GroupId for a descendant object is quite easily deducible via a stored procedure or UDF (just need to go up the tree until ParentId = NULL). The problem is, I don’t know how to achieve this in a merge replication filter: it would only allow WHERE conditions and joins. I’ve have not had much luck with joins for merge replication in general, but here we have more complex algorithm, because the number of tree levels can be different for every object. And merge replication would not allow using UDF…
View 2 Replies
View Related
Aug 12, 2015
I need to merge column values (#Status.Status) based on OrderID onto #Orders.NewStausCombined field separated by commas .
CREATE TABLE #Status
(
ID INT IDENTITY (1,1) PRIMARY KEY,
OrderID INT,
Status VARCHAR(20)
)
INSERT INTO #Status ( OrderID, Status )
[code].....
View 3 Replies
View Related
Nov 5, 2015
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
View 2 Replies
View Related
Feb 10, 2015
I have a table:
declare tableName table
(
uniqueid int identity(1,1),
id int,
starttime datetime2(0),
endtime datetime2(0),
parameter int
)
A stored procedure has new set of values for a given id. Sometimes the startime and endtime are the same, in which case I update the value of parameter. Sometimes I add a new time range (insert statement), and sometimes I delete a time range (delete statement).
I had a question on merge, with insert, delete and update and I got that resolved. However I have a different question regarding performance of the merge statement.
If my target table has hundreds of millions of records and I want to delete/update/insert a handful of records, will SQL server scan the entire target table? I can't have:
merge ( select * from tableName where id = 10 ) as target
using ...
and I can't have:
merge tableName as target
using [my query] as source on
source.id = target.id and
source.starttime = target.startime and
source.endtime = target.endtime
where target.id = 10
...
This means I cannot filter the set of rows in the target table to a handful of records where id = 10.
View 1 Replies
View Related
Apr 26, 2015
With merge/insert statements ...Is DISTINCT best way to handle problem of source table containing duplicate rows, along with WHERE NOT IN statement? the source dataset is large and having to do DISTINCT and further filtering is taxing on the ETL.
DDL
source table
CREATE TABLE [dbo].[source](
[Product_ID] [INT] NOT NULL,
[ProductCode] [VARCHAR](20) NULL,
[ProductName] [VARCHAR](100) NULL,
[ProductColor] [VARCHAR](20) NULL,
[code]....
View 0 Replies
View Related
Apr 25, 2014
I am new to T-SQL development and here's what I am trying to do.
declare @cmd varchar(255)
set @cmd = 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4)'
while (@cmd <=13)
begin
insert into #dbcheck values (@cmd)
Set @cmd = @cmd + 1
end
but it is giving me an error
'Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4)' to data type int.'
Note - #dbcheck has only one column id int
View 3 Replies
View Related
Nov 17, 2014
I have resulting rows from a query similar to the following:
The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:
SELECT
[Employee ID] = emp.employee_id,
[Coverage Code 1] = enr.coverage_code,
[Coverage Date 1] = enr.coverage_date,
[Coverage Code 2] = case when enr.product_type = 'Accident.Accident'
then enr.coverage_code else NULL end,
[Code] ....
I basically want to merge the like Employee ID's together into a single row like the following:
I know I have done this before and it is probably pretty simple.
View 4 Replies
View Related
Sep 21, 2015
Can we pass dynamic values while pivoting?
Here is example
Declare @a date, @b date
set @a='2015-09-08 22:19:29.330'
set @b='2015-09-17 22:19:29.330'
create table #DateTemp(Full_Date_Text_YYYY_MM_DD datetime,Full_Date date)
insert into #DateTemp(Full_Date_Text_YYYY_MM_DD,Full_Date)
select '2015-09-09 00:00:00.000','2015-09-09'
[Code] ......
View 3 Replies
View Related
May 2, 2014
I have log files that I am loading into a table with duration data in the format "xx hrs xx min xx sec". Only the parts that are required will be there so if duration is only 2 seconds , the column will show "2 sec".
I am trying to get the duration into in to do some analysis on it and I have come up with this query so far which returns the correct data but i am wondering if there is a way to do what I am trying in a more readable format.
CREATE TABLE #tmp(duration VARCHAR(20))
INSERT INTO #tmp
VALUES ('1 hrs 20 min 12 sec'), --4812 sec
('48 sec'), --48 sec
('39 min 1 sec'), --2341 sec
('11 hrs 1 min 1 sec'), --39661 sec
('59 min 0 sec'), --3540 sec
('2 min 50 sec') --170 sec
And this is what I have so far
SELECT CASE
WHEN CHARINDEX('hrs', duration, 1) <> 0 THEN CAST(SUBSTRING(duration, 1, CHARINDEX(' hrs', duration, 1) - 1)AS int) * 3600 + CAST(SUBSTRING(duration, CHARINDEX(' hrs', duration, 1) + 5, CHARINDEX(' min', duration, 1) - 7)AS int) * 60 + CAST(SUBSTRING(duration, CHARINDEX(' min', duration, 1) + 5, CHARINDEX(' sec', duration, 1) - 14)AS int)
WHEN CHARINDEX('hrs', duration, 1) = 0
[Code] ...
I ultimately plan on converting this to a SSIS expression so that is why I am looking to simplify it.
View 3 Replies
View Related
Oct 16, 2014
In VBA, CLng(Now) will return the integer portion of a date CLng(Now) returns 41928, CDate(41928) then returns 10/16/2014. Is there something equivalent in SQL Server that will allow me to convert an integer value to a date?
In short, how can I convert a 100 year date to Gregorian (any format)?
View 3 Replies
View Related
Dec 3, 2014
I've got a column that holds a time value in Integer form as a number from midnight (IE military time) with no leading 0s or colons.
0 = 00:00
30 = 00:30
...
1700 = 1700 etc...
How can I convert this to a real time field?
View 9 Replies
View Related
Mar 9, 2015
I need my code to add the varchar CPUTM field + varchar CPUZIPTIM field which both has time values to see if greater than 2 hours. How do I do this when both fields are varchar. The value in CPUTM field is 335:55:20.97 duration time. My code is below.
CPUTM = 335:55:20.97 duration time
CPUZIPTM = 0:00:01.96 duration time
select * FROM [SMF_DATA].[dbo].[System_Management_Facility]
WHERE ((convert(varchar(13), CONVERT(time, CPUTM) + CONVERT(time, CPUZIPTM))) > '02:00:00.00')
View 2 Replies
View Related
May 31, 2015
How do I use the CAST or CONVERT function in the code below, I require a third column (named Diff) which Minus the StartTime from the EndTime and the result is outputted in the third column (named Diff).
Calculation: @Diff = (@EndTime - @StartTime)
I still want the variables (@StartTime and @EndTime) to remain as nvarchar.
The code:
DECLARE @StartTime nvarchar(10) = '12:10';
DECLARE @EndTime nvarchar(10) = '12:30';
DECLARE @Diff time(1) = '00:00';
SELECT @StartTime AS '@StartTime', @EndTime AS '@EndTimes', @Diff AS '@Diff';
View 7 Replies
View Related
Nov 25, 2013
I am trying to pivot some data as you would normally however I am trying to also group the pivot into three sub column groups too.
Basically the scenario is that I have three sub groups Budget, Person, RenewalDate for each Service (Service being the pivot point). So for each unique service I want to display the budget person and renewal date for each service by company.
I have created two tables to illustrate the base data and the required output.
How to do this dynamically because the number of Services is unknown, i.e. it could be 4 Services or 20, each with three sub columns, budget, person and renewal date.
Please find code below. It should be quite self explanatory as to what I am trying to do.
IMPORTANT:
1. I really need it to be dynamic
2. the Services are not standardised names, they are numbered for illustration purposes only, they vary in naming convention.
create table #BaseData
(
Company nvarchar(100),
Person nvarchar(50),
[Service] nvarchar(100),
Budget int,
RenewalDate datetime
)
[Code] .....
View 4 Replies
View Related
Dec 3, 2014
I need Dynamic Partition of SQL Table.
1. What is the best practice for partitioning (on date column)
2. The project on which i am working correctly have a case where in i get the update of my status flag after few days (Say 15 - 30) in that case if my data got into partition table how to update and how to search which partition has my data
3. Is creating partition occupies more disk space?
4. Is every partition would need index?
View 7 Replies
View Related
