Database

How to loop or something a temp distinct table to sum up the totals

6-Jan-14 1:41

How should the query behave if you have a row with B2 but none with B1? Should the row with B2 be included or not?

Wrong is evil and must be defeated.
- Jeff Ello[^]

jkirkerx3-Jan-14 10:38

Re: How to loop or something a temp distinct table to sum up the totals

3-Jan-14 10:38

I'm not very good at SQL, but I did write this for SQL Server 2012

I'm trying to write TSQL to get all the stuff sold on a date, distinct list of stuff based on part number, and just add up the total qty sold, and the subtotal based on price for each item.

I'm not sure if I should loop the @Temptable results using WHERE partnumber
I'm stuck here, not even sure what words to use to search for learning. I could use a pointer or suggested method to use here.

DECLARE <a href="/Members/Year">@Year</a>AS int;
DECLARE <a href="/Members/month">@Month</a>AS int;
DECLARE <a href="/Members/Day">@Day</a>AS int;
SET <a href="/Members/Year">@Year</a>= 2013;
SET <a href="/Members/month">@Month</a>= 8;
SET <a href="/Members/Day">@Day</a>= 29;

DECLARE @startDate AS DATE;
DECLARE @stopDate AS DATE;
SET @startDate = DATETIMEFROMPARTS(@Year, <a href="/Members/month">@Month</a> <a href="/Members/Day">@Day</a> 0, 0, 0, 0);
SET @stopDate = DATETIMEFROMPARTS(@Year, <a href="/Members/month">@Month</a> <a href="/Members/Day">@Day</a> 23, 59, 59, 999);

DECLARE <a href="/Members/temp">@Temp</a>TABLE(
    PartNumber VarChar(80),
    ThumbNail VarChar(250),
    Qty INT,
    Cost Decimal,
    Price Decimal       
)
INSERT INTO <a href="/Members/temp">@Temp</a>(PartNumber, ThumbNail, Qty, Cost, Price)
SELECT PartNumber, ThumbNail, Qty, Cost, Price From CompletedOrdersCart WHERE OrderDate > @startDate AND OrderDate < @stopDate

////////////////////////////////////////////////////
// Here I have a table returned with 8 items in it called <a href="/Members/temp">@Temp</a>
06-CRL10, /images/06-cr10.jpg, 1, 6.45, 10.50
06-CRL10, /images/06-cr10.jpg, 1, 6.45, 10.50
06-CRL10, /images/06-cr10.jpg, 1, 6.45, 10.50
06-CRL10, /images/06-cr10.jpg, 1, 6.45, 10.50
33-8114, /images/33114.jpg, 1, 3.45, 6.00
33-8114, /images/33114.jpg, 1, 3.45, 6.00
33-8114, /images/33114.jpg, 1, 3.45, 6.00
33-8114, /images/33114.jpg, 1, 3.45, 6.00
////////////////////////////////////////////////////

DECLARE @SummaryItems TABLE(
    PartNumber VarChar(80),
    ThumbNail VarChar(250),
    Qty INT,
    Cost Decimal,
    Price Decimal,
    SubTotal Decimal    
)
INSERT INTO @SummaryItems (PartNumber, ThumbNail, Cost, Price)
SELECT DISTINCT(PartNumber), ThumbNail, Cost, Price FROM <a href="/Members/temp">@Temp</a>

/////////////////////////////////////////////////////////
// Here I have a table returned with 2 distinct items, 
06-CRL10, /images/06-cr10.jpg, null, 6.45, 10.50, null
33-8114, /images/33114.jpg, null, 3.45, 6.00, null
/////////////////////////////////////////////////////////

// I want to add up the total qty, and create a subtotal of SUM(qty * price)
// from the table results above <a href="/Members/temp">@Temp</a>


// display the final results here
SELECT PartNumber, Thumbnail, Qty, Cost, Price, SubTotal FROM @SummaryItems;

Jörgen Andersson4-Jan-14 19:00

Re: How to loop or something a temp distinct table to sum up the totals

4-Jan-14 19:00

I'm not sure I understood you correctly but try this:

SQL

SELECT  PartNumber
       ,ThumbNail
       ,Qty
       ,Cost
       ,Price
       ,SUM(Qty * Price) subtotal
From    CompletedOrdersCart
WHERE   OrderDate BETWEEN @startDate AND @stopDate
GROUP BY PartNumber
        ,ThumbNail
        ,Qty
        ,Cost
        ,Price

And tell us if it gets you the result you want.

It looks to me like you're thinking procedurally. But SQL is a fourth generation programming language that takes care of that for you.
Refresh your Set theory instead.

