Re: SQL: Split calendar week between a given two date time - Database Discussion Boards

SQL: Split calendar week between a given two date time

Number0523-Jun-17 3:29

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

Re: single DataSet having multiple DataTables from multiple databases

Richard Deeming16-Jun-17 2:22

Richard Deeming

16-Jun-17 2:22

So load data from different databases from the same provider then. What's the problem?

"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

Mycroft Holmes15-Jun-17 13:16

Mycroft Holmes

15-Jun-17 13:16

Many years ago I experimented with SQL Server returning multiple tables from a single stored procedure. The performance was dramatically slower then return multiple single tables from individual stored procs.

Performance may have changed in recent versions.

Never underestimate the power of human stupidity
RAH

Re: single DataSet having multiple DataTables from multiple databases

Eddy Vluggen16-Jun-17 3:18

Eddy Vluggen

16-Jun-17 3:18

MARS be slow, but selecting from a different DB is hardly noticable on SQL Server. Also doesn't need much mucking with datasets, just plain SQL.

Bastard Programmer from Hell Suspicious | :suss:

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

Error in SSIS Connection says (Test connection failed because of an error in initializing provider. ORA-12541: TNS:no listener)

indian1437-Jun-17 13:12

indian143

7-Jun-17 13:12

Hi all,

I have an SSIS Package, I am using the Oracle Database table as Datasource in my package, when I am trying to Edit and Test the Connection by putting the credentials its giving me the following error.

Test connection failed because of an error in initializing provider. ORA-12541: TNS:no listener

I did set the Oracle_Home to the right directory, still I am getting the problem.

Now I set the TNS_ADMIN also with the network folder still getting the error.

Can anybody help me in resolving this issue, any help is going to be greatly helpful, thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

-- modified 13-Jun-17 19:34pm.

Re: Error in SSIS Connection says (Test connection failed because of an error in initializing provider. ORA-12541: TNS:no listener)

mike1402125-Jun-17 21:53

mike14021

25-Jun-17 21:53

looks like the network connection isn't ok -
I would first check if the connection to the server is ok by using tnsping:
https://docs.oracle.com/cd/E11882_01/network.112/e41945/connect.htm#NETAG357

or try to connect with orcale sql developper

I need advice on storing application level encrypted data.

Gilbert Consellado5-Jun-17 18:26

Gilbert Consellado

5-Jun-17 18:26

I just want to ask, what is your solution on storing encrypted data on database? And also how you do searching on it? For now the subject database server is mysql.

Disk level encryption is not an option. I also research about database level encryption but, MySql TDE is not for me, and also if you migrate to other database server in my understanding you need to re-encrypt all the data.

So my last bet is application level encryption. And I tried to implement it using AES-GCM, but on this I will lose database searching, because encrypting the same data with the same key and id will produce different results. Now I got an idea but, i don't know what would be the risk implementing it. I was thinking blind indexing.

What I am planning to do is to store 2 version of the encrypted data. first the data will be encrypted with aes-gcm, then store another version of it using one-way encryption on this same data will produce the same hash. So the first encryption is reversible but not searchable, while the second one is not reversible but I can do whole word searching.

The obvious drawback of this is speed. But I want also to ask if in security perspective is it feasible? And also how do you do a search query on encrypted database using the partial text of the data?

Thank you

Re: I need advice on storing application level encrypted data.

Kornfeld Eliyahu Peter5-Jun-17 20:50

Kornfeld Eliyahu Peter

5-Jun-17 20:50

You do not explain what kind of data it is and what search you want to do...
As for the HASH based search - it will work only if you search for exact data, for instance 'Gilbert Consellado' will produce a constant HASH but the HASH for 'Gilbert' will not be part of it, so you will find that piece of data only if you are looking for 'Gilbert Consellado' exactly as is (include case and space and order)...

If there is a way to index the data, prior encryption, than you can use it (the HASH of the indexes) to search quickly at the DB level...

Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.

How do I write the insert sql statement for the following scenario ?

karengsh3-Jun-17 22:30

karengsh

3-Jun-17 22:30

Hi expert,

Database : MySQL

I am trying to create 2 tables with a one-to-many relationship.
Table 1 - tutor
Table 2 - subject

In my table tutor, I have id as the Primary key and it is auto-incremental.

In my subject, I have id as the Primary key and it is auto-incremental, tutorNRIC as the foreign key and tutorNRIC in my tutor table as the reference key.

For my subject, there will be more than one entry in the column tutor_subject.

For example, a tutor will sign up and then all the subjects that she can teach will be entered into that column tutor_subjet.

I'd like to check if my insert sql is correct :

SQL

private final String INSERT_QRY1 = "INSERT INTO hi5project.subject tutor_subject, tutorNRIC VALUES ('?', '?') SET tutorNRIC = hi5project.tutor.tutorNRIC";

Thanks.

Re: How do I write the insert sql statement for the following scenario ?

Mycroft Holmes4-Jun-17 13:49

Mycroft Holmes

4-Jun-17 13:49

For a start your table design is incorrect, you need a many to many table soa tutor can have mnay students and a student can have many tutors. What happens id=f a tutor leaves, or you have different tutors for different subjects?

LinkID
TutorID
StudentID

Never underestimate the power of human stupidity
RAH

Re: How do I write the insert sql statement for the following scenario ?

karengsh4-Jun-17 20:41

karengsh

4-Jun-17 20:41

Hello Mycroft Holmes,

I am not creating a table to store the students.

So, do I still need a many-to-many relationship between the table : tutor and the table : subject ?

Re: How do I write the insert sql statement for the following scenario ?

Mycroft Holmes4-Jun-17 20:58

Mycroft Holmes

4-Jun-17 20:58

Sorry my bad - but the same rule applies a tutor may have more than one subject and a subject may have more than one tutor. If these rules apply to your use case then you need a many to many table.

Never underestimate the power of human stupidity
RAH

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