How to solve the problem?

Question

1.00/5 (5 votes)

See more:

With Tb1 as
(select Date,Particulars,BillAmount,0'PaidAmount' from tblBill
union
select Date,Particulars,0'BillAmount',PaidAmount from tblPayment
)

SELECT T1.Particulars,T1.[Date],T1.[BillAmount],T1.[PaidAmount],(Sum(T2.BillAmount) - Sum(T2.PaidAmount)) as Balance FROM Tb1 as T1
            INNER JOIN
                Tb1 as T2
                ON T1.[date] >= T2.[date]
                Group By T1.Particulars,T1.[Date],T1.[BillAmount],T1.[PaidAmount]
                Order by [Date]

What I have tried:

<pre>WITH CTE AS
(
    SELECT FETCHNEXT.*
    , RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID)
    FROM FETCHNEXT 
)
	SELECT * 
		FROM CTE
		WHERE RN = 1
	UNION ALL
	
	SELECT * 
		FROM CTE
		WHERE RN = 2
	UNION ALL
	
	SELECT * 
		FROM CTE
		WHERE RN = 3

Posted 20-Feb-19 19:01pm

Member 14156756

Updated 8-Apr-19 3:00am

v5

Add a Solution

Comments

Mehdi Gholam 21-Feb-19 1:18am

Try to ask questions that are understandable and answerable, edit your question to make it so.

The Cool Cat 21-Feb-19 1:35am

Yes Member 14156756. We won't be able to help you if you won't explain what you want.

OriginalGriff 21-Feb-19 2:04am

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with. And so far, none of what you have given us makes any sense. What is "FETCHNEXT"? Where does "Anything" come from? Why do you appear to be trying to get permutations from SQL?

Use the "Improve question" widget to edit your question and provide better information.

OriginalGriff 21-Feb-19 4:49am

Adding "I NEEDED THIS TYPE OF OUTPUT, USING SELECT QUERY OR VIA CTE." does one thing: annoys people. DON'T SHOUT. Using all capitals is considered shouting on the internet, and rude (using all lower case is considered childish). Use proper capitalization if you want to be taken seriously.

It adds nothing to the question, it doesn't even try to add information. And without information - as previously requested (without US SHOUTING at YOU, you will note) - we can't help you.

So do yourself a favour, and help us to help you. Preferably without being rude?

Member 14156756 21-Feb-19 23:15pm

I Have this type of table.....
Can we get this type of output, using select query OR via CTE....
IS IT OKAY OriginalGriff????

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2019-02-20T23:56:00

You appear to be recycling the records from each group (ID) to ensure that all IDs have the same number of records. So firstly you need to know how many of each you are going to need:

SQL

declare @maxrows int = (SELECT MAX(C) FROM (SELECT ID, COUNT(*) AS C FROM Demo GROUP BY ID) AS X)

. Use a temporary file to generate "extra" rows e.g. (there are probably better ways to do this)

SQL

select * INTO #temp from Demo 
declare @continue bit = 1
WHILE @continue = 1
BEGIN
	IF (SELECT MAX(C) FROM (SELECT ID, COUNT(*) AS C FROM #temp GROUP BY ID) AS X WHERE C < @maxrows) < @maxrows
		INSERT INTO #temp select * from #Demo
	ELSE
		SET @continue = 0
END

Now you can query the temporary table assigning a row number within each ID group and get the effect you want by choosing the ORDER BY carefully...

SQL

;with cte AS
(
	SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS rn, *
	FROM #temp
)
SELECT ID, NAMES, DATEUSED
FROM cte
WHERE rn <= @maxrows
ORDER BY rn, ID

which give results

ID      NAMES   DATEUSED
1	A	NULL
2	D	NULL
3	G	NULL
1	B	NULL
2	E	NULL
3	H	NULL
1	C	NULL
2	F	NULL
3	I	NULL
1	A	NULL
2	D	NULL
3	J	NULL
1	B	NULL
2	E	NULL
3	K	NULL

Point to note: Notice the semi-colon ; when declaring the CTE. You will need that to avoid error

Msg 319, Level 15, State 1, Line 30
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

You could put the semicolon at the end of the previous statement but if you add any code after that and before the CTE you will get the error again. So it's best practice to put the semicolon right tight up against the CTE - as I have here.