Help with SQL query

Hi everyone.

I'm stuck on an SQL query, and hope one of you can help me. Have been trying to solve it all day long without any success. Doesn't even sound that difficult...

I have a table that has four columns. A combination of the first three columns is a foreign key for 'products'. The last column is a foreign key for 'stores'. This table keeps track of which products are assigned to which stores. Example of data -

Id_Prod_Grupo Id_Prod_Tipo Id_Prod_Pres Id_Cliente
---- ---- ---- ----
0 0 1 100
0 0 2 11476
0 0 3 12939
0 0 4 960
0 0 4 12941
0 0 5 1
0 0 5 10
0 0 5 960
0 0 5 15033
0 0 6 1
0 0 6 10
0 0 7 1
0 0 7 15033
0 0 7 92606

In the application, the user selects multiple stores, and the application has to display which all products are common to them. For example, if the user selects store 1 and 10, then application has to pick up products (0,0,5) and (0,0,6). Simple enough right?

Unfortunately, I can't form the query. Can someone please help me with this? If you could just give me a query that works for stores 1 and 10, I'm sure I can modify it myself in the application to make the whole process dynamic.

Thanks in advance folks!Table definitions and some sample data (INSERTs) would be helpful.|||drop table #test
create table #test(f1 int,f2 int,f3 int,f4 int)
go
insert #test values(1,0,1,1)
insert #test values(1,1,1,2)
insert #test values(0,1,1,2)
insert #test values(1,1,1,3)
insert #test values(1,0,1,3)
insert #test values(0,1,1,1)
insert #test values(1,0,1,2)
go
select distinct t2.*
from #test t1
join #test t2 on t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f3=t1.f3 and t2.f4<>t1.f4
where t1.f4 in(1,2) and t2.f4 in(1,2)|||Originally posted by sbaru
Table definitions and some sample data (INSERTs) would be helpful.

Actually the table structure etc. are completely irrelevant. Forget the whole part about the foreign keys etc... The only table to be used here is the one I showed above..

Let me see if I can make it any more clear though.. One of the earliest solutions I tried was a query like this -

SELECT DISTINCT Id_Prod_Grupo, Id_Prod_Tipo, Id_Prod_Pres FROM CVR_PRODUCTOS_TIENDA
WHERE Id_Cliente = 1 or Id_Cliente = 10

However, this query simply returns all the products that belong to EITHER one of the stores. I want products which are _common_ to both these stores. Get it?|||Originally posted by snail
drop table #test
create table #test(f1 int,f2 int,f3 int,f4 int)
go
insert #test values(1,0,1,1)
insert #test values(1,1,1,2)
insert #test values(0,1,1,2)
insert #test values(1,1,1,3)
insert #test values(1,0,1,3)
insert #test values(0,1,1,1)
insert #test values(1,0,1,2)
go
select distinct t2.*
from #test t1
join #test t2 on t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f3=t1.f3 and t2.f4<>t1.f4
where t1.f4 in(1,2) and t2.f4 in(1,2)

Worked beautifully Snail! Thanks a lot!!|||You didn't like my first solution (http://www.dbforums.com/showthread.php?postid=3656027#post3656027)?|||Originally posted by Pat Phelan
You didn't like my first solution (http://www.dbforums.com/showthread.php?postid=3656027#post3656027)?

Nice try - it works better than mine.|||Originally posted by snail
drop table #test
create table #test(f1 int,f2 int,f3 int,f4 int)
go
insert #test values(1,0,1,1)
insert #test values(1,1,1,2)
insert #test values(0,1,1,2)
insert #test values(1,1,1,3)
insert #test values(1,0,1,3)
insert #test values(0,1,1,1)
insert #test values(1,0,1,2)
go
select distinct t2.*
from #test t1
join #test t2 on t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f3=t1.f3 and t2.f4<>t1.f4
where t1.f4 in(1,2) and t2.f4 in(1,2)

