[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 738
  • 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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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