Re: query help

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Anith Sen (anith_at_bizdatasolutions.com)
Date: 07/19/04 

Date: Mon, 19 Jul 2004 10:55:33 -0500

Alex,

In the future, please include all relevant information in a simplified
format so that others can help you better. Based on your narrative and
sample data, I am assuming you have the data specific to Donations, Prayer
Requests, Gifts in three tables. Since you haven't given much specifics
about the key columns ( not the identity values, but the column or set of
columns which uniquely identify a donation, request or gift ) , unique
constraints and references, I am assuming you need the aggregate values for
each of these for all the dates within a given period. Using a calendar
table ( check out the archives on how you can come up with one easily ) you
can do something along the lines of :

SELECT dt,
       ( SELECT COUNT( * ) FROM Donations d1
          WHERE d1.dtcol = c1.dt AND d1.usr = 'joe' ),
       ( SELECT COUNT( * ) FROM DonorNeeds d1
          WHERE d1.dtcol = c1.dt AND d1.usr = 'joe' ),
       ( SELECT COUNT( * ) FROM DonorGifts d1
          WHERE d1.dtcol = c1.dt AND d1.usr = 'joe' ),
  FROM Calendar c1
 WHERE c1.dt BETWEEN '20040101' AND '20040131'

You can simplify them using CASE expressions if the referencing columns
among the three tables as well. 

-- 
Anith

Relevant Pages

  • Re: query help
    ... > Requests, Gifts in three tables. ... Using a calendar ... > You can simplify them using CASE expressions if the referencing columns ...
    (microsoft.public.sqlserver.programming)
  • Re: Resettin Running Total
    ... >OK I managed to simplify a bit my query to this one below: ... >Here is a sample data that could be played with: ... the whole length table name plus a meaningless integer - but that's up to ...
    (microsoft.public.sqlserver.programming)
  • Re: Combining 2 tables with date ranges
    ... Your sample data is a mess, but the usual way is to build a calendar ... predicates, something like: ... FROM Calendar AS C, T1, T2 ...
    (comp.databases.ms-sqlserver)
  • Re: sum multiple price between 2 dates
    ... at this point it is difficult to tell what is wrong unless sample data and expected results are provided. ... CREATE TABLE Calendar (dt DATETIME PRIMARY KEY); ... DECLARE @start_date DATETIME; ...
    (microsoft.public.sqlserver.programming)
  • Re: QUERY/PROCEDURE HELP
    ... can you post your sample data for these tables as ... Make sure you simplify them only to include the data for ... only the relevant columns. ... avoid misinterpretations. ...
    (microsoft.public.sqlserver.programming)