Go Premium for a chance to win a PS4. Enter to Win

x

SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hello,

Im trying to create a SQL table that has a datetime (date , hour and minute only) column starting from say 2017-11-17T00:00 and columns with heading say 30min , 45min, 77min, 240min, 343min which is basically grouping the datetime column in those specific minutes .

But the groupings only happen between 00:00 and 23:59 each day , and then starts the grouping again at 00:00 onwards .

have spreadsheet with starting what it should look like , would like to have about 3 years datetime from 2017-11-17 please :-)
0
Get free NFR key for Veeam Availability Suite 9.5
LVL 1
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Hello Experts

I have created 2 accounts

1) net user SQLsvc /add /expires:never "Very complex passw0rd"
2) net user SQLAgent /add /expires:never “Very complex pa$$w0rd”

I want to change the password for the first  SQLsvc  one can any one suggest a step by step procedure?
0
Hi,

The danish language have three special characters, Æ, Ø, Å - æ, ø, å
These characters are also written as Æ = AE, Ø=OE, Å=AA, æ=ae, ø=oe, å=aa

For example is the name of one city in Denmark be spelled like this: Aalborg and like this: Ålborg.

My question is if ii is possible to create a SQL search in a way that when searching for Ålborg it will return Ålborg as well as Aalborg.

In this searchstring it returns nothing when searching Aalborg because in the database only the spelling Ålborg is used.
      $query = $mysqli->prepare("SELECT * FROM ft WHERE Aar = ? AND AmtHerredSognGade LIKE '%".$sogn."%'");
    $query->bind_param("s", $start);

Open in new window

0
Hi,
I run
 
spo c:\pe\1b
Select
   krn.obj0,
   krn.obj0_id,
   krn.p3_sequence,
   krn.par0,
   ptpar0.obj1_name As par0_name,
   krn.par1,
   ptpar1.obj1_name As par1_name,
   krn.p1,
   krn.p2,
   krn.p3,
   krn.obj1_of_call,
   krn.obj1_From,
   ptpf.obj1_name As obj1_From_Name,
   krn.obj1_To,
   ptpt.obj1_name As obj1_To_Name,
   krn.por,
   krn.sent_count,
   krn.prepaid_collect_ind,
   Coalesce(krn.par2_name,ptpor.obj1_name) As par2_name,
   krn.par3,
   Coalesce(krn.par3_name,ptpd.obj1_name) As par3_name,
   krn.TS_Flag,
   krn.original_obj0_id,
   krn.orig_obj0_id,
   krn.bill_type,
   krn.event_flag,
   krn.next_obj1_to,
   (Select vp0.obj1_call_ref From tw.p2_obj1s vp0,tw.t1_p3s brl0
    Where
    krn.event_flag = 'EM'
    And brl0.obj0_id = Decode(krn.bill_type,'M',Decode(krn.issue_place,krn.obj1_of_call,krn.original_obj0_id,krn.obj0_id),krn.obj0_id)
--Decode(krn.bill_type,'M',krn.original_obj0_id,krn.obj0_id)
    And brl0.obj1_cd_to = krn.obj1_of_call
    And vp0.obj1_of_call = brl0.obj1_cd_to          
    And vp0.p1 = brl0.imp_p1 And  vp0.p2 = brl0.imp_p2 And  vp0.p3 = brl0.imp_p3
    And brl0.obj1_cd_to = vp0.obj1_of_call
    And vp0.obj1_sequence  = 1
    And brl0.p3_sequence = (Select Max(p3_sequence) From tw.t1_p3s where obj0_id = brl0.obj0_id
                                  And p3_sequence < krn.p3_sequence
                                  And obj1_cd_to = krn.obj1_of_call)) As MRN_TS,
