Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Help with SQL Query


I am trying to summarize data based on a period range in related tables.  
Table one has a column that includes a date/time field.  Table two has an
array field (MTD_1 to MTD_12) that has values I want to summarize based on
the date field in table one.

Example... Chekdate is 03/15/2007 in table one... I want to summarize MTD_1
through MTD_3 as YTD value X.  I want to be able to report what amounts to
Year to Date wages based on the month/year codes in the check date.

Is this enough information for feedback?

Not entirely sure of the spec but perhaps:

select
    t1.EmpID
,    sum (case when datepart (mm, t1.Chekdate) = 1 then t1.Amount else 0
end) as YTD_1
,    sum (case when datepart (mm, t1.Chekdate) <= 2 then t1.Amount else 0
end) as YTD_2
,    sum (case when datepart (mm, t1.Chekdate) <= 3 then t1.Amount else 0
end) as YTD_3
-- ... and so on
,    sum (case when datepart (mm, t1.Chekdate) <= 12 then t1.Amount else 0
end) as YTD_12
from
    Table1 t1
group by
    t1.EmpID

This doesn't use table 2 but it could supply the data for table 2.

--
   Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON   Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"Dan Shepherd" <DanSheph@discussions.microsoft.com> wrote in message

news:58965F2B-F94F-4497-89E2-6DEAB05743F2@microsoft.com...
I am trying to summarize data based on a period range in related tables.
Table one has a column that includes a date/time field.  Table two has an
array field (MTD_1 to MTD_12) that has values I want to summarize based on
the date field in table one.

Example... Chekdate is 03/15/2007 in table one... I want to summarize MTD_1
through MTD_3 as YTD value X.  I want to be able to report what amounts to
Year to Date wages based on the month/year codes in the check date.

Is this enough information for feedback?

-----------------------------------------------Reply-----------------------------------------------

Tom:

Thanks... I modified somewhat to look like the following:

USE CAPT;
GO
DECLARE @year varchar(4);
SET @YEAR = (DATEPART(year, GETDATE()));

select
    x_upr30300.employid
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 1 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_1
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 2 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_2
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 3 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_3
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 4 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_4
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 5 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_5
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 6 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_6
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 7 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_7
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 8 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_8
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 9 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_9
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 10 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_10
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 11 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_11
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 12 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_12
from
    x_upr30300
group by
    x_upr30300.employid

Now the question is can I make this a dynamic view or stored proc so that I
only generate the data when necessary?

You could encapsulate as a UDF and feed it @year as a parm.  BTW, I'd
declare @YEAR to be int, not varchar (4).

--
   Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON   Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"Dan Shepherd" <DanSheph@discussions.microsoft.com> wrote in message

news:1147C5EB-911F-41E1-BDE1-BDCA8AF3EB5A@microsoft.com...
Tom:

Thanks... I modified somewhat to look like the following:

USE CAPT;
GO
DECLARE @year varchar(4);
SET @YEAR = (DATEPART(year, GETDATE()));

select
    x_upr30300.employid
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 1 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_1
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 2 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_2
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 3 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_3
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 4 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_4
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 5 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_5
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 6 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_6
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 7 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_7
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 8 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_8
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 9 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_9
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 10 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_10
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 11 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_11
,    sum (case when datepart (mm, x_upr30300.Chekdate) = 12 and datepart
(yyyy, x_upr30300.chekdate) = @YEAR then x_upr30300.uprtrxam else 0
end) as MTD_12
from
    x_upr30300
group by
    x_upr30300.employid

Now the question is can I make this a dynamic view or stored proc so that I
only generate the data when necessary?

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc