Complex SQL Statements

[Vogel515]

I am working with 2 tables:

Submissions: ID, submitdate, groupid, statusid (60,000)
Status: statusid, name (7)

Currently, I have a select statement which is using nested queries and outer joins to produce a grid table with a count of submissions per group, with a given statusid

ex table:
----------------Group1----------Group2
Active------------22----------------15
Terminated------15----------------15
Withdrawn-------12----------------15

This is fairly easy, because I know how many groups I have, so I have nested queries for each of the groups. Now my dilemma is that I want to break out the counts by year, but the number of years varies for each group...

The end goal is get the table to look like this:

-----------------G1FY1----------G1FY2------G2FY1------G2FY2------G2FY3
Active------------11---------------11---------5------------5-------------5
Terminated------2----------------13---------5-------------5------------5
Withdrawn-------1----------------11---------5-------------5------------5

Any ideas? My current select statement syntax is something like this:


select distinct

status.status_desc
g1.count
g2.count

from status

left outer join (
SELECT cs.status, count(g1.id) as count
FROM submissions
GROUP BY g1.status
) g1
on status.status = g2.status

left outer join (
SELECT cs.status, count(g2.id) as count
FROM submissions
GROUP BY g2.status
) g2
on status.status = g2.status

Any help is appreciated...

 

[GeordieLife]

SELECT DISTINCT Status_Desc, (SELECT COUNT(*) FROM Submissions WHERE Status = S.Status AND Blah = S.BlahBlah) Blah

FROM Status S

etc. It's how I'd do it. But your SQL is difficult to read without indenting.

 

[Vogel515]

Geordie, thanks for the quick response. I apologize regarding the formatting of the code, it is pretty difficult in this forum.

Ok so...

I am having no difficulty getting the counts per group or a group for a given fiscal year, the issue is that I am trying to produce a query that will get me counts for every fiscal year the group had submissions and not every group gets submissions every year. It'd be one thing if I knew that each group had submissions for 2000-2005, but some groups may not have any activity in a given fiscal year and I would not want to include that. Also, I don't want to have to update the query every fiscal year. I'd like the query to check against all fiscal years to present....

Does that make sense?

 

[techfuzz]

That second explanation doesn't make much sense, but I think you can accomplish what you want by using GeordieLife's query and a GROUP BY.

techfuzz

 

[JACKDRUID]

I think the query you are looking for is a crosstab query. its native to MS access and there are work around to create it in MS sql. google crosstab query should return what you need.

 

[GeordieLife]

Yeah, it's a CROSS JOIN in SQL. But it's not instinctive.

Table 1 = 0, 1, 2, 3
Table 2 = A, B, C, D
CountTable =
0, A, 100
0, A, 200
0, B, 1
0, D, 47

SELECT *
INTO #TempCJ
FROM Table1 CROSS JOIN Table2

End up with
0, A
1, A
2, A
3, A
0, B etc etc etc
in #TempCJ

Then you
SELECT DISTINCT ColA, ColB, ISNULL(SELECT SUM(ColQty) FROM CountTable WHERE ColA = T.ColA AND ColB = T.ColB), 0) Count
FROM #TempCJ T

Gives you end table of
0, A, 300
0, B, 1
0, C, 0
0, D, 47
etc

Then, how you display it is up to you. We'd use ASP/ASP.net to loop through the recordset and add a new TR when ColA changes, or new TD when ColB changes.

Hope that this makes a little sense.

 

[Vogel515]

Thanks again for the responses, sorry my explanation was not all that clear.

There is one other issue for the time being - I can not create temporary tables, otherwise I would have created a temp table for each year / group / state and then used outer joins to combine the data where I wanted and leave the blank ones null.

I'm having a very hard time getting away from a procedural mindset into more of a set based one.

 

[brandonbull]

my first guess would try to build the Year into Quarters inside a derived table then use case when statements to count the Quarters based on the group status

 

[Vogel515]

Hey Brandon - thanks for the response.

I actually figured it out and ended up doing just that. I do not have access to windows functions or anything so I created a number of scalar subqueries and outer joined them all.