Also remember, a temptable is always the last resort.
I also don't believe the Thumbnail belongs in a transaction table, I would keep it in a Parts table.

Wrong is evil and must be defeated.
- Jeff Ello[^]

jkirkerx4-Jan-14 19:38

Re: How to loop or something a temp distinct table to sum up the totals

4-Jan-14 19:38

Thanks Jorgen!

I tried using sum, but I was told via error that I needed a group by

I'll try it Sunday morning and let you know.

The thumbnail was just to show the picture of the item.

Normally, i would just do the math in asp.net, but I thought I would expand my knowledge of SQL.

Jörgen Andersson4-Jan-14 21:04

Re: How to loop or something a temp distinct table to sum up the totals

4-Jan-14 21:04

jkirkerx wrote:
The thumbnail was just to show the picture of the item.

Yes, but you're storing multiple instances of the same item which isn't very efficient when the database grows. It also gives you many records to update when you change the picture.
Put it in a different table with an ID and join[^] it in to the base query

jkirkerx wrote:
Normally, i would just do the math in asp.net, but I thought I would expand my knowledge of SQL.

In this case it's the right place to do it. SQL excels at storing, filtering and aggregation of data. So any math that comes with the aggregation belongs to the database (as a general but not specific rule, there are always exceptions).

Wrong is evil and must be defeated.
- Jeff Ello[^]

jkirkerx5-Jan-14 7:57

Re: How to loop or something a temp distinct table to sum up the totals

5-Jan-14 7:57

I didn't think of that on the thumbnails. That took me awhile to understand just now.

My skill levels at the time 7 years ago were weak, But the lesson is something I need to implement.

Let me try the Get Clause now, will let you know how it works out.

jkirkerx5-Jan-14 8:29

Re: How to loop or something a temp distinct table to sum up the totals

5-Jan-14 8:29

That's close to exactly what I wanted. I had no idea that SQL was so, I'm not sure what word to use here, but in so few lines, it was able to produce the result I wanted. So the Group By Clause replaced the Distinct I was using to just produce to 2 items I wanted.

Now I just need to add up the total Qty for each item. The SubTotal is correct, but the Qty is still just 1, should be like 7 for the first line.

Let me see if I can solve this part myself. I may have a question

Thanks!

DECLARE <a href="/Members/Year">@Year</a>AS int;
DECLARE <a href="/Members/month">@Month</a>AS int;
DECLARE <a href="/Members/Day">@Day</a>AS int;
SET <a href="/Members/Year">@Year</a>= 2013;
SET <a href="/Members/month">@Month</a>= 8;
SET <a href="/Members/Day">@Day</a>= 29;

DECLARE @startDate AS DATE;
DECLARE @stopDate AS DATE;
SET @startDate = DATETIMEFROMPARTS(@Year, <a href="/Members/month">@Month</a> <a href="/Members/Day">@Day</a> 0, 0, 0, 0);
SET @stopDate = DATETIMEFROMPARTS(@Year, <a href="/Members/month">@Month</a> <a href="/Members/Day">@Day</a> 23, 59, 59, 999);
DECLARE @SummaryItems TABLE(
	PartNumber VarChar(80),
    ThumbNail VarChar(250),
    SDescription VarChar(250),
    Qty INT,
    Cost Decimal,
    Price Decimal,
    SubTotal Decimal      
)
INSERT INTO @SummaryItems (PartNumber, ThumbNail, SDescription, Qty, Cost, Price, SubTotal)
SELECT PartNumber, ThumbNail, SDescription, Qty, Cost, Price, SUM(Qty * Price) From CompletedOrdersCart 
WHERE OrderDate > @startDate AND OrderDate < @stopDate
GROUP BY PartNumber, Thumbnail, SDescription, Qty, Cost, Price
SELECT PartNumber, Thumbnail, SDescription, Qty, Cost, Price, SubTotal FROM @SummaryItems;

This is what I got returned, the Qty just needs to be 7, so 7 * 44.00 = 308.00

PN,      Thumbnail,                   Description,               Qty, Cost,  Price, SubTotal
06-10,   /Images/thumbnail/06-10.jpg, 10 inch Continuious Blade, 1,   22.00, 44.00, 308.00

FYI:
I tried between, but I would of had to deduct a day, and add a day for start and stop.

Christian Graus5-Jan-14 16:02

Christian Graus

5-Jan-14 16:02

Where does the QTY come from, and why should it be 7 ? if it's the result of a different group by, you can get it with a CTE[^], and then join it back in to your query.

CTEs are useful when you need to return two calculated values based on different group by statements.

Christian Graus

