Help with a basic client and date query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with a basic client and date query

Author

Topic

ccstb
Starting Member

1 Post

Posted - 2011-08-19 : 16:31:48

Hi,

I'm really new to this so I'm having a problem with what is probably a very simple query. We have a food pantry that distributes items to households, and each household has 1 or more individual clients in it.

So we have table named Distribution that has DistribDate and HouseholdID. And we have a table named Clients that has each client's HouseholdID, FirstName, LastName and a HeadofHousehold field that can be checked yes or no to indicate if the client is the head of their household (YES) or just a dependent (NO).

So what I am trying to do is write a query that returns the FirstName and LastName of all clients who are HeadofHousehold AND who received a distribution between certain daterange, lets say the time period from 30 days ago to 90 days ago. And I don't need to list them twice if they came twice - it doesn't matter to us how many times they came during the time frame, only if they came at least once or not at all.

Thanks for any help with this,
Sharon

yogi86
Starting Member

13 Posts

Posted - 2011-08-19 : 16:47:56

Lol here goes nothing:

Select distinct FirstName, LastName
From Clients C
Inner Join Distribution D on C.HouseholdID = D.HouseholdID
Where HeadofHousehold = 'YES'
and distribdate between '05152011' and '07152011'

Without having further information this is the best I can do. I hope it works for you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-20 : 01:36:15

for making it dynamic for you period modify last condition as


distribdate < dateadd(dd,datediff(dd,0,getdate())-29,0) 
and distribdate >= dateadd(dd,datediff(dd,0,getdate())-90,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources