• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 746
  • Last Modified:

SQL Query

Can anyone provide me with suggestions for combining the two SQL Queries so i will get 5 Columns and the information will match for each day?

---Spots Encoded Daily
Select Count(I.InhouseID) As 'SPOTS Enc',
      DATEPART(yy, IMCC.EncodeDateTime) As 'YEAR',
      DATEPART(m, IMCC.EncodeDateTime) As 'MONTH',
      DATEPART(dd, IMCC.EncodeDateTime) As 'DAY'

from dbo._Inhouse I Join dbo._InhouseMCC IMCC
ON I.InHouseID = IMCC.InHouseID

Where IMCC.EncodeDateTime BETWEEN '08-05-2012' AND '08-16-2012'
AND (DATEDIFF(DAY,I.FirstAirDate,DATEADD(dd,1,IMCC.EncodeDateTime))= 0)  
Group By DATEPART(yy, IMCC.EncodeDateTime),
            DATEPART(m, IMCC.EncodeDateTime),
            DATEPART(dd, IMCC.EncodeDateTime)


--- Count for Spots Encoded After 3
(Select Count(I.InhouseID) As 'SPOTS Enc > 3'
      from dbo._Inhouse I Join dbo._InhouseMCC IMCC
ON I.InHouseID = IMCC.InHouseID
Where IMCC.EncodeDateTime BETWEEN '08-05-2012' AND '08-16-2012' And DATEPART(hh, IMCC.EncodeDateTime) Between 14 and 23
AND (DATEDIFF(DAY,I.FirstAirDate,DATEADD(dd,1,IMCC.EncodeDateTime))= 0)
Group By DATEPART(yy, IMCC.EncodeDateTime),
            DATEPART(m, IMCC.EncodeDateTime),
            DATEPART(dd, IMCC.EncodeDateTime))
0
SpotIT
Asked:
SpotIT
1 Solution
 
LIONKINGCommented:
Would something like this work?

Select Count(I.InhouseID) As 'SPOTS Enc',
      SUM(CASE WHEN DATEPART(hh, IMCC.EncodeDateTime) Between 14 and 23 THEN 1 ELSE 0 END) As 'SPOTS Enc > 3',
      DATEPART(yy, IMCC.EncodeDateTime) As 'YEAR',
      DATEPART(m, IMCC.EncodeDateTime) As 'MONTH',
      DATEPART(dd, IMCC.EncodeDateTime) As 'DAY'

from dbo._Inhouse I Join dbo._InhouseMCC IMCC
ON I.InHouseID = IMCC.InHouseID

Where IMCC.EncodeDateTime BETWEEN '08-05-2012' AND '08-16-2012'
AND (DATEDIFF(DAY,I.FirstAirDate,DATEADD(dd,1,IMCC.EncodeDateTime))= 0)  
Group By DATEPART(yy, IMCC.EncodeDateTime),
            DATEPART(m, IMCC.EncodeDateTime),
            DATEPART(dd, IMCC.EncodeDateTime)

This is just a wild guess, but looking at your table structure would help...
0
 
SpotITAuthor Commented:
Thanks for the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now