My new article series is all about SQL !!!

Re: How to loop or something a temp distinct table to sum up the totals

Jörgen Andersson5-Jan-14 22:19

Re: How to loop or something a temp distinct table to sum up the totals

5-Jan-14 22:19

The partnumber and thumbnail has changed since the original post, I believe you can safely assume the database contents or some other circumstances has also changed.

Wrong is evil and must be defeated.
- Jeff Ello[^]

jkirkerx6-Jan-14 6:06

Re: How to loop or something a temp distinct table to sum up the totals

6-Jan-14 6:06

The Qty comes from like a shopping cart history table. So all cart items sold on the requested report date may contain 20 orders for the same item, so that day, 7 people ordered the same item of qty 1, so the qty would be 7. 7 was the result I was looking for to make sure the math was right.

My explanation below is crude, but it should represent the thought. I have this bad feeling about this, I think your going to rip me a new one here Poke tongue | ;-P

Shopping Cart History
date ------- PN  ---------- Qty    
08/29/2013 - 8AM  06-CR10   1
08/29/2013 - 9AM  06-CR10   1
08/29/2013 - 10AM 06-CR10   1
08/29/2013 - 11AM 12-ZZZ1   2
So the Qty would be 3

the result set would be

Items requiring fulfillment today
PN --------Qty
06-CR10    3
12-ZZZ1    2

modified 6-Jan-14 12:28pm.

Jörgen Andersson5-Jan-14 22:16

Re: How to loop or something a temp distinct table to sum up the totals

5-Jan-14 22:16

I believe you can remove also the last temptable:

SQL

DECLARE <a href="/Members/Year">@Year</a>AS int;
DECLARE <a href="/Members/month">@Month</a>AS int;
DECLARE <a href="/Members/Day">@Day</a>AS int;
SET <a href="/Members/Year">@Year</a>= 2013;
SET <a href="/Members/month">@Month</a>= 8;
SET <a href="/Members/Day">@Day</a>= 29;
 
DECLARE @startDate AS DATE;
DECLARE @stopDate AS DATE;
SET @startDate = DATETIMEFROMPARTS(@Year, <a href="/Members/month">@Month</a> <a href="/Members/Day">@Day</a> 0, 0, 0, 0);
SET @stopDate = DATETIMEFROMPARTS(@Year, <a href="/Members/month">@Month</a> <a href="/Members/Day">@Day</a> 23, 59, 59, 999);

SELECT PartNumber, ThumbNail, SDescription, sum(Qty) Qty, Cost, Price, SUM(Qty * Price) SubTotal From CompletedOrdersCart 
WHERE OrderDate > @startDate AND OrderDate < @stopDate
GROUP BY PartNumber, Thumbnail, SDescription, Cost, Price

And just like the thumbnail, the SDescription belongs in a different table, for the same reasons

jkirkerx wrote:
I tried between, but I would of had to deduct a day, and add a day for start and stop.

My bad,

SQL

WHERE OrderDate BETWEEN @StartDate AND @StopDate

is the same as

SQL

WHERE OrderDate >= @startDate AND OrderDate <= @stopDate

not

SQL

WHERE OrderDate > @startDate AND OrderDate < @stopDate

Wrong is evil and must be defeated.
- Jeff Ello[^]

jkirkerx6-Jan-14 6:15

Re: How to loop or something a temp distinct table to sum up the totals

6-Jan-14 6:15

I think I ended up doing that, and was amazed at how it worked.

Let me check,

This is the SQL in the asp.net function I used for testing. I kept the DateTimeFromParts because I stored the order date as a timestamp, and not a date, and had to be more precise with the hours minutes and seconds. I was originally BETWEEN, but I could not get a result, and it took me hours to figure it out, plus I had upgraded to SQL Server 2012 at the time. Then the timestamp dawned on me, and I spent another hour figuring out how to be more precise. I'd rather use between, and not use anything specific to 2012.

I SUM the Qty to get 7, and it worked on the test data set, not sure if I was just lucky to get that value, I need to find more data on different days to fully check it.

