Complex Query Problem

1. Junior Member
Join Date
Dec 1969
Posts
26

## Complex Query Problem

Guys, this one is gettin on to my head. pls help.<BR>I have a table in access db named "main"<BR><BR>Design is like...<BR>ID Autonumber<BR>ProductSKU Number<BR>Billnum Number<BR>BillDate Date<BR>CustomerName Text<BR>Amt Currency<BR>PaidAmt Currency<BR>PaidDate Date<BR><BR>Now, on ASP page, I need to display a report table showing the sum of balance amounts (Amt - PaidAmt) according to productSKU numbers. The day calculation is (todaysdate - BillDate). The products can repeat, so the SKU can be same for multiple records. So, it will group according to SKU numbers.<BR><BR>Eg: If I have 3 bills with ProductSKU:1350, then for those three records, I need to calculate the balance amount (Amt - PaidAmt) for each record, and add it up for those three records. Then group it according to the days passed (todaysdate - BillDate), in seperate columns as shown below. <BR><BR>SkuNumber &#124 &#060;7 days &#124 7-15 days &#124 15-30 days &#124 &#060;30days<BR><BR> 1350 Tot:\$200 - Tot:\$150 - <BR><BR>Guys, Please help me build this ASP page! <BR>

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## But what database???

It DOES matter. Tough to answer without knowing that.<BR><BR>I&#039;ll give it to you for Access, assuming that if you are using SQL Server you can figure out how to convert IIF into CASE WHEN, etc., etc.<BR><BR>I&#039;m also going to assume that your BILLDATE field contains *only* dates with no time component to them.<BR><BR>SELECT ProductSKU,<BR> SUM( IIF( Date()-BillDate &#060; 7, Amt-PaidAmt, 0 ) ) AS LessThanSeven,<BR> SUM( IIF( Date()-BillDate BETWEEN 7 AND 15, Amt-PaidAmt, 0 ) AS SevenToFifteen,<BR> SUM( IIF( Date()-BillDate BETWEEN 15 AND 30, Amt-PaidAmt, 0 ) AS FifteenToThirty,<BR> SUM( IIF( Date()-BillDate &#062; 30, Amt-PaidAmt, 0 ) AS ThirtyPlus<BR>FROM main<BR>GROUP BY ProductSKU<BR>ORDER BY ProductSKU<BR><BR>

3. Junior Member
Join Date
Dec 1969
Posts
26

## RE: But what database???

Thank You Bill! Well, its an access database. But, Please tell me how should all these values be displayed on a web page in form of a table using ASP?

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Just dump them out...

&nbsp;<BR>&#060;TABLE Border=1 CellPadding=5&#062;<BR>&#060;TR&#062;<BR> &#060;TH&#062;SKU&#060;/TH&#062;<BR> &#060;TH&#062;under 7 days&#060;/TH&#062;<BR> &#060;TH&#062;7 to 14 days&#060;/TH&#062;<BR> &#060;TH&#062;15 to 30 days&#060;/TH&#062;<BR> &#060;TH&#062;over 30 days&#060;/TH&#062;<BR>&#060;/TR&#062;<BR>&#060;%<BR>SQL = "SELECT ProductSKU, " _<BR> & " SUM( IIF( Date()-BillDate &#060; 7, Amt-PaidAmt, 0 ) ) AS LessThanSeven, " _<BR> & " SUM( IIF( Date()-BillDate BETWEEN 7 AND 14, Amt-PaidAmt, 0 ) AS SevenToFourteen, "_<BR> & " SUM( IIF( Date()-BillDate BETWEEN 15 AND 30, Amt-PaidAmt, 0 ) AS FifteenToThirty, " _<BR> & " SUM( IIF( Date()-BillDate &#062; 30, Amt-PaidAmt, 0 ) AS ThirtyPlus " _ <BR> & " FROM main GROUP BY ProductSKU ORDER BY ProductSKU"<BR><BR>Set RS = yourAlreadyOpenConnection.Execute( SQL )<BR><BR>Do Until RS.EOF<BR>%&#062;<BR> &#060;TR&#062;<BR> &#060;TD&#062;&#060;%=RS("ProductSKU")%&#062;&#060 ;/TD&#062;<BR> &#060;TD&#062;&#060;%=RS("LessThanSeven")%&#062;&# 060;/TD&#062;<BR> &#060;TD&#062;&#060;%=RS("SevenToFourteen")%&#062; &#060;/TD&#062;<BR> &#060;TD&#062;&#060;%=RS("FifteenToThirty")%&#062; &#060;/TD&#062;<BR> &#060;TD&#062;&#060;%=RS("OverThirty")%&#062;&#060 ;/TD&#062;<BR> &#060;/TR&#062;<BR>&#060;%<BR> RS.MoveNext<BR>Loop<BR>RS.Close<BR>%&#062;<BR>&#06 0;/TABLE&#062;<BR><BR>*******************<BR><BR>Noth ing special. Simple loop through the records.<BR><BR>Please tell me this isn&#039;t homework?<BR><BR>Note that I changed 15 to 14 for the 7 to 14 range, else you&#039;d have duplicate values for day 15.<BR><BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## Sorry...