(Select Max(blr.ref_no) From tw.bl_reference blr Where
   

Open in new window

0
My columns in the table is defined as below

fund1   fund2   pricingdatefund1    pricingdatefund2

I have to fetch the records in a way that  if pricing date fund 1= 15 Nov then fetch fund 1 record , if pricing date fund 2= 15 Nov then fetch fund2 record and if both of the pricingdatefund1, pricingdatefund2 matches my date then fetch both fund 1 and fund2.

I know it is complicated. I hope I have explained it

Thanks
0
Dear Experts,

I am following this

https://www.mssqltips.com/sqlservertip/2386/initialize-sql-server-replication-using-a-database-backup/

The issue now is the it seems that a domain admin account does not have the permission to conduct the replication.

We then have this error message when we tried to start the job: "Executed as user "domain account". A required privilege is not held by the client. The step failed"

So in the first and third of the jobs from the SQL Agent Service, we have to change the Run As from [REPL][Domain account][server job] to SQL Server Agent Service Account.

We really have no idea what is the required privilege of the domain account to execute the SQL replication process.
0
I am fairly new to SQL Server, working in SQL Developer 14.

I have a requirement to pull records from a table after a specified number of days has passed.

I have a lot of experience in MS Access and I could accomplish this in an Access query using the Date Function.  'TableDate' is the field containing the date.  If I wantto make sure at least 30 days have passed:

Select .....
From ....
Where Date - TableDate > 30

Is there a way to do something similar in a SQL Select?
0
i have a local work table and a linked sql server table, i am trying to insert from my local work table into the linked table but i am getting the above error, i even have a primary key in the linked table.

task0 = "INSERT INTO dbo_WorkTable ( ID, [Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], [General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, [HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE] )" _
& "SELECT WorkTable.ID, WorkTable.[L Batch ID], WorkTable.[Pay Group], WorkTable.[Pay Group Description], WorkTable.[General Ledger Account], WorkTable.[General Ledger Cost Center], WorkTable.[General Ledger Department], WorkTable.[Work Center], WorkTable.[Pay Period Ending Date], WorkTable.Hours, WorkTable.Amount, WorkTable.Week, WorkTable.[Pay Type Code], WorkTable.[Pay Type Description], WorkTable.[File Number], WorkTable.Name, WorkTable.[HOURLY SALARY], WorkTable.[FULL TIME_PART TIME], WorkTable.ACTIVE_INACTIVE, WorkTable.[HOURLY RATE]" _
& " FROM dbo_WorkTable, WorkTable; "

Open in new window


DoCmd.RunSQL (task0)


Capture34.PNG
please help as my insert statement looks fine.
0
I'm trying to do something super simple...or seems it should be. I have a script that truncates and updates a table...it creates unique combinations across three other tables for a dimension build. I was hoping to create a function/procedure that I could call from an application in a single line, rather than trying to store the sql in my application.

We are using postgresql and sqlworkbench/j. My code to create the table includes a view creation, distinct, union, and in insert into using select.

I was trying:

    CREATE OR REPLACE FUNCTION update_table()
    RETURNS void AS $$
    BEGIN
      CREATE OR REPLACE VIEW ...

     ...;
    END;
    $$ LANGUAGE plpgsql
    IMMUTABLE;

I get the error that "Create Function is not supported".

I'm admittedly a sql rookie and everything I have tried has failed, giving me one error after another. Is there a simpler way to do this? Nothing changes in my create statement...no parameters passed in, nada. It's simply that the underlying transactional data is changing and we're only keeping valid combos, so we trunc and reload the table.

Thanks in advance!
0
How do I drop all data w/in a sql table but keep the table? I need to keep the table/s but clear the data from w/in the tables.
0
Get your Disaster Recovery as a Service basics
LVL 1
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Hello experts

I have installed 2012R2 Vm and I'm installing sql 2012 in that vm I have mounted means it is not showing iso.

can you guide me where I need to go to install sql.
0
I am trying to calculate the overlapping days between 2 drugs to get an accurate reflection of days supply.  I don't even know if this is possible but I figured I'd ask.

Here is an example of what we need to be able to do if Drug A and Drug B are in the same drug class:

Drug A claim dates:         8/01/17 X 30 day supply
                                             9/01/17 X 30 day supply
                                             10/01/17 X 30 day supply

Drug B claim dates:         9/5/17 X 30 day supply
                                             10/5/17 X 30 day supply

Adding Drug A + Drug B claims would = 150 days, but that is not a correct reflection of days they are receiving therapy with drugs in the same drug class.

Accurate reflection would be:  (Drug A 90 days + Drug B 60 days) minus 55 days overlap where patient is receiving both Drug A + Drug B, so total days of therapy is more accurately 95 days.

Does anyone have any ideas on how to do this?  Thank you!
0
I have a form that has 6 data blocks and several items. When a user enters/queries an item, just before the user changes any item I would like to save the old values to a database table by using a trigger. Then I will later figure out how to save the changes to another table by using a trigger.
0
I have been working on a SQL routine that pulls POs from a legacy application and inserts them into a modern ERP solution. In our data center the process pulls in all 20,000 POs in less than 20 minutes. We just tried to deploy this routine in the customer's environment and the process took over 10 hours. This routine deals with about 10 tables in two databases that are housed in the same instance of MS-SQL 2012. What tools can I run on both ends to identify what is causing the bottleneck on their system?
0
Hi,

I am trying to import these Apache logs into an SQL Database but am unable to do so.

When I run the code below I get the following Error:

Msg 4860, Level 16, State 1, Line 4
Cannot bulk load. The file "C:\LOGS\access.2017.11.16.log" does not exist or you don't have file access rights.

Can I I Import all Files without checking for a date? 


(1 row affected)

Open in new window

     
/*****This is the share location of the log file in a variable*****/
DECLARE @FileLocation varchar(max)
SET @FileLocation = 'C:\LOGS\'

/*****This is the name of the start the file name in a variable********/
DECLARE @FileName varchar(max)
SET @FileName = 'access.'

/********This is the extention of the log file in a variable*******/
DECLARE @FileEXT varchar(4)
SET @FileEXT = '.log'

/*************This gets todays date in a variable*************/
Declare @date varchar(max)
set @date = (SELECT convert(varchar, getdate()-4, 102))

/********This puts all of the file share and info in one variable name************/
DECLARE @FilePath varchar(max)
Set @FilePath = @FileLocation + @FileName + @Date + @FileEXT

/*********** This is the SQL to insert and its written into a variable and executed************/
DECLARE @SQL nvarchar(max)

SET @SQL = N' use Downloads 
		   BULK 
		   INSERT BulkInsert 
		   FROM "' + @FilePath + 
		   '" WITH 
		   ( 
		   FIELDTERMINATOR = '','', 
		   ROWTERMINATOR = ''\n'')'
EXEC  sp_executesql @SQL


END

Open in new window

0
I have a database server that is about 4 years old.  we have a bunch of users that point their applications to this server.

the name of the server is    Alpha

We want to upgrade the sql server version from 2012 to 2016.   I think i would like to create a new server and do a clean install of sql server instead of doing an in-place upgrade.

So the new server would be bravo......  (if we do not do an in-place upgrade)
isn't it true that if i get the permissions right on bravo and restore all the databases on bravo.  that i could thereotically change the name of Alpha to lets say Alpha1 and then rename Bravo to Alpha that the users would not need any configuration changes.

Can someone please verify or send me to a document that outlines the best way to do this.
0
how do I return records from a table where the field create date (date time)  is equal to todays date?  I can't hardcode todays date so I will need to use getdate() or something.
0
$user_id,
$logged_user_id

and a relation table shown in picture

id,
following_id,
follower_id,
active(status)



can i get mutual friend ?


i have
Following Query =
 "SELECT `user_id` FROM " . T_USERS . " WHERE `user_id` IN (SELECT `following_id` FROM " . T_FOLLOWERS . " WHERE `follower_id` = {$user_id} AND `following_id` <> {$user_id} AND `active` = '1') AND `active` = '1' ";

Open in new window


Follower Query =
" SELECT `user_id` FROM " . T_USERS . " WHERE `user_id` IN (SELECT `follower_id` FROM " . T_FOLLOWERS . " WHERE `follower_id` <> {$user_id} AND `following_id` = {$user_id} AND `active` = '1') AND `active` = '1'";

Open in new window



perfectly working
sql.jpg
0
Hi,

I have data in three columns in the database that represents three levels.
Column 'Amt' is the top level
Column 'Herred' is the middle level
Column 'Sogn' it the bottom level

I have made this page: http://kroweb.dk/gfdev/dropdown/ where I have the three levels represented in three select boxes.

What I need is this:
The 'Amt' box should reflect a dropdown box with the content of the column 'Amt' in the database
The 'Herred' box should reflect a dropdown box with the content of the column 'Herred' within the chosen 'Amt'
The 'Sogn' box should reflect a dropdown box with the content of the column 'Sogn' within the chosen 'Amt' and 'Herred'.

I have this HTML (commented where I believe something more is needed :) :
                    <!-- Søge felter -->
        <div class="container" style="width:100%;padding-top:10px;background-color:#f6f6f6;">

            <!-- HEADER START -->
            <div class="row" id="header">
                <form method="POST" style="margin-left:30px;"> 
   

                     Amt                        
                        <select id="amt" name="amt">
                        <option value=""></option>
                        <option value="Content of column 'Amt'">Content of column 'Amt'</option> <!-- something else needed -->
                
                    </select>

                   Herred                        
                        <select id="herred" name="herred">
                        

Open in new window

0
Efficient way to get backups off site to Azure
LVL 1
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Dear Experts,

MS SQL 2014 Enterprise

I am assisting my client to run the SQL replication.

However, there was a database that is configured to use DB restore with norecovery and restore with a differential backp.

I later accidentally ran the snapshot agent and now it keeps displaying "the replication agent has not logged a progress message in 10 minutes".

How can I set it back to "Never started"?
0
Hi

I want to send email groupwise. For example, i want to send emails to department heads for the people working in their departments. I have 10 departments so need to send 10 emails to their heads regarding the attendance of their respective department staff.

Any solution, plz advice
0
I have a parent form of movie volumes ("frmVolumes", home-recorded VCR movies), and one volume can have multiple programs (shown in "fsubPrograms", individual movies or TV shows).  Those link with no problem.  When the frmVolumes displays, the appropriate programs display in fsubPrograms, based on frmVolume's recordsource's primary key, which is a hidden field on frmVolumes.

However, I can't get another level of subform (fsubActors) to display appropriate actors based on the highlighted program's recordsource's primary key, which is a hidden field on fsubPrograms.  It looks like all actors are loading.

I think my problem lies in the order in which Access loads forms.  Rather than use form properties for link master and child fields, I am using SQL strings in the Form_Current event.  This is a new technique for me, but I have it working and understand it for one main form and one subform.  In my experience, the subform's recordsource is retrieved before the main form's recordsource.

Linking the subform to another yet a further subform seems to be the problem.  Another version of this database that does use form properties for linking does work properly, and I get the correct actors.  I have also tested the query to verify that it only brings back the actors I want.

Here are my queries, just for a validation.  Can someone explain what I need to do so that the three recordsources are loaded in the correct order?  Am I missing a Requery/Refresh/Repaint?

0
Hello Experts,
I would like to know if there are any PC utilities to read CSV files other than Excel. I see a host of them online, but I would really like your recommendation based on your personal experiences.
0
The following chart has a filter applied, keyword "bingo"

https://fusiontables.google.com/DataSource?docid=1Gk2Gpw70hvXqjDoXVcAu8G_7xTwIv4jrhsHiEkL7#chartnew:id=4

Unfortunately the HTML publishing feature does not seem to work (the filter gets removed)

Is there any workaround i can use to publish the filtered version of the chart (apart from creating a new table with the filtered data)?

I want to be able to show the chart on it's own.

This question is as aside from https://www.experts-exchange.com/questions/29069155/Creating-a-dynamic-query-for-Google-Fusion-Tables.html

Thanks
0
Hi,

I have this SQL query:
           $query = $mysqli->prepare("SELECT * FROM ft WHERE Aar = ? AND AmtHerredSognGade LIKE '%".$sogn."%'");
            $query->bind_param("s", $start);

Open in new window


I need to be able to put in a constant in the search string before the result of $sogn.
E.g if the $sogn returns "Gudhjem" I need to put in a string before Gudhjem, in this case Bornholm, so that the total searchstring becomes "Bornholm Gudhjem".
The string I need to put in before the $sogn are represented in the table in a column with the headline "Amt" and I would naturally like to pull it from there.
I know that the string for searching for $sogn are broad with jokers but it have to be that because the "AmtHerredSognGade" field in the table is a combined field from where I need to pull out the part matching the query.

I hope that somebody have the golden idea to fix this :)
0

SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.