?
Solved

Results split into weeks

Posted on 2005-04-28
7
Medium Priority
?
278 Views
Last Modified: 2010-03-19
I have a query below:

@startdate smalldatetime,
@enddate smalldatetime
AS
SELECT     CASE WHEN contract.contractid IN (2, 16) THEN 'YH Cable' END AS contract, COUNT(job.jobid) AS COUNT
FROM         job INNER JOIN
                      contract ON job.contractid = contract.contractid INNER JOIN
                      jobcat ON job.jobcatid = jobcat.jobcatid
WHERE     (RIGHT(job.estimate, 4) <> '0000') AND (jobcat.shortcode = 'G') AND (contract.contractid IN (2, 16)) AND (job.newsitesid IS NOT NULL) AND jon.received between @startdate and @enddate
 GROUP BY CASE WHEN contract.contractid IN (2, 16) THEN 'YH Cable' END

The result it brings back is

contract           count
YHCable           2000

I now need to calculate the result by week, parameters will drive the report shortly for dates. The report should read, from Moday to Monday as below, based on the parameters @startdate and @enddate.


               04 - Apr       11-Apr         18-Apr       25-Apr
YHCable      1000           400              200          400        

Can anybody help

P
0
Comment
Question by:polynominal
7 Comments
 
LVL 14

Expert Comment

by:Jan Franek
ID: 13884879
0
 
LVL 1

Expert Comment

by:pdrg
ID: 13885849
SQL Server 2000 does not support access-style 'crosstab queries', the best you could do would be to aggregate between weeks and get your results 'tall' as opposed to 'wide' without nasty coding and sloooow performance

SQL Server 2005 supports 'TRANSPOSE' which would then be useable to turn the tall data into wide data - dunno the syntax offhand though, and it's still a beta

hth
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13886027
First, you have to create a function that will return the weeks in ranges like this:

CREATE FUNCTION [dbo].[ufn_GetWeeks] ( @pWeekStart    DATETIME,
                                       @pWeekEnd      DATETIME )
RETURNS @vWeekDates TABLE ( WeekNumber    INT,
                            WeekStart     DATETIME,
                            WeekEnd       DATETIME )
AS
BEGIN

    DECLARE @vWeekNumber            INT
    SET @vWeekNumber = 1
    WHILE @pWeekStart < @pWeekEnd
    BEGIN
        INSERT INTO @vWeekDates ( WeekNumber, WeekStart, WeekEnd )
        VALUES (@vWeekNumber,
                DATEADD(D, -DATEPART(DW, CAST(CONVERT(VARCHAR(10), @pWeekStart, 101) AS DATETIME)) + 2,
                                 CAST(CONVERT(VARCHAR(10), @pWeekStart, 101) AS DATETIME)),
                DATEADD(D, -DATEPART(DW, CAST(CONVERT(VARCHAR(10), @pWeekStart, 101) AS DATETIME)) + 8,
                                 CAST(CONVERT(VARCHAR(10), @pWeekStart, 101) AS DATETIME)))

        SET @pWeekStart = DATEADD(DD, 7, @pWeekStart)
        SET @vWeekNumber = @vWeekNumber + 1
    END

    RETURN
END
GO

Then you use this function to join with your table:

SELECT     CASE WHEN contract.contractid IN (2, 16) THEN 'YH Cable' END AS contract,
           SUM(CASE WHEN Weeks.WeekNumber = 1 AND job.received BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week1],
           SUM(CASE WHEN Weeks.WeekNumber = 2 AND job.received BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week2],
           SUM(CASE WHEN Weeks.WeekNumber = 3 AND job.received BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week3],
           SUM(CASE WHEN Weeks.WeekNumber = 4 AND job.received BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week4],
           SUM(CASE WHEN Weeks.WeekNumber = 5 AND job.received BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week5]
FROM         job INNER JOIN
                      contract ON job.contractid = contract.contractid INNER JOIN
                      jobcat ON job.jobcatid = jobcat.jobcatid,
            [dbo].[ufn_GetWeeks] ( @startdate, @enddate ) AS Weeks
WHERE     (RIGHT(job.estimate, 4) <> '0000') AND (jobcat.shortcode = 'G') AND (contract.contractid IN (2, 16)) AND (job.newsitesid IS NOT NULL) AND jon.received between @startdate and @enddate
 GROUP BY CASE WHEN contract.contractid IN (2, 16) THEN 'YH Cable' END

It's going to be hard to make the column names to be the dates in just 1 statement.

Hope this helps.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:polynominal
ID: 13887827
rafrancisco, this seems to work to an extent, how would, I change week.weeknumber.1 to a date i.e. 18 April or whatever Mondays the range fell under. I will also have to start to add other queries to it such as the one below