Damn.. found a problem. If I use this method to find common products for more than two stores, the queries returns even those products which are present in just two of the stores..|||Originally posted by anujjain
Damn.. found a problem. If I use this method to find common products for more than two stores, the queries returns even those products which are present in just two of the stores..

Use Pat Phelan solution - it works like a charm ...|||Pat Phelan, one small problem in the solution you gave me.. I need to select the fourth column (store) as well, and I can't seem to be able to modify your query to do it right.. I'm afraid I suck at SQL!

Help??

P.S. Thanks a lot for the help guys, you're the best :)|||Originally posted by anujjain
Pat Phelan, one small problem in the solution you gave me.. I need to select the fourth column (store) as well, and I can't seem to be able to modify your query to do it right.. I'm afraid I suck at SQL!

Help??

P.S. Thanks a lot for the help guys, you're the best :)

Try this is combination (I hope Pat Phelan will not be offended ;) )

select t1.* from #test t1
join (SELECT f1,f2,f3
FROM #test
WHERE f4 IN (1,2,3) -- store list goes here
GROUP BY f1, f2, f3
HAVING Count(DISTINCT f4) = 3 -- store count goes here
) as t2 on t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f3=t1.f3|||Off we go, into... Nevermind!SELECT *
FROM phrog AS a
WHERE 2 = (SELECT Count(DISTINCT Id_Cliente) -- store count goes here
FROM phrog AS z
WHERE Id_Cliente IN (1, 10) -- store list goes here
AND z.Id_Prod_Grupo = a.Id_Prod_Grupo
AND z.Id_Prod_Tipo = a.Id_Prod_Tipo
AND z.Id_Prod_Pres = a.Id_Prod_Pres)

Edited to fix two logic errors (oops)!

-PatP|||Originally posted by snail
Try this is combination (I hope Pat Phelan will not be offended ;) ) Nah, you've got to work REALLY hard to offend me. I'm pretty much rude, crude, lewd, and socially unacceptable in almost anybody's frame of reference.

-PatP|||Originally posted by Pat Phelan
Nah, you've got to work REALLY hard to offend me. I'm pretty much rude, crude, lewd, and socially unacceptable in almost anybody's frame of reference.

-PatP Nice to meet you ;)|||Pat Phelan / Snake,

Both your solutions don't work for the following set of data -

Id_Prod_Grupo Id_Prod_Tipo Id_Prod_Pres Id_Cliente
---- ---- ---- ----
0 0 5 1
0 0 5 10
0 0 5 960
0 0 5 15033
0 0 8 1
0 0 8 10
0 0 8 960
0 0 8 92606

The whole table is returned when running the query for 3 stores (1, 10 and 960), whereas row 4 and 8 shouldn't be in the results...|||Did you change the 2 count to a 3 count in my query?

-PatP|||Yes, here is the exact query I ran...

SELECT *
FROM CVR_PRODUCTOS_TIENDA AS a
WHERE 3 = (SELECT Count(DISTINCT Id_Cliente) -- store count goes here
FROM CVR_PRODUCTOS_TIENDA AS z
WHERE Id_Cliente IN (1, 10, 960) -- store list goes here
AND z.Id_Prod_Grupo = a.Id_Prod_Grupo
AND z.Id_Prod_Tipo = a.Id_Prod_Tipo
AND z.Id_Prod_Pres = a.Id_Prod_Pres)|||Uff-da! Bone-head alert. I missed an important part. I showed the products that existed in those three stores, without regard to where those products were stored (doh!). For a band-aid fix, you can use:SELECT *
FROM CVR_PRODUCTOS_TIENDA AS a
WHERE Id_Cliente IN (1, 10, 960)
AND 3 = (SELECT Count(DISTINCT Id_Cliente) -- store count goes here
FROM CVR_PRODUCTOS_TIENDA AS z
WHERE Id_Cliente IN (1, 10, 960) -- store list goes here
AND z.Id_Prod_Grupo = a.Id_Prod_Grupo
AND z.Id_Prod_Tipo = a.Id_Prod_Tipo
AND z.Id_Prod_Pres = a.Id_Prod_Pres)while I think about a more elegant fix. Sorry!

