Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with programming code. 5

Status
Not open for further replies.
Jdbenike

Jdbenike

MIS
Sep 11, 2008
74
US
Need help with SQL programming in microsoft sql server managment studio.
 
Sort by date Sort by votes
The code we are working on together won't complete ever. I had it run for hours.
 
Upvote 0 Downvote
DECLARE @TempC TABLE
( HOMELABORLEVELNAME2 VARCHAR(50),
WORKEDSHIFTID INT,
HOMELABORLEVELNAME3 VARCHAR(50),
HOMELABORLEVELNAME4 VARCHAR(50),
PERSONFULLNAME VARCHAR(64),
PERSONNUM VARCHAR(15),
AGE VARCHAR(14),
EVENTDATE DATETIME,
PUNCH1 DATETIME,
PUNCH2 DATETIME,
MINORRULENM VARCHAR(50),
TIMEINSECONDS INT,
STARTREASON VARCHAR(50)
)

INSERT INTO @TempC
SELECT /* DISTINCT */ -- No distinct here as we will aggregate later
pa.HOMELABORLEVELNAME2,
tsia.WORKEDSHIFTID,
pa.HOMELABORLEVELNAME3,
pa.HOMELABORLEVELNAME4,
pa.PERSONFULLNAME,
tspa.PERSONNUM,
datediff(yy,pa.BIRTHDTM,getdate()) AS AGE,
DateAdd(dd, 0, DateDiff(dd, 0, tspa.EVENTDATE)) As EVENTDATE,
ISNULL(tspa.INPUNCHDTM, tspa.STARTDTM) AS PUNCH1,
ISNULL(tspa.OUTPUNCHDTM, tspa.ENDDTM) AS PUNCH2,
mra.MINORRULENM,
tspa.TIMEINSECONDS,
tspa.STARTREASON
FROM WFC_PRD.dbo.VP_TIMESHEETPUNCH tspa WITH (NOLOCK),
WFC_PRD.dbo.VP_PERSON pa WITH (NOLOCK),
WFC_PRD.dbo.PRSNMINORRULEMM pma WITH (NOLOCK),
WFC_PRD.dbo.MINORRULE mra WITH (NOLOCK),
WFC_PRD.dbo.TIMESHEETITEM tsia WITH (NOLOCK)
WHERE tspa.PERSONID = pa.PERSONID
AND tspa.TIMESHEETITEMID = tsia.TIMESHEETITEMID
AND datediff(yy,pa.BIRTHDTM,getdate()) < 18
AND tspa.PERSONID = pma.PERSONID
AND pma.MINORRULEID = mra.MINORRULEID
AND tspa.TMSHTITEMTYPEID not in (1, 2, 3)
AND tspa.LABORLEVELNAME4 <> ''
AND tspa.PAIDSW = 1
AND (tspa.EVENTDATE >= '2008-06-01 00:00:00.000'
AND tspa.EVENTDATE < '2008-06-02 00:00:00.000')
-- ORDER BY tspa.EVENTDATE , tsia.WORKEDSHIFTID ASC

