Help with SQL code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with SQL code

Author

Topic

furrelkt
Starting Member

49 Posts

Posted - 2005-07-29 : 10:11:54

Hello to all,
I need some help, I have a very complex sql code, but the jest of it is this...I took out everything to make it very easy to understand.

select
these columns,
sum(case when these values in the table.column are between these dates then this else that) ETC1,
sum(case when these values in the table.column are between these dates then this else that) ETC2,
sum(case when these values in the table.column are between these dates then this else that) ETC3,
.....till ETC12
From
table's and joins
where
this equals that
group by
these

I dont understand how to sum up all the ETC1-ETC3, in the select statement. I am writing a report for our company using actuate, reguardless of the reporting tool, i need to use the sql coding and not the graphical code in the reporting tool. The ETC1-ETC12 might contain NULL or nothing. I want to add these values together, is there a way to declare a variable to these ETC1-ETC12 values? Thanks,
any help will surely be appreciated. I can send any part of the code if needed.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-29 : 10:23:03

Something like this

Select columns,ECT1+ECT2+ECT3 as summation from(
select
these columns,
sum(case when these values in the table.column are between these dates then this else that) ETC1,
sum(case when these values in the table.column are between these dates then this else that) ETC2,
sum(case when these values in the table.column are between these dates then this else that) ETC3,
.....till ETC12
From
table's and joins
where
this equals that ) T
group by columns

Otherwise post table structure with sample and expected data

Madhivanan

Failing to plan is Planning to fail

furrelkt
Starting Member

49 Posts

Posted - 2005-07-29 : 10:27:58

so is it like a derived table?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-29 : 10:37:24

Yes it is

Madhivanan

Failing to plan is Planning to fail

furrelkt
Starting Member

49 Posts

Posted - 2005-07-29 : 13:10:30

that is cool if i really understood how to write that...:)
I am studing my The Guru's Guide to T-SQL, and am trying to understand. If you could shed any light that would be great. Here is three of the ETC's actual code from the sql statement.
SUM(case when :Monthly=1 and s.SLICE_DATE BETWEEN dateadd("m",0, :StartDate) AND dateadd("m",1, :StartDate)-1 and s.FIELD=1 then s.SLICE
else case when :Monthly=0 and s.SLICE_DATE BETWEEN dateadd("ww",0, :StartDate) AND dateadd("ww",1, :StartDate)-1 and s.FIELD=1 then s.SLICE end end) ETC1,
SUM(case when :Monthly=1 and s.SLICE_DATE BETWEEN dateadd("m",1, :StartDate) AND dateadd("m",2, :StartDate)-1 and s.FIELD=1 then s.SLICE
else case when :Monthly=0 and s.SLICE_DATE BETWEEN dateadd("ww",1, :StartDate) AND dateadd("ww",2, :StartDate)-1 and s.FIELD=1 then s.SLICE end end) ETC2,
SUM(case when :Monthly=1 and s.SLICE_DATE BETWEEN dateadd("m",2, :StartDate) AND dateadd("m",3, :StartDate)-1 and s.FIELD=1 then s.SLICE
else case when :Monthly=0 and s.SLICE_DATE BETWEEN dateadd("ww",2, :StartDate) AND dateadd("ww",3, :StartDate)-1 and s.FIELD=1 then s.SLICE end end) ETC3,

s is from table...
INNER JOIN niku.AGE_RateSlice s ON a.prID=s.PRJ_OBJECT_ID

hope that can shed some light. Thanks

Kristen
Test

22859 Posts

Posted - 2005-07-29 : 13:56:34

I'm not recognising:

:Monthly

and