-PatP|||Pat Phelan,

You da man! :) Works perfectly. Thanks a lot!|||Just because I like simple solutions, could you also try:SELECT *
FROM CVR_PRODUCTOS_TIENDA AS a
WHERE Id_Cliente IN (SELECT Id_Cliente
FROM CVR_PRODUCTOS_TIENDA AS z
WHERE Id_Cliente IN (1, 10, 960) -- store list goes here
AND z.Id_Prod_Grupo = a.Id_Prod_Grupo
AND z.Id_Prod_Tipo = a.Id_Prod_Tipo
AND z.Id_Prod_Pres = a.Id_Prod_Pres
GROUP BY z.Id_Cliente
HAVING Count(DISTINCT z.Id_Cliente) = 3)) -- store count goes hereThis shouldn't change the execution plan, but it does simplify the query because you only need to include the store list once and store count once in this query.

-PatP|||Originally posted by Pat
Nah, you've got to work REALLY hard to offend me. I'm pretty much rude, crude, lewd, and socially unacceptable in almost anybody's frame of reference.
Sounds very familiar...Wait a minute, that's my last annual review you're quoting! Where did you get it?|||Originally posted by rdjabarov
Sounds very familiar...Wait a minute, that's my last annual review you're quoting! Where did you get it? You be amazed at the stuff we've got posted on the walls around here! ;)

-PatPsql

help with sql query

hello all,
I need some urgent help with a query.
basically I have a simple table (sql server 2005 database) with this
kind of data
user date
john 1 apr
mark 31 mar
paul 2 apr
john 30 mar
john 14 apr
paul 4 apr
I need to build a query that, for a given user, retrieves the user
with the latest date only (the date field is formatted in the table as
datetime), for example john 14 apr...or paul 4 apr....
I'm kind of stuck...any help is greatly appreciated!
thanks in advance!
zz
Here is one way:
;WITH RankedUsers
AS
(SELECT [user], [date],
ROW_NUMBER() OVER(
PARTITION BY [user]
ORDER BY [date] DESC) AS seq
FROM Foo)
SELECT [user], [date]
FROM RankedUsers
WHERE seq = 1;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Help With SQL Query

Hello,
With the following table how would I create a query that would return all
rows whos EndDate minus StartDate is more than 28 Days.
TableName: Customers
ID - Integer
CustomerID - VarChar
StartDate - Date
EndDate - Date
Table:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
2 Mike1 8/25/06 9/15/06
3 Dinah 8/23/06 9/1/06
4 James 7/11/06 8/30/06
The Query Should Return:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
4 James 7/11/06 8/30/06
Thanks,
Chuck
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>
|||Thank You
Chuck
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uHoF7yQ2GHA.3656@.TK2MSFTNGP04.phx.gbl...
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>

Help with SQL Query