SELECT HOMELABORLEVELNAME2,
a.WORKEDSHIFTID,
HOMELABORLEVELNAME3,
HOMELABORLEVELNAME4,
PERSONFULLNAME,
PERSONNUM,
AGE,
EVENTDATE,
/*
Select the row for the same workshift for same person (if workedshiftid doesn't hanle that, uncomment personnum statement.
If this is null, which it will be at end of shift, then use the punch out from this record.
Take the date difference in seconds and if greater than or equal to 1800 (30 mins) then sum 1 else 0.
*/
(Case
When DateDiff(second, PUNCH2, IsNull
((SELECT MIN(PUNCH1)
FROM @TempC b
WHERE b.WORKEDSHIFTID = a.WORKEDSHIFTID
AND b.EVENTDATE = a.EVENTDATE
AND b.PUNCH1 > a.PUNCH1 /* AND b.PERSONNUM = a.PERSONNUM*/) , PUNCH2)) >= 1800
Then 1
Else 0
End) As BreakInfraction,
(Case When Sum(
MINORRULENM,
SUM(TIMEINSECONDS) AS WorkTime,
TOTALTIME,
STARTREASON
FROM @TempC a,
(SELECT WORKEDSHIFTID, TOTALTIME=SUM(TIMEINSECONDS) FROM @TempC GROUP BY WORKEDSHIFTID) as ttlTime
WHERE a.WORKEDSHIFTID = ttlTime.WORKEDSHIFTID
and TotalTime > 21600
GROUP BY HOMELABORLEVELNAME2,
a.WORKEDSHIFTID,
HOMELABORLEVELNAME3,
HOMELABORLEVELNAME4,
PERSONFULLNAME,
PERSONNUM,
AGE,
EVENTDATE,
MINORRULENM,
STARTREASON,
PUNCH1,
PUNCH2,
TOTALTIME


This code works. The data comes back as in the picture. It brings back a '1' everytime an actual 30 minute infraction incurrs. Now from here can you help me finish? I need to filter out the shifts where the '1' happens. So, Can we do a sum of the value via workedshiftid, then not show if equal to 1? Or along those lines?

Thank you
 
 http://i36.tinypic.com/zwlzwn.jpg
Upvote 0 Downvote
This is very difficult for me. Please understand that it's not easy to understand your schema. What's worse is that you didn't even follow my advice. I suggested that you create a table variable with a RowId Int Identity(1,1) column, which makes the self join more efficient. Instead, you chose to use a subquery approach, which is more difficult to write and slower to execute.

Anyway... I'll try...

Code: 
DECLARE @TempC TABLE
 ( HOMELABORLEVELNAME2      VARCHAR(50),
   WORKEDSHIFTID            INT,
   HOMELABORLEVELNAME3      VARCHAR(50),
   HOMELABORLEVELNAME4      VARCHAR(50),
   PERSONFULLNAME           VARCHAR(64),
   PERSONNUM                VARCHAR(15),
   AGE                      VARCHAR(14),
   EVENTDATE                DATETIME,
   PUNCH1                   DATETIME,
   PUNCH2                   DATETIME,
   MINORRULENM              VARCHAR(50),
   TIMEINSECONDS            INT,
   STARTREASON              VARCHAR(50)
 )

INSERT INTO @TempC
SELECT /* DISTINCT */ -- No distinct here as we will aggregate later
            pa.HOMELABORLEVELNAME2,
            tsia.WORKEDSHIFTID,
            pa.HOMELABORLEVELNAME3,
            pa.HOMELABORLEVELNAME4,
            pa.PERSONFULLNAME,
            tspa.PERSONNUM, 
            datediff(yy,pa.BIRTHDTM,getdate()) AS AGE, 
            DateAdd(dd, 0, DateDiff(dd, 0, tspa.EVENTDATE)) As EVENTDATE, 
            ISNULL(tspa.INPUNCHDTM, tspa.STARTDTM) AS PUNCH1,
            ISNULL(tspa.OUTPUNCHDTM, tspa.ENDDTM) AS PUNCH2,
            mra.MINORRULENM,
            tspa.TIMEINSECONDS,
            tspa.STARTREASON
FROM WFC_PRD.dbo.VP_TIMESHEETPUNCH tspa WITH (NOLOCK),
     WFC_PRD.dbo.VP_PERSON pa WITH (NOLOCK),
     WFC_PRD.dbo.PRSNMINORRULEMM pma WITH (NOLOCK),
     WFC_PRD.dbo.MINORRULE mra WITH (NOLOCK),
     WFC_PRD.dbo.TIMESHEETITEM tsia WITH (NOLOCK)
WHERE  tspa.PERSONID = pa.PERSONID
AND    tspa.TIMESHEETITEMID = tsia.TIMESHEETITEMID
AND    datediff(yy,pa.BIRTHDTM,getdate()) < 18
AND    tspa.PERSONID = pma.PERSONID
AND    pma.MINORRULEID = mra.MINORRULEID
AND    tspa.TMSHTITEMTYPEID not in (1, 2, 3)
AND    tspa.LABORLEVELNAME4 <> '' 
AND    tspa.PAIDSW = 1 
AND    (tspa.EVENTDATE >= '2008-06-01 00:00:00.000'
AND    tspa.EVENTDATE <  '2008-06-02 00:00:00.000') 
-- ORDER BY tspa.EVENTDATE , tsia.WORKEDSHIFTID ASC

SELECT HOMELABORLEVELNAME2,
       a.WORKEDSHIFTID,
       HOMELABORLEVELNAME3,
       HOMELABORLEVELNAME4,
       PERSONFULLNAME,
       PERSONNUM, 
       AGE, 
       EVENTDATE, 
         /* 
            Select the row for the same workshift for same person (if workedshiftid doesn't hanle that, uncomment personnum statement.
            If this is null, which it will be at end of shift, then use the punch out from this record.
            Take the date difference in seconds and if greater than or equal to 1800 (30 mins) then sum 1 else 0.
            */
       (Case 
           When DateDiff(second, PUNCH2, IsNull
           ((SELECT MIN(PUNCH1) 
           FROM @TempC b 
           WHERE b.WORKEDSHIFTID = a.WORKEDSHIFTID 
           AND b.EVENTDATE = a.EVENTDATE 
           AND b.PUNCH1 > a.PUNCH1 /* AND b.PERSONNUM = a.PERSONNUM*/) , PUNCH2)) >= 1800 
           Then 1 
           Else 0 
           End) As BreakInfraction/*,
       (Case When Sum(    
       MINORRULENM,
       SUM(TIMEINSECONDS) AS WorkTime,
       TOTALTIME, 
       STARTREASON*/
FROM @TempC a
     Inner Join 
       (SELECT WORKEDSHIFTID, TOTALTIME=SUM(TIMEINSECONDS) FROM @TempC GROUP BY WORKEDSHIFTID) as ttlTime
       On a.WORKEDSHIFTID = ttlTime.WORKEDSHIFTID
     Left Join (
         Select A.WorkedShiftId
         From   @TempC As a
         Where  DateDiff(second, PUNCH2, IsNull
           ((SELECT MIN(PUNCH1) 
           FROM @TempC b 
           WHERE b.WORKEDSHIFTID = a.WORKEDSHIFTID 
           AND b.EVENTDATE = a.EVENTDATE 
           AND b.PUNCH1 > a.PUNCH1 /* AND b.PERSONNUM = a.PERSONNUM*/) , PUNCH2)) >= 1800
         ) As ShiftsWithBreakInfractions
         On a.WorkedShiftId = ShiftsWithBreakInfractions.WorkedShiftId
Where TotalTime > 21600
      And ShiftsWithBreakInfractions.WorkedShiftId Is NULL
GROUP BY HOMELABORLEVELNAME2,
       a.WORKEDSHIFTID,
       HOMELABORLEVELNAME3,
       HOMELABORLEVELNAME4,
       PERSONFULLNAME,
       PERSONNUM, 
       AGE, 
       EVENTDATE, 
       MINORRULENM,
       STARTREASON,
       PUNCH1,
       PUNCH2,
       TOTALTIME

This is pretty much a stab in the dark. Hope it helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Your stab in the dark was amazing, and it works completely great.

I was even throwing this bone to guys on experts xchange for the lasat 5 days and no one could do it.

You nailed it in one shot with new code you didnt help with.

I had to change it b/c the original way we approached it wasent going to work in reporting services for me.

Plus after one of my advisors recommended me to do some things different as well. Either way. If you live in wisconsin, or anyone near, Iam up for sending you some 12 pack or something as thank you. =P

Yet your probably more eastern or western.
 
Upvote 0 Downvote
I'm glad I was able to help.

I live near Philadelphia, and I rarely drink. I do appreciate the sentiment, though.

Looking at that last image you posted, it appears as though this query takes approximately 22 seconds to run (or at least it did before my last suggested modification). This is one of those cases where I feel confident that the query could be made MUCH faster. Unfortunately, the only way I would be able to help with that is if I could actually have access to the data. Certainly, this isn't possible.

I would like to make 2 suggestions that will probably improve performance a lot.

1. I see that you are using a table variable to store intermediate values, which is fine. The problem here is that you are storing a TON of information in the table variable. Way more than you actually need. For example, you are storing PERSONFULLNAME in the table variable. The table variable exists so that you can perform some calculations. There are no calculations involved using person name. If you remove that from the table variable, and then link to another table in the main query to get that information, your performance will probably improve. Basically, what I am saying is, remove extraneous information from the table variable, and then link back to another table later to get that information.

2. You probably know about indexes on tables. They exist to make queries faster, which is a good thing. Most people don't realize that you can create one index on table variables. You can't do this the same way that you would for real tables. However, you can put a primary key on a table variable, and since primary keys are implemented through a UNIQUE INDEX, you essentially get an index on the table variable. If you end up with a lot of data in the table variable, adding an index (primary key) will improve performance. The trick is.... primary keys must be unique, so you need to be a little careful in what you choose for your primary key. Something like this....

Code: 
DECLARE @TempC TABLE
 ( HOMELABORLEVELNAME2      VARCHAR(50),
   WORKEDSHIFTID            INT,
   HOMELABORLEVELNAME3      VARCHAR(50),
   HOMELABORLEVELNAME4      VARCHAR(50),
   PERSONFULLNAME           VARCHAR(64),
   PERSONNUM                VARCHAR(15),
   AGE                      VARCHAR(14),
   EVENTDATE                DATETIME,
   PUNCH1                   DATETIME,
   PUNCH2                   DATETIME,
   MINORRULENM              VARCHAR(50),
   TIMEINSECONDS            INT,
   STARTREASON              VARCHAR(50)
   [!]Primary Key (WORKEDSHIFTID, EVENTDATE, PUNCH1, PUNCH2)[/!]
 )

When you add a primary key to a table variable, it will take more time to populate it, but it will be faster to retrieve from it. Since the final query is using the table variable 3 times, you will probably notice an overall performance improvement. That improvement may be huge (or it may not). I encourage you to put the primary key line in to the query and then run it again. If you decide to do this, please post the execution time of the query before the addition and again after, because I'm curious to know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I kept the code as is.
With the 'TOTALTIME' field which calculates total seconds for a day. How can I convert that into hh:mm or hh:mm:ss
doesent matter. Basically i need to convert the total seconds into a hh:mm format

someone recommended
dbo.fn_Seconds_to_HoursMinutes(YourSecondsFieldName)
but it doesent work in sql server 2000



DECLARE @TempC TABLE
( HOMELABORLEVELNAME2 VARCHAR(50),
WORKEDSHIFTID INT,
HOMELABORLEVELNAME3 VARCHAR(50),
HOMELABORLEVELNAME4 VARCHAR(50),
PERSONFULLNAME VARCHAR(64),
PERSONNUM VARCHAR(15),
AGE VARCHAR(14),
EVENTDATE DATETIME,
PUNCH1 DATETIME,
PUNCH2 DATETIME,
MINORRULENM VARCHAR(50),
TIMEINSECONDS INT,
STARTREASON VARCHAR(50)
)

INSERT INTO @TempC
SELECT /* DISTINCT */ -- No distinct here as we will aggregate later
pa.HOMELABORLEVELNAME2,
tsia.WORKEDSHIFTID,
pa.HOMELABORLEVELNAME3,
pa.HOMELABORLEVELNAME4,
pa.PERSONFULLNAME,
tspa.PERSONNUM,
datediff(yy,pa.BIRTHDTM,getdate()) AS AGE,
DateAdd(dd, 0, DateDiff(dd, 0, tspa.EVENTDATE)) As EVENTDATE,
ISNULL(tspa.INPUNCHDTM, tspa.STARTDTM) AS PUNCH1,
ISNULL(tspa.OUTPUNCHDTM, tspa.ENDDTM) AS PUNCH2,
mra.MINORRULENM,
tspa.TIMEINSECONDS,
tspa.STARTREASON
FROM WFC_PRD.dbo.VP_TIMESHEETPUNCH tspa WITH (NOLOCK),
WFC_PRD.dbo.VP_PERSON pa WITH (NOLOCK),
WFC_PRD.dbo.PRSNMINORRULEMM pma WITH (NOLOCK),
WFC_PRD.dbo.MINORRULE mra WITH (NOLOCK),
WFC_PRD.dbo.TIMESHEETITEM tsia WITH (NOLOCK)
WHERE tspa.PERSONID = pa.PERSONID
AND tspa.TIMESHEETITEMID = tsia.TIMESHEETITEMID
AND datediff(yy,pa.BIRTHDTM,getdate()) < 18
AND tspa.PERSONID = pma.PERSONID
AND pma.MINORRULEID = mra.MINORRULEID
AND tspa.TMSHTITEMTYPEID not in (1, 2, 3)
AND tspa.LABORLEVELNAME4 <> ''
AND tspa.PAIDSW = 1
AND (tspa.EVENTDATE >= '2008-06-01 00:00:00.000'
AND tspa.EVENTDATE < '2008-06-10 00:00:00.000')
-- ORDER BY tspa.EVENTDATE , tsia.WORKEDSHIFTID ASC

SELECT HOMELABORLEVELNAME2,
a.WORKEDSHIFTID,
HOMELABORLEVELNAME3,
HOMELABORLEVELNAME4,
PERSONFULLNAME,
PERSONNUM,
PUNCH1,
PUNCH2,
AGE,
EVENTDATE,
/*
Select the row for the same workshift for same person (if workedshiftid doesn't hanle that, uncomment personnum statement.
If this is null, which it will be at end of shift, then use the punch out from this record.
Take the date difference in seconds and if greater than or equal to 1800 (30 mins) then sum 1 else 0.
*/
(Case
When DateDiff(second, PUNCH2, IsNull
((SELECT MIN(PUNCH1)
FROM @TempC b
WHERE b.WORKEDSHIFTID = a.WORKEDSHIFTID
AND b.EVENTDATE = a.EVENTDATE
AND b.PUNCH1 > a.PUNCH1 /* AND b.PERSONNUM = a.PERSONNUM*/) , PUNCH2)) >= 1800
Then 1
Else 0
End) As BreakInfraction,
MINORRULENM,
SUM(TIMEINSECONDS) AS WorkTime,
TOTALTIME,
STARTREASON
FROM @TempC a
Inner Join
(SELECT WORKEDSHIFTID, TOTALTIME=SUM(TIMEINSECONDS) FROM @TempC GROUP BY WORKEDSHIFTID) as ttlTime
On a.WORKEDSHIFTID = ttlTime.WORKEDSHIFTID
Left Join (
Select A.WorkedShiftId
From @TempC As a
Where DateDiff(second, PUNCH2, IsNull
((SELECT MIN(PUNCH1)
FROM @TempC b
WHERE b.WORKEDSHIFTID = a.WORKEDSHIFTID
AND b.EVENTDATE = a.EVENTDATE
AND b.PUNCH1 > a.PUNCH1 /* AND b.PERSONNUM = a.PERSONNUM*/) , PUNCH2)) >= 1800
) As ShiftsWithBreakInfractions
On a.WorkedShiftId = ShiftsWithBreakInfractions.WorkedShiftId
Where TotalTime > 21600
And ShiftsWithBreakInfractions.WorkedShiftId Is NULL
GROUP BY HOMELABORLEVELNAME2,
a.WORKEDSHIFTID,
HOMELABORLEVELNAME3,
HOMELABORLEVELNAME4,
PERSONFULLNAME,
PERSONNUM,
AGE,
EVENTDATE,
MINORRULENM,
STARTREASON,
PUNCH1,
PUNCH2,
TOTALTIME
ORDER BY a.WORKEDSHIFTID, PUNCH1

 
Upvote 0 Downvote
You can convert seconds elapsed to an Hour:Minute representation a couple different ways.

Code: 
Declare @Test Int
Set @Test = 62520

Select Convert(VarChar(5), DateAdd(Minute, @Test / 60, 0), 108)

Select Convert(VarChar(2), @Test / 3600) + ':' + Convert(VarChar(2), @Test / 60 % 60)

In the first example, you divide the number of seconds by 60 (to get minutes). You then add that number of minutes to Jan 1, 1900. Then, finally, convert to varchar with style = 108. Notice I use varchar(5) which effectively truncates the seconds.

In the second example, you divide by 3600 to get the number of hours and then divide by 60 and mod by 60 to get the number of minutes. There are probably 1/2 a dozen ways to get this. Each code operates differently if the number of seconds exceeds 1 day. There are 86400 seconds in a day. If the number of seconds exceeds 1 day, the first example will remove that information, where the seconds example will allow for it. Like this...

Code: 
Declare @Test Int
Set @Test = 95000

[green]-- Shows 02:23[/green]
Select Convert(VarChar(5), DateAdd(Minute, @Test / 60, 0), 108)

[green]-- Shows 26:23[/green]
Select Convert(VarChar(2), @Test / 3600) + ':' + Convert(VarChar(2), @Test / 60 % 60)

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Yes, Thank you.
Select Convert(VarChar(5), DateAdd(Minute, @Test / 60, 0), 108)

I just thru that in the report as

Select Convert(VarChar(5), DateAdd(Minute, TotalTime/ 60, 0), 108)

Thank You.
 
Upvote 0 Downvote
datediff(yy,pa.BIRTHDTM,getdate()) AS AGE
we were using as age, but it was rounding up the last month so I was missing those people when I ran the report

i changed it too
datediff(days,pa.BIRTHDTM,getdate()) / 365
yet then, it was rounding up again.. for example.. if someones birthday was 10/08/1990.. it would list them as 18 when they are technically still 17

is there another way to do this in my complex code to get a persons birthdate exactly right? down to the day itself
 
Upvote 0 Downvote
Try this...

[tt][blue](CONVERT(INT, CONVERT(VARCHAR(8), GetDate(), 112)) - CONVERT(INT, CONVERT(VARCHAR(8), pa.BIRTHDTM, 112))) /10000[/blue][/tt]

** Code borrowed from Vongrunt on a site I'm not allowed to mention. [sad]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
datediff(days,pa.BIRTHDTM,getdate()) / 365.242199

this works.

 
Upvote 0 Downvote
and that works 20 times better cause it takes away the numbers after the age. thanks
 
Upvote 0 Downvote
Iam trying to add code to my sql now so that let's say the report is ran every week on sunday at time 00:00:00.000 in reporting services(which is set up in reporting services), I want it to grab all the information up to seven days back.

So far I developled


and tspa.EVENTDATE >= case DATEPART(dw, GETDATE())
when 1 then cast(datepart(year, getdate()) as varchar) + '-' + cast(datepart(month, getdate()) as varchar) + '-' + cast(datepart(day, getdate()) as varchar) + ' 00:00:00.000'
when 2 then cast(datepart(year, getdate() - 1) as varchar) + '-' + cast(datepart(month, getdate() - 1) as varchar) + '-' + cast(datepart(day, getdate() - 1) as varchar) + ' 00:00:00.000'
when 3 then cast(datepart(year, getdate() - 2) as varchar) + '-' + cast(datepart(month, getdate() - 2) as varchar) + '-' + cast(datepart(day, getdate() - 2) as varchar) + ' 00:00:00.000'
when 4 then cast(datepart(year, getdate() - 3) as varchar) + '-' + cast(datepart(month, getdate() - 3) as varchar) + '-' + cast(datepart(day, getdate() - 3) as varchar) + ' 00:00:00.000'
when 5 then cast(datepart(year, getdate() - 4) as varchar) + '-' + cast(datepart(month, getdate() - 4) as varchar) + '-' + cast(datepart(day, getdate() - 4) as varchar) + ' 00:00:00.000'
when 6 then cast(datepart(year, getdate() - 5) as varchar) + '-' + cast(datepart(month, getdate() - 5) as varchar) + '-' + cast(datepart(day, getdate() - 5) as varchar) + ' 00:00:00.000'
when 7 then cast(datepart(year, getdate() - 6) as varchar) + '-' + cast(datepart(month, getdate() - 6) as varchar) + '-' + cast(datepart(day, getdate() - 6) as varchar) + ' 00:00:00.000'
when 8 then cast(datepart(year, getdate() - 7) as varchar) + '-' + cast(datepart(month, getdate() - 7) as varchar) + '-' + cast(datepart(day, getdate() - 7) as varchar) + ' 00:00:00.000'
end

Yet that code is only returning the last 2 days. Any suggestions?
 
Upvote 0 Downvote
I would handle this by creating a couple variables. One to represent the starting date (of the report) and another for the ending date.

For example:

Code: 
Declare @StartDate DateTime
Declare @EndDate DateTime

Set @EndDate = DateAdd(week, DateDiff(Week, 0, GetDate()), -1)
Set @StartDate = @EndDate - 7

[green]-- Select the results to check[/green]
Select @StartDate As StartDate, @EndDate As EndDate

(Your query here...)
and tspa.EVENTDATE >= @StartDate
And tspa.EVENTDATE < @EndDate

so... let me explain a couple things....

First:

[tt]
DateAdd(week, [blue]DateDiff(Week, 0, GetDate())[/blue], -1)
[/tt]

DateDiff returns an INTEGER (which is important). The first argument indicates the interval (in this case week). The ) represents Jan 1, 1900. So, DateDiff returns an integer number representing the number of weeks that have elapsed since Jan 1, 1900. For today, this number is 5675. Next, we add that number of weeks to Jan 1, 1900. Which returns Oct 6, 2008. Since this is Monday, and you want Sunday, you need to subtract 1 day.

Next, there is a shortcut when dealing with dates. If you want to add or subtract whole number of days, you can simply do that.

Anyway, the general idea is... calculate the end date (for the report). Then set the start date to be 7 days prior to that. And finally.... use those values to filter your data.

You may need to tweak the values a little to suit your purposes, but hopefully you get the idea.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
so throw this in the report?

Declare @StartDate DateTime
Declare @EndDate DateTime

Set @EndDate = DateAdd(week, DateDiff(Week, 0, GetDate()), -1)
Set @StartDate = @EndDate - 7

-- Select the results to check
Select @StartDate As StartDate, @EndDate As EndDate

where tspa.EVENTDATE >= case DATEPART(dw, GETDATE())
when 1 then cast(datepart(year, getdate()) as varchar) + '-' + cast(datepart(month, getdate()) as varchar) + '-' + cast(datepart(day, getdate()) as varchar) + ' 00:00:00.000'
when 2 then cast(datepart(year, getdate() - 1) as varchar) + '-' + cast(datepart(month, getdate() - 1) as varchar) + '-' + cast(datepart(day, getdate() - 1) as varchar) + ' 00:00:00.000'
when 3 then cast(datepart(year, getdate() - 2) as varchar) + '-' + cast(datepart(month, getdate() - 2) as varchar) + '-' + cast(datepart(day, getdate() - 2) as varchar) + ' 00:00:00.000'
when 4 then cast(datepart(year, getdate() - 3) as varchar) + '-' + cast(datepart(month, getdate() - 3) as varchar) + '-' + cast(datepart(day, getdate() - 3) as varchar) + ' 00:00:00.000'
when 5 then cast(datepart(year, getdate() - 4) as varchar) + '-' + cast(datepart(month, getdate() - 4) as varchar) + '-' + cast(datepart(day, getdate() - 4) as varchar) + ' 00:00:00.000'
when 6 then cast(datepart(year, getdate() - 5) as varchar) + '-' + cast(datepart(month, getdate() - 5) as varchar) + '-' + cast(datepart(day, getdate() - 5) as varchar) + ' 00:00:00.000'
when 7 then cast(datepart(year, getdate() - 6) as varchar) + '-' + cast(datepart(month, getdate() - 6) as varchar) + '-' + cast(datepart(day, getdate() - 6) as varchar) + ' 00:00:00.000'
when 8 then cast(datepart(year, getdate() - 7) as varchar) + '-' + cast(datepart(month, getdate() - 7) as varchar) + '-' + cast(datepart(day, getdate() - 7) as varchar) + ' 00:00:00.000'
end

and tspa.EVENTDATE >= @StartDate
And tspa.EVENTDATE < @EndDate

or when you said 'enter query here you meant the whole code'?
 
Upvote 0 Downvote
You showed the where clause (or perhaps just part of it). What I was suggesting is that you remove your part and replace it with mine.


Clear as mud?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Convert(VarChar(2), sum(timeinseconds) / 3600) + ':' + Convert(VarChar(2), sum(timeinseconds) / 60 % 60) as TotalTime,

with this code for time calculations..
is their a way to get two decimal places?

so right now.. I have some solutions come back as 25:4
and I need another 0 on that.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
190
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
483
Chris Miller
Chris Miller
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
592
Chris Miller
Chris Miller
G12Consult
  • Locked
  • Question
Help with some code
Replies
2
Views
105
SaltyTheFrog
SaltyTheFrog
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
345
gmmastros
gmmastros

Part and Inventory Search

Sponsor

Back
Top