Re: Totaling previous months figures - Database Discussion Boards

First Prev Next

Re: remotely accessing sql

Md. Marufuzzaman22-Jun-09 0:03

Md. Marufuzzaman

22-Jun-09 0:03

Hi,
Actually when you remotely perform any SQL request (using OPENROWSET , BLOB type data) SQL server
need the AuC first. That you have the permission to perform this operation.

Md. Marufuzzaman

Totaling previous months figures

Muammar©19-Jun-09 12:30

Muammar©

19-Jun-09 12:30

Hey,
I have this table:

JAN    FEB    MAR   APR
---    ---    ---   ---
10     10     10    10

Suppose I'm in March now, how would I total the values of the two previous months (ie. JAN and FEB) to have 20

Please help.

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Re: Totaling previous months figures

Henry Minute19-Jun-09 12:59

Henry Minute

19-Jun-09 12:59

Well, if that was what I wanted to do, I wouldn't start from there.
Has this design been forced on you, or are you free to choose your own?

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Re: Totaling previous months figures

Muammar©19-Jun-09 21:21

Muammar©

19-Jun-09 21:21

It is forced on me, here's the big picture:

         Jan   Feb   Mar   Apr
         ---   ---   ---   ---
Budget   10    10    10    10
Actual   10    5     10    0
...      0     0     0     0
...      0     0     0     0
...      0     0     0     0

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Re: Totaling previous months figures

Henry Minute20-Jun-09 1:03

Henry Minute

20-Jun-09 1:03

Sorry to be so long in replying.

I'm with Luc here, unless somebody else comes up with a viable solution I'd be investigating PIVOT. I have never used it, so I can't be of much help.

Good Luck! Smile | :)

Re: Totaling previous months figures

Muammar©20-Jun-09 3:18

Muammar©

20-Jun-09 3:18

Thanks Henry, Yes, it can be solved using pivot tables or the decode function but my problem is far more complicated to be solved by either of them alone!
Thanks for your time mate!

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Re: Totaling previous months figures

Jerry Hammond21-Jun-09 4:50

Jerry Hammond

21-Jun-09 4:50

Is that a SQL table or a flatfile table such as Excel?

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

Re: Totaling previous months figures

Muammar©21-Jun-09 5:31

Muammar©

21-Jun-09 5:31

Hey Jerry,
It's a SQL Table, but dont worry, I've already found another way round, although it's not professional but it solves my problem. It was for a crystal report file and I figured out I can simply create a typed dataset with the exact names and data types of those of the generated query, generate the datatable fields from the invoking form using ado and set the datasource of the report to the dataset I just created.

Thank you for your time Jerry!

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Re: Totaling previous months figures

Luc Pattyn19-Jun-09 13:10

Luc Pattyn

19-Jun-09 13:10

Hi,

the natural way a datatable grows is by adding rows, not columns. So a more natural design would have only two columns/fields, say "month" and "total", hence it would look like:

 MONTH   TOTAL
200901    10
200902    10
200903    10
200904    10

and now it becomes very easy to:
- add another month
- select some months and perform calculations on their data

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.

Re: Totaling previous months figures

Muammar©19-Jun-09 21:23

Muammar©

19-Jun-09 21:23

Hey Luc,
Thanks but I'm kinda forced to this table structure:

         Jan   Feb   Mar   Apr
         ---   ---   ---   ---
Budget   10    10    10    10
Actual   10    5     10    0
...      0     0     0     0
...      0     0     0     0
...      0     0     0     0

Is there any way to do it with this structure??

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Re: Totaling previous months figures

Luc Pattyn20-Jun-09 0:41

Luc Pattyn

20-Jun-09 0:41

Muammar© wrote:
Is there any way to do it with this structure??

Anything is possible, however I have never chosen such hard way of doing things, so I don't know any details.
FWIW: there is a PIVOT command that rotates a table.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.

Re: Totaling previous months figures

Muammar©20-Jun-09 3:15

Muammar©

20-Jun-09 3:15

Thanks Luc, Yes, it can be solved using pivot tables or the decode function but my problem is far more complicated to be solved by either of them alone!
Thanks for your time mate!

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Re: Totaling previous months figures

Henry Minute20-Jun-09 1:00

Henry Minute

20-Jun-09 1:00

Absolutely. That's why I said, I wouldn't start from there. With people being forced to use designs like that, it's little wonder that some of the questions asked in the forums seem to be a bit daft.

Re: Totaling previous months figures

Niladri_Biswas22-Jun-09 20:54

Niladri_Biswas

22-Jun-09 20:54

The answer is pretty simple

SELECT (SUM(JAN) + SUM(FEB)) LAST_2_MONTHS_RESULT FROM TBLMONTH

This query will work even for if the column values are null.

e.g.

