HELP WITH CROSS-TAB QUERY

[mjjks]

I'm having hard time figuring out this cross-tab query. And it's Monday too.

If anyone can point me in the right direction, I'd greatly appreciate it. Data in a table is like this:

DEF_SEG_ID   DEF_SEG_NM   ENTRY_DTTTM
----------   ----------   -----------
  60          Segment 1    12/25/2006
  64          Segment 2    12/30/2006

I have a query that gets count of segments for a particular date range and time period. I want to modify query to get an output like this:

DEF_SEG_ID  DEF_SEG_NM  4AM_COUNT 7AM_COUNT
----------  ----------  --------- ---------   
  60        Segment 1     28        12
  64        Segment 2     35        43

Tried to use CASE, but can't make it simple enough and get ton of errors
Here's the query:

SELECT R.DEF_SEG_ID, D.DEF_SEG_NM, 
COUNT(DISTINCT LTRIM(STR(MONTH(R.ENTRY_DTTM))) + '/' + LTRIM(STR(DAY(R.ENTRY_DTTM))) 
+ '/' + LTRIM(STR(YEAR(R.ENTRY_DTTM)))) AS '4AM COUNT'
FROM RD_COND_HIST R
INNER JOIN RD_DEF_SEG D ON R.DEF_SEG_ID = D.DEF_SEG_ID
WHERE (DATEPART(HH,ENTRY_DTTM) >= 3 AND DATEPART(HH,ENTRY_DTTM) <= 5)
AND ENTRY_DTTM >= @_STARTDATE AND ENTRY_DTTM < @_ENDDATE
GROUP BY R.DEF_SEG_ID, D.DEF_SEG_NM
ORDER BY R.DEF_SEG_ID

Thank you

 

[bborissov]

SELECT R.DEF_SEG_ID,
       D.DEF_SEG_NM,
       SUM(CASE WHEN DATEPART(HH,ENTRY_DTTM) >= 3 AND
                     DATEPART(HH,ENTRY_DTTM) <= 5
                THEN 1
                ELSE 0 END) AS 4AM_COINT,
       SUM(CASE WHEN DATEPART(HH,ENTRY_DTTM) >= 6 AND
                     DATEPART(HH,ENTRY_DTTM) <= 8
                THEN 1
                ELSE 0 END) AS 7AM_COINT
FROM RD_COND_HIST R
INNER JOIN RD_DEF_SEG D ON R.DEF_SEG_ID = D.DEF_SEG_ID
WHERE ENTRY_DTTM >= @_STARTDATE AND ENTRY_DTTM < @_ENDDATE
GROUP BY R.DEF_SEG_ID, D.DEF_SEG_NM
ORDER BY R.DEF_SEG_ID

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[mjjks]

Thank you Borislav. It works nicely, and I modified to include more time periods, however I get different results.

For example, with my original 'Count' query I get 4AM_COUNT = 30.
With SUM/CASE like you suggested, I get 4AM_COUNT = 38.

 

[bborissov]

That is because you COUNT DISTINCT date. I am not sure what you want in that case, can you post some example data and desired result from it?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[mjjks]

Right. COUNT DISTINCT allowed to count only once if there were more than one record within time period and that's what I want.

In an example below, both highlighted records fall between 3 AM and 5 AM for the same date, but I want to count it only once. Is it possible with an approach you suggested? Thanks.

60	SEXTON MT PASS	2007-01-01 03:31:51.707
60	SEXTON MT PASS	2007-01-02 03:45:33.213
[highlight]60	SEXTON MT PASS	2007-01-03 03:39:30.840
60	SEXTON MT PASS	2007-01-03 03:57:32.577[/highlight]
60	SEXTON MT PASS	2007-01-04 03:34:55.660
60	SEXTON MT PASS	2007-01-04 04:04:31.207
60	SEXTON MT PASS	2007-01-04 05:03:44.770

 

[bborissov]

