Group on particular date every year

Hi,
We have a load of personal records like (e.g.) these
Name, Function, Stardate,Enddate
Fred,assistent, div1, 1/1/2001, 31/12/2004
Richard,assistent, div2, 1/1/2001, 28/2/2003
Richard,director, div2, 1/3/2003, 31/7/2003
...
Now we need a matrix showing how many people works at date 1/7 of a
particular year and what function they had
2001 2002 2003 2004
ass dir ass dir ass dir ass dir
Div1 1 0 1 0 1 0 0 0
Div2 1 0 1 0 0 1 0 0
---
Total 2 0 2 0 1 1 0 0
The biggest problem I have is determining who is at 1/7 in what function and
group on that info.
Can somebody get me started? I'm trying to work with the IIF function but
with no good results.
Thak you very much
DanielDaniel,
If you aren't using SQL Server 2005, then this won't work. SQL Server
2005 introduced a function called PIVOT. Check out the SQL below to see
how it works.
SQL:
SELECT
div as Division, [2001-assistant], [2001-director], [2002-assistant],
[2002-director], [2003-assistant], [2003-director], [2004-assistant],
[2004-director]
FROM
(
SELECT div, '2001-' + [function] AS yearFunction, name FROM
divFunctions WHERE (startDate <= CONVERT(DATETIME, '2001-01-07
00:00:00', 102)) AND (endDate >= CONVERT(DATETIME, '2001-01-07
00:00:00', 102))
UNION
SELECT div, '2002-' + [function] AS yearFunction, name FROM
divFunctions WHERE (startDate <= CONVERT(DATETIME, '2002-01-07
00:00:00', 102)) AND (endDate >= CONVERT(DATETIME, '2002-01-07
00:00:00', 102))
UNION
SELECT div, '2003-' + [function] AS yearFunction, name FROM
divFunctions WHERE (startDate <= CONVERT(DATETIME, '2003-01-07
00:00:00', 102)) AND (endDate >= CONVERT(DATETIME, '2003-01-07
00:00:00', 102))
UNION
SELECT div, '2004-' + [function] AS yearFunction, name FROM
divFunctions WHERE (startDate <= CONVERT(DATETIME, '2004-01-07
00:00:00', 102)) AND (endDate >= CONVERT(DATETIME, '2004-01-07
00:00:00', 102))
) AS sourceTable
PIVOT
(count(name) FOR yearFunction IN
([2001-assistant],[2001-director],[2002-assistant],[2002-director],[2003-assistant],[2003-director],[2004-assistant],[2004-director]))
AS pivotTable
ORDER BY
div
Results: (formatting might get messed up because of word wrap)
Division 2001-assistant 2001-director 2002-assistant 2002-director
2003-assistant 2003-director 2004-assistant 2004-director
-- -- -- -- --
-- -- -- --
div1 1 0 1 0 1
0 1 0
div2 1 0 1 0 1
1 0 0
Hope your using 2005. Hope this helps.
-Josh
Daniel wrote:
> Hi,
> We have a load of personal records like (e.g.) these
> Name, Function, Stardate,Enddate
> Fred,assistent, div1, 1/1/2001, 31/12/2004
> Richard,assistent, div2, 1/1/2001, 28/2/2003
> Richard,director, div2, 1/3/2003, 31/7/2003
> ...
> Now we need a matrix showing how many people works at date 1/7 of a
> particular year and what function they had
> 2001 2002 2003 2004
> ass dir ass dir ass dir ass dir
> Div1 1 0 1 0 1 0 0 0
> Div2 1 0 1 0 0 1 0 0
> ---
> Total 2 0 2 0 1 1 0 0
> The biggest problem I have is determining who is at 1/7 in what function and
> group on that info.
> Can somebody get me started? I'm trying to work with the IIF function but
> with no good results.
> Thak you very much
> Daniel

Group by expiration date

Is there a way to get this down to one select statement and group it as such
in Reporting services?
select custnmbr,contnbr,enddate,Cast(total as Money) from svc00600
where enddate >=getdate() and enddate <=getdate()+30
order by enddate asc
select custnmbr,contnbr,enddate,Cast(total as Money) from svc00600
where enddate >=getdate()+30 and enddate <=getdate()+60
order by enddate asc
select custnmbr,contnbr,enddate,Cast(total as Money) from svc00600
where enddate >=getdate()+60 and enddate <=getdate()+120
order by enddate ascYou could try either of the options below. Then group on the new
psuedo-field DaysOut
select custnmbr,contnbr,enddate,Cast(total as Money), '30' as DaysOut
from svc00600
where enddate >=getdate() and enddate <=getdate()+30
UNION
select custnmbr,contnbr,enddate,Cast(total as Money), '60' as DaysOut
from svc00600
where enddate >=getdate()+30 and enddate <=getdate()+60
UNION
select custnmbr,contnbr,enddate,Cast(total as Money), '120' as DaysOut
from svc00600
where enddate >=getdate()+60 and enddate <=getdate()+120
order by enddate asc
SELECT custnmbr, contnbr, enddate, CAST(total AS MONEY),
'DAYSOUT' = CASE
WHEN enddate >= getdate() AND enddate <= DATEADD(DD,30,getdate())
THEN '30'
WHEN enddate >= DATEADD(DD,30,getdate()) AND enddate <=DATEADD(DD,60,getdate()) THEN '60'
WHEN enddate >= DATEADD(DD,60,getdate()) AND enddate <=DATEADD(DD,120,getdate()) THEN '120'
END
FROM svc00600
WHERE enddate >=getdate() AND enddate <= DATEADD(DD,120,getdate())
ORDER BY enddate ASC|||Awesome, thanks.
"Ches" wrote:
> You could try either of the options below. Then group on the new
> psuedo-field DaysOut
>
> select custnmbr,contnbr,enddate,Cast(total as Money), '30' as DaysOut
> from svc00600
> where enddate >=getdate() and enddate <=getdate()+30
> UNION
> select custnmbr,contnbr,enddate,Cast(total as Money), '60' as DaysOut
> from svc00600
> where enddate >=getdate()+30 and enddate <=getdate()+60
> UNION
> select custnmbr,contnbr,enddate,Cast(total as Money), '120' as DaysOut
> from svc00600
> where enddate >=getdate()+60 and enddate <=getdate()+120
> order by enddate asc
>
> SELECT custnmbr, contnbr, enddate, CAST(total AS MONEY),
> 'DAYSOUT' = CASE
> WHEN enddate >= getdate() AND enddate <= DATEADD(DD,30,getdate())
> THEN '30'
> WHEN enddate >= DATEADD(DD,30,getdate()) AND enddate <=> DATEADD(DD,60,getdate()) THEN '60'
> WHEN enddate >= DATEADD(DD,60,getdate()) AND enddate <=> DATEADD(DD,120,getdate()) THEN '120'
> END
> FROM svc00600
> WHERE enddate >=getdate() AND enddate <= DATEADD(DD,120,getdate())
> ORDER BY enddate ASC
>|||Be Careful--this query will possibly produce wrong results. If you do
<=getdate()+30 in the first group and >=getdate()+30 in the second, you will
potentially have a customer show up in both results. Your query should look
like:
select custnmbr,contnbr,enddate,Cast(total as Money), '30' as DaysOut
from svc00600
where enddate >=getdate() and enddate <=getdate()+30
UNION
select custnmbr,contnbr,enddate,Cast(total as Money), '60' as DaysOut
from svc00600
where enddate >getdate()+30 and enddate <=getdate()+60
UNION
select custnmbr,contnbr,enddate,Cast(total as Money), '120' as DaysOut
from svc00600
where enddate >getdate()+60 and enddate <=getdate()+120
order by enddate asc
Even still, this query will list account with less than 30, between 31 than
60 and between 61 and 120--not necessarily the accounts that are 30 days
overdue, 60 days over or 120 days over as I would assume you are interested
in.
Brian
"Jeff Metcalf" <JeffMetcalf@.discussions.microsoft.com> wrote in message
news:BBC3F268-B818-40FB-9D58-BDD8A2199F6E@.microsoft.com...
> Awesome, thanks.
> "Ches" wrote:
>> You could try either of the options below. Then group on the new
>> psuedo-field DaysOut
>>
>> select custnmbr,contnbr,enddate,Cast(total as Money), '30' as DaysOut
>> from svc00600
>> where enddate >=getdate() and enddate <=getdate()+30
>> UNION
>> select custnmbr,contnbr,enddate,Cast(total as Money), '60' as DaysOut
>> from svc00600
>> where enddate >=getdate()+30 and enddate <=getdate()+60
>> UNION
>> select custnmbr,contnbr,enddate,Cast(total as Money), '120' as DaysOut
>> from svc00600
>> where enddate >=getdate()+60 and enddate <=getdate()+120
>> order by enddate asc
>>
>> SELECT custnmbr, contnbr, enddate, CAST(total AS MONEY),
>> 'DAYSOUT' = CASE
>> WHEN enddate >= getdate() AND enddate <= DATEADD(DD,30,getdate())
>> THEN '30'
>> WHEN enddate >= DATEADD(DD,30,getdate()) AND enddate <=>> DATEADD(DD,60,getdate()) THEN '60'
>> WHEN enddate >= DATEADD(DD,60,getdate()) AND enddate <=>> DATEADD(DD,120,getdate()) THEN '120'
>> END
>> FROM svc00600
>> WHERE enddate >=getdate() AND enddate <= DATEADD(DD,120,getdate())
>> ORDER BY enddate ASC
>>|||Hey,
Querying GreatPlains data, huh?
You can avoid the union query with the following:
select custnmbr,contnbr,enddate datediff(dd, enddate, getdate()),
aging30 = case when datediff(dd, enddate, getdate())<=30 then Cast(total as
money) else 0 end ,
aging60 = case when datediff(dd, enddate, getdate())Between 31 and 60 then
cast(total as money) else 0 end
from SVC00600 order by enddate desc
and if you really wanted you could even sum and group reight in this query
Have you thought about using a matrix report and doing the grouping on the
report?
HS
"goodman" <goodman93@.hotmail.com> wrote in message
news:OcbuGbj3FHA.2144@.TK2MSFTNGP09.phx.gbl...
: Be Careful--this query will possibly produce wrong results. If you do
: <=getdate()+30 in the first group and >=getdate()+30 in the second, you
will
: potentially have a customer show up in both results. Your query should
look
: like:
:
: select custnmbr,contnbr,enddate,Cast(total as Money), '30' as DaysOut
: from svc00600
: where enddate >=getdate() and enddate <=getdate()+30
: UNION
: select custnmbr,contnbr,enddate,Cast(total as Money), '60' as DaysOut
: from svc00600
: where enddate >getdate()+30 and enddate <=getdate()+60
: UNION
: select custnmbr,contnbr,enddate,Cast(total as Money), '120' as DaysOut
: from svc00600
: where enddate >getdate()+60 and enddate <=getdate()+120
: order by enddate asc
:
: Even still, this query will list account with less than 30, between 31
than
: 60 and between 61 and 120--not necessarily the accounts that are 30 days
: overdue, 60 days over or 120 days over as I would assume you are
interested
: in.
:
: Brian
:
:
: "Jeff Metcalf" <JeffMetcalf@.discussions.microsoft.com> wrote in message
: news:BBC3F268-B818-40FB-9D58-BDD8A2199F6E@.microsoft.com...
: > Awesome, thanks.
: >
: > "Ches" wrote:
: >
: >> You could try either of the options below. Then group on the new
: >> psuedo-field DaysOut
: >>
: >>
: >> select custnmbr,contnbr,enddate,Cast(total as Money), '30' as DaysOut
: >> from svc00600
: >> where enddate >=getdate() and enddate <=getdate()+30
: >> UNION
: >> select custnmbr,contnbr,enddate,Cast(total as Money), '60' as DaysOut
: >> from svc00600
: >> where enddate >=getdate()+30 and enddate <=getdate()+60
: >> UNION
: >> select custnmbr,contnbr,enddate,Cast(total as Money), '120' as DaysOut
: >> from svc00600
: >> where enddate >=getdate()+60 and enddate <=getdate()+120
: >> order by enddate asc
: >>
: >>
: >>
: >> SELECT custnmbr, contnbr, enddate, CAST(total AS MONEY),
: >> 'DAYSOUT' = CASE
: >> WHEN enddate >= getdate() AND enddate <= DATEADD(DD,30,getdate())
: >> THEN '30'
: >> WHEN enddate >= DATEADD(DD,30,getdate()) AND enddate <=: >> DATEADD(DD,60,getdate()) THEN '60'
: >> WHEN enddate >= DATEADD(DD,60,getdate()) AND enddate <=: >> DATEADD(DD,120,getdate()) THEN '120'
: >> END
: >> FROM svc00600
: >> WHERE enddate >=getdate() AND enddate <= DATEADD(DD,120,getdate())
: >> ORDER BY enddate ASC
: >>
: >>
:
:

GROUP BY column-name problem (need expression)

Please advise on how to get the GROUP BY coded in an acceptable way:

DECLARE @.LO INT
DECLARE @.HI INT
DECLARE @.StartDate varchar(10)
DECLARE @.EndDate varchar(10)

SELECT @.StartDate = '01/01/2005'
SELECT @.EndDate = '06/30/2005'
SELECT @.LO= 250
SELECT @.HI= 333

SELECT
StateCD
, CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
Convert(varchar(4),@.HI)
, 'StartingDate' = @.StartDate
, 'ThruDate' = @.EndDate
, JumboAmount = SUM(JumboAmount)
, JumboMortgages = SUM(JumboMortgages)
, JumboFIXMortgages= SUM(JumboFIXMortgages)
, JumboFIXAmount = SUM(JumboFIXAmount)
, JumboARMMortgages = SUM(JumboARMMortgages)
, JumboARMAmount= SUM(JumboARMAmount)
FROM LoanDetails T INNER JOIN dbo.ZipCodesPreferred Z
ON T.StateCD = Z.FIPS_State AND T.CountyCD = Z.FIPS_County AND T.Zip =
Z.ZipCode
GROUP BY
StateCD
, CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
Convert(varchar(4),@.HI)
, 'StartingDate' = @.StartDate
, 'ThruDate' = @.EndDateYou don't need the non-column expressions in the GROUP BY list. Column
aliases aren't permitted either. Try:

...
GROUP BY statecd, countycd, zip, Z.cityname, Z.statecode, Z.countyname

--
David Portas
SQL Server MVP
--|||Here is how a SELECT works in SQL ... at least in theory. Real
products will optimize things, but the code has to produce the same
results.

a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The <table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query.

b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (i.e. reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.

c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items.

d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.

e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The
"AS" operator can also give names to expressions in the SELECT
list. These new names come into existence all at once, but after the
WHERE clause, GROUP BY clause and HAVING clause has been executed; you
cannot use them in the SELECT list or the WHERE clause for that reason.

If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).

f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.

g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the SELECT
clause list, and the sorting is done there. The ORDER BY clause cannot
have expression in it, or references to other columns because the
result set has been converted into a sequential file structure and that
is what is being sorted.

As you can see, things happen "all at once" in SQL, not "from left to
right" as they would in a sequential file/procedural language model. In
those languages, these two statements produce different results:
READ (a, b, c) FROM File_X;
READ (c, a, b) FROM File_X;

while these two statements return the same data:

SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;

Think about what a confused mess this statement is in the SQL model.

SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;

That is why such nonsense is illegal syntax.