CODEWITHSUNDEEP
sqlsql-server
Green
Green

Reputation: 83

how to streamline a select query

Hi I need to streamline a select query that will yield an array of 108 results.

The current query looks like this:

SELECT (
    SELECT ISNULL(Sum(Dollars), 0)
    FROM TableX
    WHERE Column3 = 1
        AND eventdate BETWEEN @start
            AND @end
        AND datepart(mm, eventdate) = 1
    )

column 3 values: 1, 4, 3, 14, 17, 10, 9, 13, 6

so essentially... 1-1,1-2,1-3 to 1-12 and then4-1,4-2,4-3 to 4-12, etc.

because I'm new to this stuff, I just copy and pasted the code and adjusted the values but it's now a 25,000 character query which is way too long.

Any help or pointers in how to loop these would be much appreciated.

Thanks

CREATE VIEW TableX (UNIQUEID,FILEKEY,EVENTDATE,GROUP1,GROUP2,GROUP3,PDN,RATE,HOURS,DOLLARS) AS
SELECT 167278 AS UNIQUEID,1 AS FILEKEY,'2014-07-21 00:00:00.000' AS EVENTDATE,1 AS GROUP1,2 AS GROUP2,1 AS GROUP3,14 AS PDN,0 AS RATE,3 AS HOURS,0 AS DOLLARS
UNION ALL
SELECT 167277,1,'2014-07-24 00:00:00.000',1,2,1,1,5,1,5
UNION ALL
SELECT 167276,1,'2014-07-25 00:00:00.000',1,4,1,1,5,1,5
UNION ALL
SELECT 167269,1,'2014-07-07 00:00:00.000',1,2,1,1,5,8,40
UNION ALL
SELECT 167007,1,'2014-07-04 00:00:00.000',1,1,1,4,1,8,8
UNION ALL
SELECT 167006,1,'2014-07-06 00:00:00.000',1,1,1,1,1,1,1
UNION ALL
SELECT 166932,1,'2014-05-26 00:00:00.000',1,1,1,4,1,8,8
UNION ALL
SELECT 166904,1,'2013-12-25 00:00:00.000',1,1,1,4,18.25,8,146
UNION ALL
SELECT 166903,1,'2014-01-01 00:00:00.000',1,1,1,4,18.25,8,146
UNION ALL
SELECT 166902,1,'2013-11-28 00:00:00.000',1,1,1,4,18.25,8,146;

Upvotes: 0

Views: 648

Answers (1)

Dan Bracuk
Dan Bracuk

Reputation: 20804

This might meet your needs:

  select column3
       , datepart(mm, eventdate) monthNum
       , isnull(sum(dollars)) dollarSum
    from tablex
   where eventDate between @start and @end
group by column3, 
         datepart(mm, eventdate) monthNum

Having said that, selecting the month but not the year is not a good idea.

Upvotes: 1

Related Questions