Database

thakrerakesh wrote:

This way I want to print in PDF file

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --avoids deadlock

SELECT DISTINCT UserID, StateCount_of_A, StateCount_of_B FROM tablename AS T1
LEFT JOIN (SELECT UserID, COUNT(*) AS StateCount_of_A FROM tablename AS TA WHERE State='A') ON TA.UserID = T1.UserID
LEFT JOIN (SELECT UserID, COUNT(*) AS StateCount_of_B FROM tablename AS TB WHERE State='B') ON TB.UserID = T1.UserID
ORDER BY UserID

SELECT UserId
,SUM(StateA) StateCount_of_A
,SUM(StateB)  StateCount_of_B
FROM (
  SELECT UserId
  ,CASE WHEN [State]='A' THEN 1 ELSE 0 END StateA
  ,CASE WHEN [State]='B' THEN 1 ELSE 0 END StateB
  FROM Junk
) T
GROUP BY UserID

Kujtim Hyseni wrote:

I cannot register new server in SQL Server 2008

Kujtim Hyseni wrote:

Yes I hit 'test' but I got the following message:

Kujtim Hyseni wrote:

And ... ?

CREATE TABLE #Temp
(
	ID INT IDENTITY(1,1),
	col1 VARCHAR(50),
	Col2 VARCHAR(50),
	Col3 VARCHAR(50),
	position VARCHAR(50)
)


CREATE TABLE #Temp2
(
	col1 VARCHAR(50),
	Col2 VARCHAR(50),
	Col3 VARCHAR(50),
	position VARCHAR(50)
)


 
INSERT INTO #Temp
SELECT 'aaa', 'rack1', '1-3', '3' UNION
SELECT 'bbb', 'rack2', '22-23', '2' UNION
SELECT 'ccc', 'rack3', '19-20', '2' 



DECLARE @TotalRowCount  INT
DECLARE @RowCount INT
DECLARE @Positon INT
DECLARE @I INT

SET @RowCount = 1

SELECT @TotalRowCount = COUNT(*) FROM #Temp

WHILE @RowCount <= @TotalRowCount
BEGIN

	SELECT @Positon = Position FROM #Temp WHERE Id = @RowCount

	SET @I = 1

	WHILE @I <= @Positon
	BEGIN

		INSERT INTO #Temp2
		SELECT Col1, Col2, Col3, (SUBSTRING(Col3,0,CHARINDEX('-',Col3)) + @I - 1) FROM #Temp  WHERE Id = @RowCount
		
		SET @I = @I + 1
	END
	
	SET @RowCount = @RowCount + 1
	
END

SELECT * FROM #Temp2



DROP TABLE #Temp
DROP TABLE #Temp2