Tag: tsql

Nested While loops in SQL?

I want to change my SQL code which generates n table using two nested While loops, like: DECLARE @count1 INT SET @count1 = 2012 DECLARE @count2 INT SET @count2 = 1 WHILE @count1 <= 2016 BEGIN WHILE @count2 <= 12 create table LGDfigRecov as select … from … WHERE FD0.mo_id=count2 AND FD0.an_id= count1 … SET […]

How to add sequence number for groups (new number if same group occurs again) in an SQL query

I have a question quite similar to How to add sequence number for groups in a SQL query without temp tables To take the sample from there, I have a data-structure that consists of the first two columns of this table, and I want to generate the third column in my query: Record Group GroupSequence […]

Aggregate and alias on UNPIVOT

How can I rename (alias) the default States (eg: QLD to Quensland) as an output from the below query? Also how can I aggregate (SUM) the states’ forecast to a higher level (eg: National)? SELECT PRODUCT_BK, Month, State, Forecast FROM (SELECT * FROM Forecast_Table) t UNPIVOT (Forecast FOR State IN (SA_NT, QLD, VIC_TAS, WA, NSW_ACT)) […]

How can I avoid encoding when using “FOR XML PATH”?

Using FOR XML PATH encodes my HTML making my hyperlinks useless. I see that others are using the value method to prevent encoding. However, this strips out the table structure I need. I could do a search replace, effectively decoding the html. That does not seem like the right approach. The Company column in this […]

SQL import from openrowset mixed type correctly

I have a column in Excel I’m trying to import, and it includes codes like the following: 01166 1166 2354654765432 xx132 I use IMEX=1 and everything imports as TEXT but when I assign the select to temporary table with nvarchar type, the codes with long numbers become incorrect: 1001051 becomes 1.00105e+006 I tried to cast […]

What does two return statements inside an SQL function mean?

I am new to SQL. I am analyzing this SQL Function that calculates Safety stock and I ran into something that I have never seen before. This is a Scalar function that returns a decimal. However the end of the code goes like this: DECLARE @SafetyStockUnitsCalcNew decimal(14,2) RETURN( SELECT STDEV( . . Code for calculating […]

Update with Join

SourceTable ———– Global_id int Product_id varchar Category int Status int Client —— Client_id Global_id … References ———- Client_id Category int Status int … I am trying to update values of Category and Status columns in References table. The source data with which to update comes from the following query (returns more than one row). select […]

SQL Server with Cross Join Unpivot

I have a unique cross apply. I have a table that looks like this. ID What I would like to see to see is if this can be split into 2 rows and 4 columns. I would like to split the SUB together and the APP together. ClaimID === SUB Cost === APP Cost === […]

TSQL get COUNT of rows that are missing from right table

There was one other SIMILAR answer but it is 2 pages long and my requirement doesn’t need that. I have 2 tables, tableA and a tableB, and I need to find the COUNTS of rows that are present in tableA but are not present in tableB OR if update_on in tableB is not today’s date. […]

Purge job optimization

SQL Server 2008 R2 Enterprise I have a database with 3 tables that I am keeping a retention time of 15 days. This is a logging database that is very active and about 500 GB in size and eats about 30GB a day unless purged. I can’t seem to get caught up on one of […]

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