T-SQL (SS2K8) :: How To Split One Record Into Multiple Records In Query Based On Start And End Date

Aug 27, 2014

I would like to have records in my Absences table split up into multiple records in my query based on a start and end date.

A random record in my Absences table shows (as an example):

resource: 1
startdate: 2014-08-20 09:00:00.000
enddate: 2014-08-23 13:00:00.000
hours: 28 (= 8 + 8 + 8 + 4)

I would like to have 4 lines in my query:

resource date hours
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4

Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.

What I now get is:

resource date hours
...
1 2014-08-19 NULL
1 2014-08-20 28
1 2014-08-21 28
1 2014-08-22 28
1 2014-08-23 28
1 2014-08-24 NULL
...

... instead of the correct hours per date (8, 8, 8, 4).

A very simplified extract of my code is:

DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-08-31'
SELECTh.res_id AS Resource,
t.datevalue,
(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,
(SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours
FROMResources h (NOLOCK)
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t

p.s.The 9538 value in the HRAbsenceHours function refers to the absences-workflowID.I can't get this solved.

View 1 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Get Start And End Date Based On A Criteria?

Apr 17, 2014

I have a scenario where i need to get the starting and ending date time based on the crieteria. The criteria is I always have my start date as NS or GS in the data column and my end date as GX so i need NS or GS to be my strart date based on ts Ascending and my end date as GX to be displayed in the same columns .

Create Table Test
(Tsq INT IDENTITY (1,1),
Data Varchar (150),
ts datetime,
Tpkt_type int)
insert into test values ('GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2')

[code]....

Expected Output

---------Data----------------- ts as starttime--------------tpkt_type------data-----------------------ts as endtime--------tpkttype-
'GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2' 'GX,1,0000000000000000000000000','2013-11-13 09:47:37.007','4'
'GS,000020,000021,000022,000023','2013-11-13 09:50:25.987','2', 'GX,1,0000000000000000000000000','2013-11-13 09:50:40.920','4'
'GS,000020,000021,000022,000023','2013-11-13 09:51:28.330','2', 'GX,1,0000000000000000000000000','2013-11-13 09:51:43.257','4'
'NS,000020,000021,000022,000023','2013-12-17 16:51:09.063','18', 'GX,1,0000000000000000000000000','2013-12-17 16:51:15.257','4'

View 9 Replies View Related

T-SQL (SS2K8) :: How To Change Multiple Records In One Record

Apr 14, 2015

i have a query i.e.

select project, description from table
with results

chocolate | white
chocolate | black
chocolate | brown
sugar | black
vinegar | yellow

i would like to get a result like:

chocolate | white, blakc, brown
sugar | black
vinegar | yellow

so I would like to get all values of one project in one records included with separators/a space in it

View 8 Replies View Related

T-SQL (SS2K8) :: Only One Record From Table With Multiple Records?

Sep 4, 2015

I have a scenario where ID has three flags.

For example

ID flag1 flag2 flag3
1 0 1 0
2 1 0 0
1 1 0 0
1 0 0 1
2 0 1 0
3 0 1 0

Now I want the records having flag2=1 only.. I.e ID=3 has flag2=1 where as ID = 1 and 2 has flag1 and flag3 =1 along with flag2=1. I don't want ID=1 and 2.

I can't make ID unique or primary. I tried with case when statements but it I am somehow missing the basic logic.

View 5 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

Results Produce A Single Record Based Off Of Parameters. Want To Change It So It Returns Multiple Records.

Dec 20, 2007

I have a query that will return one record as its results if you provide two variables: @login and @record_date. This works great if you only want one result. However, now what I want to do is not provide those variables and get the result set back for each login and record_date combination. The hitch is that there are several other variables that are built off of the two that are supplied. Here is the query:

DECLARE @login char(20), /*This sets the rep for the query.*/
@record_date datetime, /*This is the date that we want to run this for.*/
@RWPY decimal(18,2), /*This is the required wins per year.*/
@OCPW decimal(18,2), /*This is the opportunities closed per week.*/
@OACW decimal(18,2), /*This is opportunities advanced to close per week.*/
@TOC decimal(18,2), /*This is the total number of opportunities in close.*/
@OANW decimal(18,2), /*This is opportunities advanced to negotiate per week.*/
@TON decimal(18,2), /*This is the total number of opportunities in negotiate.*/
@OADW decimal(18,2), /*This is the opportunities advanced to demonstrate per week*/
@TOD decimal(18,2), /*This is the total number of opportunities in demonstrate.*/
@OAIW decimal(18,2), /*This is the opportunities advanced to interview per week.*/
@TOI decimal(18,2), /*This is the total number of opportunities in interview.*/
@OCW decimal(18,2), /*This is the opportunities created per week.*/
@TOA decimal(18,2) /*This is the total number of opportunities in approach.*/

SET @login = 'GREP'
SET @record_date = '12/18/2007'
SET @RWPY = (SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)/(SELECT target_deal FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)))
SET @OCPW = (SELECT @RWPY/weeks FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OACW = (SELECT @OCPW/cls_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOC = (SELECT @OACW*(cls_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OANW = (SELECT @OACW/neg_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TON = (SELECT @OANW*(neg_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OADW = (SELECT @OANW/dem_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOD = (SELECT @OADW*(dem_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OAIW = (SELECT @OADW/int_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOI = (SELECT @OAIW*(int_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OCW = (SELECT @OAIW/app_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOA = (SELECT @OCW*(app_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

SELECT loginname,
CAST(@TOA AS decimal(18,1)) AS [Opps in Approach],
app_time AS [Approach Average Time],
app_perc_adv AS [Approach Perc Adv],
CAST(@TOI AS decimal(18,1)) AS [Opps in Interview],
int_time AS [Interview Average Time],
int_perc_adv AS [Interview Perc Adv],
CAST(@TOD AS decimal(18,1)) AS [Opps in Demonstrate],
dem_time AS [Demonstrate Average Time],
dem_perc_adv AS [Demonstrate Perc Adv],
CAST(@TON AS decimal(18,1)) AS [Opps in Negotiate],
neg_time AS [Negotiate Average Time],
neg_perc_adv AS [Negotiate Perc Adv],
CAST(@TOC AS decimal(18,1)) AS [Opps In Close],
cls_time AS [Close Average Time],
cls_perc_adv AS [Close Perc Adv]
FROM #pipelinehist
WHERE loginname = @login AND record_date = @record_date

Here is some sample data to use with this. With this sample data what I want to get back is a total of 30 records in the result set each with its data specific to the login and record_date of that returned record.

CREATE TABLE #pipelinehist (
glusftboid int IDENTITY(1,1) NOT NULL,
record_date datetime NOT NULL,
loginname char(20) NOT NULL,
app_new float NOT NULL,
app_time float NOT NULL,
app_perc_adv float NOT NULL,
int_time float NOT NULL,
int_perc_adv float NOT NULL,
dem_time float NOT NULL,
dem_perc_adv float NOT NULL,
neg_time float NOT NULL,
neg_perc_adv float NOT NULL,
cls_time float NOT NULL,
cls_perc_adv float NOT NULL,
target_deal money NOT NULL,
annual_quota money NOT NULL,
weeks int NOT NULL
) ON [PRIMARY]

INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'AREP', 56.8, 26.9, 0.57, 29.5, 0.47, 20, 0.67, 80.7, 0.53, 2.1, 0.97, 2194.93, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'BREP', 33.2, 0.5, 0.9, 7.7, 0.77, 8, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'CREP', 210.2, 0.3, 0.87, 6.6, 0.5, 13.7, 0.4, 16.3, 0.43, 1.5, 0.91, 461.25, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'DREP', 47.6, 5, 0.53, 33.3, 0.6, 57.5, 0.53, 50, 0.7, 1.5, 1, 2045.7, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'EREP', 75.3, 110.9, 0.47, 36, 0.5, 17.4, 0.87, 20.3, 0.6, 7.2, 0.83, 2021.74, 775000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'FREP', 17.2, 23.3, 0.73, 6.8, 0.8, 6.3, 0.93, 29.7, 0.67, 15.5, 0.83, 2218.95, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'GREP', 105.4, 67, 0.2, 32.9, 0.43, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'HREP', 116.4, 118.5, 0.33, 30.9, 0.77, 46.3, 0.77, 46.3, 0.6, 0.9, 0.97, 1735.13, 1150000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'IREP', 143.3, 9, 0.77, 96, 0.17, 21.6, 0.77, 39.9, 0.43, 0.9, 0.93, 1385.43, 400000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 67.6, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'KREP', 107.6, 38.2, 0.23, 47.5, 0.47, 21.3, 0.77, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'LREP', 18.6, 8.3, 0.87, 23.2, 0.57, 2.6, 0.87, 12.2, 0.67, 1, 1, 1229.02, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'MREP', 4, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.4, 1, 1091.22, 350000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'NREP', 54, 21.6, 0.57, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'OREP', 37.6, 24.4, 0.57, 50.1, 0.43, 6.7, 0.87, 15.6, 0.73, 0.9, 0.97, 1163.48, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'AREP', 57.2, 32.5, 0.6, 29.5, 0.47, 20, 0.67, 85.6, 0.5, 2.1, 0.97, 2194.93, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'BREP', 33.9, 0.5, 0.93, 7.8, 0.73, 8.3, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'CREP', 152.1, 0, 0.87, 4.3, 0.67, 9.7, 0.47, 15.7, 0.47, 1.8, 0.85, 396.43, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'DREP', 80.5, 9.8, 0.5, 40.7, 0.57, 68.3, 0.43, 64.2, 0.57, 1.5, 1, 2045.7, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'EREP', 61, 92.1, 0.5, 31, 0.53, 16.9, 0.83, 17.7, 0.6, 7.3, 0.83, 2318.04, 775000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'FREP', 19.4, 21.1, 0.7, 5.3, 0.77, 2.2, 0.93, 33.3, 0.7, 9.7, 0.87, 1937.17, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'GREP', 81.7, 40.5, 0.3, 33, 0.37, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'HREP', 128.6, 115.7, 0.3, 30.9, 0.77, 46.3, 0.77, 48.8, 0.6, 0.9, 0.97, 1728.29, 1150000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'IREP', 100.9, 3.4, 0.77, 86.2, 0.27, 18, 0.8, 54.7, 0.37, 0.9, 0.93, 1385.43, 400000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 63.5, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'KREP', 285.2, 36.5, 0.1, 46, 0.43, 24.2, 0.73, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'LREP', 17.6, 7.3, 0.9, 21.5, 0.57, 1.7, 0.87, 12.2, 0.67, 1, 1, 1250.54, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'MREP', 26.7, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.3, 1, 979.7, 350000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'NREP', 61.6, 20.8, 0.5, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'OREP', 31.6, 16.9, 0.63, 50.1, 0.43, 7.2, 0.87, 19.5, 0.7, 0.9, 0.97, 1303.48, 330000, 50)

View 3 Replies View Related

T-SQL (SS2K8) :: Most Updated Records From Multiple Join Query

Mar 28, 2014

i have Two tables... with both the table having LastUpdated Column. And, in my Select Query i m using both the table with inner join.And i want to show the LastUpdated column which has the maxiumum date value. i.e. ( latest Updated Column value).

View 5 Replies View Related

Split One Row In Multiple Row Based Upon Integer Value

Jun 24, 2014

My code looks like this for now:

Code:
SELECT CDELIG.CDELIG_QTE AS [Quantity],
(ARTICLE.ART_PRIXPUBLIC * CDELIG.CDELIG_QTE) * 100 AS [Total line price],
(CDELIG.CDELIG_PU_SUBACC_MNT * CDELIG.CDELIG_QTE) * 100 AS [Grant]
FROM CDELIG
INNER JOIN ARTICLE ON ARTICLE.ART_ID = CDELIG.ART_ID
And the result like this:

Quantity Total line price Grant
120 84000 0.0000
255 178500 0.0000
1 700 0.0000

The problem is that if a quantity is higher than 99 i got to split the row. And the others has to change also because the quantity would have change.The expected result will be like this:

Quantity Total line price Grant
99 69300 0
21 14700 0
99 69300 0
99 69300 0
57 39900 0
1 700 0

View 6 Replies View Related

Split A Row Into Multiple Rows Based On Column

Sep 5, 2014

I need to split a row into multiple rows based on multiple column values.

time_id = 111
employee_id = 222
time_in = 10:00
time_out = 16:00
break1_in = 12:00
break1_out = 13:00
break2_in = 14:00
break2_out = 15:00

I would like to break this into multiple time_in/time_out based on if they have breaks. Breaks are not required and will come across blank if non are taken.

row 1
time_in 10:00
time_out 12:00

row 2
time_in 13:00
time_out 14:00

row 3
time_in 15:00
time_out 16:00

View 2 Replies View Related

T-SQL (SS2K8) :: Split Function That Allows Multiple Character Delimiter

Mar 25, 2015

I have a table with a varchar column that is delimited by char(227)+char(228)
i.e.,

OrNo=7807ãäSeqNo=1ãäPStopType=PãäPCity=TillsonburgãäPSt=ONãäPZIP=N4G4J1ãäPAdr1=10 ROUSE STREETãäPAdr2=ãäPLat=42.8625ãäPLon=-80.7267ãäDStopType=DãäDCity=GaffneyãäDSt=SCãäDZIP=29341ãäDAdr1=121 PAN AMERICAN DRIVE

Looking for split function that accepts a multiple character value as a delimiter?

View 7 Replies View Related

Split One Row Into Multiple Rows Based On Time Elements

Feb 5, 2007

I'm dealing with a problem.

The record information example

DateTimeStart , DateTimeEnd , action , duration (seconds)
2007-02-02 10:30:22 , 2007-02-02 11:30:22 action1 , 600

what i want is for every half hour between start and end a record

10.30 action1
11.00 action1
11.30 action1

how can i create this, i'm a little stuck on this

View 2 Replies View Related

T-SQL (SS2K8) :: Count Record Based On Group

Dec 10, 2014

This my table named myData

CREATE TABLE [dbo].[myData](
[idx] [int] NULL,
[paymentMethod] [nvarchar](200) NULL,
[daerahKutipan] [int] NULL,
[payer] [int] NULL,

[code]....

I want to calculate the number of payer Group By paymentMethod. The number of payer must be divided by daerahKutipan. So far, my query as follow

select paymentMethod,
COUNT(CASE WHEN daerahKutipan = 1 THEN payer ELSE 0 END) figure_Seremban,
COUNT(CASE WHEN daerahKutipan = 3 THEN payer ELSE 0 END) figure_KualaPilah,
COUNT(CASE WHEN daerahKutipan = 4 THEN payer ELSE 0 END) figure_PortDickson,
COUNT(CASE WHEN daerahKutipan = 5 THEN payer ELSE 0 END) figure_Jelebu,
COUNT(CASE WHEN daerahKutipan = 6 THEN payer ELSE 0 END) figure_Tampin,
COUNT(CASE WHEN daerahKutipan = 7 THEN payer ELSE 0 END) figure_Rembau,

[code]....

View 1 Replies View Related

T-SQL (SS2K8) :: How To Add Previous Row End Date As Next Row Start Date

Mar 14, 2014

Let's say the first row returned has StartDate = 1/1/2014 and EndDate is 1/2/2014. The next row I want the StartDate to equal the previous row EndDate so it would be 1/2/2014 as StartDate. This compounds every row basically the third row StartDate would be the second row EndDate. All in one select statement if it can be done. Using SQL2008r2.

View 9 Replies View Related

DTS - Split Single Source Record (text) To Multiple Target (sql)

Aug 31, 2000

I am using DTS and VBScript in DataPump tasks in order to transfer large amounts of data from text files to an SQL database.

As the database uses a normalized schema, there is often the case of inserting multiple records in a destination table from various fields of the same record of the source text file.

For example, if the source record contains information about goods sold like date, customer, item code, item name and total amount, and does so for a maximum of 3 goods per sale (row), therefore has the structure:

[date], [custid], [code1], [name1], [amount1], [code2], [name2], [amount2], [code3], [name3], [amount3]

trying to transfer that record to a [SALES] target table (in a normalized database), we would have to split each source record as follows:

[date], [custid], [code1], [name1], [amount1]
[date], [custid], [code2], [name2], [amount2]
[date], [custid], [code3], [name3], [amount3]

What is the best way to do this using DTS?

I have tried using a datapump task and VBScript, and I guess it has to do with the DTSTransformStat_**** constants, but none of those I used seems to work

Vasilis Siatravanis,
siatravanisv@interamerican.gr , vasilliss@hotmail.com

View 6 Replies View Related

SQL Server 2008 :: Split Single Row Into Multiple Rows Based On Column Value (quantity)

Jan 30, 2015

Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.

SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE
FROM @SPLITROW s
INNER JOIN master.dbo.spt_values t ON t.type='P'
AND t.number BETWEEN 1 AND s.QTY

Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?

View 1 Replies View Related

Integration Services :: SSIS Execute Multiple Packages Based On Conditional Split

Sep 4, 2015

We are building a dataload application where parameters are store in a table. And there are multiple packages for each load.There is a column IsChecked column if it is 1 then only the child package should execute.Created a master package. In which i have taken execute SQL task in that storing a results in variable and based on the result the child package should execute. But In executesql task i selected result set as full result set.  I am getting the below error.

[Execute SQL Task] Error: Executing the query "SELECT  isnull(ID ,0) AS ID FROM DataLoadParameter..." failed with the following error: "The type of the value (DBNull) being assigned to variable "User::LoadValue" differs from the current variable type (Int32). Variables may not change type during execution. Variable types are strict, except for variables of type Object.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

View 3 Replies View Related

SQL Server 2008 :: Split Varchar Variable To Multiple Rows And Columns Based On Two Delimiter

Aug 5, 2015

declare @var varchar(8000)
set @var='Name1~50~20~50@Name2~25.5~50~63@Name3~30~80~43@Name4~60~80~23'

---------------------

Create table #tmp(id int identity(1,1),Name varchar(20),Value1 float,Value2 float,Value3 float)
Insert into #tmp (Name,Value1,Value2,Value3)
Values ('Name1',50,20,50 ), ('Name2',25.5,50,63 ), ('Name3',30,80,43 ), ('Name4',60,80,23)

select * from #tmp

I want to convert to @var to same like #tmp table ..

"@" - delimiter goes to rows
"~" - delimiter goes to columns

View 6 Replies View Related

T-SQL (SS2K8) :: Calculation - Get Records Based On 100 / 80 / 60 / 40 Miles Radius

Feb 9, 2015

I have zip code database and i am trying to get the records based on 100/80/60/40 miles radius. Below the sample structure of my table

Create table ZipCodes(ID bigint primary key identity(1,1),ZipCode varchar(10),
Address varchar(4000), city varchar(50),state varchar(50),Latitude float,Longitude float)If i pass the Zipcode as '10021'

I need to get zipcodes and other details of my table which is 20 mile radius circle.

I Google through and got couple of article explains about using Haversine Formula. Also, following link has a function to calculate the distance.

View 9 Replies View Related

Obtaining Business Hours Based On Start And End Date

Jul 23, 2005

I have a transaction log that tracks issues from a call center. Eachtime an issue is assigned to someone else, closed, etc. I get a timestamp. I have these time stamps for the beginning of an issue to theend of an issue and I'd like to determine how many business hours theseissues were open.Issue BeginDt Enddt Total hours1 3/29/05 5:00 PM 4/1/05 2:00 PM 69Basically, this is the type of data I'm looking at and my hours of workare from 7:30 - 5:00 weekdays. I need to come up with a way to removeall nonbusiness hours, weekends, & holidays from the difference of thetwo dates. Issues can span for 2-3 days or 20-30 days.Please let me know if anyone has any ideas or has done something likethis before.Thanks!

View 5 Replies View Related

SQL Server 2014 :: Split Time Span Into Multiple Records?

Oct 30, 2015

I have a table that includes the fields below:

ID - INT
Machine - TINYINT
StartTime - DATETIME
EndTime - DATETIME

What I am trying to do is figure out how much time is used for production per day. The problem is, there are production runs that run over midnight and possible multiple days without ending. For example, if I have the following data:

ID - 1
Machine - 2
StartTime - 2015-09-01 22:00:00.000
EndTime - 2015-09-03 22:00:00.000

So what I am looking for is taking the above record and turning it into 3 records like below:

ID Machine StartTime EndTime
1 2 2015-09-01 22:00:00.000 2015-09-01 23:59:59.999
1 2 2015-09-02 00:00:00.000 2015-09-02 23:59:59.999
1 2 2015-09-03 00:00:00.000 2015-09-03 22:00:00.000

View 5 Replies View Related

T-SQL (SS2K8) :: User Defined Function That Returns Start And End Date

Oct 15, 2014

Any UDF that accepts a Month and Year and returns Start Date and End Date.

For example @Month = 01 and @Year = 2014 would return a StartDate of 2014-01-01 and an EndDate of 2014-01-31.

View 8 Replies View Related

T-SQL (SS2K8) :: Pass Dates For Previous Months Start And End Date

Mar 16, 2015

I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1,
DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1

View 1 Replies View Related

T-SQL (SS2K8) :: Copy Master Record And Its Detail Records

Aug 26, 2014

I have been asked to give the users the ability to copy a set of records within the database. The current setup is

Master Table: JSA1
JSAID: (PK, int, not null)
JOBTITLE(nvarchar(200), null)
PlantNumber(int, not null)

Detail Table: tblSteps
STEPID (PK, int, not null)
JSAID (FK, int, not null)
StepNo (int, null)
BasicSteps (nvarchar(200), null)
DBPhoto(varbinary(max), null)

The plant number field is a location based field that the application uses to filter/select data on for the end users. What they want to be able to do is to select a record, select another location from a dropdown list and then click a button that duplicates the master record and the detail records to the new location.

I am thinking that a stored procedure passing the JSAID and new Location number to do it, I am just not sure how to get the new ID when I go to copy the detail records.

View 4 Replies View Related

SQL Server 2008 :: Loop Through Date Time Records To Find A Match From Multiple Other Date Time Records?

Aug 5, 2015

I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.

E.g.

Query 1

Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'

Table

Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'

Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.

I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.

View 5 Replies View Related

Integration Services :: SSIS - Split Multi Value Column Into Multiple Records?

Oct 12, 2014

I have one scenario

Table
Col1. Col2
1. A,b,c,df,ghf
2. C,b
3. B

Output should be

Col1. Col2
1. A
1. B
1. C
1. Df
1. Ghf
2. C
2. B
3. B

View 9 Replies View Related

SQL 2012 :: SSRS Page Break Based On Start And End Date Parameters

Jul 7, 2015

I have a detailed report in ssrs in which data can come from start date and end date parameters.

but the problem is. for example i gave startdate as 01/01/2015 and end date as 09/31/2015 then the data must be in displayed in such a way that jan month in one tab and feb month data in one tab and sooo on to sep month data in new tab when i export to Excel.

Is this possible in ssrs ?

View 1 Replies View Related

T-SQL (SS2K8) :: Better Method To Detect Record With Multiple / All 0s?

Feb 17, 2015

Better method to detect records with one or more 0's, other than hard coding all of the potential values. For example, I need to detect account duplicates where the duplicate contains multiple/all 0's in the "MasterAccount" column:

DECLARE @Account TABLE(AccountNo varchar(3), MasterAccount varchar(10) )
INSERT INTO @Account
SELECT 'ABC','0000000ABC' UNION
SELECT 'DEF','0000000DEF' UNION
SELECT 'ABC','0000000000' UNION ---- unwanted duplicate
SELECT 'DEF','00000' UNION ---- unwanted duplicate

[Code] ....

Is there a better method than this?

View 5 Replies View Related

T-SQL (SS2K8) :: Join 2 Table Based On Date

Jul 16, 2014

I have 2 tables:

Table Transaction
-----------------
EmpID TransDate
00001 1/1/2014
00001 1/2/2014
00001 1/3/2104
00001 1/4/2014
00001 1/5/2014
00001 1/6/2014
00001 1/15/2014
00001 2/1/2014
00001 2/2/2014
00001 2/20/2004 ....

Table Master
---------------------------
EmpID EffectiveDateFr Group
00001 1/1/2014 A
00001 1/5/2014 B
00001 1/9/2014 C
00001 2/1/2014 B
00001 2/20/2014 A ....

I want to create query the output should be:

EmpID TransDate Group
00001 1/1/2014 A
00001 1/2/2014 A
00001 1/3/2104 A
00001 1/4/2014 A
00001 1/5/2014 B
00001 1/6/2014 B
00001 1/15/2014 C
00001 2/1/2014 B
00001 2/2/2014 B
00001 2/20/2004 A

View 4 Replies View Related

T-SQL (SS2K8) :: Insert Multiple Values Based On Parameter

Jul 22, 2014

I need to write SP where user select SUN to MON check boxes. If user select Class A with sun,mon and wed check boxes then i need to insert data as below

CLASS Days
A sun
A Mon
A wed

View 6 Replies View Related

Select Record Based On Max Date

Sep 12, 2012

I have the following table:

Occ_Num Feature_Num Trans_Date Peril_Desc
123 1 1-2-2012 Water
123 1 1-11-2012 Ice
123 2 1-2-2012 Other
123 2 1-13-2012 Other
123 2 1-19-2012 Wind

I want to select each Occ_Num, Feature_NUM, Trans_Date, and PERIL_Desc but with only the Peril that was part of the max trans_date.

So i would want the following from above:

Occ_Num Feature_Num Trans_Date Peril_Desc
123 1 1-11-2012 Ice
123 2 1-19-2012 Wind

I'm having trouble with the syntax need to accomplish this.

View 2 Replies View Related

Selecting Only 1 Record Based On Multiple Criteria

Jan 31, 2014

I have inherited a query which currently returns multiple instances of each work order because of the joined tables. The code is here and I've detailed the criteria needed below but need the best way to accomplish this:

Select h.worknumber, h.itemcode, h.descr, h.task_descr, h.qty, h.itemised,
h.serialnum, h.manufacturer, h.model_id, h.depot, h.date_in, h.date_approved,
h.est_complete_date, h.actual_complete_date, h.meterstart, h.meterstop,
h.custnum, h.name cust_name, h.addr1, h.addr2, h.town, h.county, h.postcode,
h.country_id, h.contact, h.sitename, h.siteaddr1, h.siteaddr2, h.sitetown,

[Code] ....

Each work order should only be returned once, and with the following additional criteria:

1. i.meter - this should return only the lowest number from that file.

2. sm.next_calendar_date - this should return only the most recent date out of those selected for the certificates on this piece of equipment

3. wh.meterstop as [Last Service Hours],
wh.date_created as [Last Service] - this should return the number from wh.meterstop at the most recent wh.date_created for that piece of equipment.

View 1 Replies View Related

Return Value Based On Record With Multiple Types

Jan 15, 2015

I want to return Order records which are one type and don't have the other type.

The issue is I have Orders with which has 2 distriubtion types .

Example
Order 12345 has Type S and Type X.
Order 67891 has Type S

I only want to return Order 67891 that are s Type and does not have type X

View 1 Replies View Related

Case Statement - Calculate No Of Days Based On User Input Start And End Date

Jan 5, 2015

I have to produce a report to calculate no of days based on user input start date and end date. I have tried to explain below.

say for eg: in the tables I have emp name
user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff
within his work period he did different roles:

PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days
PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days
PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days
Total days 53 Days

For this :

I calculated datediff + 1 and got sub jobs days BUT

say financial director wants to see Title of 'Sub Jobs' with 'Days' from 1st Dec to 31st Dec

so on paper I calulated as :

1-31 Dec 2014
PhaniMarketing NULL (Do not fall in Req Dt)
PhaniAdmin 20 (Deduct 2 days of Nov & calculated 20 days of Dec)
PhaniCRM 11 (Deduct 20 days of Nov and deduct 11 days of Jan so for Dec , we got 11 days)
Total days 31

HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.

View 4 Replies View Related







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