JAN     FEB     MARCH   APRIL
---     ---     -----   -----
10	10	10	10
NULL	20	11	12
20	40	1	1
30	NULL	5	6
NULL	NULL	NULL	NULL

The answer for such a case is

LAST_2_MONTHS_RESULT
-------------------
130

Hope it helps
Smile | :)

Niladri Biswas

Re: Totaling previous months figures

Muammar©22-Jun-09 22:47

Muammar©

22-Jun-09 22:47

Thanks mate!
That was quite an answer but still it doesnt answer my question, all you did is add the previous two months literally, anyways, I've already worked that out using a stupid syntax from C# that has nothing to do with SQL. Thanks anyways mate!

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Re: Totaling previous months figures

Niladri_Biswas30-Jun-09 7:01

Niladri_Biswas

30-Jun-09 7:01

Hi,

Does this solution solved your problem. If so, please close the issue by marking the

question's answer as a "good answer".

If not then please response so that we can come to know where is the precise problem and

will help with all posible efforts.

Because if you get the right solution and is not marking it as good answer, then it is not

getting close and is listing in the Unanswered questions section , so it is becoming

difficult to filter out which are the true unresolved issues.

Please co-operate.

Niladri Biswas

Re: Totaling previous months figures

Muammar©30-Jun-09 8:02

Muammar©

30-Jun-09 8:02

Thanks again, but I already said your earlier replay doesnt answer my question and it would be misleading to mark it as a good answer.

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

SP to record inputs plus random records from 2nd table

munklefish19-Jun-09 5:13

munklefish

19-Jun-09 5:13

Hi,

I'm in need of a stored procedure which takes 5 input values and then stores them in 'Table A'. This is simple enough.

However i also need to get data from a 2nd table 'Table B', return 5 random records(1 column only) and then input these records into 'Table A' along with the other data mentioned above. (Making a single record / row of 10 bits of information)

Hopefully this makes sense.

I know how to create the select statement for the above, but how do i then capture this data within the stored procedure and use it in the insert statement?

Thanks! D'Oh! | :doh:

Re: SP to record inputs plus random records from 2nd table

Niladri_Biswas22-Jun-09 22:40

Niladri_Biswas

22-Jun-09 22:40

Your question is not very clear, because

a) You never specified if the data in TableA has been inserted already and

then you want to update the 6th column of TableA [ As you

specified that the first 5 fields of TableA will have values supplied from

the Stored Proc which indicates that the 6th column will have values from

TableB ] with the values from TableB.

b) The Table Structure of both the tables are absent.

c) What will be the ultimate output [ Any rough sketch will make the

picture more clear]

d) Whether Table2's column will have predefined value or not. If not

whether the values needs to be inserted at runtime in TableB and then it

has to be fetched and next to be inserted in TableA?

e) Any dependency is lying between the two tables(e.g. Referential

Integrity Constraint)

However, I am assuming that TableA has the following structure

TableA

Col1 Col2 Col3 Col4 Col5 Col6

All are Varchar(50) types.

N.B.~ Col6 will have the value from TableB

TableB

RandomCol

Again Varchar(50)

And my Stored Proc is as under:

ALTER PROCEDURE InsertRecordsIntoTableA
	-- Add the parameters for the stored procedure here
	(
		@VarCol1 AS VARCHAR(50),
		@VarCol2 AS VARCHAR(50),
		@VarCol3 AS VARCHAR(50),
		@VarCol4 AS VARCHAR(50),
		@VarCol5 AS VARCHAR(50)
	)
AS
BEGIN
	
	-- VARIABLE DECLARATION
	DECLARE @RANDOMCOLUMNVALUES VARCHAR(50)

   -- STEP 1: DECLARE A CURSOR
	DECLARE MYRANDOMCURSOR CURSOR FOR

		SELECT    RANDOMCOL
		FROM      TABLEB

	-- STEP 2: OPEN THE CURSOR
	OPEN MYRANDOMCURSOR 

	FETCH MYRANDOMCURSOR INTO @RANDOMCOLUMNVALUES

	-- STEP 3: START THE LOGIC

	WHILE @@Fetch_Status = 0

	BEGIN

		-- STEP 4: INSERT THE COMBINED RECORDS INTO TABLE A

			INSERT INTO TABLEA(COL1,COL2,COL3,COL4,COL5,COL6)

			VALUES(@VarCol1,@VarCol2,@VarCol3,@VarCol4,@VarCol5,@RANDOMCOLUMNVALUES)
		
	-- STEP 5: GET THE NEXT RECORD
	FETCH MYRANDOMCURSOR INTO @RANDOMCOLUMNVALUES           

   END

	--STEP 6: CLOSE THE CURSOR
	CLOSE MYRANDOMCURSOR

	--STEP 6: DEALLOCATE THE CURSOR
	DEALLOCATE MYRANDOMCURSOR
	