I originally posted this in microsoft.public.sqlserver.datamining, which
apparently is a dead zone. My apologies for the redundancy:
I need help with a query, and unfortunately, my SQL skills aren't
particularly advanced. My sample data looks something like this:
Name City Date
Smith New York Jan. 23, 2004
Jones New York May 1, 2004
Brown New York Aug. 18, 2004
Johnson Chicago Feb. 23, 2004
Chrysler Chicago April 23, 2004
Ford Chicago Sept. 3, 2004
I'd like to run a query which will give me the latest or last entry
(date-wise) relative to city. The result would look something like this:
Name City Date
Brown New York Aug. 18, 2004
Ford Chicago Sept. 3, 2004
Is there an easy solution to this? Thanks for any and all input.
btw, is this the best ms newsgroup to post sql queries questions, or is
there a more appropriate one? Thanks again.
steve.
SELECT t1.Name, t1.City, t1.Date
FROM some_table t1
WHERE NOT EXISTS(SELECT NULL FROM some_table t2
WHERE t2.City = t1.City AND t2.Date > t1.Date)
The best newsgroup for query questions btw, is
microsoft.public.sqlserver.programming.
Jacco Schalkwijk
SQL Server MVP
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:eT%23zMA$yEHA.3028@.TK2MSFTNGP10.phx.gbl...
>I originally posted this in microsoft.public.sqlserver.datamining, which
> apparently is a dead zone. My apologies for the redundancy:
> I need help with a query, and unfortunately, my SQL skills aren't
> particularly advanced. My sample data looks something like this:
> Name City Date
> Smith New York Jan. 23, 2004
> Jones New York May 1, 2004
> Brown New York Aug. 18, 2004
> Johnson Chicago Feb. 23, 2004
> Chrysler Chicago April 23, 2004
> Ford Chicago Sept. 3, 2004
> I'd like to run a query which will give me the latest or last entry
> (date-wise) relative to city. The result would look something like this:
> Name City Date
> Brown New York Aug. 18, 2004
> Ford Chicago Sept. 3, 2004
> Is there an easy solution to this? Thanks for any and all input.
> btw, is this the best ms newsgroup to post sql queries questions, or is
> there a more appropriate one? Thanks again.
> steve.
>
|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:ePk1xD$yEHA.1300@.TK2MSFTNGP14.phx.gbl...
> SELECT t1.Name, t1.City, t1.Date
> FROM some_table t1
> WHERE NOT EXISTS(SELECT NULL FROM some_table t2
> WHERE t2.City = t1.City AND t2.Date > t1.Date)
> The best newsgroup for query questions btw, is
> microsoft.public.sqlserver.programming.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
Thanks for both.
steve.

Help with SQL Query

