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

Untitled.jpg errror display as not a valid month while executing the querry


/* Formatted on 2018/09/18 13:55 (Formatter Plus v4.8.8) */
SELECT a.dat, b.division, b.sm_name, b.dt, b.cust_code
  FROM (SELECT dat
          FROM (SELECT       TO_DATE (:r_from_cust_main_acnt_code,
                                      'DD/MM/RRRR'
                                     )
                           + ROWNUM
                           - 1 dat
                      FROM ot_job_exec_head
                CONNECT BY LEVEL <=
                                TO_DATE (:r_till_cust_main_acnt_code,
                                         'DD/MM/RRRR'
                                        )
                              - TO_DATE (:r_from_cust_main_acnt_code,
                                         'DD/MM/RRRR'
                                        )
                              + 1)
         WHERE ROWNUM <= 31) a
       LEFT JOIN
       (SELECT division, sm_name, dt, cust_code
          FROM (SELECT division, sm_name,
                       TO_CHAR (TRUNC (TO_DATE (dat, 'DD-MM-YYYY')),
                                'DD-Mm'
                               ) dt,
                       cust_code
                  FROM (SELECT vssv_field_02 division, vssv_name sm_name,
                               jeh_dt dat, jed_flex_01 cust_code,
                               jed_flex_03 purpose, jed_flex_11 next_action
                          FROM …
0
Powerful Yet Easy-to-Use Network Monitoring
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Hi,

I have a table with data looking like this:
Sql result
The purpose of the table is to register a log for Employees so you can see the times they've worked.
As you can see each day has a rowID and also a TimeClockCatID. The rowID is the id for the specific day they are registering for and the TimeClockCatID is showing if they checked in (1) or checked out (2) from the system.

Is it possible for me to count and sum how much time an employee has worked a day by comparing the TimeClockCatID = 1 with TimeClockCatID  = 2 for each rowID?

Thanks for help!

Peter
0
Would like to update all the values  in a table (tOffice) to False (-1) for the field name "Courier"

I've tried some variations of this SQL command without luck

DoCmd.RunSQL "UPDATE toffice SET Courier  =  -1

Courier is a yes/no variable.

What is wrong with my syntax?  Should I be using an SQL update for this?
0
Need help figuring out why my dashboard continues to show the color white when the hex color I am specifying is green and orange.
SELECT WO_NUMBER AS WONUM
      ,PART_NUMBER AS PN
      ,DESCRIPTION AS DESCR
      ,CASE CONTRACT_TYPE WHEN 'PBH Pool' THEN 'PBH' ELSE 'TM' END AS CONTRACT
      ,DAYS_OPEN AS TAT
      ,CASE WARRANTY_REQUESTED WHEN 'F' THEN ' ' WHEN 'T' THEN 'W' END AS INFO
      ,TECH
,case when priority = 1 or priority = 2 then '#FFFF00'
when days_open > 3 or priority >= 1 then '#ff0000' 
when days_open < '-1' then '#66b65d'
when days_open in ('0', '-1') then '#f68835' 
end row_color
FROM BA_VIEW_WIP_SHOP_SCREENS
WHERE STATUS IN ('OPEN', 'AWT SHOP EVAL', 'SHOP EVALUATION') AND SHOP = 'SH ELE/FUE ARO'
order by days_open desc

Open in new window

Capture.JPG
0
I have an MS SQL query that will have dynamic columns that I would like to pivot the results.  I am not sure how code the SQL to do this.  The column names will vary in name and number of columns.  One order may have 4 columns.  The next order may 5 columns and the names (gate_id) can be different in each case.

This is the query:
SELECT TOP (100) PERCENT 
       CASE 
          WHEN confirmed > 0 THEN CAST(confirmed AS decimal(8)) / CAST(operations AS decimal(8))  
	      ELSE 0 
	   END AS Attn, 
	   gate_id, so_id, plant_id
FROM (SELECT TOP (100) PERCENT dbo.mfg_order_oper.plant_id, dbo.mfg_order_oper.so_id, COUNT(dbo.mfg_order_oper.oper_id) AS operations, 
             COUNT(CASE WHEN oper_end IS NOT NULL THEN 1 END) AS confirmed, dbo.sys_mfg_workctr.gate_id, dbo.sys_mfg_gate.gate_seq
      FROM dbo.mfg_order_oper 
	  INNER JOIN dbo.mfg_order 
	    ON dbo.mfg_order_oper.plant_id = dbo.mfg_order.plant_id AND dbo.mfg_order_oper.order_nbr = dbo.mfg_order.order_nbr 
	  INNER JOIN dbo.sys_mfg_workctr 
	    ON dbo.mfg_order_oper.plant_id = dbo.sys_mfg_workctr.plant_id AND dbo.mfg_order_oper.workctr = dbo.sys_mfg_workctr.workctr 
	  LEFT OUTER JOIN dbo.sys_mfg 
	    ON dbo.sys_mfg_workctr.mfg_id = dbo.sys_mfg.mfg_id AND dbo.sys_mfg_workctr.plant_id = dbo.sys_mfg.plant_id 
	  INNER JOIN dbo.sys_mfg_gate 
	    ON dbo.sys_mfg_workctr.gate_id = dbo.sys_mfg_gate.gate_id AND dbo.sys_mfg_gate.mfg_id = dbo.sys_mfg_workctr.mfg_id
      WHERE (dbo.mfg_order.cancelled = 0 AND 

Open in new window

0
Hi Expert,

I want to insert multiple records on a single query and my records are in an external file.

Please any advice for me ...

I have 2 Approach:: Please suggest which one will work effectively.

1: Using external table ... "insert into table select * from exttbl;"
2: Using SQL loder .
0
Hi,

I have a trigger that updates my detail table. I would like to use the same trigger to also update the parent table based on a value in the detail table. I keep getting an error "The multi-part identifier "Citations.LogDetails.ParentLogNo" could not be bound."

The trigger works when I use a literal numeric value but not when I pass a value.  Is there a way to pass the value from the detail table in the first update statement to the update statement in the second table?

Thanks



/****** Object:  Trigger [Citations].[tgr_LogDetailsLastUpDated]    Script Date: 9/14/2018 7:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [Citations].[tgr_LogDetailsLastUpDated]
ON [Citations].[LogDetails]

AFTER UPDATE AS
 
  UPDATE Citations.LogDetails
  SET LastUpdatedOn = GETDATE()
  WHERE LogDetailID IN (SELECT DISTINCT LogDetailID FROM Inserted)

 UPDATE Citations.LogHeader
 SET LogHeader.TotPlatesPerLog = (SELECT COUNT(DISTINCT(VehiclePlate)) FROM Citations.LogDetails WHERE ParentLogNo = Citations.LogDetails.ParentLogNo)
 WHERE LogHeader.INT_ID = Citations.LogDetails.ParentLogNo
0
I need a reliable way to host a set of MS SQL server 2012 databases and can access them through an IP address. I already have a host, however, it costs me to add any SQL database to the site, and my site is basically used to host the databases of what will be my applications clients... Is there a cheaper way to save these database and use an IP address(s) to access them in the web?
0
Dear expert

Please help me with this MSSQL Query:

SELECT FirstName LastName DatetimeCancel
FROM [Prod].[System].[Approvement] aa 
join [Prod].[Contact].[Individual] ci on aa.PersonalIdentityNumberSender = ci.PersonalIdentityNumber join [Prod].[Contact].[Address] ca 
on ci.Id = ca.Id join [Prod].[Subscription].[Member] rsm on rsm.IdMember = ca.Id join [Prod].[Invoice].[Receiver] ser on ser.Id = ca.Id
where ApprovementStatusId = 7 and aa.DatetimeCancel is not null and aa.Id not in (select id from [Prod].[System].[Approvement] where ApprovementStatusId = 5 or ApprovementStatusId = 3 or ApprovementStatusId = 4 or ApprovementStatusId = 6 or ApprovementStatusId = 8 or ApprovementStatusId = 9 or ApprovementStatusId = 10 or ApprovementStatusId = 11 or ApprovementStatusId = 12 or ApprovementStatusId = 13 )

Open in new window


Result return with doublets because DatetimeCancel is diffirent, now I want only show the lastest time as result, in this case 2018-05-25 others ignore. Distinct can only random remove one (or more) doublets.
Is there a way to do this?
SC.PNG
0
I need to pass a global variable to a query column name so that my report legend automatically labels the data correctly.

I can declare a global variable that is accessible to my queries in other ways, but I've never tried to do so for a column name and I suspect their is some magical use of special characters, quotes, and brackets that will accomplish this.... right?  Since the brackets alone simply uses everything inside the brackets as the column name, I'm hoping there is some syntax I can use in the SQL statement to go get my global variable set as the column name instead.

Public Function get_global254(G_name As String) As Variant

     Select Case G_name
            Case "MetricValue1"
                    get_global254 = MetricValue1  'query format: get_global254("MetricValue1")
          
    End Select
End Function

Open in new window


I want to set the value of MetricValue1 when the user opens a form that has this value set to their choosing.

Then I want to have the column for the data produced in a query to have that name.  Instead of just using AS [Value 1], and make the use look up what they decided to call Value 1, I've assigned it to MetricValue1 as a global string, and would like it to appear as the column name as I'm trying to do below see:  Sum(Tbl_MetricData.Value) AS  get_global254("MetricValue1"),


SELECT Tbl_MetricData.ValueDate, Sum(Tbl_MetricData.Goal) AS Goal, Sum(Tbl_MetricData.Value) AS  get_global254("MetricValue1"), …
0
Determine the Perfect Price for Your IT Services
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Hello Experts,

How to search for values in a column that has leading zeros? Count them?

I know this is just simple to experts but I searched but cannot find any answers.

See attach file.

Thank you.
FindandCountLeadingZeros_inValue.xlsx
0
Dear expert

I got problem with a query.

  SELECT ID
  FROM [system_Prod].[System].[Approvement]
  where ApprovementStatusId = 7

Open in new window

This will show approvementStatusId = 7. Problem is next one query, I want all result ID that show up, all the duplex removes include the ID that got duplex it self for exemple: ID: 5000, with ApprovementStatusid = 7 shows up, and ID 5000 got also  ApprovementStatusID = 4, if it detect this, it will remove ID 5000 completely from the result. Is this possible to do?
0
Hi,

I am trying to write a relatively simple query.

given this table of data:

row_id      tr_rowid_debtor      tr_account      tr_to_client      tr_to_agency      tr_posted_date      tr_payment_date
1      2      1      90            1/06/2018      1/06/2018
2      2      1            40      2/06/2018      2/06/2018
3      3      16            30      2/06/2018      2/06/2018
4      4      16            20      2/06/2018      2/06/2018
5      5      16            10      1/06/2018      1/06/2018
6      7      1            12      1/07/2018      1/07/2018
7      7      1      23            1/06/2018      1/06/2018
8      7      1      25            1/08/2018      1/08/2018
9      2      16            12      1/06/2018      1/06/2018
10      3      16            30      1/06/2018      1/06/2018
11      4      16            450      1/06/2018      1/06/2018
12      5      16            459      1/05/2018      1/05/2018
13      7      16            654      1/05/2018      1/05/2018

and this is my current query:

select tr.tr_rowid_debtor,max(tr.tr_posted_date)
from de_transaction as tr
where tr.tr_account = 1
group by tr.tr_rowid_debtor

i am trying to add this in as well:
 (tr.tr_to_agency+tr.tr_to_client) as amount,

what I am trying to achieve is for each tr_rowid_debtor I am trying to get the latest transaction - which is determined by the highest tr_posted_date  - and I want the associated amount as well.  Thus the  (tr.tr_to_agency+tr.tr_to_client) as amount.

Could someone please assist?

many thanks!!
0
How can I create one SQL that will give me a 'TOTAL' from:

The result of this:

SELECT   (  SUM  (UnitCost  * Qty)- (SUM ( PmtRecd))   ) AS BALANCE
FROM dbo.BillingLines WHERE CaseId = 20816  Group by  CaseId

Minus the result of this:

SELECT SUM (Amount) as total
FROM dbo.PaymntSchedule WHERE   Caseid = 20816
0
In Oracle SQL how could I do create this Query.  I would like to check for multiple conditions with the where clause.

select * from lawprod.WHSHIPLINE where company=10 location=98MDC update-date='01/20/2018'

It works if I do this

select * from lawprod.WHSHIPLINE where company=10  

However, I would like to check for more conditions like

I would like to include data of the company=10 and location=98MDC and update_date = 01/20/2018

Thanks for any help!
0
Can anyone tell if its possible to use bcp queryout to do the below as I cannot get it, we have sql 2012 r2 (If I simply bcp the table out it works but I don't want at the columns and we need the order by?):  


DECLARE @data nvarchar(2000);
SELECT @data = '      bcp "select job,item,description,extruder,PiecesPerMachHr,qtyordered,operationstart,operationend,rawmaterial,unit_weight,u_m,tooling,us_length,suffix       from einc_app.dbo.EnsIntouch1
                        order by extruder,operationstart" queryout "\\eincintouch\Intouch2\Download\SytelineSch.csv" -c -t, -T -SEINCDATA01';
PRINT @data
exec xp_cmdshell @data ;

When run SQL gives me
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]        [-K application intent]
NULL



Thanks
0
Hi all.

I have the following sql query in my Visual Studio application the data is displayed the following way:

YEAR--TOTAL SALES---ABC---DEF---GHI

but I want to change the order of the columns to be:

YEAR---ABC---DEF---GHI---TOTAL SALES

Any idea how I can do this? Thank you in advance!

SELECT *
FROM (
    SELECT 
        Year,ProductLine, Sales,TotalYearSales AS [Total Sales]
           FROM CRMProductLineSales
           WHERE PMID = '55655'
          
) as s
PIVOT
(	SUM(Sales)
    FOR ProductLine IN (ABC, DEF, GHI)
)AS pvt
ORDER BY Year

Open in new window

0
Using Transact SQL in SQL SERVER Management studio.  I have always used a Case statement when choosing. Can I use an If then

Example:
SELECT [Order ID],
[Order Amount],
[Order date],
if shipped = 1 then
    'yes'
else
   'no'
End  as shipStatus
FROM Orders
WHERE [Order ID] between 2934 and 2944
0
Dear expert.

I need help with this query:

SELECT
    DB.name,
    SUM(CASE WHEN type = 0 THEN MF.size * 8 / 1024 ELSE 0 END) AS DataFileSizeMB,
    SUM(CASE WHEN type = 1 THEN MF.size * 8 / 1024 ELSE 0 END) AS LogFileSizeMB,
	SUM (DataFileSizeMB + LogFileSize) as TotalSize
FROM
    sys.master_files MF
    JOIN sys.databases DB ON DB.database_id = MF.database_id
GROUP BY DB.name
ORDER BY DataFileSizeMB DESC

Open in new window

SUM (DataFileSizeMB + LogFileSize) as TotalSize is not working, i need to add two result together.
Any idea?
Thx
0
Redefine Your Security with AI & Machine Learning
Redefine Your Security with AI & Machine Learning

The implications of AI and machine learning in cyber security are massive and constantly growing, creating both efficiencies and new challenges across the board. Check out our on-demand webinar to learn more about how AI can help your organization!

Dear expert i need help on this sql query:

select sum (money)
from dayground
where Daycode = 1 and Typecod = 4 and date = '20790606'
and money >= 25000

divide by

select count (memberno)
from dayground
where Daycode = 1 and Typcod = 4 and date = '20790606'
and money >= 25000

Anyway to do it?
Thx
0
When this stored proc executes, it doesn't seem to be able to evaluate whether the variable @SQLAgentJobType contains the text 'Incremental' or 'Manual'.  Right now the value is set in the SSIS environment as 'Manual', but it executed the steps for 'Incremental'. If I reverse the order of the IF -blocks, it will execute the steps for 'Manual' first if the value is set to 'Incremental'.  As an aside, before anyone complains about the start and end dates as being nvarchar(20)-- don't. They are set this way because they are concatenated to another set of SSIS environment variables for an openquery statement needed elsewhere in the SSIS package.  Please review and advise.

ALTER PROCEDURE [etl].[ResetProjectDaterangeVariables]
	-- Add the parameters for the stored procedure here
( @SQLAgentJobType varchar(20),
  @FolderName nvarchar(128),
  @EnvironmentName nvarchar(128),
  @startDate nvarchar(20),
  @endDate nvarchar(20)
)
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

	SET NOCOUNT ON;

	 DECLARE
	 -- Note: The string dates are created in order to deal with cobbling together an Informix SQL statement as part of a 
	 --       concatenated text string built by variables used in source dataflows

	 @tempstartDate nvarchar(20)
	,@tempendDate nvarchar(20)
	-- leave room for a leading space
	,@fixedEndTime nvarchar(9)
	,@fixedStartTime nvarchar(9)
	,@var sql_variant;

   -- DEBUG [uncomment]
   --  declare 

Open in new window

0
SQL Server Database:

I need to update by top X like:

Update Top 2000 set ......

I don't find an example and when I try it errors. Is there something similar?

I am getting a time out on my update so I want to do it in pieces. Any ideas?

thanks!
0
I want to get the past 3 months sales data and display it something like:

August 2018 - $.....
July 2018 - $....
June 2018 - $.....

I have tried something like this:

        $this->db->query("SELECT SUM(`order_total`) as `totalSales`
        FROM `order_summary`
        WHERE `order_date` >= LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 3 MONTH
        ");
        $results = $this->db->resultSet();
        return $results;

Open in new window


And I tried to put `order_total` in a foreach loop but it just spits out one figure. I am guessing there is something wrong with the SQL query?
0
Hello,

We have an Access DB that we recently uploaded the tables to SQL Server and we are still using the ACCESS frontend so we are linked to the tables.  I am trying to figure out why the ID field is not populating when a new record is created?

the ID field is the Key field.  In access we still show it to be Field Size: Replication ID and Required.

what am I missing?
0
Hello Experts,

I have SQL table Data that I want to Order by UpdateNumber Column ascending order but I cannot get it to work. I know this is just simple.

I really appreciate all the help. Please see attach.

Thank you.
OrderByUpdateNumber.xlsx
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.