SQL Select within a select

Question

I'm creating a dataset that will be displayed in an SSRS report.

I have a query in a job that puts a count into a table [dbo].[CountMetersDue] on a rolling basis on the 1st of every month; the value changes throughout the month so need to take a snapshot at beginning.

I have the report set up which uses a custom expression to produce a cumulative trend graph. Basically takes one value, divides by another to work out a percentage. Therefore I have two queries that need combining... Took me ages to get my head round all this!

I just need help with the last bit.

    SELECT (SELECT [Count] 
        FROM   [MXPTransferDev].[dbo].[CountMetersDue] 
        WHERE  [MXPTransferDev].[dbo].[CountMetersDue].[DateTime] = 
               [MXPTransferDev].[dbo].[Readings].[dateRead]) AS [MetersDue], 
       COUNT(readingid)                                      AS [TotalReadings], 
       CONVERT(DATE, dateread)                               AS [dateRead] 
FROM   [MXPTransferDev].[dbo].[Readings] 
WHERE  ( [MXPTransferDev].[dbo].[Readings].[dateRead] BETWEEN 
                '01-may-11' AND '31-may-11' ) 
       AND ( webcontactid IS NOT NULL ) 
       AND ( meter = 1 ) 
GROUP  BY CONVERT(DATE, [MXPTransferDev].[dbo].[Readings].[dateRead]) 

CREATE TABLE [dbo].[CountMetersDue](
    [Count] [int] NULL,
    [DateTime] [datetime] NULL
) ON [USER]

GO

ALTER TABLE [dbo].[CountMetersDue] 
ADD  CONSTRAINT [DF_CountMetersDue_DateTime]  DEFAULT (getdate()) FOR [DateTime]
GO

