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

Using a CTE to get totals, would like assistance on adjusting CTE to concatenate multiple rows
I want the output in the following format:
GC total: 177  36/141 HS total: 10/10
These results are for totals as well active and inactive

;WITH
gcttl AS (
            SELECT  COUNT( sg.code)  as gcttl                                      
            FROM   SCM.CV3SecurityGroup sg join SCM.cv3usersecuritygroup usg (nolock) on usg.SecurityGroupGUID = sg.GUID
            join CV3User u (nolock) on u.guid = usg.UserGUID
            WHERE u.active IN (0,1) and sg.code = 'Growth Charts'
            ),
gcttlinactive as
            (
            SELECT  COUNT( sg.code)  as inactivecode                             
            FROM   SCM.CV3SecurityGroup sg join SCM.cv3usersecuritygroup usg (nolock) on usg.SecurityGroupGUID = sg.GUID
            join CV3User u (nolock) on u.guid = usg.UserGUID
            WHERE u.active =0 and sg.code = 'Growth Charts'
            )
 , gcttlactive as
            (
            SELECT  COUNT( sg.code)  as activecode                               
            FROM   SCM.CV3SecurityGroup sg join SCM.cv3usersecuritygroup usg (nolock) on usg.SecurityGroupGUID = sg.GUID
            join CV3User u (nolock) on u.guid = usg.UserGUID
            WHERE u.active =1 and sg.code = 'Growth Charts'
             )
