?
Solved

SQL help to get each sum for each date in database

Posted on 2007-07-25
2
Medium Priority
?
196 Views
Last Modified: 2010-03-20
How can I display the query below by each unique date [ed door dt]. I'd like to get the counts below for each day.

SELECT     (SELECT     COUNT(*)
                       FROM          edstats
                       WHERE      [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                              [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) < 30) AS ct30,
                          (SELECT     COUNT(*)
                            FROM          edstats
                            WHERE      [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                                   [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN 30 AND 60) AS ct60,
                          (SELECT     COUNT(*)
                            FROM          edstats
                            WHERE      [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                                   [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN 60 AND 90) AS ct90,
                          (SELECT     COUNT(*)
                            FROM          edstats
                            WHERE      [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                                   [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN 90 AND 120) AS ct120,
                          (SELECT     COUNT(*)
                            FROM          edstats
                            WHERE      [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                                   [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) > 120) AS ct120plus
0
Comment
Question by:cseink
2 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 200 total points
ID: 19564868
SELECT     e.[ED DOOR DT],
(SELECT     COUNT(*)
                       FROM          edstats
                       WHERE      [ED DOOR DT] = e.[ED DOOR DT] AND [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                              [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) < 30) AS ct30,
                          (SELECT     COUNT(*)
                            FROM          edstats
                            WHERE     [ED DOOR DT] = e.[ED DOOR DT] AND  [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                                   [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN 30 AND 60) AS ct60,
                          (SELECT     COUNT(*)
                            FROM          edstats
                            WHERE     [ED DOOR DT] = e.[ED DOOR DT] AND  [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                                   [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN 60 AND 90) AS ct90,
                          (SELECT     COUNT(*)
                            FROM          edstats
                            WHERE    [ED DOOR DT] = e.[ED DOOR DT] AND   [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                                   [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN 90 AND 120) AS ct120,
                          (SELECT     COUNT(*)
                            FROM          edstats
                            WHERE    [ED DOOR DT] = e.[ED DOOR DT] AND   [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([ED DOOR DT], [ED DOOR TM]),
                                                   [dbo].ConvertToDateTime([TRANS TO FLR DT], [TRANS TO FLR TM])) > 120) AS ct120plus
FROM edstats e
0
 

Author Comment

by:cseink
ID: 19564886
Perfect. Thanks
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question