sql query

Question

Post reply

sql query

ih-815117

SSC Enthusiast

Points: 107
More actions
July 21, 2008 at 3:08 am

#381802

Hi,
Can anyone help me to construct a sql query? I have a query that calculates quantity of hours left on the card:
SELECT
cd.Cuts as [total hours],
ct.CutId,
ct.ratio,
ct.cuts,
(cd.cuts-sum((ct2.cuts*ct2.ratio))) AS [hours left],
CONVERT(varchar(11), ct.Date, 105) AS [date]
FROM
Cut.Card AS cd
INNER JOIN Cut.Cut AS ct
INNER JOIN Cut.Cut AS ct2
INNER JOIN Cut.Cut AS ct
INNER JOIN Cut.Cut AS ct2
ON ct.CardId = ct2.CardId AND ct.CutId >= ct2.CutId
ON cd.CardId = ct.CardId
where
…….
GROUP BY
cd.Cuts,
ct.CutId,
ct.Cuts,
ct.Ratio,
ct.Date
ORDER BY
cd.CardId
ct.CutId,
ct.Date
it works and calculates fine as long as I ‘order by cutId’, BUT I need to sort the output by dates and the resluts I get, are very confusing, because some ‘hours’ have been put in the database later like:
cutid date
717425-01-2008
717525-01-2008
717618-01-2008
736028-01-2008
Can I somehow help me to calculate the hours left on the card by date, not by cutid????
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table structure, sample data and expected results. Thanks

See - http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 2

use the following in the Order by clause instead of ct.date.

CONVERT(float, ct.Date, 105)

I hope it will help you.

Atif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

ih-815117 SSC Enthusiast Points: 107 More actions · Answer 3

Sorry, it was a bit confusing

The table structure looks like this:

CutId (PK, int, not null)

CardId(FK, int, not null)

Date(datetime, not null)

Cuts(numeric(6,2), not null)

Ratio(numeric(6,2), not null)

The result I get right now looks like:

717325-01-20080.501.50-33.7500

717425-01-20080.251.50-34.1250

717525-01-20080.501.50-34.8750

717618-01-20080.501.00-35.3750

736028-01-20080.251.00-35.6250

736128-01-20083.001.00-38.6250

Where total_hours – sum(hours*ratio) and ordered by cutid, it works fine but, I need the same results ordered by date. When I try to 'order by date' I get:

717618-01-20080.501.00-35.3750

714821-01-20080.501.50-3.5000

714921-01-20080.501.50-4.2500

715021-01-20082.001.00-6.2500

715121-01-20080.501.50-7.0000

715221-01-20080.501.00-7.5000

It calculates based on Cutid, what I need is to make a calculation based on date, meaning it should start to calculate hours left on the card starting from the ‘18-01-2008’

Something like this:

717618-01-20080.501.00-0,500

714821-01-20080.501.50-1,250

714921-01-20080.501.50-2,000

is it possible?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

If possible, please post the table definition as a CREATE Table and the sample data as insert statements. It really helps when trying to test a query to see if it works.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ih-815117 SSC Enthusiast Points: 107 More actions · Answer 5

Thanks, I solved the problem. It works fine with temporary tables:)