That last RS should read<BR> &#060;TD&#062;&#060;%=RS("ThirtyPlus")%&#062;&#060 ;/TD&#062;<BR>to match the name in the SQL code.<BR><BR><BR>

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## By the way...

We could also code that loop part of that simply as:<BR><BR>Do Until RS.EOF <BR> Response.Write "&#060;TR&#062;" & vbNewLine<BR> For f = 0 To RS.Fields.Count-1<BR> Response.Write "&#060;TD&#062;" & RS(f).Value & "&#060;/TD&#062;" & vbNewLine<BR> Next<BR> Response.Write "&#060;/TR&#062;" & vbNewLine<BR> RS.MoveNext <BR>Loop <BR>RS.Close <BR>%&#062; <BR>&#060;/TABLE&#062; <BR><BR>*************<BR><BR>Hope you see why.<BR>

7. Junior Member
Join Date
Dec 1969
Posts
26

## RE: By the way...

Hey Bill! Thank you for your prompt response. Well, I tried running the code, but it gives the following error while it executes the SQL query:<BR><BR>Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)<BR>[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression <BR><BR>It would be great if you could review the code and check the SQL query syntax. Thanks again for the support.

8. Senior Member
Join Date
Dec 1969
Posts
96,118

## I am missing some right parentheses...

...in that code. SOrry.<BR><BR>SELECT ProductSKU,<BR> SUM( IIF( Date()-BillDate &#060; 7, Amt-PaidAmt, 0 ) ) AS LessThanSeven,<BR> SUM( IIF( Date()-BillDate BETWEEN 7 AND 15, Amt-PaidAmt, 0 )[hl="yellow"]&nbsp;)&nbsp;[/hl] AS SevenToFifteen,<BR> SUM( IIF( Date()-BillDate BETWEEN 15 AND 30, Amt-PaidAmt, 0 )[hl="yellow"]&nbsp;)&nbsp;[/hl]AS FifteenToThirty,<BR> SUM( IIF( Date()-BillDate &#062; 30, Amt-PaidAmt, 0 )[hl="yellow"]&nbsp;)&nbsp;[/hl]AS ThirtyPlus<BR>FROM main<BR>GROUP BY ProductSKU<BR>ORDER BY ProductSKU<BR><BR>Funny how I got the first one right and messed up the other three. Hah. That&#039;s what copy/paste will do to you.<BR><BR>

9. Senior Member
Join Date
Dec 1969
Posts
96,118

## See below <eom>

.<BR>

10. Junior Member
Join Date
Dec 1969
Posts
26

## RE: I am missing some right parentheses...

Well, Mr.Bill. That&#039;s amazing response speed... I was just going to type that I did find out the missing parentheses and I found your reply out here. I am amazed!!! Thanks a zillion for helping me out. Can you please pass on your MSN ID, Yahoo ID or ICQ number. It will be great to be in touch with you.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•