CODEWITHSUNDEEP
sqlsql-server-2008ms-access-2010
user2011628
user2011628

Reputation: 31

SUM by month in a view

Here is my statement in a SQL 2008 View.

SELECT TOP (100) PERCENT
    dbo.[Billing Detail].Chart
,   dbo.[Billing Detail].[Transaction Code]
,   SUM(dbo.[Billing Detail].Units) AS [Total Billed]
FROM
    dbo.[Billing Detail]
    INNER JOIN dbo.Patient
    ON dbo.[Billing Detail].Chart = dbo.Patient.[Chart Number]
WHERE
    (dbo.[Billing Detail].[Transaction Code] = 'H2016')
    AND (dbo.[Billing Detail].[Service Date 1] BETWEEN '2013-1-01 00:00:00.000'
                                               AND     '2013-1-31 00:00:000')
GROUP BY
    dbo.[Billing Detail].Chart
,   dbo.[Billing Detail].[Transaction Code]
ORDER BY
    dbo.[Billing Detail].Chart

I'm trying to make an IF statement where SUM of Units between 1-1-13 and 1-31-13 out put is JAN, all dates between 2-1-13 and 2-28-13 = FEB...and so on for the rest of the year. Then I am needing to export this information in to Access 2010 or Excell 2010.

Upvotes: 0

Views: 380

Answers (3)

sgeddes
sgeddes

Reputation: 62841

If I'm understanding your question correctly, you want to add columns for each of the months and then sum the units for those date ranges?

Assuming so, something like this might work (I've simplified your table structure, but this should get you going in the right direction):

SELECT ID, Month, SUM(Units)
FROM (
SELECT  ID, Units, 
    CASE 
       WHEN Month(dt) = 1 THEN 'Jan'
       WHEN Month(dt) = 2 THEN 'Feb'
       WHEN Month(dt) = 3 THEN 'Mar'
    END as Month
FROM         Sample
) t
GROUP BY ID, Month

And the SQL Fiddle.

--EDIT

Actually depending on your desired results, it might be best to include the Year in your WHERE criteria instead of the CASE statement. Really depends on your data...

SELECT ID, Month, SUM(Units)
FROM (
SELECT  ID, Units, 
    CASE 
       WHEN Month(dt) = 1 THEN 'Jan'
       WHEN Month(dt) = 2 THEN 'Feb'
       WHEN Month(dt) = 3 THEN 'Mar'
    END as Month
FROM         Sample
WHERE Year(dt) = 2013
) t
GROUP BY ID, Month

Upvotes: 0

anon
anon

Reputation:

Here is what I think your view should look like. The last two WHERE clauses assume you want to hard-code the view to handle the year 2013 only (and here is why you shouldn't use BETWEEN). If you want to be able to use the view for any year, just drop those two clauses, and apply them when you select from the view (just like you should be doing with the ORDER BY).

SELECT
  [month] = DATEADD(MONTH, DATEDIFF(MONTH, 0, bd.[Service Date 1]), 0),
  bd.Chart,
  bd.[Transaction Code], 
  SUM(bd.Units) AS [Total Billed]
FROM
  dbo.[Billing Detail] AS bd
INNER JOIN
  dbo.Patient AS p
  ON bd.Chart = p.[Chart Number]
WHERE
  bd.[Transaction Code] = 'H2016'
  AND bd.[Service Date 1] >= '20130101'
  AND bd.[Service Date 1] < '20140101'
GROUP BY 
  DATEADD(MONTH, DATEDIFF(MONTH, 0, bd.[Service Date 1]), 0);

Now you're on your own to getting this data into Excel or Access. If it is a one-time task, Excel will pretty much be able to import your results to grid or results to file output. If you need to automate it you can experiment with linked servers to either, but you may be better approaching this from a different angle: linking to the view in Access or having an external application (say, C# command line, PowerShell, etc.) read the results and then output it in the file format you need (.CSV is typically easiest, depending on the data).

Upvotes: 2

Ann L.
Ann L.

Reputation: 13965

Here's what I'd do:

  1. Write a query similar to the one you have, that covers the entire date range you want to cover, that groups units on Year and Month (or just Month if you're only covering one year).

  2. Export the data to Access or Excel. You can connect Access to your database tables through ODBC; you can do something similar in Excel, or else use SSMS to export your query results to a CSV file and import it into the tool of your choice. (SSIS would be even better, but if you don't have that, this can still be done.)

  3. Using Access or Excel, pivot the data on your Month field into the layout you desire.

While you can use the PIVOT keyword in SQL and get a column for each month, you'll have to do a lot of typing and you won't get any wizard-type guidance. In Access or Excel, there's an easy-to-use interface to set up your pivot tables and tweak such things as summary rows or subtotals.

Upvotes: 0

Related Questions