CREATE TABLE [dbo].[Readings](
    [readingId] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [dateRead] [datetime] NOT NULL,
    [meter] [int] NOT NULL,
    [webcontactid] [bigint] NULL,

Readings

readingId   meter   reading dateRead            webcontactid
583089  4   3662    2011-05-25 15:00:33.040         479
583207  3   682     2011-05-25 15:00:33.027         479
583088  2   98064   2011-05-25 15:00:33.007         479

CountMetersDue

Count   DateTime
2793    2011-12-01 00:00:00.000
1057    2011-05-01 14:08:20.437
610     2011-03-01 00:00:00.000

By the way, adding two spaces to the end of a line results in a line break. That's a bit easier on the eyes than this monolithic block of text. — Mr47, May 27 '11 at 09:01
Can you post the full DDL for your tables? It will make it easier to help. — Tony, May 27 '11 at 11:24
I think your table design might need to change. You have a table `[CountMetersDue]` which has a field `[Count]`. Shouldn't the count of meters due be derived from a query and not read from a table? And why do you need to select the `TOP 1` from the table? How many other count rows are there in this table? As I said before, post the DDL for the tables as there may be another way to get the results you need. — Tony, May 27 '11 at 11:31
reposted latest version of query. I could match the column name of countmeters due to match the other date column and then would it be possible to join the tables? — jhowe, May 27 '11 at 11:34
@jeff - Without the DDL for the tables it's hard to know how they could be joined. You can trim the number of fields to only show the ones involved in this query. — Tony, May 27 '11 at 11:44
@jeff - The `CREATE TABLE ...` statements for the two tables involved in your query. That way others can create the tables and test their theories. DDL -> Data Definition Language (http://en.wikipedia.org/wiki/Data_Definition_Language) — Tony, May 27 '11 at 13:13

score 2 · Accepted Answer · edited Feb 22 '18 at 21:06

Second stab at answering your question (will probably need some clarification from yourself before the answer is correct):

/* DDL: 2 tables [CountMetersDue] & [Readings]
    [CountMetersDue]
        ([DateTime] datetime,
        [Count] int)

    [Readings]
        ([ReadingId] bigint,
        [dateRead] datetime,
        [webcontactid] bigint,
        [meter] int)

    [CountMetersDue] - contains 1 record on the first of every month, with count of the number of readings at that date
    [Readings] - contains all the individual readings

    ie: 
        [CountMetersDue]
        01-Jan-2011     1000
        01-Feb-2011     2357
        01-Mar-2011     3000

        [Readings]
        1   01-Jan-2011     11  1
        2   02-Jan-2011     12  1
        3   03-Jan-2011     13  1
        ...
*/

    SELECT
    CONVERT(DATE, [dbo].[Readings].[dateRead]) AS dateRead, 
    COUNT([dbo].[Readings].[readingId]) AS TotalReadings,
    [dbo].[CountMetersDue].[Count] AS MetersDue

FROM
    [CountMetersDue]             /* get all count meters due */
    left join [Readings]           /* get any corresponding Reading records  
                                       where the dateRead in the same month as
                                       the CountMetersDue */
        on DATEPART(year, Readings.dateRead) = DATEPART(year, [CountMetersDue].[DateTime]) /* reading in same year as CountMetersDue */
        and DATEPART(month, Readings.dateRead) = DATEPART(month, [CountMetersDue].[DateTime]) /* reading in same month as CountMetersDue */
        WHERE  ([MXPTransferDev].[dbo].[Readings].[dateRead]) BETWEEN 
               @StartDate AND @EndDate
       AND ( webcontactid IS NOT NULL ) 
       AND ( meter = 1 ) 
GROUP BY
    [dbo].[CountMetersDue].[Count],CONVERT(DATE, [dbo].[Readings].[dateRead])

Thanks for that Andrew I will check it... i've provided some sample data in my original query... — jhowe, May 27 '11 at 14:39
@jeff no worries, I've just put in a minor correction (bracket in wrong place). Thanks for providing sample data, can you also provide sample output? — Andrew Bickerton, May 27 '11 at 14:46
Andrew nearly there... been testing your query and editing some bits as because the dates were all different the group by wasn't working correctly so had to use a day convert... Will let you know how it goes on Tuesday and show you my final code! Thanks for your help! — jhowe, May 27 '11 at 15:18
@jeff no worries, when you've got the final code, please edit the answer to help others :-) — Andrew Bickerton, May 27 '11 at 15:52
Andrew quick question before I mark this as answer what was the reason for the left outer join? — jhowe, May 31 '11 at 08:52
@Jeff put in left outer join as I assumed you wanted to cater for if there are no readings in that month (ie: still return the month and CountMetersDue, but have NULL for count of readings) — Andrew Bickerton, May 31 '11 at 11:33

Mr47 · Answer 2 · 2011-05-27T10:52:51.657

1

This would be the query you are looking for then?
Subqueries, as they are called, can be included by enclosing them in parentheses '()'.

SELECT (SELECT [Count] FROM [xxxxx].[dbo].[CountMetersDue] AS tabA WHERE tabA.[datefield] = tabB.dateRead) AS [MetersDue], COUNT(readingId) AS [TotalReadings], CONVERT(DATE, dateRead) AS [dateRead]
FROM         [xxxxx] AS tabB
WHERE     (dateRead BETWEEN @StartDate AND @EndDate) AND (webcontactid IS NOT NULL) AND (meter = 1)
GROUP BY CONVERT(DATE, dateRead)

edited May 27 '11 at 10:52

answered May 27 '11 at 08:46

Mr47

2,655
1
19
25

sorry I didn't make it clear there's two different tables... the select top value is a static value that doesn't change I'm guessing I need to use a join, maybe a cross join? – jhowe May 27 '11 at 08:50
Does the suggested query not work then? I would think that my query is correct if the 'select top' is a static value... – Mr47 May 27 '11 at 08:51
If this helped you out, you could accept the answer. If it did not, please comment as to what errors you might be having, if you please :) – Mr47 May 27 '11 at 09:19
hi misinterpreted requirements... need historical entries in the select top table, so there will be 1 entry for feb, march, april etc. I need to bring back the count in the select top table to match the date in the main query... – jhowe May 27 '11 at 10:33
do i need to add my date parameters into the sub query or can I do some sort of cross join or something? I tried deleting the select top bit and order by hoping it would use the parameters in the where clause but guess not... – jhowe May 27 '11 at 10:34
Edited my query to match your request. – Mr47 May 27 '11 at 10:53

SQL Select within a select

2 Answers2