Hey all,
My abridged table structure is:
UNQ Number Student ID Date Attendance
1 1 1-feb-05 Y
2 2 2-feb-05 N
3 3 3-feb-05 Y
4 4 4-feb-05 C
5 1 .
6 2 .
7 3
8 4
9 1
10 2
11 3
12 4
13 1
14 2
15 3
16 4
and so on:
I need an output like (between two dates):
Student ID Present Absent Cancelled
1 10 4 3
These numbers are just examples.
I know I can write a query like:
select a.[student id],count(distinct a.[unique number]) from attendancetable
as a where a.[attendance]='Y' and a.[date]>='01-feb-05' and
a.[date]<='01-feb-05' group by a.[student id]
to get a result like:
Student ID Present
1 10
But, how can I get all the three information in one output?
Thank you.Try the following.
SELECT StudentID,
count(CASE Attendance WHEN 'Y' THEN 1 ELSE 0 END) AS 'Present',
count(CASE Attendance WHEN 'N' THEN 1 ELSE 0 END) AS 'Absent',
count(CASE Attendance WHEN C' THEN 1 ELSE 0 END) AS 'Cancelled'
FROM Attendance_Table
WHERE Date>= YourStartDate and Date <= YourEnddate
GROUP BY StudentID
GO
Gavin|||Thanks plenty!
A little modification:
SELECT distinct [Student ID],
count(CASE WHEN attendance='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN attendance='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE when Attendance='C' THEN 1 ELSE null END) AS 'Cancelled'
FROM StudentScheduleAttendanceDetails
WHERE Date>= '01-jan-03' and Date <= '01-feb-06' GROUP BY [Student ID] order
by [student id]
GO
did it. Thanks again!
"celtic_kiwi" <gavin.jolly@.gmail.com> wrote in message
news:1107658677.037863.312350@.f14g2000cwb.googlegroups.com...
> Try the following.
> SELECT StudentID,
> count(CASE Attendance WHEN 'Y' THEN 1 ELSE 0 END) AS 'Present',
> count(CASE Attendance WHEN 'N' THEN 1 ELSE 0 END) AS 'Absent',
> count(CASE Attendance WHEN C' THEN 1 ELSE 0 END) AS 'Cancelled'
> FROM Attendance_Table
> WHERE Date>= YourStartDate and Date <= YourEnddate
> GROUP BY StudentID
> GO
> --
> Gavin
>|||One other thing:
Is it possible to show Present / Total as a column ?
SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE when a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
count(CASE when a.[Attendance] = '' then 1 else null END) as 'Future',
count(CASE when a.[Attendance] like '%' then 1 else null END) as 'Total'
FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
WHERE Date>= '01-jan-01' and
Date <= '01-jan-08'
and a.[student id]<>0 and b.[student id]=a.[student id]
and a.[student id] like '%'
GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail] order by
a.[student id]
somthing like:
SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE when a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
count(CASE when a.[Attendance] = '' then 1 else null END) as 'Future',
count(CASE when a.[Attendance] like '%' then 1 else null END) as 'Total'
(Present / Total) * 100 as "Present Ratio"
FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
WHERE Date>= '01-jan-01' and
Date <= '01-jan-08'
and a.[student id]<>0 and b.[student id]=a.[student id]
and a.[student id] like '%'
GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail] order by
a.[student id]
So, I get something like
1011 A B a@.b.com 10 20 30 5 65 15.3
Can you please tell me if this can be done?
Thank you
Vince
"Vince" <nmvkPLEASERMVTHIS@.vsnl.net> wrote in message
news:uC63Lr$CFHA.2620@.tk2msftngp13.phx.gbl...
> Thanks plenty!
> A little modification:
> SELECT distinct [Student ID],
> count(CASE WHEN attendance='Y' THEN 1 ELSE null END) AS 'Present',
> count(CASE WHEN attendance='N' THEN 1 ELSE null END) AS 'Absent',
> count(CASE when Attendance='C' THEN 1 ELSE null END) AS 'Cancelled'
> FROM StudentScheduleAttendanceDetails
> WHERE Date>= '01-jan-03' and Date <= '01-feb-06' GROUP BY [Student ID]
order
> by [student id]
> GO
> did it. Thanks again!
> "celtic_kiwi" <gavin.jolly@.gmail.com> wrote in message
> news:1107658677.037863.312350@.f14g2000cwb.googlegroups.com...
>|||I think I have to only use a stored procedure, declare the variables, and
select the variable...
"Vince" <nmvkPLEASERMVTHIS@.vsnl.net> wrote in message
news:umGSdBADFHA.2600@.TK2MSFTNGP09.phx.gbl...
> One other thing:
> Is it possible to show Present / Total as a column ?
>
> SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
> count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
> count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
> count(CASE when a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
> count(CASE when a.[Attendance] = '' then 1 else null END) as 'Future',
> count(CASE when a.[Attendance] like '%' then 1 else null END) as 'Total'
> FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
> WHERE Date>= '01-jan-01' and
> Date <= '01-jan-08'
> and a.[student id]<>0 and b.[student id]=a.[student id]
> and a.[student id] like '%'
> GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail] order by
> a.[student id]
> somthing like:
> SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
> count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
> count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
> count(CASE when a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
> count(CASE when a.[Attendance] = '' then 1 else null END) as 'Future',
> count(CASE when a.[Attendance] like '%' then 1 else null END) as 'Total'
> (Present / Total) * 100 as "Present Ratio"
>
> FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
> WHERE Date>= '01-jan-01' and
> Date <= '01-jan-08'
> and a.[student id]<>0 and b.[student id]=a.[student id]
> and a.[student id] like '%'
> GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail] order by
> a.[student id]
> So, I get something like
> 1011 A B a@.b.com 10 20 30 5 65 15.3
> Can you please tell me if this can be done?
> Thank you
> Vince
>
> "Vince" <nmvkPLEASERMVTHIS@.vsnl.net> wrote in message
> news:uC63Lr$CFHA.2620@.tk2msftngp13.phx.gbl...
> order
>|||Glad I could help. I am new to this group and have a few questions open
so feel I should at least answer what I can.
Sorry, I should have left the function as SUM. The following will work
as well.
SELECT distinct [Student ID],
sum(CASE WHEN attendance='Y' THEN 1 ELSE 0 END) AS 'Present',
sum(CASE WHEN attendance='N' THEN 1 ELSE 0 END) AS 'Absent',
sum(CASE when Attendance='C' THEN 1 ELSE 0 END) AS 'Cancelled'
FROM StudentScheduleAttendanceDetails
WHERE Date>= '01-jan-03' and Date <= '01-feb-06' GROUP BY [Student ID]
order
by [student id]
GO|||Just guessing but this should work. But I have not tried doing
calculations on groups.
SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE WHEN a.[Attendance]='C' THEN 1 ELSE null END) AS
'Cancelled',
count(CASE WHEN a.[Attendance] = '' THEN 1 ELSE null END) as 'Future',
count(a.[Attendance]) as 'Total'),
count(CASE a.[attendance]
WHEN 'Y' THEN 1
WHEN 'N' THEN 1
WHEN 'C' THEN 1
ELSE null END) / count(a.[Attendance]) as 'Total')*100 as 'Present
Ratio'
FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
WHERE Date>= '01-jan-01' and
Date <= '01-jan-08' and
a.[student id]<>0 and
b.[student id]=a.[student id]
/* and a.[student id] like '%' NOT SURE WHAT THIS LINE IS FOR' */
GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail]
ORDER BY a.[student id]|||Thanks Gavin! Really appreciate your help.
"celtic_kiwi" <gavin.jolly@.gmail.com> wrote in message
news:1107664607.420207.136340@.g14g2000cwa.googlegroups.com...
> Glad I could help. I am new to this group and have a few questions open
> so feel I should at least answer what I can.
> Sorry, I should have left the function as SUM. The following will work
> as well.
> SELECT distinct [Student ID],
> sum(CASE WHEN attendance='Y' THEN 1 ELSE 0 END) AS 'Present',
> sum(CASE WHEN attendance='N' THEN 1 ELSE 0 END) AS 'Absent',
> sum(CASE when Attendance='C' THEN 1 ELSE 0 END) AS 'Cancelled'
> FROM StudentScheduleAttendanceDetails
> WHERE Date>= '01-jan-03' and Date <= '01-feb-06' GROUP BY [Student ID]
> order
> by [student id]
> GO
>|||Again, thank you! It worked, I just had to cast as float...That [student id]
like '%' was to be like '3%' (all student ids beginning with 3)..
SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE WHEN a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
count(CASE WHEN a.[Attendance] = '' THEN 1 ELSE null END) as 'Future',
count(a.[Attendance]) as 'Total',
cast(cast(count(CASE when a.[attendance]='Y' THEN 1 else null end)as float)
/
count( a.[attendance])* 100 as decimal(4,0))
as 'Present Ratio (%)'
FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
WHERE Date>= '01-jan-01' and
Date <= '01-jan-08' and
a.[student id]<>0 and
b.[student id]=a.[student id]
GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail]
ORDER BY a.[student id]
Again, thanks for your help.
"celtic_kiwi" <gavin.jolly@.gmail.com> wrote in message
news:1107666089.497943.221340@.g14g2000cwa.googlegroups.com...
> Just guessing but this should work. But I have not tried doing
> calculations on groups.
> SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
> count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
> count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
> count(CASE WHEN a.[Attendance]='C' THEN 1 ELSE null END) AS
> 'Cancelled',
> count(CASE WHEN a.[Attendance] = '' THEN 1 ELSE null END) as 'Future',
> count(a.[Attendance]) as 'Total'),
> count(CASE a.[attendance]
> WHEN 'Y' THEN 1
> WHEN 'N' THEN 1
> WHEN 'C' THEN 1
> ELSE null END) / count(a.[Attendance]) as 'Total')*100 as 'Present
> Ratio'
> FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
> WHERE Date>= '01-jan-01' and
> Date <= '01-jan-08' and
> a.[student id]<>0 and
> b.[student id]=a.[student id]
> /* and a.[student id] like '%' NOT SURE WHAT THIS LINE IS FOR' */
> GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail]
> ORDER BY a.[student id]
>

