Database Discussion Boards

Re: How do I get all the Ids from my last insert in Mysql?

karengsh28-Jun-17 0:36

karengsh

28-Jun-17 0:36

But, how do I insert one at a time when my situation requires me to insert by batch ?

Re: How do I get all the Ids from my last insert in Mysql?

ZurdoDev28-Jun-17 9:48

ZurdoDev

28-Jun-17 9:48

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.

Access ComboBox Query Question

Bassam Abdul-Baki27-Jun-17 14:16

Bassam Abdul-Baki

27-Jun-17 14:16

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!

Web - BM - RSS - Math - LinkedIn

Need to load bulk Data with some conditions

indian14327-Jun-17 10:14

indian143

27-Jun-17 10:14

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."

Re: Need to load bulk Data with some conditions

Mycroft Holmes27-Jun-17 19:26

Mycroft Holmes

27-Jun-17 19:26

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

Re: Need to load bulk Data with some conditions

indian14328-Jun-17 8:14

indian143

28-Jun-17 8:14

Hi,

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."

Re: Need to load bulk Data with some conditions

indian14312-Jul-17 7:41

indian143

12-Jul-17 7:41

Hey buddy, I tried this and mostly +ve that it should be working, but if you find any flaws in it please point me out, related to anything in this Code.

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."

ORDER BY issue with Interbase XE SQL Server

User 1106097925-Jun-17 2:32

User 11060979

25-Jun-17 2:32

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.

Re: ORDER BY issue with Interbase XE SQL Server

Eddy Vluggen25-Jun-17 3:06

Eddy Vluggen

25-Jun-17 3:06

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 Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: ORDER BY issue with Interbase XE SQL Server

User 1106097925-Jun-17 3:24

User 11060979

25-Jun-17 3:24

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) Sniff | :^)

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. Laugh | :laugh:

modified 19-Jan-21 21:04pm.

Re: ORDER BY issue with Interbase XE SQL Server

Eddy Vluggen25-Jun-17 3:40

Eddy Vluggen

25-Jun-17 3:40

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 Smile | :)

You're welcome ofc Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

SQL: Split calendar week between a given two date time

Number0523-Jun-17 3:29

Number05

23-Jun-17 3:29

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....

Re: SQL: Split calendar week between a given two date time

Richard Deeming23-Jun-17 6:18

Richard Deeming

23-Jun-17 6:18

This works for me:

SQL

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

Re: SQL: Split calendar week between a given two date time

Number0526-Jun-17 22:06

Number05

26-Jun-17 22:06

Thank You

This worked for me too Big Grin | :-D

Re: SQL: Split calendar week between a given two date time

Number0527-Jun-17 5:10

Number05

27-Jun-17 5:10

Hi Richard Deeming