END

Hope this answers you question.

If not, please tell your problem precisely with necessary

description

It can even be solved without using CURSOR and with a while loop

Please be specific about your question so that others can understand

properly.
Smile | :)

Niladri Biswas

Helps on this Convertion Problem

Vimalsoft(Pty) Ltd19-Jun-09 3:49

Vimalsoft(Pty) Ltd

19-Jun-09 3:49

Good Day all i have the Following Query

select distinct convert(varchar(10), (convert(smalldatetime, c.descr)), 120) [Date], tt.Sess [Session], m.descr [Subject], ctyp.Abrev [PaperType], ct.Number [PaperNumber], a.GrpName [Campus], a.Duration, CASE WHEN MAV.STUDENTS IS NULL THEN a.Students ELSE MAV.STUDENTS END [Students], v.descr [Venue], v.Capacity, s.descr [Staff] 
             -- into #Timetable 
              from sol_actv_time tt 
              inner join tbl_clmn c on c.id=tt.Dy 
              inner join tbl_actv a on a.id = tt.actv 
              inner join tbl_cntc ct on ct.id = a.cntcid 
              inner join tbl_cntc_typ ctyp on ctyp.id = ct.cntctyp 
              inner join tbl_modl m on m.id = ct.modlid 
              inner join tbl_term t on t.id = a.term 
              left outer join sol_actv_venu ttVenu on ttVenu.actv = a.id 
              left outer join mtm_actv_venue mav on mav.venue=ttVenu.Venu and mav.actv=a.id 
              left outer join tbl_venue v on v.id = ttVenu.Venu 
              left outer join sol_actv_staff ttStaff on ttStaff.Actv = a.id 
              left outer join tbl_staff s on s.id = ttStaff.Staff 
              select activity.* from #Timetable activity for xml auto, elements

I get the Following Error

Msg 295, Level 16, State 3, Line 2
Conversion failed when converting character string to smalldatetime data type.

Thanks

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Re: Helps on this Convertion Problem

DoctorMick19-Jun-09 5:37

DoctorMick

19-Jun-09 5:37

I don't have sql server in front of me so I can't test unfortunately.

What is the data type of c.descr?

Re: Helps on this Convertion Problem

Vimalsoft(Pty) Ltd19-Jun-09 5:38

Vimalsoft(Pty) Ltd

19-Jun-09 5:38

Its a String but its in a Date Format.

Thanks i have resolved this by accepting the data as data from my presentation layer.

Thanks Thumbs Up | :thumbsup:

Table Transfer

Paul Unsworth19-Jun-09 3:38

Paul Unsworth

19-Jun-09 3:38

Hi All

I'm not a SQL guy, so I'm stuggling a bit. I hope you guys can help me.

I have an SSIS package which copies tables from one server to another. However, the source tables will, from time to time, get new fields added to it. My understanding of SSIS is that when you are copying a table, you either need the destination table to already exist, or you need to create the table via a predetermined table build script. Is there a way of copying the source table to the destination regardless of what fields are in it?
(we could just change the SSIS package to reflect the changes, but it can take over a week for our DBA's to swap it out for us)

Any help would be greatly appreciated. Smile | :)

Thanks

oooo, the Jedi's will feel this one....

Re: Table Transfer

Mycroft Holmes20-Jun-09 0:13

Mycroft Holmes

20-Jun-09 0:13

Paul Unsworth wrote:
s there a way of copying the source table to the destination regardless of what fields are in it

Not to my knowledge and your DBAs will already have told you this Big Grin | :-D

It is, via a LOT of design commitment create a package that reads the source structure and changes the destination structure but I would take a suit of armour when you approach the DBAs, they will first deny it is possible, then point out it is unreasonable and then quit if you insist. There is nothing a DBA hates more than someone screwing with their data structure.

I have done this when the business rule was that columns were only ever ADDED and never MOVED, but it was a right, royal PITA.

Never underestimate the power of human stupidity
RAH

Re: Table Transfer

Paul Unsworth21-Jun-09 21:32

Paul Unsworth

21-Jun-09 21:32

Haha. Fairy nuff. I suppose it makes sense. Although not quite what I want.

I suppose I'm just a control freak... Laugh | :laugh:

It's just our DBA's are as quick as geriatric slugs. Countless forms to fill in, just so that they can take a week to think about it, then either say no, hope that the requestee forgets about it, or say "yes. It will be operational in 2 weeks...". Can be a little frustrating.

I hope not all DBA's are like this. I may look into DB admin myself...

Thanks for you reply. Big Grin | :-D

oooo, the Jedi's will feel this one....

Last Visit: 31-Dec-99 18:00 Last Update: 12-May-24 1:40

Refresh

ᐊ Prev 1...825 826 827 828829830 831 832 833 834 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.