Tag: sql

Take IP Addresses only from log file and save to File, Table, or .CSV

Take IP Addresses only from log file and save to File, Table, or .CSV I have a log file with entries like so: 2010-09-13 00:00:01 69.143.116.98 – W3SVC2 STREAM 209.22.66.152 80 GET /p7pm/p7popmenu.js – 200 0 7700 379 188 .org Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+6.0;+WOW64;+GoogleT5;+SLCC1;+.NET+CLR+2.0.50727;+Media+Center+PC+5.0;+.NET+CLR+3.5.30729;+.NET+CLR+3.0.30618;+.NET4.0C) – .org/ Mozilla/5.0+(compatible;+Yahoo!+Slurp/3.0;+.com) – waste.html 2010-09-13 08:52:15 67.195.112.157 – W3SVC2 STREAM 209.22.66.152 80 GET […]

Using pivot in tsql

Let’s say I have this table: Name Occupation Samantha Doctor Julia Actor Maria Actor Meera Singer Ashely Professor Ketty Professor Christeen Professor Jane Actor Jenny Doctor Priya Singer I want to have this output: Doctor Professor Singer Actor Jenny Ashley Meera Jane Samantha Christeen Priya Julia NULL Ketty NULL Maria I want to convert the […]

How to provide dynamic usage of select query in SQL Server 2008 based on a variable value

I have a table myTable with these columns and sample data: Id Name Period0Status Period1Status Period2Status ——————————————————- 1 Mark 1 2 3 2 John 2 3 3 3 Brad 1 1 1 4 John 3 3 3 5 Mark 1 3 2 etc… What I want is to use those data but the column names […]

Stored Procedure is setting variable to value from wrong row

I am writing a stored procedure to query a specific table. The behavior I am seeing is really weird though. First let me layout my table design and some sample queries, to make this easier to explain: Table Structure: All Rows for specific Reservation ID (Only 1 Row) Notice the value for the FieldName column, […]

SQL query for calculating mtd, ytd values

I have a table with columns ID, Title, Date, Amount. I need to get MTD, YTD amount values against each transaction based on Title, Date. Is there anyone who had done this before?

How to get Running total in SQL when values are not same?

I am trying to get the running total for Week (like Show as ‘Running Total in’ in Excel Pivot table. I have tried with the below query. select A.*, (SELECT SUM(b.value) FROM Tbl_Running_Total_Test B WHERE B.Fweek <= A.Fweek AND A.level1=B.level1 AND A.level2=B.level2 AND A.level3=B.level3 AND A.level4=B.level4 ) from Tbl_Running_Total_Test A Here is a SQL Fiddle: […]

Alter table with millions of rows with default value

I am trying to run an alter command like below, except this table has 40 million rows, and the query takes forever. ALTER TABLE [dbo].[ConsumerProduct] ADD IsPendDefault [bit] NOT NULL DEFAULT ((0)) GO Hence I thought of doing the alter with null, than updating every column for 40 million rows in batches of 5000 and […]

Empty XML node causes Error converting data type varchar to numeric

How do I simply insert NULL if the node is empty or absent? CREATE TABLE myxml ( “hours” DECIMAL(11,2) ); DECLARE @xml XML = ‘<?xml version=”1.0″ encoding=”UTF-8″?> <Data> <Employee> <NUMHOURS>0.5</NUMHOURS> </Employee> <Employee/> <Employee> <NUMHOURS>5</NUMHOURS> </Employee> <Employee> <NUMHOURS/> </Employee> </Data>’; INSERT INTO myxml (“hours”) SELECT t.c.value(‘NUMHOURS[1]’,’DECIMAL(11,2)’ ) FROM @xml.nodes(‘/Data/Employee’) t(c) The empty node <NUMHOURS/> causes: Error […]

Can we make join between two stored procedures in SQL Server

I have this stored procedure: CREATE PROCEDURE [dbo].[TestPackageAccept] AS BEGIN SELECT A.Id, a.PackageNumber, a.Size, a.Code, a.TestPackageOrder, B.status as LineCheckState, B.ReportNumber as LineCheckReportNumber, B.SubmitDateTime as LineCheckSubmitDateTime, c.status as CleaningState,c.ReportNumber as CleanReportNumber, c.SubmitDateTime as CleanSubmitDateTime, c.status as ReInstatement, d.ReportNumber as ReInstatementReportNumber, d.SubmitDateTime as ReInstatementSubmitDateTime, E.status as Flushing, e.ReportNumber as FlushingReportNumber, e.SubmitDateTime as FlushingSubmitDateTime, f.status as Drying, f.ReportNumber […]

sql server – For XML Raw – Add custom attribute to the element

I am trying to generate the XML in the following desired format in SQL Server 2014. But i am unable to make this small change of adding the attribute name to the Param elements. I simply want to add the “name” attribute to the Param element with the value. Current SQL Statements:- Declare @requestMessage xml, […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.