Sorry for late reply. I think you want to have 4 as count from the data you provide. 1 for 01, 1 - 02, 1 - 03 and 1 for 04. Right? If this is so, how many periods you want to have in that query?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[mjjks]

Thanks for getting back to me with it.
As far as time periods go, I have 5, between 3-5,6-8,9-11,14:30-16:30,19:30-21:30.

And right, that would yield 4 as duplicate dates that fall within the same time range wouldn't be counted.

If that isn't possible, I'm thinking to have 5 separate "distinct" selects, insert them into temp tables and then select from temp tables, joining on common key.

Thanks for your help.

 

[bborissov]

OK, this is almost the same, but w/o using temporary table:

SELECT DEF_SEG_ID,
       DEF_SEG_NM,
       MAX(CASE WHEN Period = 1 THEN Cnt ELSE 0 END) AS 4AM,
       MAX(CASE WHEN Period = 2 THEN Cnt ELSE 0 END) AS 7AM,
       MAX(CASE WHEN Period = 3 THEN Cnt ELSE 0 END) AS 10AM
--- more MAX() here
FROM (SELECT R.DEF_SEG_ID,
             D.DEF_SEG_NM,
             COUNT(DISTINCT
                   CONVERT(varchar(10),R.ENTRY_DTTM,112))
                   AS Cnt,
             1 AS Period 
             FROM RD_COND_HIST R
                  INNER JOIN RD_DEF_SEG D
                        ON R.DEF_SEG_ID = D.DEF_SEG_ID
             WHERE (DATEPART(HH,ENTRY_DTTM) >= 3 AND
                    DATEPART(HH,ENTRY_DTTM) <= 5)AND
                    ENTRY_DTTM >= @_STARTDATE    AND
                     ENTRY_DTTM < @_ENDDATE
             GROUP BY R.DEF_SEG_ID, D.DEF_SEG_NM
      UNION ALL
      SELECT R.DEF_SEG_ID,
             D.DEF_SEG_NM,
             COUNT(DISTINCT
                   CONVERT(varchar(10),R.ENTRY_DTTM,112))
                   AS Cnt,
             2 AS Period 
             FROM RD_COND_HIST R
                  INNER JOIN RD_DEF_SEG D
                        ON R.DEF_SEG_ID = D.DEF_SEG_ID
             WHERE (DATEPART(HH,ENTRY_DTTM) >= 6 AND
                    DATEPART(HH,ENTRY_DTTM) <= 8)AND
                    ENTRY_DTTM >= @_STARTDATE    AND
                     ENTRY_DTTM < @_ENDDATE
             GROUP BY R.DEF_SEG_ID, D.DEF_SEG_NM
      UNION ALL
      SELECT R.DEF_SEG_ID,
             D.DEF_SEG_NM,
             COUNT(DISTINCT
                   CONVERT(varchar(10),R.ENTRY_DTTM,112))
                   AS Cnt,
             3 AS Period 
             FROM RD_COND_HIST R
                  INNER JOIN RD_DEF_SEG D
                        ON R.DEF_SEG_ID = D.DEF_SEG_ID
             WHERE (DATEPART(HH,ENTRY_DTTM) >= 9 AND
                    DATEPART(HH,ENTRY_DTTM) <= 11)AND
                    ENTRY_DTTM >= @_STARTDATE    AND
                     ENTRY_DTTM < @_ENDDATE
             GROUP BY R.DEF_SEG_ID, D.DEF_SEG_NM
--- more unions here) Test
GROUP BY DEF_SEG_ID,
         DEF_SEG_NM

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[mjjks]

Well, that worked, kind of. Count is correct, but this time it ended up with extra DEF_SEG_ID rows included.
Basically, it included all segments.

It resulted in 141 rows, instead of 85.
Part I don't get is how those extra segment rows got included as each UNION filters by date and time.

 

[bborissov]

I don't know you data
You may have extra DEF_SEG_ID if you have more DEF_SEG_NM than you want.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[mjjks]

Anyhow, thanks for your time and suggestions, Borislav.