dateadd("m" ...

I didn't think you could have quotes in there - and FWIW I use
DATEADD(Month, ...
DATEADD(Day, ...
because I think it's much easier to read then
DATEADD(m, ... (is that Month? Miniute? Millisecond? ...)

Kristen

furrelkt
Starting Member

49 Posts

Posted - 2005-07-29 : 14:01:12

Kristen, thanks for looking at this topic, any help you can provide will be much appreciated. This is a parameter :Monthly, and dateadd "m" can be "m" or "mm" for month. But the real issue i have is i need to add these ETC rows together, and i can't figure how to add them, thanks for any help.

Kristen
Test

22859 Posts

Posted - 2005-07-29 : 14:12:42

I'm not recognising the syntax; Is this SQL Server, or something else?

Kristen

furrelkt
Starting Member

49 Posts

Posted - 2005-07-29 : 14:46:42

SQL Server, THis is based on actuate reporting tool. But i still use the sql query analyzer to write and edit code. I just take out any of the params used from the report. thanks

Kristen
Test

22859 Posts

Posted - 2005-07-29 : 15:04:07

OK, well I'm not familiar with Actutate, so probably best in this forum to get the query working in SQL Query Analyser and then you can substitute parameters and other syntax specific to Actutate.

I tried using a string as the first parameter to DATAADD() in SQL and it worked find - so I've learnt something today!

I'm not sure which bit you aren't understanding. If its this type of syntax:


SELECT SUM(CASE WHEN SomeColumn BETWEEN 1 AND 2 THEN 1 ELSE 0 END) AS [OneToTwo],
       SUM(CASE WHEN SomeColumn BETWEEN 2 AND 3 THEN 1 ELSE 0 END) AS [TwoToThree]
FROM MyTable ...

then this technique is used to make a conditional tally based on the value of a column; typically used for Cross Tabs where you want Column 1 to be "Total sales for January", Column 2 to be "Total sales for February" and so on (works either for "Total Sales Value" or "Number of Sales")

Kristen

furrelkt
Starting Member

49 Posts

Posted - 2005-07-29 : 15:14:18

Kristen,
This sql works perfectly, i am sorry, maybe i am not explaining it very good. I am new to this and might not express my self correctly. so, here is goes. I am not having any problem with any of the parameters or Dateadd(). what the main question is when i have the ETC1,ETC2,ETC from the
SUM(case when :Monthly=1 and s.SLICE_DATE BETWEEN dateadd("m",0, :StartDate) AND dateadd("m",1, :StartDate)-1 and s.FIELD=1 then s.SLICE
else case when :Monthly=0 and s.SLICE_DATE BETWEEN dateadd("ww",0, :StartDate) AND dateadd("ww",1, :StartDate)-1 and s.FIELD=1 then s.SLICE end end) ETC1,

HOW do i add these ETC rows up in sql? like Madhivanan was saying, i think the way to go is a derived table but i am unfamiliar with it, i am trying to get it though.
thanks a lot for your help.
Keri

Kristen
Test

22859 Posts

Posted - 2005-07-29 : 15:25:20

OK, so the basics of a derived table are
[code]
SELECT *
FROM MyTable
[code]
can be replaced with
[code]
SELECT *
FROM
(
SELECT A, B, C
FROM MyTable
) X
[code]
you are then free to use "A", "B" and "C" in the outer SELECT etc.

So ... if you have a query that can calculate some stuff, e.g. ETC1,ETC2,ETC3, but you cannot easily further manipulate that data, then "wrapping" the query in another makes those "columns" available to the outer query. SO, for example:
[code]
SELECT ETC1 + ETC2 as [GrandTotal], C
FROM
(
SELECT A+A1+A2 as [ETC1],
B+B1+B2 as [ETC2],
C
FROM MyTable
) X
[code]
But I dunno if I've grasped the bit you are stuck with?

Kristen

furrelkt
Starting Member

49 Posts

Posted - 2005-08-01 : 11:57:56

Thanks Kristen,
I think that i am more confused with the naming of the inner query. I guess that is where i get stuck... more of where to stick it into the whole query... i guess. I am going to keep trying tho! :) Thanks so much for your help

Keri

Kristen
Test

22859 Posts

Posted - 2005-08-01 : 12:53:13

OK, lets stick some alias names and stuff in there and see if it helps :

We could do a SELECT in two steps:


CREATE TABLE W (...)  -- Make temporary table
INSERT INTO W         -- Populate it
(
    X, Y, Z
)
SELECT T1.A AS [X],
       T1.B AS [Y],
       T2.C AS [Z]
FROM MyTable1 T1
     JOIN MyTable2 T2
          ON T2.ID = T1.ID

and then we could use


SELECT W.X, W.Y, W.Z
FROM W
WHERE W.X > W.Y

or instead we could put a "nested select" where "FROM W" is, and thus not have to create the temporary table - SQL seemlessly makes the temporary table, and does it using less resources, so its quicker and there's no delete of the table afterwards and all that sort of stuff.

So we wind up with:


SELECT W.X, W.Y, W.Z
FROM
(
   SELECT T1.A AS [X],
          T1.B AS [Y],
          T2.C AS [Z]
   FROM MyTable1 T1
         JOIN MyTable2 T2
              ON T2.ID = T1.ID
) W
WHERE W.X > W.Y

In the "Inner" select the tables are aliased as T1 and T2. Within that nested select things can be referenced by those aliases. Note that I've given alias names to the selected columns of X, Y and Z, and that's what the outer select sees them as (and the table Alias will be "W"; you don't have to alias the columns, except to avoid ambiguity, but you DO have to Alias the "outcome" of the nested select ("W" in this example).

In the outer SELECT the table names T1 and T2, as well as the original column names A, B and C are not valid (basically, they are not "in scope").

Kristen

furrelkt
Starting Member

49 Posts

Posted - 2005-08-03 : 14:25:27

Hi Kristen,
Thanks for all your help, i am sure i am going to keep asking questions and studying the past topics to get better at sql. I am a beginner but i will keep trying. Thanks again, i really appreciate your help.

Keri

Kristen
Test

22859 Posts

Posted - 2005-08-03 : 16:09:05

No problem, have a good one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources