|
Just noting....
"lob", from the file name suggests binary data.
In general, programming languages differentiate between binary and text data.
Your code used a "String" type.
That works under one or both of the following conditions.
1. The files wear in fact text
2. Excel did not attempt to translate them.
You can validate the above by exporting at least one file and verifying that it matches the input both in length and content.
|
|
|
|
|
Hi,
I am using MySQL.
Can someone tell me how to write the sql query for retrieving all the last inserted Ids (plural) ?
I have a table in which a column has been inserted by serveral rows everytime depending on how many subject the users tick in the checkboxes.
Now, I have to get all the auto-incremental Ids from all the Ids that have just been inserted.
Is there a way to do it via sql query ?
|
|
|
|
|
|
Hi Richard,
I won't be able to know because each time the subject_Ids varies, it is dependent on the user's no of checkboxes that have been ticked.
So, how ?
|
|
|
|
|
You will have to use some other logic to find out how many new records have been added. Possibly by finding the highest ID number before you start.
|
|
|
|
|
In MS SQL Server, you'd use the OUTPUT clause. Unfortunately, there doesn't seem to be an equivalent for MySQL.
Selecting the top N rows won't work - another user might have inserted rows between your INSERT and SELECT statements. That's why the LAST_INSERT_ID function exists.
MySQL :: MySQL 5.7 Reference Manual :: 27.8.15.3 How to Get the Unique ID for the Last Inserted Row[^]
I suspect you might need to insert the values one at a time, and use LAST_INSERT_ID to retrieve the ID.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
But, how do I insert one at a time when my situation requires me to insert by batch ?
|
|
|
|
|
You can add a timestamp or guid column to mark them. Essentially, you need to have your own logic.
There are two kinds of people in the world: those who can extrapolate from incomplete data.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
It's been a while since I've used Access.
I have an Access database with three tables summarized as follows.
Table1
- ID (Primary Key)
- Title (Text)
Table2
- ID (Primary Key)
- Type (Table1.ID combobox lookup single select)
- Other_Fields
Table3
- ID (Primary Key)
- Type (Table1.ID combobox lookup single select)
- Data (Table2.Type combobox multiselect using a query)
- Other_Other_Fields
Without using a form, I would like the Data combobox in Table3 to display only the rows in Table2 where Table2.Type = Table3.Type (=Table1.ID). An inner join is what I'm using, but it's joined to Table1.
SELECT Table2.ID, Table2.Type, Table2.Other_Fields
FROM Table2 INNER JOIN Table1 ON Table2.Type = Table1.ID
WHERE (Table2.Type=Table3.Type);
Here's the problem, according to most of the examples that I've 'oogled, this is done by using a form and requerying the data upon combo update. However, I'm trying to implement this in the table.
The problem that I'm seeing is that when I click on any of the Data fields in Table3, it uses the Type ID of the very first row in Table3. If I change that value of the first row's Type field, the table gets repopulated using this new value for all rows. However, if I click on the Home | Records | Refresh menu (not Refresh All), the row that is selected stays selected and the combobox gets populated with the correct data using that row's Type ID.
So my question is, is there a workaround for this without using a form or am I banging my head against a wall? Is this a "by-design feature" (aka bug)?
Thanks!
|
|
|
|
|
Hi All,
I have three Tables, TableA, TableB and TableC, TableA is loaded from a huge Flat file, TableB is Temp Table or Table Variable, TableC is the Table which is in Production.
I want to load only those records into TableB which are not existing in TableC, I have to check all the columns of the TableC for loading into TableB, means if there are duplicate rows coming from TableA (or from Flat file which already exists in TableC) then those shouldn't be loaded into TableB, we need to check the duplication for all the Columns in every row.
Any advice will be greatly helpful, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
What have you tried or what ideas do you have?
One way would be to use a left outer join on all the fields and records that are null will need inserting.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am planning to try this way
select * from SAW_raw.[EOC].[EoCFalloutSolicitationResponseErrorImportStaging] a
inner join [EOC].[FalloutSolicitationResponseErrorDetail] b
on b.CoveredID=a.[0 Covered ID]
and b.CoveredindividualsInsuranceIDorCardnumber=a.[1 Covered individuals Insurance ID/card number]
and b.UserDefinedField2=a.[2 User Defined field 2]
and b.SSNorITIN=a.[3 SSN or ITIN]
and b.FirstName=a.[4 First Name]
and b.MiddleName=a.[5 Middle Name]
and b.LastName=a.[6 Last Name]
and b.Suffix=a.[7 Suffix]
and b.DateofBirth=a.[8 Date of Birth]
and b.UserDefinedField1=a.[9 User Defined field 1]
and b.UserDefinedField3=a.[10 User Defined field 3]
and b.CoveredUserDefinedField1=a.[11 Covered User Defined field 1]
and b.CoveredUserDefinedField2=a.[12 Covered User Defined field 2]
and b.CoveredUserDefinedField3=a.[13 Covered User Defined field 3]
and b.CoveredUserDefinedField4=a.[14 Covered User Defined field 4]
and b.Comments=a.[15 Comments]
and b.SolicitationRequiredIndicator=a.[16 Solicitation Required indicator]
and b.ErrorMessage=a.[17 Error Message]
Put this query with not exists condition, but I have only rough ideas but if something good comes in I would love to use it though or while loop with Table Variable is another option, not sure if it effects the performance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
ALTER procedure [EOC].[UpdateFalloutSolicitationResponseErrorDetail] (@FullFileName nvarchar(max)='')
AS
BEGIN
TRUNCATE TABLE Saw_raw.[EOC].[FalloutSolicitationResponseErrorDetail_InsertedRecords]
TRUNCATE TABLE Saw_raw.[EOC].[WorkInserted]
DECLARE @TeamId bigint =0, @MaxInstanceIdFromSaw_2 bigint=0, @ActivityId bigint=0
, @BatchId bigint=0, @StatusId int=0, @CreatedbyAccountId int=0, @DepartmentId int=1, @PieceCount int=1, @RecordCount int =0;
SELECT top 1 @TeamId=Id FROM SAW_2.[dbo].[Team] WHERE TeamName='MEP - Small Group' AND DepartmentId=@DepartmentId
SELECT top 1 @ActivityId=Id FROM SAW_2.[dbo].Activity WHERE ActivityName='EoC – Facets Inbound Error Report' AND TeamId=@TeamId
SELECT top 1 @BatchId=Id FROM SAW_2.[dbo].Batch WHERE BatchName ='EoC Fallout Error Reports'
SELECT top 1 @CreatedbyAccountId=Id FROM SAW_2.[dbo].Account WHERE LanId ='SYSTEM'
select @StatusId=Id from SAW_2.[dbo].[Status] WHERE StatusName='New'
IF EXISTS (
SELECT * FROM sys.key_constraints
WHERE type = 'PK'
AND OBJECT_NAME(parent_object_id) = 'FalloutSolicitationResponseErrorDetail'
)
BEGIN<br />
ALTER TABLE [EOC].[FalloutSolicitationResponseErrorDetail]
DROP CONSTRAINT PK_FalloutSolicitationResponseErrorDetail_InstanceId
END
IF EXISTS (SELECT name FROM sys.indexes<br />
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_CoveredID')
BEGIN
DROP INDEX IX_FalloutSolicitationResponseErrorDetail_CoveredID ON [EOC].[FalloutSolicitationResponseErrorDetail];<br />
END
IF EXISTS (SELECT name FROM sys.indexes<br />
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_CoveredindividualsInsuranceIDorCardnumber')
BEGIN
DROP INDEX IX_FalloutSolicitationResponseErrorDetail_CoveredindividualsInsuranceIDorCardnumber ON [EOC].[FalloutSolicitationResponseErrorDetail];<br />
END
IF EXISTS (SELECT name FROM sys.indexes<br />
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_SSNorITIN')
BEGIN
DROP INDEX IX_FalloutSolicitationResponseErrorDetail_SSNorITIN ON [EOC].[FalloutSolicitationResponseErrorDetail];<br />
END
IF EXISTS (SELECT name FROM sys.indexes<br />
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_LastName')
BEGIN
DROP INDEX IX_FalloutSolicitationResponseErrorDetail_LastName ON [EOC].[FalloutSolicitationResponseErrorDetail];<br />
END
IF EXISTS (SELECT name FROM sys.indexes<br />
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_DateofBirth')
BEGIN
DROP INDEX IX_FalloutSolicitationResponseErrorDetail_DateofBirth ON [EOC].[FalloutSolicitationResponseErrorDetail];<br />
END
IF EXISTS (SELECT name FROM sys.indexes<br />
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_IsActive')
BEGIN
DROP INDEX IX_FalloutSolicitationResponseErrorDetail_IsActive ON [EOC].[FalloutSolicitationResponseErrorDetail];<br />
END
MERGE INTO
EOC.FalloutSolicitationResponseErrorDetail a
USING
SAW_raw.[EOC].[EoCFalloutSolicitationResponseErrorImportStaging] b
ON
--a.InstanceId = b.Id
LTRIM(RTRIM(ISNULL(a.CoveredID, '')))=LTRIM(RTRIM(ISNULL(b.[0 Covered ID],'')))
AND LTRIM(RTRIM(ISNULL(a.CoveredindividualsInsuranceIDorCardnumber, '')))=LTRIM(RTRIM(ISNULL(b.[1 Covered individuals Insurance ID/card number],'')))
AND LTRIM(RTRIM(ISNULL(a.UserDefinedField2, '')))=LTRIM(RTRIM(ISNULL(b.[2 User Defined field 2],'')))
AND LTRIM(RTRIM(ISNULL(a.SSNorITIN, '')))=LTRIM(RTRIM(ISNULL(b.[3 SSN or ITIN],'')))
AND LTRIM(RTRIM(ISNULL(a.FirstName, '')))=LTRIM(RTRIM(ISNULL(b.[4 First Name],'')))
AND LTRIM(RTRIM(ISNULL(a.MiddleName, '')))=LTRIM(RTRIM(ISNULL(b.[5 Middle Name],'')))
AND LTRIM(RTRIM(ISNULL(a.LastName, '')))=LTRIM(RTRIM(ISNULL(b.[6 Last Name],'')))
AND LTRIM(RTRIM(ISNULL(a.Suffix, '')))=LTRIM(RTRIM(ISNULL(b.[7 Suffix],'')))
AND convert(nvarchar(MAX), a.DateofBirth, 110) = convert(nvarchar(MAX), Cast(b.[8 Date of Birth] as Datetime), 110)
AND LTRIM(RTRIM(ISNULL(a.UserDefinedField1, '')))=LTRIM(RTRIM(ISNULL(b.[9 User Defined field 1],'')))
AND LTRIM(RTRIM(ISNULL(a.UserDefinedField3, '')))=LTRIM(RTRIM(ISNULL(b.[10 User Defined field 3],'')))
AND LTRIM(RTRIM(ISNULL(a.CoveredUserDefinedField1, '')))=LTRIM(RTRIM(ISNULL(b.[11 Covered User Defined field 1],'')))
AND LTRIM(RTRIM(ISNULL(a.CoveredUserDefinedField2, '')))=LTRIM(RTRIM(ISNULL(b.[12 Covered User Defined field 2],'')))
AND LTRIM(RTRIM(ISNULL(a.CoveredUserDefinedField3, '')))=LTRIM(RTRIM(ISNULL(b.[13 Covered User Defined field 3],'')))
AND LTRIM(RTRIM(ISNULL(a.CoveredUserDefinedField4, '')))=LTRIM(RTRIM(ISNULL(b.[14 Covered User Defined field 4],'')))
AND LTRIM(RTRIM(ISNULL(a.Comments, '')))=LTRIM(RTRIM(ISNULL(b.[15 Comments],'')))
AND LTRIM(RTRIM(ISNULL(a.SolicitationRequiredIndicator, '')))=LTRIM(RTRIM(ISNULL(b.[16 Solicitation Required indicator],'')))
AND LTRIM(RTRIM(ISNULL(a.ErrorMessage, '')))=LTRIM(RTRIM(ISNULL(b.[17 Error Message],'')))
WHEN MATCHED THEN
UPDATE SET
a.CoveredID=b.[0 Covered ID]
,a.CoveredindividualsInsuranceIDorCardnumber=b.[1 Covered individuals Insurance ID/card number]
,a.UserDefinedField2=b.[2 User Defined field 2]
,a.SSNorITIN=b.[3 SSN or ITIN]
,a.FirstName=b.[4 First Name]
,a.MiddleName=b.[5 Middle Name]
,a.LastName=b.[6 Last Name]
,a.Suffix=b.[7 Suffix]
,a.DateofBirth=b.[8 Date of Birth]
,a.UserDefinedField1=b.[9 User Defined field 1]
,a.UserDefinedField3=b.[10 User Defined field 3]
,a.CoveredUserDefinedField1=b.[11 Covered User Defined field 1]
,a.CoveredUserDefinedField2=b.[12 Covered User Defined field 2]
,a.CoveredUserDefinedField3=b.[13 Covered User Defined field 3]
,a.CoveredUserDefinedField4=b.[14 Covered User Defined field 4]
,a.Comments=b.[15 Comments]
,a.SolicitationRequiredIndicator=b.[16 Solicitation Required indicator]
,a.ErrorMessage=b.[17 Error Message]
,a.ModifiedDate=getdate()
,a.ModifiedBy=user_name()
WHEN NOT MATCHED THEN
INSERT
(
--InstanceId,
CoveredID
,CoveredindividualsInsuranceIDorCardnumber
,UserDefinedField2
,SSNorITIN
,FirstName
,MiddleName
,LastName
,Suffix
,DateofBirth
,UserDefinedField1
,UserDefinedField3
,CoveredUserDefinedField1
,CoveredUserDefinedField2
,CoveredUserDefinedField3
,CoveredUserDefinedField4
,Comments
,SolicitationRequiredIndicator
,ErrorMessage
,CreatedDate
,CreatedBy
,ModifiedDate
,ModifiedBy
)
VALUES
(
--b.[Id],
b.[0 Covered ID]
,b.[1 Covered individuals Insurance ID/card number]
,b.[2 User Defined field 2]
,b.[3 SSN or ITIN]
,b.[4 First Name]
,b.[5 Middle Name]
,b.[6 Last Name]
,b.[7 Suffix]
,b.[8 Date of Birth]
,b.[9 User Defined field 1]
,b.[10 User Defined field 3]
,b.[11 Covered User Defined field 1]
,b.[12 Covered User Defined field 2]
,b.[13 Covered User Defined field 3]
,b.[14 Covered User Defined field 4]
,b.[15 Comments]
,b.[16 Solicitation Required indicator]
,b.[17 Error Message]
,GetDate()
,CURRENT_USER
,GetDate()
,CURRENT_USER
)
OUTPUT
Inserted.InstanceId
,Inserted.CoveredID
,Inserted.CoveredindividualsInsuranceIDorCardnumber
,Inserted.UserDefinedField2
,Inserted.SSNorITIN
,Inserted.FirstName
,Inserted.MiddleName
,Inserted.LastName
,Inserted.Suffix
,Inserted.DateofBirth
,Inserted.UserDefinedField1
,Inserted.UserDefinedField3
,Inserted.CoveredUserDefinedField1
,Inserted.CoveredUserDefinedField2
,Inserted.CoveredUserDefinedField3
,Inserted.CoveredUserDefinedField4
,Inserted.Comments
,Inserted.SolicitationRequiredIndicator
,Inserted.ErrorMessage
,Inserted.CreatedDate
,Inserted.CreatedBy
,Inserted.ModifiedDate
,Inserted.ModifiedBy
INTO
SAW_raw.[EOC].[FalloutSolicitationResponseErrorDetail_InsertedRecords]
(
InstanceId
,CoveredID
,CoveredindividualsInsuranceIDorCardnumber
,UserDefinedField2
,SSNorITIN
,FirstName
,MiddleName
,LastName
,Suffix
,DateofBirth
,UserDefinedField1
,UserDefinedField3
,CoveredUserDefinedField1
,CoveredUserDefinedField2
,CoveredUserDefinedField3
,CoveredUserDefinedField4
,Comments
,SolicitationRequiredIndicator
,ErrorMessage
,CreatedDate
,CreatedBy
,ModifiedDate
,ModifiedBy
);
INSERT INTO Saw_2.[dbo].[Work]
(<br />
[BatchId]
,[StatusId]
,[ActivityId]
,[CreatedbyAccountId]<br />
,[PieceCount]<br />
,[BscReceiptDateTs]
,[WorkflowCreatedTs]<br />
,[InstanceId]<br />
)
OUTPUT
inserted.Id
, inserted.BatchId
,inserted.StatusId
,inserted.ActivityId
,inserted.CreatedbyAccountId<br />
,inserted.PieceCount<br />
,inserted.BscReceiptDateTs
,inserted.WorkflowCreatedTs<br />
,inserted.InstanceId
INTO Saw_raw.[EOC].[EOCWorkInserted]
(
Id
, BatchId
,StatusId
,ActivityId
,CreatedbyAccountId<br />
,PieceCount<br />
,BscReceiptDateTs
,WorkflowCreatedTs<br />
,InstanceId
)
SELECT @BatchId, @StatusId, @ActivityId, @CreatedbyAccountId, @PieceCount,getdate() ,getdate(), InstanceId
FROM SAW_raw.[EOC].[FalloutSolicitationResponseErrorDetail_InsertedRecords]
--FROM SAW_raw.[EOC].[EoCFalloutSolicitationResponseErrorImportStaging] WHERE Id NOT IN
--(
-- SELECT InstanceId FROM Saw_2.[dbo].[Work] WHERE ActivityId=@ActivityId
--);
SELECT @RecordCount=Count(*) FROM Saw_raw.[EOC].[FalloutSolicitationResponseErrorDetail_InsertedRecords]
UPDATE [Saw_2].[dbo].[FileLoad] SET [RecordCount]=@RecordCount
WHERE rtrim(ltrim([FullFileName]))=rtrim(ltrim(@FullFileName))
IF NOT EXISTS (
SELECT * FROM sys.key_constraints
WHERE type = 'PK'
AND OBJECT_NAME(parent_object_id) = 'FalloutSolicitationResponseErrorDetail'
)
BEGIN
ALTER TABLE [EOC].[FalloutSolicitationResponseErrorDetail]
ADD CONSTRAINT PK_FalloutSolicitationResponseErrorDetail_InstanceId PRIMARY KEY CLUSTERED (InstanceId);
END
IF NOT EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_CoveredID')
BEGIN
CREATE NONCLUSTERED INDEX IX_FalloutSolicitationResponseErrorDetail_CoveredID
ON [EOC].[FalloutSolicitationResponseErrorDetail](CoveredID);
END
IF NOT EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_CoveredindividualsInsuranceIDorCardnumber')
BEGIN
CREATE NONCLUSTERED INDEX IX_FalloutSolicitationResponseErrorDetail_CoveredindividualsInsuranceIDorCardnumber
ON [EOC].[FalloutSolicitationResponseErrorDetail](CoveredindividualsInsuranceIDorCardnumber);
END
IF NOT EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_SSNorITIN')
BEGIN
CREATE NONCLUSTERED INDEX IX_FalloutSolicitationResponseErrorDetail_SSNorITIN
ON [EOC].[FalloutSolicitationResponseErrorDetail](SSNorITIN);
END
IF NOT EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_LastName')
BEGIN
CREATE NONCLUSTERED INDEX IX_FalloutSolicitationResponseErrorDetail_LastName
ON [EOC].[FalloutSolicitationResponseErrorDetail](LastName);
END
IF NOT EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_DateofBirth')
BEGIN
CREATE NONCLUSTERED INDEX IX_FalloutSolicitationResponseErrorDetail_DateofBirth
ON [EOC].[FalloutSolicitationResponseErrorDetail](DateofBirth);
END
IF NOT EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_IsActive')
BEGIN
CREATE NONCLUSTERED INDEX IX_FalloutSolicitationResponseErrorDetail_IsActive
ON [EOC].[FalloutSolicitationResponseErrorDetail](IsActive);
END
END
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Dear Experts
I have a more or less complex query in my database which returns in average about 50 to 100 rows from a table let's say TBL_A containing about 1 million rows (this with left join to another table containg about 10 million records).
The query itself returns the result in about 0.03 seconds which is pretty much nice I think. The amount of data per row is much less than 1KB.
But as soon I add an "ORDER BY TBL_A.ID" the query needs 1.5 seconds; Where TBL_A.ID is the indexed primary key, integer.
I assume the "query compiler" does try to use the index on TBL_A.ID to perform sorting and thats why the whole PLAN becomes inefficient.
Question:
Is there a way to tell the SQL Server not to use "TBL_A.ID index" for sorting and therfore force an "in Memory sorting"?
Any hint is appreciated, also if it is MS SQL related, I think I can transform it by myself to Interbase.
Note:
A work around of course would be that I stay without ORDER BY in the query and let the client sort the result, but I don't like to do this.
Thank you very much in advance for your hints.
modified 19-Jan-21 21:04pm.
|
|
|
|
|
0x01AA wrote: The query itself returns the result in about 0.03 seconds which is pretty much nice I think.
If you insert into a new (temp) table and sort those 50-100 rows after the query has executed with a new command, how much time would that take?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thank you very much for your reply.
Using a temp table, then it takes nearly 0 seconds, but temp table with Interbase is very uncomfortable compared to MSSQL. And unfortunatelly there is nothing like like CTE in Interbase (at least what I know) 
Thanks again.
[Edit]
:lol:
Visited your Profile and read "I'm a Delphi-convert", so then I assume you know interbase. I'm on the way to become hopefully a "Borland c++" convert. 
modified 19-Jan-21 21:04pm.
|
|
|
|
|
0x01AA wrote: Using a temp table, then it takes nearly 0 seconds, but temp table with Interbase is very uncomfortable compared to MSSQL.
Wrap it in a transaction. Without committing, there is no temp table; should isolate the operation. Would work best as a stored procedure 
You're welcome ofc
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi,
I want to list a calendar weeks which falls between two given dates.
Ex: StartDate :2016-01-04 02:19:45.000 and EndDate : 2016-08-31 07:57:37.000
I want output as follows :
StartDate EndDate
------------------------------------------------------------------
2016-01-04 02:19:45.000 2016-01-10 00:00:00.000
2016-01-11 00:00:00.000 2016-01-17 00:00:00.000
2016-01-18 00:00:00.000 2016-01-24 00:00:00.000
2016-01-25 00:00:00.000 2016-01-31 00:00:00.000
2016-02-01 00:00:00.000 2016-02-07 00:00:00.000
2016-02-08 00:00:00.000 2016-02-14 00:00:00.000
2016-02-15 00:00:00.000 2016-02-21 00:00:00.000
2016-02-22 00:00:00.000 2016-02-28 00:00:00.000
. . . .
. . . .
. . . .
. . . .
. . . .
2016-08-01 00:00:00.000 2016-08-07 00:00:00.000
2016-08-08 00:00:00.000 2016-08-14 00:00:00.000
2016-08-15 00:00:00.000 2016-08-21 00:00:00.000
2016-08-22 00:00:00.000 2016-08-28 00:00:00.000
2016-08-29 00:00:00.000 2016-09-04 07:57:37.000
I have tried the following code but did not get the accurate output.
------------------------------------------------------------------------------
WITH CW as (
SELECT --@StartTime STARTDATE
-- DATEADD(WEEK, DATEDIFF(WEEK, 0, @StartTime), 0) FirstDayOfWeek
DATEADD(WK, DATEDIFF(WK, 0, @StartTime), 0) STARTDATE
, DATEADD(WK, DATEDIFF(WK, 0, @StartTime), 6) ENDDATE
UNION ALL
SELECT DATEADD(WW, 1, STARTDATE)
, DATEADD(WW, 1, ENDDATE)
FROM CW
WHERE DATEADD(WW, 1, STARTDATE) <= @EndTime )
Thanks in advance....
|
|
|
|
|
This works for me:
DECLARE @StartDate datetime2(0) = '2016-01-04 02:19:45.000';
DECLARE @EndDate datetime2(0) = '2016-08-31 07:57:37.000';
DECLARE @EndDay date = CAST(@EndDate As date);
WITH CW (StartDate, EndDate) As
(
SELECT
@StartDate,
DateAdd(wk, 1, CAST(CAST(@StartDate As date) As datetime2(0)))
UNION ALL
SELECT
EndDate,
CASE
WHEN DateAdd(wk, 1, EndDate) < @EndDay THEN DateAdd(wk, 1, EndDate)
ELSE @EndDate
END
FROM
CW
WHERE
EndDate < @EndDay
)
SELECT
StartDate,
EndDate
FROM
CW
;
Output:
StartDate EndDate
------------------- -------------------
2016-01-04 02:19:45 2016-01-11 00:00:00
2016-01-11 00:00:00 2016-01-18 00:00:00
...
2016-08-22 00:00:00 2016-08-29 00:00:00
2016-08-29 00:00:00 2016-08-31 07:57:37
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank You This worked for me too 
|
|
|
|
|
Hi Richard Deeming
I have few more doubts please help to resolve it.. 
EX: Suppose for the same machine(machineID = 50) there are two different process started at different time. First one(process P1) started at 2016-01-04 02:19:45 and ends at 2016-06-08 07:57:37.000. Second one(process P2) started at 2016-10-01 02:19:45 and ends at 2016-12-31 07:57:37.000. So how can we take calendar week between two dates for the same machine? i mean startdate is 2016-01-04 02:19:45 and enddate is 2016-12-31 07:57:37.000 for the machineID = 50.
output should come in the following way -
StartDate EndDate
------------------- -------------------
2016-01-04 02:19:45 2016-01-11 00:00:00
2016-01-11 00:00:00 2016-01-18 00:00:00
...
2016-08-22 00:00:00 2016-08-29 00:00:00
2016-08-29 00:00:00 2016-08-31 07:57:37
2016-09-26 02:19:45 2016-10-03 00:00:00
2016-10-03 00:00:00 2016-10-10 00:00:00
2016-10-10 00:00:00 2016-10-17 00:00:00
...
2016-12-26 00:00:00 2016-12-31 07:57:37
Also i want to calculate the total duration for each process P1 and P2 based on there startdate and enddate.
Thanks in advance..
|
|
|
|
|
Assuming you have input data that looks like this:
MachineID ProcessID StartDate EndDate
--------- --------- --------- -------
50 1 2016-01-04 02:19:45 2016-06-08 07:57:37
50 1 2016-10-01 02:19:45 2016-12-31 07:57:37
Then something like this should work:
WITH CW (ProcessID, StartDate, EndDate, RunEnd, RunEndDay) As
(
SELECT
ProcessID,
StartDate,
DateAdd(wk, 1, CAST(CAST(StartDate As date) As datetime2(0))),
EndDate,
CAST(EndDate As date)
FROM
YourTable
WHERE
MachineID = 50
UNION ALL
SELECT
ProcessID,
EndDate,
CASE
WHEN DateAdd(wk, 1, EndDate) < RunEndDay THEN DateAdd(wk, 1, EndDate)
ELSE RunEnd
END,
RunEnd,
RunEndDay
FROM
CW
WHERE
EndDate < RunEndDay
)
SELECT
ProcessID,
StartDate,
EndDate
FROM
CW
ORDER BY
ProcessID,
StartDate
;
Output:
ProcessID StartDate EndDate
--------- --------- -------
1 2016-01-04 02:19:45 2016-01-11 00:00:00
1 2016-01-11 00:00:00 2016-01-18 00:00:00
...
1 2016-05-30 00:00:00 2016-06-06 00:00:00
1 2016-06-06 00:00:00 2016-06-08 07:57:37
2 2016-10-01 02:19:45 2016-10-08 00:00:00
2 2016-10-08 00:00:00 2016-10-15 00:00:00
...
2 2016-12-17 00:00:00 2016-12-24 00:00:00
2 2016-12-24 00:00:00 2016-12-31 07:57:37
Calculating the duration will involve calling DATEDIFF[^] to work out the difference between the start and end dates.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello Richard Deeming
Its not working as per my requirement. For the first set of dates the output data is repeating.
I mean to say, for the startdate :2016-01-04 02:19:45 and enddate :2016-06-08 07:57:37 the total number of calendar week is 35.so the output is repeating for the same but for second set of dates i am getting proper output.I have used the same code snippet witch you have suggested.
Following is the output which i am getting
StartTime EndTime
2016-01-04 02:19:45.000 2016-01-11 00:00:00.000
2016-01-11 00:00:00.000 2016-01-18 00:00:00.000
2016-01-11 00:00:00.000 2016-01-18 00:00:00.000
2016-01-18 00:00:00.000 2016-01-25 00:00:00.000
2016-01-18 00:00:00.000 2016-01-25 00:00:00.000
2016-01-18 00:00:00.000 2016-02-01 00:00:00.000
2016-01-25 00:00:00.000 2016-02-01 00:00:00.000
2016-01-25 00:00:00.000 2016-02-08 00:00:00.000
....
2016-08-29 00:00:00.000 2016-08-31 07:57:37.000
2016-10-01 02:19:45.000 2016-01-11 00:00:00.000
2016-10-03 00:00:00.000 2016-10-10 00:00:00.000
2016-10-10 00:00:00.000 2016-10-17 00:00:00.000
2016-10-17 00:00:00.000 2016-10-24 00:00:00.000
....
2016-12-19 00:00:00.000 2016-12-26 00:00:00.000
2016-12-26 00:00:00.000 2016-12-31 07:57:37.000
Please let me know my mistake. Thanks in advance.
|
|
|
|
|
Looks like the date ranges for the two processes are overlapping.
Try selecting the process ID as well as the dates.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello Richard Deeming,
yeah then i will check on that. And thanks for your valuable time and suggestions.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
