|
I am new to SQL coding and having trouble grouping and generating sub totals and Grand Totals for a report generated by this query:
SELECT ISNULL(CAST(dteAccountingDate AS varchar (20)),'') AS MonthEnd,Payee, ClientName,
ISNULL (CAST (Accrual as varchar (20)),'') AS Accrual,
ISNULL (CAST (Payment as varchar (20)),'') AS Payment,
ISNULL (CAST (OverUnder as varchar (20)),'') AS OverUnder,
ISNULL (CAST (Salaries as varchar (20)),'') AS AnnualisedSalaries,
ISNULL (CAST (PercentVal as varchar (20)),'') AS Percentage,
ISNULL (CAST (CountMem as varchar (20)),'') AS NoMember,
ISNULL (CAST (CostPerMem as varchar (20)),'') AS CostPerMember
FROM #tmp INNER JOIN tblClientParents
ON #tmp.fkiClientParentID = tblClientParents.pkiClientParentID
AND Payee NOT IN ('Opening Value','Current Year','Sub Totals','Totals', '','______________','Prior Year Payment')
My primary objective is to have results grouped and summaries generated by Payee and ClientName.
I tried group by and order by but they don't give me the required results; even tried Compute By. I want to use Group by With Rollup but the problem is i only want to generate summaries of three columns Accrual, Payment and CountMem.
Is there a way to Group by all the columns in the select query but have Rollup only on the three columns? Or any other ways to generate summaries on the three columns only?
I also tried Group By Rollup, it doesn't work with SQL Server 2008 that i am using.
Need some help here please!
Thanks in advance
|
|
|
|
|
|
Hi Niladri
Thanks for the article, it gives me a better understanding of Grouping and Rollup. I applied it by inserting Case Grouping on Payee and ClientName but still didn't yield the desired results. May be i don't grasp it quite clearly, but you see in the example in the article they had two columns (CustomerName and ItemName), so when grouping they just group by the two columns. In my case i have 10 columns and only have to summarise 3 columns (Payment, Accrual, CountMem) and group by Payee and ClientName only.
Please help me out here.
|
|
|
|
|
You have to check whether any users are connected to sql server database and if any user is connected to database, you have to disconnect the user(s) and run a process in a job. How do you do the above in a job?
|
|
|
|
|
Sounds like a homework question, but.....
Take a look at SP_WHO and KILL, but be warned, you need admin (sa) rights to do a kill.
Ashish Kumar Vyas wrote: run a process in a job.
not sure what you mean here. Run some sql? run a stored proc? what?
anyway, hopefully this will point you in the right direction.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
how to insert multiple rows in sql
using a single sql command
thanks in advance
arun
|
|
|
|
|
It depends where the data is coming from... Can you be a little more specific?
|
|
|
|
|
thanks for the response
i wanted to add around 500000 rows of data in a table as atemplate for my project
one field can be the primary key which will be auto incremented
all others are repeated values...
so is it possible for me to add multiple rows at one shot
|
|
|
|
|
If you have all of that data in a table, you can use:
Insert into DataBaseTable
select * from SourceTable
You can also use Bulk Insert[^] for doing this.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
i dont have a alternate table
____ let me give u a clear picture
lets assume my table is details with coloumns ID(primary key)(Auto incriment), Name
so my insert statement is INSERT INTO DETAILS VALUES("abcd");
i want the same data 500000 times
do i need to run it in a loop
or is there any other method of adding multiple rows
|
|
|
|
|
See my cartesian product answer below.
|
|
|
|
|
I figured he had no data, but wanted 500K records to test with...
|
|
|
|
|
 My bad. I thought he needs to insert data from somewhere to his DB.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
The easiest way is to use another table to create a cartesian product (ie: selecting from two or more tables with nothing linking them causes a cartesian product - one row for every possible combination).
The following select statement will return over a million rows on my test SQL Server:
select 'a'
from dbo.sysobjects as so1, dbo.sysobjects as so2
Extending this into an insert statement, you would do this:
insert into TABLENAME(FIELD1, FIELD2)
select 'a', 'b'
from dbo.sysobjects as so1, dbo.sysobjects as so2
which will basically insert the values a and b into your table a million or so times... If you have a table that autonumbers, then you will have a million consecutive numbers generated with which to test with.
Hope this helps.
|
|
|
|
|
thanks a lot bro its working for meee
|
|
|
|
|
No worries... glad I could help!
|
|
|
|
|
Wow! this is a real good way. 
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
If u are using
SQL SERVER 2008 , you can use
Table Valued Parameter .
Form your front end application(assuming .net), u need to send via a datatable to ur TVP.
Niladri Biswas
|
|
|
|
|
pls explain
i m sorry i didnt get wat u said ..
pls
|
|
|
|
|
How can I use the bulk insert function with an online stream of data?
Can I simply do:
Bulk Insert datatable
From 'http://www.websites/sample.txt'
or
Do I need to create a webclient, wrap it with a reader/writer (assuming "sreader") and do:
Bulk Insert datatable
From 'sreader'
Thanks!
|
|
|
|
|
You may find examples already built using google, especially for yahoo stock data
|
|
|
|
|
I work with both SQL Server Express 2008 and Microsoft Visual Web Studio Express 2008. However the two programs don't seem to be "in sync" properly. 
Probably this is because I set up the database wrong, but I'm not sure. I first add a new database and a table in VWD (Visual Web), which creates two files, a MDF and a log file under
Documents\VisualWebStudio2008Express\Websites\Website\APP folder.
Next, I tried attaching that database file in SQL Server Management Studio Express. Since the Express version could not access any document saved under My Documents (which I later found out), I copied the two files and pasted them directly under my C:\ directory, and successfully attached the database into SQL Server Management by pointing its path to the database under C:\.
Now here's where all the weird things started happening.
1. The file path of the newly attached database in SQL Server Management Studio shows that it's now pointing to
Documents\VisualWebStudio2008Express\Websites\Website\APP folder
instead of C:\, which was where I originally pointed to. Hmm, strange...
2. To test it out, I inserted some data into the database via SQL Server, and when going back to VWD and opening up the table, surprisingly the file is updated accordingly. 
3. Thinking that I wouldn't need the two files in C:\ anymore, I deleted them. But now going back to SQL Server, I found that the database is now gone!  Of course in VWD I could still open the database because it's pointing to the file under Documents\VisualWebStudio....
This is all very confusing, and I couldn't figure out what's pointing at what actually.
Could you guys please help me out? I'd greatly appreciate it.
Thanks!!
|
|
|
|
|
You should not delete MDF and LDF database files. You should only tell the database to limit log file sizes and shrink the log file if it is already too big.
|
|
|
|
|
Thank you Ted, though I'm still confused about why in SQL Server Management Studio, it shows that it's pointing to the database under the VWD folder rather than the C:\ folder, and when I delete the files in the C:\ folder, I don't see them anymore in SQL Server Management Studio? 
|
|
|
|
|
Hi,
I have got a test table in A database with user details. Now i have imported temp table in B database with user details.
Now i want to match if test & temp table contains same set of users, if not then display the extra number of users in either of the table.
In temp and test table only matching value could be email id.
How can i achieve this ?
Thank You
Andyyy
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