I have few more doubts please help to resolve it.. Frown | :(

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..

Re: SQL: Split calendar week between a given two date time

Richard Deeming27-Jun-17 5:37

Richard Deeming

27-Jun-17 5:37

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:

SQL

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

Re: SQL: Split calendar week between a given two date time

Number0528-Jun-17 4:30

Number05

28-Jun-17 4:30

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.

Re: SQL: Split calendar week between a given two date time

Richard Deeming28-Jun-17 7:46

Richard Deeming

28-Jun-17 7:46

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

Re: SQL: Split calendar week between a given two date time

Number053-Jul-17 1:41

Number05

3-Jul-17 1:41

Hello Richard Deeming,

yeah then i will check on that. And thanks for your valuable time and suggestions.

Basic Entity Framework Question

Kevin Marois21-Jun-17 7:54

Kevin Marois

21-Jun-17 7:54

When you create an EF Data Context using DB first it creates data models for you. Those models are in the EDMX. I created the data in a project called DAL. Therefore the data context and the data models are all in that project.

So then I add this project to a solution along side a WPF/MVVM project. To use the data models I then reference the DAL project, which gives me access to the data models.

With EF is this the right way to do this? Directly reference the generated Data Models? Or do you somehow use your own data models? How do the generated Data Models handle INotifyPropertyChanged implementation in the WPF/MVVM project?

If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.

Re: Basic Entity Framework Question

mike1402125-Jun-17 21:24

mike14021

25-Jun-17 21:24

I use Viewmodels for implementing INotifyPropertyChanged and a Converter which copies members of datalayer EF Model Classes to Viewmodel Classes.

Example for viewmodel base class: https://stackoverflow.com/questions/36149863/how-to-write-viewmodelbase-in-mvvm-wpf

PDO Return Single Result, How To Return Different Record From 1 Column Multiple Rows

Member 1326709919-Jun-17 1:21

Member 13267099

19-Jun-17 1:21

I am making a custom Sitemap from my database and stuck with following Issue

SQL Query:

SQL

SELECT term_id FROM wp_term_taxonomy WHERE taxonomy="product-cat" || taxonomy="product-brand"

Query Output

term_id
365
369
370

It returns all term_id from my database through which I gather category slugs from another table to make sitemap URLs. Below is the code and it works fine as you can see in (sitemap output), but unfortunately I am unable to extract next row term_id & it shows always same term_id to me as a result 'Same URL'

My Sitemap Output

PHP

<urlset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:image="http://www.google.com/schemas/sitemap-image/1.1" xmlns="http://www.sitemaps.org/schemas/sitemap/0.9" xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9 http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">
<url>
<loc>
http://domain.com/search-page/?product-cat=mobiles-tablets	365
</loc>
<changefreq>always</changefreq>
<priority>1.0</priority>
</url>
<url>
<loc>
http://domain.com/search-page/?product-cat=mobiles-tablets	365
</loc>
<changefreq>always</changefreq>
<priority>1.0</priority>
</url>
</urlset>

CODE

PHP

<?php
header("Content-type: text/xml");
$i=0;
$xml = '<?xml version="1.0" encoding="UTF-8"?>';
$xml.= "\n".'<urlset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:image="http://www.google.com/schemas/sitemap-image/1.1" xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9 http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd" xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';
  $db = new PDO('mysql:host=xxx;dbname=xxx', 'xx', 'xxx');
  $stmt[$i] = $db->query('SELECT count(*) FROM wp_term_taxonomy WHERE taxonomy="product-cat" || taxonomy="product-brand"');
  $rowcount = $stmt[$i]->fetchColumn();
      for ($i=0; $i<2; $i++)
      {
        $sth[$i] = $db->query('SELECT term_id FROM wp_term_taxonomy WHERE taxonomy="product-cat" || taxonomy="product-brand"');
        $t_id[$i] = $sth[$i]->fetchColumn();
        $stmt[$i] = $db->query('SELECT taxonomy FROM wp_term_taxonomy WHERE term_id = '.$t_id[$i].'');
        $t_taxonomy[$i] = $stmt[$i]->fetchColumn();
        $stmt[$i] = $db->query('SELECT slug FROM wp_terms WHERE term_id = '.$t_id[$i].'');
        $t_slug[$i] = $stmt[$i]->fetchColumn();
      }
        echo $xml;
     for ($i=0; $i<2; $i++)

      {

        $xml.= "\n\t\t".'<url>'."\n";
        $xml.= "\t\t\t".'<loc>'."http://domain.com/search-page/?$t_taxonomy[$i]=$t_slug[$i]"."\t$t_id[$i]\t$i\t$rowcount".'</loc>';
        $xml.= "\n\t\t\t".'<changefreq>always</changefreq>';
        $xml.= "\n\t\t\t".'<priority>1.0</priority>';
        $xml.= "\n\t\t".'</url>'."\n";
      }
    ?>


<?php 
$xml.= "\n".'</urlset>';
$handle = fopen('sitemap_custom.xml','w+');
fwrite($handle,$xml);
fclose($handle);
?>

What I Need?

I want to extract next row term_id (ideal if it based on loop[0,1,2]) right now it shows me only result of term_id=365

single DataSet having multiple DataTables from multiple databases

Hailu Worku Obsse14-Jun-17 21:24

Hailu Worku Obsse

14-Jun-17 21:24

It is possible to have a single DataSet having multiple DataTables from multiple databases. If so can you just tell me how ?

Re: single DataSet having multiple DataTables from multiple databases

Richard Deeming15-Jun-17 1:51

Richard Deeming

15-Jun-17 1:51

Yes it is.

var ds = new DataSet();

using (var connection = new SqlConnection("..."))
{
    var da = new SqlDataAdapter("SELECT ...", connection);
    da.SelectCommand.Parameters.AddWithValue(...);
    da.Fill(ds, "FirstTable");
}

using (var connection = new OleDbConnection("..."))
{
    var da = new OleDbDataAdapter("SELECT ...", connection);
    da.SelectCommand.Parameters.AddWithValue(...);
    da.Fill(ds, "SecondTable");
}

If one of your queries returns multiple result-sets, you might need to use TableMappings to specify the table names:
DataAdapter DataTable and DataColumn Mappings | Microsoft Docs[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: single DataSet having multiple DataTables from multiple databases

Hailu Worku Obsse16-Jun-17 2:17

Hailu Worku Obsse

16-Jun-17 2:17

Thank you for your replay .... however, I am looking different databases from the same provider

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.