SELECT     CASE WHEN contract.contractid IN (2, 16) THEN 'YH Cable' END AS contract, COUNT(job.jobid) AS Expr1
FROM         job INNER JOIN
                      contract ON job.contractid = contract.contractid INNER JOIN
                      jobcat ON job.jobcatid = jobcat.jobcatid INNER JOIN
                      complete ON job.jobid = complete.jobid
WHERE     (RIGHT(job.estimate, 4) <> '0000') AND (jobcat.shortcode = 'G') AND (contract.contractid IN (2, 16)) AND (job.newsitesid IS NOT NULL) AND
                      (job.newsitesid <> '') AND complete.engcomplete between @startdate and @enddate
GROUP BY CASE WHEN contract.contractid IN (2, 16) THEN 'YH Cable' END

This uses engcomplete as the dates

How would I do this

Thanks  P
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13888320
>> how would, I change week.weeknumber.1 to a date <<

I don't think this is possible with just a single SELECT statement.

For your new query, simply change the job.received to complete.engcomplete like this:

SELECT     CASE WHEN contract.contractid IN (2, 16) THEN 'YH Cable' END AS contract,
           SUM(CASE WHEN Weeks.WeekNumber = 1 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week1],
           SUM(CASE WHEN Weeks.WeekNumber = 2 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week2],
           SUM(CASE WHEN Weeks.WeekNumber = 3 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week3],
           SUM(CASE WHEN Weeks.WeekNumber = 4 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week4],
           SUM(CASE WHEN Weeks.WeekNumber = 5 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week5]
FROM         job INNER JOIN
                      contract ON job.contractid = contract.contractid INNER JOIN
                      jobcat ON job.jobcatid = jobcat.jobcatid,
            [dbo].[ufn_GetWeeks] ( @startdate, @enddate ) AS Weeks
WHERE     (RIGHT(job.estimate, 4) <> '0000') AND (jobcat.shortcode = 'G') AND (contract.contractid IN (2, 16)) AND (job.newsitesid IS NOT NULL) AND
                      (job.newsitesid <> '') AND complete.engcomplete between @startdate and @enddate
 GROUP BY CASE WHEN contract.contractid IN (2, 16) THEN 'YH Cable' END
0
 

Author Comment

by:polynominal
ID: 13893197
Hi I have a third query to add which uses SUM instead of count

SELECT     contract.contractid, SUM(workitem.quantity) AS Expr1
FROM         job INNER JOIN
                      jobcat ON job.jobcatid = jobcat.jobcatid INNER JOIN
                      contract ON job.contractid = contract.contractid INNER JOIN
                      workitem ON job.jobid = workitem.jobid INNER JOIN
                      template ON workitem.templateid = template.templateid
WHERE     (RIGHT(job.estimate, 4) <> '0000') AND (jobcat.shortcode = 'G') AND (contract.contractid IN (2, 16)) AND (job.newsitesid IS NOT NULL) AND (job.newsitesid <> '') and job.received is between @startdate and @enddate AND (workitem.accepted = 1) AND
                      (template.template IN ('N69', 'N65', 'N64', 'N307', 'N308'))
GROUP BY contract.contracted

How would sum fit into this equation

Many Thanks for your help so far, much appreciated
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13894449
You simply change the 1 in the THEN statement to your column, like this:

SELECT     contract.contractid,
SUM(CASE WHEN Weeks.WeekNumber = 1 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN workitem.quantity ELSE 0 END) AS [Week1],
           SUM(CASE WHEN Weeks.WeekNumber = 2 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN workitem.quantity ELSE 0 END) AS [Week2],
           SUM(CASE WHEN Weeks.WeekNumber = 3 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN workitem.quantity ELSE 0 END) AS [Week3],
           SUM(CASE WHEN Weeks.WeekNumber = 4 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN workitem.quantity ELSE 0 END) AS [Week4],
           SUM(CASE WHEN Weeks.WeekNumber = 5 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN workitem.quantity ELSE 0 END) AS [Week5]
FROM         job INNER JOIN
                      jobcat ON job.jobcatid = jobcat.jobcatid INNER JOIN
                      contract ON job.contractid = contract.contractid INNER JOIN
                      workitem ON job.jobid = workitem.jobid INNER JOIN
                      template ON workitem.templateid = template.templateid
WHERE     (RIGHT(job.estimate, 4) <> '0000') AND (jobcat.shortcode = 'G') AND (contract.contractid IN (2, 16)) AND (job.newsitesid IS NOT NULL) AND (job.newsitesid <> '') and job.received is between @startdate and @enddate AND (workitem.accepted = 1) AND
                      (template.template IN ('N69', 'N65', 'N64', 'N307', 'N308'))
GROUP BY contract.contracted
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question