|
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.
|
|
|
|
|
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.
|
|
|
|
|
|
I am making a custom Sitemap from my database and stuck with following Issue
SQL Query:
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
<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
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
|
|
|
|
|
It is possible to have a single DataSet having multiple DataTables from multiple databases. If so can you just tell me how ?
|
|
|
|
|
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
|
|
|
|
|
Thank you for your replay .... however, I am looking different databases from the same provider
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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 
If you can't read my code, try converting it here[^]
|
|
|
|
|
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.
|
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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 :
private final String INSERT_QRY1 = "INSERT INTO hi5project.subject tutor_subject, tutorNRIC VALUES ('?', '?') SET tutorNRIC = hi5project.tutor.tutorNRIC";
Thanks.
|
|
|
|
|
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
|
|
|
|
|
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 ?
|
|
|
|
|
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
|
|
|
|
|
OK. Tks.
Can I know if the ammended database schema is correct ?
Create table tutor(
id integer, auto_increment;
tutorName varchar(50), not null,
tutorNRIC varchar(50), not null, (Primary Key)
tutorEmail varchar(50), not null,
);
Create table subject(
id integer, auto_increment;
subject varchar950), not null;
subject_id, integer, not null, (Primary Key)
_;
Create table tutor_subject(
tutorNRIC integer,
subject_id integer,
Primary Key(tutorNRIC, subject_id),
Foreign Key(tutorNRIC) references tutor(tutorNRIC),
Foreign Key(subject_id) references subject(subject_id));
|
|
|
|
|
In the Tutor_Subject table I would use the Tutor ID field. The NRIC is a user input field and should not be used as a foreign key.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi Mycroft,
Is it possible to use tutorNRIC as my primary key instead of tutorID ? my tutorID is just an index for me to keep track how many people have signed up.
Another question is that how should I do my insert statement based on many to many relationship ?
|
|
|
|
|
karengsh wrote: Is it possible to use tutorNRIC as my primary key
Yes it is possible just wrong. A user should NEVER be able to edit a primary key.
karengsh wrote: my tutorID is just an index for me to keep track how many people have signed up.
That statement means you need to do some reading/learning on database design. A primary key should be stupid and used for just one purpose, to identify the record, NOTHING else.
Normally you would create the primary records and when you link them (via the UI) you create the link record with the 2 primary keys.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