"DECLARE @startDate AS DATE; " & _
        "DECLARE @stopDate AS DATE; " & _
        "SET @startDate = DATETIMEFROMPARTS(@Year, @Month, @Day, 0, 0, 0, 0); " & _
        "SET @stopDate = DATETIMEFROMPARTS(@Year, @Month, @Day, 23, 59, 59, 999); " & _
        "DECLARE @SummaryItems TABLE(" & _
            "PartNumber VarChar(80), " & _
            "ThumbNail VarChar(250), " & _
            "SDescription VarChar(250), " & _
            "Qty INT, " & _
            "Cost Decimal, " & _
            "Price Decimal, " & _
            "SubTotal Decimal " & _
        ") " & _
        "INSERT INTO @SummaryItems (PartNumber, ThumbNail, SDescription, Qty, Cost, Price, SubTotal) " & _
        "SELECT PartNumber, ThumbNail, SDescription, SUM(Qty), Cost, Price, SUM(Qty * Price) From CompletedOrdersCart  " & _
        "WHERE OrderDate > @startDate AND OrderDate < @stopDate " & _
        "GROUP BY PartNumber, Thumbnail, SDescription, Qty, Cost, Price " & _
        "SELECT PartNumber, Thumbnail, SDescription, Qty, Cost, Price, SubTotal FROM @SummaryItems;"

modified 6-Jan-14 12:44pm.

Jörgen Andersson6-Jan-14 8:17

Re: How to loop or something a temp distinct table to sum up the totals

6-Jan-14 8:17

You need to remove Qty from the GROUP BY or you might get "unexpected" results.

And I believe you can also skip @SummaryItems. It doesn't serve any purpose.

Wrong is evil and must be defeated.
- Jeff Ello[^]

jkirkerx6-Jan-14 7:01

Re: How to loop or something a temp distinct table to sum up the totals

6-Jan-14 7:01

Click!

Oh, I see now what your saying. If the Thumbnail image is changed during the day, I can end up with 2 thumbnail image locations in a dataset. So which one would be right. I'm open to error with my SQL, and it will back fire on me soon, because it's history data.

So I should get rid of the thumbnail, and make a different request for that data. That's why I should store the image data in another table, and use a join. I never thought of that. That could take me a week to change in the entire program, but it must be done.

Slap me in the back of the head.

Jörgen Andersson6-Jan-14 8:36

Re: How to loop or something a temp distinct table to sum up the totals

6-Jan-14 8:36

It's called normalization, and we have all walked that way.

While the explanations for Normalization in Wikipedia[^] is pretty good.
I personally recommend one[^] made by Peso (a bit of a legend on a different forum), because he's explaining by example.

Wrong is evil and must be defeated.
- Jeff Ello[^]

jkirkerx6-Jan-14 9:02

6-Jan-14 9:02

Wow Thanks for the optimization tips!

I do need to expand my knowledge here and use the tools right. I'll play around with the TSQL in SQL Manager to see the various results and read the lessons.

The temp table was cool to know, but if it can be done without it, then better.

Not work correctly

Member 100045982-Jan-14 23:05

Member 10004598

2-Jan-14 23:05

Hi

I try to update a value inside a table (ExTable01) but updated value is not true.Please help.

When I apply update Saved wrong value is 891257536,00 (Column type real False ) but True value is 891257560 ( --> 97352 * 9155 = 891257560 )

Best regards

Shameel2-Jan-14 23:14

Shameel

2-Jan-14 23:14

Which database system? And how are you updating? If through an update statement, where is it?

Member 100045983-Jan-14 0:07

Member 10004598

3-Jan-14 0:07

MSSQL-TSQL
From Calucaltor TRUE RESULT : 164016 * 9155 = 1501566480

I check form profiler:

Update TestTable Set Con=@Con, Ust=@Ust, En =@En,Tem=@Tem, Where ID=@ID',N'@Con decimal(6,0),@Ust decimal(4,0),@En decimal(10,0),@Tem decimal(3,2), ID bigint',@Con=164016,@Ust=9155,@En=1501566480,@Tem=7.50,@ID=95452

Finally I check database to see value:

select str(En,25,5) as ToSeevalue, En, Con , Ust,* from TestTable where ID = 95452
-- REsult (WRONG OUTCOME):
ToSeevalue: 1501566464.00000 En: 1,501566E+09 Con: 164016 Ust: 9155

Shameel3-Jan-14 0:41

Shameel

3-Jan-14 0:41

I don't understand how ToSeevalue is updated, is it a calculated field?

Richard MacCutchan3-Jan-14 1:11

Richard MacCutchan

3-Jan-14 1:11

What do you mean by "wrong outcome"? Please explain exactly what, in the above, is considered wrong.

Veni, vidi, abiit domum

Simon_Whale3-Jan-14 1:53

Simon_Whale

3-Jan-14 1:53

Also what types are the following?

Con
Ust
En

Every day, thousands of innocent plants are killed by vegetarians.

Help end the violence EAT BACON

Member 100045983-Jan-14 3:57

Member 10004598

3-Jan-14 3:57

I found a solution .Column data type was real I changed it.Now it's decimal (38,4)