Date aggregation and formatting

Hi everyone,
I could use some help on date aggregation and formatting; just starting out
with SQL programming.
My raw data looks like this:
04/17/2005 Location1 Discharged
04/17/2005 Location2 Discharged
04/17/2005 Location 3 Discharged
04/17/2005 Location 4 Discharged
04/17/2005 Location 1 Admitted
04/17/2005 Location 2 Registered
Etc. The point is the date is a start date and the row includes a location
and the status of that row. Underneath it all of course there is a key (not
displayed cause it's irrelevant) called VisitID. All columns are
varchars (I know, it's assinine) but I can't change that now; just need to
make do with it.
I would like to just get tallies per date and location for each status.
I'm only looking at 7 days prior to today.
Something like this:
4/13/05 Location1 Admitted n
Discharged n
Registered n
Location2 Admitted n
Discharged n
Registered n
Location3 Admitted n
Discharged n
Registered n
4/14/05 Location 1 Admitted n
Discharged n
Registered n
Or something similar (maybe better display even) which shows each date and
location only once but tallies each kind of status. Make sense?
Here's my clunky code thus far:
Select Convert(VarChar(10), Cast(Left(StartTime, 8) As DateTime), 101)
DateName, Disposition, LKLocation =
CASE
WHEN SUBSTRING (LKLocation, 5,4) = '0368' THEN 'Location1'
WHEN SUBSTRING (LKLocation, 5,4) = '0884' THEN Location2
WHEN SUBSTRING (LKLocation, 5,4) = '0346' THEN 'Location3'
Else 'Unknown'
END
from MyTable
Where (Left(StartTime, 8) >= convert(char(8), DateAdd(d, -7, getdate()), 112
)
and Left(StartTime, 8) <= convert(char(8), getdate(), 112))
Group By StartTime, LKLocation, Disposition
Having Disposition = 'Registered' or Disposition = 'Discharged' or
Disposition = 'Admitted'
Order by DateName
I tried using
Select Convert(VarChar(10), Cast(Left(StartTime, 8) As DateTime), 101)
DateName, Count (distinct Disposition)
But as you probably know, it is returning count of 1 for each row with three
records per date.
What am I missing?
ThnxJust use COUNT(*) instead of count(distinct disposition), and use the
columns in your select in your group by as well:
Select Convert(VarChar(10), Cast(Left(StartTime, 8) As DateTime), 101)
DateName, Disposition, LKLocation =
CASE
WHEN SUBSTRING (LKLocation, 5,4) = '0368' THEN 'Location1'
WHEN SUBSTRING (LKLocation, 5,4) = '0884' THEN Location2
WHEN SUBSTRING (LKLocation, 5,4) = '0346' THEN 'Location3'
Else 'Unknown'
END
, COUNT(*) AS amount
from MyTable
Where (Left(StartTime, 8) >= convert(char(8), DateAdd(d, -7, getdate()),
112)
and Left(StartTime, 8) <= convert(char(8), getdate(), 112))
AND Disposition IN 'Registered', 'Discharged', 'Admitted')
Group By Convert(VarChar(10), Cast(Left(StartTime, 8) As DateTime), 101),
CASE
WHEN SUBSTRING (LKLocation, 5,4) = '0368' THEN 'Location1'
WHEN SUBSTRING (LKLocation, 5,4) = '0884' THEN Location2
WHEN SUBSTRING (LKLocation, 5,4) = '0346' THEN 'Location3'
Else 'Unknown'
END
, Disposition
Order by DateName
Jacco Schalkwijk
SQL Server MVP
"hbcp_1" <hbcp_1@.discussions.microsoft.com> wrote in message
news:EB7D1B1F-7AFC-4D7F-B722-CF0A13A90E50@.microsoft.com...
> Hi everyone,
> I could use some help on date aggregation and formatting; just starting
> out
> with SQL programming.
> My raw data looks like this:
> 04/17/2005 Location1 Discharged
> 04/17/2005 Location2 Discharged
> 04/17/2005 Location 3 Discharged
> 04/17/2005 Location 4 Discharged
> 04/17/2005 Location 1 Admitted
> 04/17/2005 Location 2 Registered
> Etc. The point is the date is a start date and the row includes a
> location
> and the status of that row. Underneath it all of course there is a key
> (not
> displayed cause it's irrelevant) called VisitID. All columns are
> varchars (I know, it's assinine) but I can't change that now; just need to
> make do with it.
> I would like to just get tallies per date and location for each status.
> I'm only looking at 7 days prior to today.
> Something like this:
> 4/13/05 Location1 Admitted n
> Discharged n
> Registered n
> Location2 Admitted n
> Discharged n
> Registered n
> Location3 Admitted n
> Discharged n
> Registered n
> 4/14/05 Location 1 Admitted n
> Discharged n
> Registered n
>
> Or something similar (maybe better display even) which shows each date and
> location only once but tallies each kind of status. Make sense?
> Here's my clunky code thus far:
> Select Convert(VarChar(10), Cast(Left(StartTime, 8) As DateTime), 101)
> DateName, Disposition, LKLocation =
> CASE
> WHEN SUBSTRING (LKLocation, 5,4) = '0368' THEN 'Location1'
> WHEN SUBSTRING (LKLocation, 5,4) = '0884' THEN Location2
> WHEN SUBSTRING (LKLocation, 5,4) = '0346' THEN 'Location3'
> Else 'Unknown'
> END
> from MyTable
> Where (Left(StartTime, 8) >= convert(char(8), DateAdd(d, -7, getdate()),
> 112)
> and Left(StartTime, 8) <= convert(char(8), getdate(), 112))
> Group By StartTime, LKLocation, Disposition
> Having Disposition = 'Registered' or Disposition = 'Discharged' or
> Disposition = 'Admitted'
> Order by DateName
> I tried using
> Select Convert(VarChar(10), Cast(Left(StartTime, 8) As DateTime), 101)
> DateName, Count (distinct Disposition)
> But as you probably know, it is returning count of 1 for each row with
> three
> records per date.
> What am I missing?
> Thnx
>
>|||Cool, that's great. Thanks.
"Jacco Schalkwijk" wrote:

> Just use COUNT(*) instead of count(distinct disposition), and use the
> columns in your select in your group by as well:
> Select Convert(VarChar(10), Cast(Left(StartTime, 8) As DateTime), 101)
> DateName, Disposition, LKLocation =
> CASE
> WHEN SUBSTRING (LKLocation, 5,4) = '0368' THEN 'Location1'
> WHEN SUBSTRING (LKLocation, 5,4) = '0884' THEN Location2
> WHEN SUBSTRING (LKLocation, 5,4) = '0346' THEN 'Location3'
> Else 'Unknown'
> END
> , COUNT(*) AS amount
> from MyTable
> Where (Left(StartTime, 8) >= convert(char(8), DateAdd(d, -7, getdate()),
> 112)
> and Left(StartTime, 8) <= convert(char(8), getdate(), 112))
> AND Disposition IN 'Registered', 'Discharged', 'Admitted')
> Group By Convert(VarChar(10), Cast(Left(StartTime, 8) As DateTime), 101),
> CASE
> WHEN SUBSTRING (LKLocation, 5,4) = '0368' THEN 'Location1'
> WHEN SUBSTRING (LKLocation, 5,4) = '0884' THEN Location2
> WHEN SUBSTRING (LKLocation, 5,4) = '0346' THEN 'Location3'
> Else 'Unknown'
> END
> , Disposition
> Order by DateName
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "hbcp_1" <hbcp_1@.discussions.microsoft.com> wrote in message
> news:EB7D1B1F-7AFC-4D7F-B722-CF0A13A90E50@.microsoft.com...
>
>