12 Replies - 2260 Views - Last Post: 24 January 2013 - 12:32 PM

#1 Mohsin01   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 111
  • Joined: 13-April 12

Getting Second last row as null in sql server

Posted 24 January 2013 - 10:20 AM

Hi Guys,

I am getting sum of rows at end and i want to make second last row as null row. and i have no idea how to do that. And i wanna make my last row as Bold. I mean font of last row as Bold.

Here is my query for sum and select
SELECT Cast(EmployeeID AS VARCHAR(6)) AS EmployeeID,FullDate,[day],in1,out1,in2,out2,total_Hours, duty_hours, overtime, salary, allowence
FROM    EmployeeHours
  
Union all
select 'Total',null,null,null,null,SUM(Out1),null, SUM (total_Hours), SUM (duty_hours), SUM (overtime), SUM (salary), SUM (allowence)
FROM   EmployeeHours







Is This A Good Question/Topic? 0
  • +

Replies To: Getting Second last row as null in sql server

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15481
  • View blog
  • Posts: 62,021
  • Joined: 12-June 08

Re: Getting Second last row as null in sql server

Posted 24 January 2013 - 10:23 AM

Two things - why do you want the second to the last row to be null?

Second - bold... where? Where are you displaying this data? Font formatting really don't have a whole mess of anything to do with the actual database management.
Was This Post Helpful? 0
  • +
  • -

#3 Mohsin01   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 111
  • Joined: 13-April 12

Re: Getting Second last row as null in sql server

Posted 24 January 2013 - 10:51 AM

Because I am getting sum in the last row so i want some space between sum and the upper rows. and to make it prominant thats why bold.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15481
  • View blog
  • Posts: 62,021
  • Joined: 12-June 08

Re: Getting Second last row as null in sql server

Posted 24 January 2013 - 10:54 AM

Wait.. are you trying to make a sql query into some sort of ... report? All that fancy report stuff should be handled elsewhere.. not in the management of a DB.
Was This Post Helpful? 0
  • +
  • -

#5 Mohsin01   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 111
  • Joined: 13-April 12

Re: Getting Second last row as null in sql server

Posted 24 January 2013 - 11:01 AM

Yeahhh You got my Point :gun_bandana: :).

I was getting big problems with crystal reports.Because i dont know anything about crystal reports.Big time Naab. so i side kicked it for now and tried using Grid in c#. that why i am trying to make it look bit good.

Please solve my problem if you can. :helpsmilie:
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15481
  • View blog
  • Posts: 62,021
  • Joined: 12-June 08

Re: Getting Second last row as null in sql server

Posted 24 January 2013 - 11:02 AM

Okay.. so are you in a grid on C# or are you in SQL's management studios? You know.. that's a super large difference.
Was This Post Helpful? 0
  • +
  • -

#7 Mohsin01   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 111
  • Joined: 13-April 12

Re: Getting Second last row as null in sql server

Posted 24 January 2013 - 11:08 AM

Yeah i know. Sorry for that. I should had told that i am using c# with sql server.
actually i was more concerned with formula for getting null row. And i know that if i know bold it can only be done in c# or some tool like that not in sql server :).

So, yes c# with sql server
Was This Post Helpful? 0
  • +
  • -

#8 Mohsin01   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 111
  • Joined: 13-April 12

Re: Getting Second last row as null in sql server

Posted 24 January 2013 - 11:19 AM

Lol,i have done that last row as bold. just tried this.:D
MyDataGridView.Rows[MyDataGridView.RowCount - 1].DefaultCellStyle.Font = new Font("tahoma", 14, FontStyle.Bold, GraphicsUnit.Point);



Please help with making second last row as null
Was This Post Helpful? 0
  • +
  • -

#9 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15481
  • View blog
  • Posts: 62,021
  • Joined: 12-June 08

Re: Getting Second last row as null in sql server

Posted 24 January 2013 - 11:19 AM

Okay.. now we may be cooking with gas.

Take a look at what you are conceptually doing. You have a bunch of time data... and then you mash in a row of the sum of said data... Do those really belong to each other? No.. not really. Heck you had to cast your employee id as a varchar just so you union with a column who's value is "total". See.. the train is off the tracks there.

You should make your first call to get all the time data.. add that to your grid, and then make the call to get the summary information that you may or may not want to try and mash into the grid. See then you can add your special formatting, bolding, etc. Personally it think it would convey the wrong information if they were in the same grid.. but hey.. what evs..

That being said - if you still want to mash things together then you can add the 'empty row' *AFTER* the data comes back. You know the last row is always the total row so just add an empty row before the data is attached to the grid.

Lastly - all of this can be simply swept aside if you just spent the thirty minutes shoring up your crystal or MS Reporting Service knowledge. You know - build reports with actual report tools.
Was This Post Helpful? 0
  • +
  • -

#10 Mohsin01   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 111
  • Joined: 13-April 12

Re: Getting Second last row as null in sql server

Posted 24 January 2013 - 11:52 AM

Yeah, You are Right. this could have done in 30 mins. Right now i have to do this anyhow. And i will learn Crystal reports. BTW,, i started with crystal reports but had some problem and big problem was i didnt know how to use sql statements in crystal reports. i googled but couldnt find much.

Anyways, I couldnt do to make 2nd last row empty. Please give some syntax if you can.

Thanks !!!!
Was This Post Helpful? 0
  • +
  • -

#11 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15481
  • View blog
  • Posts: 62,021
  • Joined: 12-June 08

Re: Getting Second last row as null in sql server

Posted 24 January 2013 - 11:54 AM

Ah.. no. We really don't just doll out code here.. we work with you to find a solution. So.. I gave you three possible solutions above - which have you investigated?
Was This Post Helpful? 0
  • +
  • -

#12 Mohsin01   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 111
  • Joined: 13-April 12

Re: Getting Second last row as null in sql server

Posted 24 January 2013 - 12:29 PM

You said that just add empty row. Don't know how to do that.
if i knew,why would i bother you :). Thats why i asked for some syntax :).
Was This Post Helpful? 0
  • +
  • -

#13 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15481
  • View blog
  • Posts: 62,021
  • Joined: 12-June 08

Re: Getting Second last row as null in sql server

Posted 24 January 2013 - 12:32 PM

Okay you are talking about adding it to the query - that is not what I am talking about. On the client side - where the data comes back what does it go into? How do you attach it to your grid? In what ever data set/collection/setup you are using that.. THAT is where you would add the empty row.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1