, hsttl as
            (
            SELECT  COUNT( sg.code)  as "hsttlcode"                        
            FROM   SCM.CV3SecurityGroup sg join SCM.cv3usersecuritygroup usg (nolock) on usg.SecurityGroupGUID = sg.GUID
            join CV3User u (nolock) on u.guid = usg.UserGUID
            WHERE (u.active IN (0,1) and sg.code = 'Growth Charts' and …
0
ON-DEMAND: 10 Easy Ways to Lose a Password
LVL 1
ON-DEMAND: 10 Easy Ways to Lose a Password

Learn about the methods that hackers use to lift real, working credentials from even the most security-savvy employees in this on-demand webinar. We cover the importance of multi-factor authentication and how these solutions can better protect your business!

I am trying to convert a decimal value into minutes in a SQL Server view. Right now I have the following and it's giving the correct result to a degree.

CONVERT(DATETIME, dbo.jodrtg.fsetuptime / 24, 108) AS SetUpMinutes

Open in new window


The result I am getting for a decimal value of .75 is: 1900-01-01 00:45:00.000. How can I get rid of the 1900-01-01 and the trailing 00.000? I only need hours and minutes.
0
Hi:
I have a crosstab query which take over 5 minutes rendering data
Table 1 : @TempTable has 1100 records.
Table 2 : Days   table has 30 records which is days numbers from 1 to 30 (Arabic month)
 --this is my query:
  SELECT FullData.day,  a.DailyAttendanceID
      ,FullData.BSC_NO
     ,bsc_name = FullData.bsc_name
      ,a .BSC_UNIT
      ,a .BSC_SECTION_NO
      ,a .DailyAttendanceTypeID  
     , PTDattendanceCodes = isnull (a .PTDattendanceCodes,'-')
     ,a .DailyAttendanceDescriptions
      ,a .WhoCreated
      ,a .AttendanceDate
     ,a .GetDate
     ,a .myTime  
      ,a .IsFingerPrintExists
     ,a . myDay
             
FROM  
(select * from Days  
CROSS JOIN
(SELECT DISTINCT [BSC_NO],bsc_name from @TempTable) as Data ) as FullData
LEFT JOIN
@TempTable as a on FullData.day = a.myDay
      AND FullData.[BSC_NO] = a.[BSC_NO]
      order by FullData .BSC_NO , FullData .day

any idea please

my output result is like what U C in here:
myData
0
Hello,

We have a 2012 dell SQL server running a Fusion IO card.

Now that Sandisk bought Fusion-IO what should we buy to replace this server?

Anything faster come out? Does Dell still sell Fusion IO Cards?

This for the Greenway practice management software.

This server is still fast but needs to get FASTER :)

Thanks!
0
Hello Experts,

I'm very new to SQL Queries,

I'm trying to run the following query on the attached data

SELECT
  *
FROM CSVDatabase.dbo.sales_info
  GROUP BY Company HAVING AVG(Sales)

Open in new window


However, I'm getting the following error:

1 An expression of non-boolean type specified in a context where a condition is expected, near 'SET'. SQL.sql 4 1

The dataset is as follows:
sql
Can someone let me know what the error relates to and how to fix it

Thanks

Carlton
0
How to add a check box field from a sql database using php. I am getting my report working already, what I want to accomplish is a check box field to EDIT that particular row

I am able to get my report, and once I get my report I just want to check it, and be able to edit that row,
Tnx
here is my code to populate report
$result = $conn->query($sql);
$total=0;$jobs=0;$total2=0;

if ($result->num_rows > 0) {
    // output data of each row
 
    while($row = $result->fetch_assoc()) {
        //echo "<br> Date: ". $row["fecha"]. "   - Job: ". $row["job"]. "   ". $row["tipo"]. "  - Amount " . $row["valor"] . "<br>";
        printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>", $row["fecha"], $row["job"],$row["tipo"],$row["valor"],$row["dinero"]);
        $total=$total+$row["valor"];
        $total2=$total2+$row["dinero"];
        $jobs=$jobs+1;
    }
} else {
    echo "0 results";
}

Open in new window

0
I have been reading up about transaction isolation levels in SQL Server.
I understand the differences between READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE, but the waters became muddy when I tried to understand SNAPSHOT.
How is the SNAPSHOT isolation level different from READ COMMITTED and REPEATABLE READ ?
0
Hi,

I have one SQL Table below.



how to write delete query in stored procedure if group by multiple condition.

for example,

if country "101" and level "1", then it should not delete and through message "Child record Exists"

Because, Country "101" will have 4 child records. it should ask user to delete first child records in the combination of

Child record for country "101" and country code is A1,A2,A3 and  level is "2,3,4"..

"Level = "1" is always parent key and it should allow if any child key is there in level.

for example, for country "101" there will have 3 child records (level 2,3,4). once these records has deleted then only,

level "1" should allow to delete

How to check the condition in SQL stored procedure...
0
Hi, how can I pass a comma delimited string (or just 1 value) as input parameter to DB2 stored proc? I tried this -

CREATE PROCEDURE VALID_STATUS(IN IN_DOMAIN_ID VARCHAR(4096))
    SPECIFIC VALID_STATUS
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    NULL CALL
    LANGUAGE SQL EXTERNAL ACTION
    INHERIT SPECIAL REGISTERS
BEGIN
DECLARE cur1 CURSOR WITH RETURN FOR
select a.ENTITY_ID,a.ATTR_VAL from ENTITY_ATTR a where a.ENTITY_TYP='EMAIL_DOMAIN' and a.ATTR_TYP='TLS' and a.ENTITY_ID in (' || @IN_DOMAIN_ID  || ');
OPEN cur1 ;
END

;

The proc should run as follows - call VALID_STATUS('D1')  (with a single parameter) or call VALID_STATUS('D1','D2','D3','D4') (any no of parameters)
0
consumed the most virtual memory: sqlservr.exe (1688) consumed 68628185088 bytes, I got this msg, server hang 2 twice , since yesterday and restarted the whole server from VC
Early the sql memory was configured was max default setting

and now I have have configured SQL sever 54488 MB SQL Max server memory(MB) currently.

after i have run full backup, the whole memory was consumed was already..sp_who2.xlsx

Not sure, wht is the issue, what needs to check

Environment
windows 2008 r2 enterprise,  has 64gb ram
sql server std 2008 r2
Single instance, db size is 485gb, full backup size is 78gb

the below query and attached file, is the output after few hours of backup completed

Physical Memory (MB)       Available Memory (MB)      Total Page File (MB)      Available Page File (MB)      System Cache (MB)      System Memory State
65535                                             6312                                    69629                           9970                                             3652          Available physical memory is high

sp_who2.xlsx
0
Ultimate Tool Kit for Technology Solution Provider
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Hi All

Greetings!!!

Hope you are well.

I need to copy a Schema from one machine to other machine,

Both machines have Db2 instance of same version (10.5)

But Schema is having data of LOB data type.

Which method is simple to copy the entire schema with structure and data.

I know ADMIN_COPY_Schema only supports on same machine and under same instance .

But db2move utility does not support LOB data type (I think).

Any inputs on this Please.

Thanks in Advance!!!!
0
Need an SQL Server 2008R2 "On Insert" trigger on TABLE CT_PACKLIST_TRACKING_INFO
Table Definition
ID (autonumber)
ORDER_ID
PACKLIST_ID
TRACKING_INFO

On insert, if the PACKLIST_ID has a comma space in it Ex: PACKLIST_ID = "DC181592, DC181593, DC181594"
I need to insert seperate records for each PACKLIST_ID in the string.

Ex: Record is inserted with PACKLIST_ID = "DC181592"
    Do nothing.

Ex: Record is inserted with 3 PACKLIST_ID = "DC181592, DC181593, DC181594"
    Capture the PACKLIST_ID within the trigger.
    Update the inserted record = change the PACKLIST_ID to "DC181592"
   
    For each different Packlist ID
        Insert a new record
        Ex: Insert record for DC181593, loop Insert record for DC181594

Any help is greatly appreciated.

I did find a view for spliting PACKLIST_ID
CREATE FUNCTION [dbo].[split](
          @delimited NVARCHAR(MAX),
          @delimiter NVARCHAR(100)
        ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
        AS
        BEGIN
          DECLARE @xml XML
          SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

          INSERT INTO @t(val)
          SELECT  r.value('.','varchar(MAX)') as item
          FROM  @xml.nodes('/t') as records(r)
          RETURN
        END
 go

that could be called from the trigger:
select * from dbo.split('DC182111, DC182110, DC182109',', ')
or select * from dbo.split(PACKLIST_ID,', ')
0
How we could tuning top sql queries from AWR without using sql advisor? How to check for better execution plan? How to check if better sql profile/baseline should be used?
I would like to see an examples how you guys doing it.
Thanks in advance.
0
Hi Everyone,
Using Jreports tool and need to write a sql query to combine the two fields into in output column in the report.  The query below does that however, if either one is Null the query returns blank for the record.  is there anyone to do an "if field is null then return just the contents from the field that is not null." or something to that affect?
the two fiels are "detail" and "Reference_text"

@"Columns.detail" +" "+ @"Columns.reference_text"
0
Dear Experts,

 I need help to create a view from the following table, the view contain 2 columns ID & sum of Hold time which is difference between each Hold_RELEASE & HOLD



ORDERID  |  STATE                   |  Timestamp
1                |  HOLD                    | 12-Nov-13 06:03:46
1                |  HOLD_RELEASE   | 18-Nov-13 04:13:33
1                |  HOLD                    | 19-Dec-13 03:12:34
1                |  HOLD_RELEASE   | 25-Dec-13 12:04:55
2                | HOLD                     | 12-Oct-13 06:22:33
2                | HOLD_RELEASE    | 23-Nov-13 08:12:11


Thanks
0
Hello Experts,

I'm just getting started learning SQL and Python.

Compiling sql queries and python queries is pretty tough going.

I was wondering if someone could let me know if there are any GUI tools out there they could recommend to help write sql and python queries?

Thanks

Carlton
0
Raw_data.xlsxHello Expert,

I have a table with following record details.
SR_NO ROOM_NO	   FLOOR_NO	TIME_830AM_930AM	FLOOR1	TIME_930AM_1030AM	FLOOR2	TIME_1030AM_1130AM	FLOOR3	TIME_1130AM_0130AM	FLOOR4[/b]
        1	        216              	2ND	                ME       	           H1	                       NULL	           NULL	              BE	                             G1	                   NULL	                    NULL
        2	        216	                2ND                       	ME	                   H1	                       GE	                      I1	             NULL	                           NULL                      NULL		            NULL
        3	        216	                3RD   	              NULL         	NULL	                       EE	                     E1	             NULL                            NULL                         FE	                      F1
        4	        216	                3RD	                      NULL	        NULL	                      NULL	           NULL	              AA	                             A1	                   NULL	                    NULL
        5	     217	                1ST	                      NULL	        NULL	                       BB	                     B1	             NULL	                           NULL	                   NULL	                    NULL

Open in new window

Looking for O/p like this!

SR_NO ROOM_NO	   FLOOR_NO	TIME_830AM_930AM	FLOOR1	TIME_930AM_1030AM	FLOOR2	TIME_1030AM_1130AM	FLOOR3	TIME_1130AM_0130AM	FLOOR4[/b]
        1	     216              	2ND	                ME       	           H1	                       NULL	           NULL	              BE	                             G1	                   NULL	                    NULL
        2	     216	                2ND                       	ME	                   H1	                       GE	                      I1	             NULL	                           NULL                      NULL		            NULL
        3	     216	                3RD   	              NULL         	NULL	                       EE	                     E1	               AA                                  A1                           FE	                      F1

Open in new window

Thanks in Advance.
Looking-for-Output.xlsx
0
DBMS and RDBMS

I did some online reading, where it stated that:

<<The key difference is that RDBMS (relational database management system) applications store data in a tabular form, while DBMS applications store data as files. Does that mean there are no tables in a DBMS? There can be, but there will be no “relation” between the tables, like in a RDBMS>>

*** so how can you make a query fetch several pieces of Data from different files in DBMS

SQLLite if I am correct is DBMS rather than RDBMS

what are other DBMS Databases and RDBMS?

THank you
0
I have a failed SQL backup and have no clue why this is happening? Can anyone help with this?

Failed:(-1073548784) Executing the query "BACKUP DATABASE [JDE910] TO  DISK = N'\\\\x.x.x.x..." failed with the following error: "Cannot open backup device '\\\\x.x.x.x\\JDE Images\\SQL Backup\\Daily\\JDE910_backup_2018_07_14_220001_8461617.bak'. Operating system error 1311(There are currently no logon servers available to service the logon request.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


X.X.X.X are the IP address of the webserver jde
x.x.x.x is the ip address of the NAS device where the backup has to be moved
0
Become a Leader in Data Analytics
Become a Leader in Data Analytics

Gain the power to turn raw data into better business decisions and outcomes in your industry. Transform your career future by earning your MS in Data Analytics. WGU’s MSDA program curriculum features IT certifications from Oracle and SAS.  

Hello ,

I have some queries  regrading NEW WSUS server setup , Please provide your suggestion on below mentioned points.

1.  Which DB should use WID or SQL Express ?
2. Is it recommended to push Services pack to  WSUS client machine like (win8/10 ),As services pack comes with hefty file size &  it may impact network perform if same time all machine try to get update from WSUS?
3. In case of downstream wsus server , Do we need to have DB for respective downstream server ?

Please help me out with these queries .

Thanks
0
So i have a user schema like this:

var user_schema = new Schema({
   username:{type:String,required: true, unique : true, trim: true},
   college:{type:String,required: true},
   password:{type:String,required: true, trim: true},
   email:{type:String,required: true, unique : true, trim: true},
   phone:{type:Number,required: true, unique : true, trim: true},
   dp:{type:String},
   tag:{type:String},
   description:{type:String},
   friends:[{type:String}],
   pending:[{type:String}],
   skills:{type:String},
   bucket:[{type:String}]
  });

Open in new window

and my objective is, to search the all the documents in the collection to get people based on the following conditions:

1. They should not be in the users' "friends" array.
2. They should not be in the users' "pending" array.
3. They should have the same "tag" (a string value) as the user.

So, basically I have to compare the users' fields ("friends","pending" and "tags"), with fields of all documents in the whole collection.

How do I do it, using mongoose (nodejs mongodb library)
0
I'm running Power Bi Reporting Service 2018 Version 1.2.6648.38132

The problem I am running into is when running scheduled subscriptions, only 1 subscription can be run at a time.

When starting the subscription manually through the SQL Agent (while the other one is running), the status of the subscription goes directly to: The report server has encountered a configuration error.

I notice that the SQL Agent is starting the job, but it does not seem to trigger the Reporting Service to run the subscription. (Image attached)

Do you have any suggestions?



See the error message in the attached file.
ssrs---schedules.jpg
SQL-Agent-vs-SSRS-Job.jpg
0
I am running System Center Configuraiton Manager 2016 (SCCM) on a Server 2016 Hyper-V virtual server.

When the server boots up I always have to go into the Windows services and manually start the SQL Server CEIP service (MSSQLSERVER), SQL Server Agent (MSSQLSERVER), SQL Server Reporting Service, (MSSQLSERVER), & CONFIGURATION_MANAGER_UPDATE services since these services don't start automatically.

I have already created a batch file that will start these services when it is run and I would prefer not to change the startup type on these services from automatic to automatic (delayed start) unless absolutely necessary.

What can I do to fix this issue so these services will automatically start and won't have to be manually started every time the server boots up?
0
I m trying to Install SQL 2008r2 on newly bulit Windows Server20088r2. It requires dotnetframework 3.5
However I added roles & features for dotnet.
It still requires
0
Hi,

im curious if there is a way to do the following in PHP but within a stored procedure with SQL on the fly

$managers = array(
	0 => array(
		"id"	=> 1,
		"name"  => "manager",
		"email"	=> "email"
	)
	...
)

foreach($managers AS $d){
	//send mail
	mail(...);
	
	//Insert into messages table
	INSERT INTO...

}

Open in new window


Essentially, i would want to SELECT the manager from a table and then immediately insert it a message into the massage table

for example:

1. select manager 1
2. insert message into message table for manager 1
3. repeat for manager 2
,...so on

I would handle sending the email using php

I KNOW i can do this in a few steps such as
1. Select all the managers and organize them into an array to loop over
2. loop over the managers and create an array of emails as well as sql statements i can create a transaction with and submit the inserts all at once
3. complete loop
4. send the transaction to sql
5. send the email

but i am curious if i can do it one for one within a stored procedure

Thanks in advance
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.