Help with SQL Query

I originally posted this in microsoft.public.sqlserver.datamining, which
apparently is a dead zone. My apologies for the redundancy:
I need help with a query, and unfortunately, my SQL skills aren't
particularly advanced. My sample data looks something like this:
Name City Date
Smith New York Jan. 23, 2004
Jones New York May 1, 2004
Brown New York Aug. 18, 2004
Johnson Chicago Feb. 23, 2004
Chrysler Chicago April 23, 2004
Ford Chicago Sept. 3, 2004
I'd like to run a query which will give me the latest or last entry
(date-wise) relative to city. The result would look something like this:
Name City Date
Brown New York Aug. 18, 2004
Ford Chicago Sept. 3, 2004
Is there an easy solution to this? Thanks for any and all input.
btw, is this the best ms newsgroup to post sql queries questions, or is
there a more appropriate one? Thanks again.
steve.SELECT t1.Name, t1.City, t1.Date
FROM some_table t1
WHERE NOT EXISTS(SELECT NULL FROM some_table t2
WHERE t2.City = t1.City AND t2.Date > t1.Date)
The best newsgroup for query questions btw, is
microsoft.public.sqlserver.programming.
Jacco Schalkwijk
SQL Server MVP
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:eT%23zMA$yEHA.3028@.TK2MSFTNGP10.phx.gbl...
>I originally posted this in microsoft.public.sqlserver.datamining, which
> apparently is a dead zone. My apologies for the redundancy:
> I need help with a query, and unfortunately, my SQL skills aren't
> particularly advanced. My sample data looks something like this:
> Name City Date
> Smith New York Jan. 23, 2004
> Jones New York May 1, 2004
> Brown New York Aug. 18, 2004
> Johnson Chicago Feb. 23, 2004
> Chrysler Chicago April 23, 2004
> Ford Chicago Sept. 3, 2004
> I'd like to run a query which will give me the latest or last entry
> (date-wise) relative to city. The result would look something like this:
> Name City Date
> Brown New York Aug. 18, 2004
> Ford Chicago Sept. 3, 2004
> Is there an easy solution to this? Thanks for any and all input.
> btw, is this the best ms newsgroup to post sql queries questions, or is
> there a more appropriate one? Thanks again.
> steve.
>|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ePk1xD$yEHA.1300@.TK2MSFTNGP14.phx.gbl...
> SELECT t1.Name, t1.City, t1.Date
> FROM some_table t1
> WHERE NOT EXISTS(SELECT NULL FROM some_table t2
> WHERE t2.City = t1.City AND t2.Date > t1.Date)
> The best newsgroup for query questions btw, is
> microsoft.public.sqlserver.programming.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
Thanks for both.
steve.sql

Help With SQL Query

Hello,
With the following table how would I create a query that would return all
rows whos EndDate minus StartDate is more than 28 Days.
TableName: Customers
ID - Integer
CustomerID - VarChar
StartDate - Date
EndDate - Date
Table:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
2 Mike1 8/25/06 9/15/06
3 Dinah 8/23/06 9/1/06
4 James 7/11/06 8/30/06
The Query Should Return:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
4 James 7/11/06 8/30/06
Thanks,
ChuckSELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message news:O6E$smQ2GHA.5048@.T
K2MSFTNGP05.phx.gbl...
> Hello,
>
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
>
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
>
> Table:
>
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
>
> The Query Should Return:
>
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
>
> Thanks,
>
> Chuck
>
>|||Thank You
Chuck
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uHoF7yQ2GHA.3656@.TK2MSFTNGP04.phx.gbl...
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>